perfstat/perfstat, OScanner reveals them all

Unfortunately, creating database accounts like “create user test identified by test” is common practice. Leaving default accounts with default passwords open is as common too. OScanner reveals those weak points mercilessly.

Oracle Scanner 1.0.6 by patrik@cqure.net
————————————————–
[-] Checking host 120.23.45.104
[-] Checking sid (t432) for common passwords
[x] Got IO Exception
[-] Checking sid (t433) for common passwords
[-] Account DBSNMP/DBSNMP found
[-] Enumerating system accounts for SID (t433)
[-] Succesfully enumerated 22 accounts
[-] Account MDSYS/MDSYS is locked
[-] Account ORDPLUGINS/ORDPLUGINS is locked
[-] Account ORDSYS/ORDSYS is locked
[-] Account OUTLN/OUTLN found
[-] Account PERFSTAT/PERFSTAT found
[-] Checking sid (t434) for common passwords
[-] Account DBSNMP/DBSNMP found
[-] Enumerating system accounts for SID (t434)
[-] Succesfully enumerated 122 accounts
[-] Account MDSYS/MDSYS is locked
[-] Account ORDPLUGINS/ORDPLUGINS is locked
[-] Account ORDSYS/ORDSYS is locked
[-] Account OUTLN/OUTLN found
[-] Account PERFSTAT/PERFSTAT found
…
(This output is not from a real database, its faked

OScanner can be downloaded here. Installation is easy: unzip it. Since it is written in java it can be run on a variety of platforms. Using OScanner is as easy as installing it:

C:\Programme\oscanner> scanner.bat -s 192.168.2.3
Oracle Scanner 1.0.6 by patrik@cqure.net
————————————————–
[-] Checking host 192.168.2.3
…

OScanner saves its reports in xml files. The xml files can be displayed as a tree by OScanner Report Viewer:

C:\Programme\oscanner> reportviewer.exe oscanner_192_168_2_3_report.xml

I don’t like the viewer. I use OScanner like this:

C:\Programme\oscanner> scanner.bat -s 192.168.2.3 > out.txt

Oscanner is a great little tool, but documentation is poor, actually there is no documentation at all.

Oracle Remote Diagnostic Agent (RDA)

Oracle Remote Diagnostic Agent is a great tool to collect detailed information about an oracle database and its environment. It’s not only a great tool for troubleshooting but also very helpful for documenting an Oracle environment, for example after installation, production start, etc.

Installing and configuring RDA takes a few easy steps. Installation and configuration is described in Metalink note 314422.1. RDA can be downloaded for Windows and different flavours of Unix from Metalink.

Have a look at the output of Remote Diagnostic Agent: RDA_Report.zip

List of Databases

Today is one of the first days at my new employer Abraxas. Everything is quite new to me. Servers and databases are not yet familiar. I must get an overview. At the previous site I was tracking all servers and databases in an Excel sheet. This list of databases proved helpful in many situations. Management wants a list of all Oracle installations for whatever reason? Just give them the Excel sheet without all the technical information which they don’t need.

List of Databases

Materialized View Replication

This post is for my friend Gerry, who - as a newby to Oracle - has to support a replicated environment. Gerry is my successor at Kanton Luzern. Management didn’t look for a new DBA because they said it’s hopeless to find somebody. Instead they decided to give somebody inhouse the opportunity. Gerry is highly motivated to learn Oracle. He took the chance.

Kanton Luzern uses LiveLink for web content management. LiveLink websites are stored in an Oracle database. Kanton Luzern hosts 144 websites.
There is a staging server where content is being added/edited and a live server where websites are accessed from the internet. New content is replicated from stage to live.

Whenever a content manager wishes to publish new or edited content to the live webserver he may trigger replication just by clicking on a button inside the content management tool. This is not a standard feature of LiveLink Content Manager. I wrote an extension for Content Manager, and a stored procedure to control replication.

Materialized View Replication has been introduced with Oracle 8i. Back then it was called Snapshot Replication. For a quick overview of materialized views read Duncan Davies’ post.
To set up materialized view replication is rather simple. But it takes a few steps.

hpfuchs@hplnbk:~> sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jan 13 10:18:18 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect hpfuchs/*****
Connected.

SQL> CREATE TABLE tab1 AS SELECT * FROM dict;

Table created.

Step 1 - Materialized View Log at Master Site
All changes in a base table are written to its materialized view log.

SQL> CREATE MATERIALIZED VIEW LOG ON tab1;
create materialized view log on tab1
*
ERROR at line 1:
ORA-12014: table ‘TAB1′ does not contain a primary key constraint

Ups! Materialized view log can’t be created on tables with no primary key. Let’s create primary key for tab1.

SQL> desc tab1;
Name Null? Type
—————————————– ——– —————————-
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)

SQL> ALTER TABLE tab1 ADD CONSTRAINT pk_tab1 PRIMARY KEY (table_name);

Table altered.

SQL> CREATE MATERIALIZED VIEW LOG ON tab1;

Materialized view log created.

Step 2 - Set up Materialized View Site
Most likely the materialized view site is another Oracle database. I have only one database on my notebook. Therefore I just create another user who is the owner of the replication site.

SQL> connect system/*****
Connected.
SQL> CREATE USER otherdb IDENTIFIED BY ***** DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

SQL> GRANT connect, resource, create materialized view, create database link TO otherdb;

Grant succeeded.

Step 3 - Database Link
To access objects in another database you need a database link between the two databases. Because I have only one database on my notebook, I will create a loopback database link. Omit the @{connect identifier} - in my case: @orcl - to create a real database link.

SQL> connect otherdb/*****
Connected.

SQL> CREATE DATABASE LINK orcl.kt.lunet.ch@orcl CONNECT TO hpfuchs IDENTIFIED BY ***** USING ‘orcl’;

Database link created.

Step 4 - Materialized View

SQL> CREATE MATERIALIZED VIEW mv1 REFRESH FAST AS SELECT * FROM tab1@orcl.kt.lunet.ch@orcl;

Materialized view created.

Materialized views can be indexed.

SQL> CREATE INDEX ix_mv1_objecttype ON mv1 (table_name);

Index created.

Step 5 - Refresh Group
Materialized views can be grouped together into refresh groups. Refresh groups are replicated as a whole. My replication interval is set to one hour. Materialized view replication uses DBMS_JOBS to schedule its intervals. To switch off all scheduled replication activity set JOB_QUEUE_PROCESSES to 0 at the replication site. Make sure all non-replication jobs use DBMS_SCHEDULER. You better check V$JOBS before changing that parameter to 0…

SQL> BEGIN
dbms_refresh.make(
name => ‘mv1_refgroup’,
list => ‘MV1′,
next_date => sysdate,
interval => ’sysdate + 1′,
implicit_destroy => true,
lax => true);
END;
/

PL/SQL procedure successfully completed.

Step 6 - Refresh At Will
The following command refreshes group “mv1_refgroup”.

SQL> exec dbms_refresh.refresh(name => ‘mv1_refgroup’);

PL/SQL procedure successfully completed.

People Say Database When They Mean Schema

On my current job I manage 111 databases on 28 servers. Some are mid size and clustered but most of them are quite tiny. My predecessor created databases for every customer and application, and test databases for all production databases. That means a good deal of those 111 databases aren’t production.

The tiniest of my databases stores only 17 MB of user data. For what waste more than 300 MB of memory for these 17 MB of actual user data? It seems that customers quite often order databases when they actually mean schemas. In most cases many small applications of a customer can be grouped together in one database. And when different departments or even customers use the same application it could be grouped together in one database too.

My goal is to have as few databases as necessary on as few servers as necessary. It’s somehow better to have one mid size database on high availlability hardware than 20 tiny tiny databases on three old standalone servers. It’s easier to manage and less expensive.

I Have a New Job

The last six years I was working as an Oracle & SQL Server DBA for the public administration of canton Lucerne in the heart of Switzerland. Besides the DBA role I was also maintaining a few database centric .NET applications. The public administration of canton Luzern committed to Microsoft products with an enterprise agreement. That means no more Oracle databases in the long term. I was offered to do Microsoft MOSS (Share Point) instead. But somehow I feel at home with Oracle products more than with Microsoft products. That’s why I decided not to take the chance and go for MOSS but started looking for a new job.

In my old job I was working with a wide range of products. Too wide to delve deeply into all of them. I want to narrow this range and focus on something. In the past I already put most of my learning efforts into Oracle technology. And I want to focus even more on Oracle. It’s what I like most and do best. Preferably my new job shouldn’t be in-house IT but at a managed services provider. The company I was looking for should not be a pure managed services provider but also do software development. Perhaps I could use my developer skills too at some point.

I didn’t have to look far. These days it’s easy to find a new job because there are lots of opportunities for database administrators. Starting on February 1st I will work as an Oracle DBA for Abraxas Informatik AG in Zurich. Abraxas’ activities is divided into three areas:

• Business Process Integration
• Software Development
• Managed Services

I count the days…

Static Data Dictionary Views

Static Data Dictionary Views and Dynamic Performance Views are the core of database administration. They are of crucial importance. Whoever wants to learn Oracle database administration has to get familiar with these views. I began to compile a list of most important Static Data Dictionary Views here. There are hundreds more but those in my list you will most likely use in your daily life as a DBA. Some content is still missing in my list. Perhaps I can add it over holidays.

Of course, GUI tools like Enterprise Manager or SQL Developer offer the same information in a more user friendly manner. But all those GUI tools rely on static dictionary views and dynamic performance views. As a DBA you will likely get into situations where there is no GUI tools available. At times you will be alone with your SQL*Plus skills. Your mastery of data dictionary views will make all the difference!

How to Delete Windows Service?

On my Windows 2003 test server I install and remove a lot of Oracle and SQL Server software. I create and drop databases a lot. Usually I work in a rush on that server, so removing instances and software isn’t always so clean. Sometimes a windows services sticks and cannot be removed. It has to be cleaned up manually. The easiest way to remove windows services is deleting their entries in the registry. All windows services are listed under

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

Once you delete an entry its service is removed and will not show up in the services panel anymore after a system reboot.

Oracle 11g Available for Download

Oracle 11g linux x86 is now available for download. Product documentation can be found here. And here’s an overview of 11g new features.

Is the Market Ready for SQL Server 2008?

Today I downloaded and installed the latest SQL Server 2008 Community Technical Preview from connect.microsoft.com. Downloading the preview requires you to sign up for Microsoft Connect which is free of charge. SQL Server 2008 is scheduled for release by early 2008.

We run 26 databases on a SQL Server 2000 machine for customers from every corner of the public administration. Out of those customers only one has already asked if we are going to migrate to SQL Server 2005. Most applications on that server would run as well on SQL Server 2005. Many software companies have not yet finished implementing SQL Server 2005 features into their applications. And many IT professionals are in the middle of developing their SQL Server 2005 skills. I think the market is not ready for SQL Server 2008 at the beginning of next year.

Oracle Database 11g Launch - Live Webcast

Tomorrow July 11 at 04:00 AM CET or 10:00 AM ET Oracle will launch its 11g Database. The launch event will be webcastet live here.

APEX on Port 80

The Oracle HTTP Server used by APEX defaults to port 7777. If this port is otherwise used, OHS defaults to 7778, and so on. Now perhaps you don’t want to run your Application on a cryptic URL like http://apex-s10bas.youdomain.com:7777/pls/apex, but rather on www.apex.yourdomain.com/pls/apex. On this matter the Oracle HTTP Server or APEX Documentation will not help you if you are not familiar with the configuration of Apache Server.

But actually it’s quite easy to move APEX to port 80. You only have to alter the file httpd.conf in directory D:\oracle\orahttp101\Apache\Apache\conf:

#before
#Port 7777
#Listen 7777

#after
Port 80
Listen 7777
Listen 80

From now on Oracle HTTP Server listens on Port 80. To change the URL you have to go to httpd.conf again and alter the server name. You need to have a DNS entry for the server name:
ServerName www.apex.yourdomain.com
Probably Oracle HTTP Server now doesn’t run anymore after you restart it. This is because port 80 is already used by something else. Perhaps there is another webserver listening on port 80? IIS? Or is it the Oracle Enterprise Manager Agent? Indeed! If you install Oracle HTTP Server on a dedicated server EMAgent will be installed as well and runs on port 80. But it’s easy to change this after the installation. Look for emd.properties in directory D:\oracle\orahttp101\sysman\config and change
EMD_URL=http://APEX-S10BAS.yourdomain.com:/emd/main/
into something like this:
EMD_URL=http://APEX-S10BAS.yourdomain.com:7778/emd/main/

Reboot your server, if EM Agent (not DB console) is still listening on port 80.

PeteFinnigan.com

“If you use Oracle database software and you value the data held in the databases you have deployed then this is a site that you need to visit and digest.”

That’s true indeed. PeteFinnigan.com is a top site on Oracle security. It has security tools, a large collection of whitepapers, a forum, and of course Pete Finnigan’s Oracle Security Weblog. I visit PeteFinnigan.com almost daily.

Testing a Connection from Client to SQL Server

A connection from client to SQL Server can easily be checked with the help of a .udl file. This is also helpful for building a connection string.

Step 1 - Create a .udl file
Create a text file and name it for example test.udl
Double click on test.udl to open the connection dialog.

Step 2 - Choose the type of connection
Go to the first tab “Provider”

Choose “Microsoft OLE DB Provider for SQL Server” or any other provider according to your needs.

Step 3 - Set up connection properties
Go to the second tab “Connection”, choose the server, enter username/password and choose a database from the drop down list.

Click on “Test connection” to connect to verify your connection properties. On the next two tabs you can set advanced properties.

Step 4 - Connection String
Once you click on “OK” a connection string based on your connection settings is written to test.udl. After you open test.udl you will find a connection string like this.

[oledb]; Everything after this line is an OLE DB initstringProvider=SQLOLEDB.1;Password=zueh%32g;Persist Security Info=True;User ID=goktak;Initial Catalog=msdb;Data Source=SQL-P239MON

"Failure would be heaped upon failure, disaster upon disaster"

Over on Dizwell Informatics you’ll read a great post about a real life restore/recovery experience and lessons learned.

“I’ve gone on at some length on this matter partly because if you had told me that failure would be heaped upon failure, disaster upon disaster, rendering all recovery plans null and void, I would never have believed you. One thing after another went wrong that weekend, closing off one recovery avenue after another, and whilst I had literally just posted here about risk minimisation, ‘15 links in a chain’ and ‘the chain is only as strong as its weakest link’; and whilst I told management that I had no confidence in our current recovery capabilities; I confess to nevertheless having had a sneaking belief that it would probably be alright on the night. I honestly wasn’t expecting all 15 links to fail at the same time -but that’s exactly what they did.”

Read the whole post over on Dizwell Informatics…

11g Mentioned in Oracle Apex Documentation

Oracle Database 11g is already mentioned on many pages of the Oracle Application Express (Apex) 3.0 Documentation which was released on March 16, 2007. This is because Oracle Database 11g seems to have HTTP server and PL/SQL gateway built into the DBMS itself.

Oracle Application Express (Apex) Documentation Library

Oracle PL/SQL Developer Certified Associate

Yesterday I passed the last exam for my “Oracle PL/SQL Developer Certified Associate” certificate. There are two exams:

1. 1Z0-007 Introduction to Oracle9i: SQL
2. 1Z0-147 Program with PL/SQL

Introduction to SQL can be taken online. I found the examination surprisingly difficult. There were a few rather strange questions - and lots uf stuff I didn’t prepare because I thought I had almost 10 years of experience in SQL programming and didn’t need much preparation. Programm with PL/SQL can only be taken at Prometric testing centers. I prepared myself well for this examination. This might be the reason why I found the PL/SQL examination a lot easier than the SQL examination. Have a look into:

1. Dependencies Management (be aware of ideptree as well, but don’t bother about dbms_utility
2. Calling Procedures and Functions from SQL*Plus and from within PL/SQL blocks
3. Package Management
4. Trigger Types, Trigger Timing, Trigger Events, and Triggers, Triggers, Triggers…

And with a little bit of experience you probably won’t fail the examination. I wonder why they focus so much on triggers. There were more than 10 questions about triggers, but only a single question about exception handling.
And there were strange questions too. Imagine there are three objects, a procedure (x) that selects from a view (y) which is based on a table (z). X, y and z are dependent or referenced objects. Now there was a list of about five or six statements like “x is an indirect dependent of y”. You had to pick two right answers. But believe me, there was only one right answer, not two

The given time for those examinations is short. 66 questions in 90 minutes. That’s less than 90 seconds a question. You have to be fast, you can’t waste time. Here are a few advices for passing those Oracle examinations:

• Read the question carefully
• “Examine this code”. Don’t examine the code but read the question first. You look at the code only after you have read the question, because only then you know what you are looking for.
• If you are not quite sure about the answer to a question, mark it and come back to it at the end.
• Use the pvc sheet and marker pen they give to you. For example if you have to pick two right answers out of five, write A B C D E on the sheet. Then cross all answers wich are wrong for sure. After that focus only on the remaining possible answers.
• Again, if there are questions for example about dependencies (or anything like that) you better use pen/sheet and draft a diagram.

Hardening Oracle Servers

On Tonguç Yilmaz Oracle Blog I clicked on a link to “Project Lockdown - A phased approach to securing your database infrastructure” recently. This is an exceptional article about hardening Oracle servers, written by Arup Nanda and published on OTN.

Off Topic - Monty Python on Youtube

http://www.youtube.com/watch?v=mkj4Wm6INFY
http://www.youtube.com/watch?v=IqhlQfXUk7w
http://www.youtube.com/watch?v=2H6DSoqZz_s

:-))

Killing Oracle Sessions of a User

We are hosting around a hundred websites for the public administration on a web content management system which is built on top of an Oracle database. There is a stage and a live environment with snapshot replication. Sometimes websites on the live environment have to be dropped and recreated. One website is represented by one Oracle user, so in fact the user has to be dropped. This is not possible as long as the webserver holds open sessions.
The following procedure”kill_sessions” terminates all sessions which are connected with a certain username.

CREATE OR REPLACE PACKAGE livelink IS
PROCEDURE kill_sessions (in_username IN VARCHAR2);
PROCEDURE drop_live_site (in_username IN VARCHAR2);
PROCEDURE create_live_site (in_username IN VARCHAR2,
in_url IN VARCHAR2,
in_pl IN VARCHAR2,
in_std IN NUMBER);
PROCEDURE start_repl (in_username IN VARCHAR2);
END livelink;
/

CREATE OR REPLACE PACKAGE BODY livelink IS
PROCEDURE kill_sessions (in_username IN VARCHAR2)
IS
CURSOR sessions_cur (uname VARCHAR2) IS
SELECT TO_CHAR(sid) AS sid,
TO_CHAR(serial#) AS serial#
FROM v$session
WHERE username = uname;
BEGIN
FOR sessions_rec IN sessions_cur(UPPER(in_username))
LOOP
EXECUTE IMMEDIATE ‘ALTER SYSTEM KILL SESSION ”’
|| sessions_rec.sid
|| ‘, ‘ sessions_rec.serial#
|| ””;
END LOOP;
END;
– … some more code here …
END livelink;
/

Creating an ERD of an Existing Oracle Database With DB Designer 4

Today I was asked to prepare an ERD of an existing Oracle Database for a meeting. Forces DB Designer 4 was the tool of my choice. Although it’s written for MySQL (and there weren’t any bugfixes or releases since long time), some features work fine with Oracle too.
To get an ERD do the following.

“Reverse Engineering”
Open DB Designer 4, then open an empty canvas with “File -> New”. Choose the menu “Database -> Reverse Engineering”.

A DB Connection must be set up. Here it is wise to connect with the system user to avoid a bunch of error messages during the reverse engineering process.

Connect to the database. A new dialog opens. Here you can choose schema or tables which you want to include in the ERD. Everything other can be left to default values.

And here is the ERD

Saving the ERD as XML or PNG
This is not really possible with hundreds or even thousands of tables. Often DB Designer pops up a firework of error messages and crashes at the end.

Printing the ERD
Printing works fine, even with large ERDs. If you can print PDFs it is also an easy way to share ERDs with others. To Print out an ERD choose “File -> Page & Printer Setup”.

And at the end all those pages have to be glued together

Oracle Magazine March/April Issue

The march/april issue of Oracle Magazine is here. The online edition can be found here. Free subscriptions to the print edition can be done here.

New Book on RMAN Backup & Recovery

After almost two weeks of waiting the new RMAN Backup & Recovery book published by Oracle Press arrived this morning

Recently Lutz Hartmann posted the table of contents on his blog. It must be a good read. My to-do-list is almost empty and with all projects I am well ahead of time -> There is lots of time for reading at the moment I’m going to post a small review when I’m done.

PsExec - Run Programm on Remote Computer (Windows)

On 1st of April there will be elections in Lucerne. We are responsible for the application as well as for server infrastructure and redundancy of the whole solution. It’s a very important project for the public administration of Lucerne (which I work for). One important part of the project is to publish provisional election results on the internet during election day. Result pages are generated using SQL Server Reporting Services (SSRS). Whenever somebody adds results, reports have to be rendered on whichever reportserver is available. The rendering process on the report server has to be started from the Oracle database server. A script on server B has to be started from server A.

With PsExec a programm on server B can easily be started from server A. Here is a sample:

C:> psexec \\serverb -u domain\username -p password c:\programme\gugus\helloworld.exe

If you login on server A and server B with the same domain account, you can omit -u and -p. PsExec can be downloaded here.

Reporting Services 2005 - Using Parameters from SQL Query

Parameters can be passed to Reporting Services 2005 to be used in a WHERE clause. Values can also be selected from a dropdown list in Report Viewer.

How can this list of parameters be queried from a database? In our example a report of database server nodes has to show nodes filtered by customers. In the dropdown list only customers who have nodes should be listed.

Step 1 - Add parameter to the first dataset (Nodes)
Use @customer for SQL Server and :customer for Oracle.

As parameter value add =Parameters!customer.Value in the parameters tab.

Step 2 - Set up a second dataset (NodesCustomer) for the dropdownlist

Enter a SQL statement to come up with customers who own nodes. Leave everything else to the default.

Step 3 - Set parameter properties
Go to “Report -> Report Parameters”

 

Set properties to “from query”, choose the second dataset (NodesCustomer) and customer as the value field.

Filofax or Palm?

It’s the same old question again and again. Filofax or Palm? Actually there are lots of possible combinations, here a few in brief:

Palm and Cellphone
Palm for PIM (Personal Information Management) and a cellphone for internetaccess. This was my solution for years. It’s great for time management and answering emails on the go. But text entry with Graffiti isn’t convenient. Therefore I had a foldable keyboard in my bag. That makes three devices then. And for sketches and anything written in Arabic I also had pen and paper with me, plus textmarkers and postit bookmarks which I need while reading books. You’ll certainly need a backpack for all that stuff…

Smartphone
I gave Palm Treo a try and used it for about a year. It was great for email, keeping contacts and scheduling appointments. With the small but comfortable keyboard notetaking was easy. Textnotes I mean; for these kinds of notes I was still carying pen and paper around. The camera was crap, the mp3 player as well. After a year of using this Smartphone the phone part started to make strange noises during phonecalls and it became more and more difficult to follow a conversation. Warranty of course lasts only one year…

Filofax and Cellphone
I like reading in cafés. While reading I frequently take notes and draw sketches. A Palm is no good for this kind of note taking. A Filofax on the other hand is ready for anything. And I don’t want to answer anymore emails on the go.

Here is what I do:

• Cellphone: Contacts, Camera, MP3, Phone, SMS
• Filofax: Time management (Goals, Tasks, Appointments … ), Note taking

Oracle Magazine

This is the january/february issue of Oracle Magazine. All articles of the current issue can be found here.

Oracle Magazine is published bimonthly and is free of charge. Subscribe here.

Off Topic - Sony Ericsson K800i

Recently I prolonged my mobile phone contract with Orange and got a Sony Ericsson K800i Cybershot cameraphone.

It features a 3.2 mega pixels digital camera with real flashlight. The results it produces are not bad at all for a cameraphone, see here what’s possible if a good photographer uses the K800i. This new cameraphone is with me all the time and I use it often to take pictures of anything.

I have a camera but I lack the skills. This website (german) is helpful.

Oracle PodCasts

Today I was strolling around on the OTN website and found those Oracle TechCasts.

Keeping Up with Reading Blogs, Websites and Mailinglists

I frequently read blogs, websites, forums and mailinglists. It takes me quite a bit of time to keep up with filtering and reading, about an hour a day. Most of this one hour is time which is lost anyway, for example during boring meetings, at the busstop etc.
It would be time consuming to check all websites daily if there is new content. RSS readers do that very well. Desktop RSS readers have been around for a few years. Recently I descovered Google Reader.

Google Reader has the advantage that it doesn’t matter from where I access it. I can tag interresting posts to display a link to them in a ticker on my blog. If I have no time to read the whole post or article when I check Google Reader, I can (only on Windows) transfer the article with one mouse click to my Palm.

The oracle-l mailinglist is anyway going directly to my Palm over GPRS. With the Orange World Option I can send and receive email unlimited. I use SnapperMail on my Palm.

Learning the vi Editor

I know basic commands of vi, and would like to learn advanced editing. I found Learning the vi Editor to be the best book on the subject.

Most IT books outdate rather quickly. The last edition of this one is from 1998, and it still is very useful. Table of contents can be found here, sample chapter here, the book can be bought here.

When I brought home this new book yesterday and began to read a few pages, my son (one and half years old), wanted that book to play with.

When I tried to take it back he cried. Only after more than half an hour later I was able to take it again. But that time, the book looked as if ten people already studied it…

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

The book Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions, written by Tom Kyte, is an excellent introduction to Oracle architecture. It is not only a great book for developers, but for DBAs as well. Many a DBA might find a thing or two new to him. Tom Kyte doesn’t only walk trough Oracle features, but gives guidelines when to use them and - especially important - when not to use them.

Tom Kyte writes in a style which makes this book a page breaker, and everything is proven by examples. Table of contents can be found here, sample chapter here.

The book can be bought as a paperback for $49, or - like most APRESS books - as a PDF version for $25. I bought the PDF version. It has three advantages:

1. It is half price
2. It is searchable
3. I can print a couple of pages and read them for example while shopping with family, no need to carry around heavy books.

I wish I had a similar book when I started using Oracle Database as a developer in 1999.

OCP Certification - What for?

Some time ago I received my OCP certification. It doesn’t mean I am an expert now, it probably also doesn’t mean I have better chances on the job market, there are more important criterias to that.

What I gained through OCP certification and preparation for it:

Familiarity with documentation and other books
Every morning before going to office I went to a cafeteria for almost an hour of reading documentation/books. I kept this habit also after certification, although not constantly every day. I have the whole 10g documentation set on my Palm.

Confidence with Backup/Recovery
Since I startet OCP preparation I spend every thursday morning with systematic backup/recovery practicing. I will keep this habit.

I know what I don’t know
The preparation for OCP gave me a broad overview over features I never used so far. I know what I don’t know, there is many things I don’t know, which I still can learn. It would be boring to stick to what I already know and just do my job. I like to go to office in the morning, and I never come home in the evening without something I’ve learned during the day.

Trying, Experimenting
A System Administrator asked me during preparation for OCP, what’s going on on your Testserver? There was a lot of activity. Because what’s the point in learning Oracle knowledge without trying it out and play with the things I learn about? For trying out things, I have a testserver which is connected to TSM TDPO, and on my PC there is two VMs (Linux/Windows).

Conclusion:
OCP is a very good point of start for people with some on the job experience, who want to learn more about Oracle.

My employer supports my efforts to improve my DBA skills. Oracle University Classes and Exams are paid. And, most important, he takes care of bills from book stores

DB Designer 4

DB Designer 4, an open source DB design tool developed by fabFORCE.net, compares according to their website with tools like Erwin, Rational Rose and Oracle Designer.

For MySQL databases this is true. In conjunction with Oracle, DB Designer 4 still has some shortcomings. For example it is not possible to select “Oracle” as the default database type, although it is listed under -> DB Designer Options -> Database Options. Whenever you try to select “Oracle” it jumps back to “MySQL”. Other options are ODBC, SQLite and MSSQL, neither of them can be selected.

It is still possible to connect to an Oracle database. Reverse engineering of an existing schema works very well, see the picture above. But it is not possible to design tables and then synchronize them with the database to create them directly. However, a CREATE TABLE script can be copied to the clipboard or a file, but since it is designed for MySQL the script has to be edited for use with Oracle.

DB Designer 4 can be downloaded for Windows and Linux here, documentation can be found here.

Removing Duplicate Rows

Oracle
For our example we need to insert a duplicate row into HR.EMPLOYEES first. The new row has to be identical on first_name, last_name, email and department_id columns. To insert such a row, we have to disable the unique constraint on the HR.EMPLOYEES table.

ALTER TABLE employees DISABLE CONSTRAINT emp_email_uk;

INSERT INTO employees (
EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER,
HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID,
DEPARTMENT_ID
) VALUES (
‘501′, ‘Donald’, ‘OConnell’, ‘DOCONNEL’, ‘650.507.9833′, TO_DATE(’21.06.99′,’DD.MM.RR’), ‘IT_PROG’, ‘2600′,null,’124′,’50′;

Duplicate rows can be removed like this:

DELETE FROM employees
WHERE rowid IN (
SELECT rowid
FROM employees
MINUS
SELECT MAX(rowid)
FROM employees
GROUP BY first_name, last_name, email, department_id
);

Rows will be considered duplicate, if they match on first_name, last_name, email and department_id. In production it is not wise to delete duplicate rows without copying the table first.

SELECT * FROM employees INTO employees_copy;

To enable the emp_email_uk unique constraint again:

ALTER TABLE employees ENABLE CONSTRAINT emp_email_uk;

SQL Server
In SQL Server it is a bit trickier to delete duplicate rows. It is not possible without a second table, for example a temporary table. For our examples we need a table with a few rows, one of them is duplicate.

CREATE TABLE t (
c1 INT NULL,
c2 INT NULL,
c3 INT NULL);

INSERT INTO t (c1, c2, c3) VALUES (1, 2, 3);
INSERT INTO t (c1, c2, c3) VALUES (4, 5, 6);
INSERT INTO t (c1, c2, c3) VALUES (7, 8, 9);
INSERT INTO t (c1, c2, c3) VALUES (7, 8, 9);

It’s easy if a row is considered duplicate when all column values are identical:

SELECT DISTINCT * INTO #t_tmp FROM t;
TRUNCATE TABLE t;

INSERT t SELECT * FROM #t_tmp;
DROP TABLE #t_tmp;

SELECT * FROM t;

If somebody by accident has loaded the same file twice into our table, and we have a column to log the insert date, rows are duplicate but not identical. SELECT DISTINCT won’t help anymore here.

ALTER TABLE t ADD d DATETIME NOT NULL DEFAULT getdate();

INSERT INTO t (c1, c2, c3) VALUES (1, 2, 3);
INSERT INTO t (c1, c2, c3) VALUES (4, 5, 6);
INSERT INTO t (c1, c2, c3) VALUES (7, 8, 9);

SELECT * FROM t;

Here a UNIQUE INDEX with the IGNORE_DUP_KEY option may help.

CREATE TABLE #t_tmp (
c1 INT NULL,
c2 INT NULL,
c3 INT NULL,
d DATETIME NULL);

CREATE UNIQUE INDEX ui ON #t_tmp (c1, c2, c3) WITH IGNORE_DUP_KEY;

INSERT #t_tmp SELECT * FROM t;
TRUNCATE TABLE t;

INSERT t SELECT * FROM #t_tmp;
DROP TABLE #t_tmp;

SELECT * FROM t;

How to Save Large Objects with ASP.NET 2.0 to SQL Server 2005

Prepare Database for Storage of Large Objects
VARBINARY(MAX) datatype is used for storage of binary large objects (BLOB) in SQL Server 2005. The formerly used IMAGE datatype is still available for compatibility reasons but should not be used for new projects.

Best practice to avoid fragmentation of the PRIMARY-filegroup is to create a text/image-filegroup for storing large objects. The BIN column in the TAB table is assigned to the text/image-filegroup named BLOB.

Write Binary Data to SQL Server
The following procedure writes binary data contained in byte[] binary_file into the database.

private void SaveFile(byte[] binary_file)
{
int dong;

using (SqlConnection con = new SqlConnection(”…”))
{
using (SqlCommand com = con.CreateCommand())
{
com.CommandText = “INSERT INTO tab (bin) VALUES (@raw)”;
com.Parameters.AddWithValue(”@raw”, binary_file);
com.CommandTimeout = 3600;

con.Open();
dong = com.ExecuteNonQuery();
}
}
}

Depending on your business requirements mimetype, filename, creator, date created, description and other information has to be inserted as well. CommandTimeout is the time in seconds available to the insert statement to finish. Default in SQL Server 2005 is set to 60 seconds. For binary files bigger than a few MB it’s not enough.

Read Binary Data from SQL Server
The following procedure reads binary data from SQL Server. Again it is important to set CommandTimeout to a higher value.

private byte[] LoadFile(int fid)
{
byte[] binary_file = null;

using (SqlConnection con = new SqlConnection(”…”))
{
using (SqlCommand com = con.CreateCommand())
{
com.CommandText = “SELECT bin FROM tab WHERE fid = @fid”;
com.Parameters.AddWithValue(”@fid”, fid);
com.CommandTimeout = 3600;

con.Open();
binary_file = (byte[]) com.ExecuteScalar();
}
}

return binary_file;
}

SQL Server 2005 can store up to 2 GB of data in a single VARBINARY(MAX) field. Oracle 10g however can store a maximum of 128 terrabytes in a single BLOB field. Quite a bit more than SQL Server.

Nightly Full Export Without Showing SYSTEM Password Using Data Pump

In addition to RMAN backup I have a full export batch running every night on all databases. The SYSTEM password has been kept in the batch script. Whoever can logon to the system and read the file knows the SYSTEM password.

In 10g with DBMS_DATAPUMP called from a simple PL/SQL package I can schedule the nightly export job without any batch scripts, using DBMS_SCHEDULER.

– run as SYS:
GRANT EXECUTE ON dbms_lock TO system;

– run as SYSTEM to create directory
CREATE DIRECTORY full_exp AS ‘F:OraExp{SID}’;

– Create Stored Procedure for Full Export
CREATE OR REPLACE PACKAGE sp_fullexport
IS
PROCEDURE exp;
END sp_fullexport;

CREATE OR REPLACE PACKAGE BODY sp_fullexport
IS
h1 NUMBER := NULL; PROCEDURE exp
IS
BEGIN

– remove old export
BEGIN
UTL_FILE.FREMOVE (’FULL_EXP’,'fullexp.dmp’);
EXCEPTION
…
END;

– wait until the file is removed
BEGIN
DBMS_LOCK.sleep(30);
END;

– do the export
BEGIN
h1 := DBMS_DATAPUMP.open (
operation => ‘EXPORT’,
job_mode => ‘FULL’,
job_name => ‘fullexpjob’,
version => ‘COMPATIBLE’);

DBMS_DATAPUMP.add_file(
handle => h1,
filename => ‘FULLEXP.LOG’,
directory => ‘FULL_EXP’,
filetype => 3);

DBMS_DATAPUMP.add_file(
handle => h1,
filename => ‘FULLEXP.DMP’,
directory => ‘FULL_EXP’,
filetype => 1);

DBMS_DATAPUMP.START_JOB(h1);

EXCEPTION
…
END;
END;
END sp_fullexport;

/* run procedure
exec sp_fullexport.exp;
*/

After the package is created, a scheduler job is needed to run the export procedure every night

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
job_class_name => ‘backup’,
resource_consumer_group => ‘DEFAULT_CONSUMER_GROUP’,
comments => ‘Backup, Export etc’);
END;

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘full_export’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘SP_FULLEXPORT.EXP’,
start_date=>’30-MAR-06 07.00.00 PM Europe/Zurich’,
repeat_interval => ‘FREQ=DAILY;BYHOUR=19′,
end_date=>’30-MAR-22 07.00.00 PM Europe/Zurich’,
job_class => ‘backup’,
comments => ‘Full Export’);
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE (’full_export’);
END;
/

/* Run job directly without scheduling
exec DBMS_SCHEDULER.RUN_JOB (’full_export’);
*/