Sunday, May 25, 2008

Another great tool: SQL Developer

I like great tools. An efficient tool will help you do a piece of work faster than when you are on your own. If the tool is made by someone who is close to the material chances are the tool is efficient. The tool I use most is SQL Developer. It is easy to use and I promote it to any developer where I work, especially if they only need a quick access to the data dictionary, run some simple (or complex) SQL statements. It is not invasive, contrary to other tools it does not provide all the DBA tools that may be useful in some cases, but also may be rather intrusive and put a severe load on oracle instance as it queries the internal v$-tables.

Installation of SQL Developer can hardly be easier; unpack a zip-file, and execute the program. It asks for the java.exe if you downloaded the version without java and asks you if you would like to migrate from a previous version. Connection can be done through JDBC, meaning no oracle client is necessary, but in case you have the client software installed, it lets you pick a connection from a list of known tns-connections.

Latest version 1.5 adds support for CVS and Subversion, and lets you generate documentation in html-format. Very useful for offline-browsing or add to some intra net server.

Another tool I use is Schemester. Simple and efficient to capture a data model from the database and visualize the foreign keys. Schemester used to be located at, but that site seems to have been abandoned, and I have not seen much activity around it for some time now. As you upgrade or change the data model in Schemester it can generate "delta DB DDL script". On top of my wish list for SQL Developer are these features from Schemester. If the status quo sets the standard they are likely to be implemented without some bugs that Schemester has.

Sue Harper posts updates about SQL Developer on her blog, you won't have too many posts to catch up, but the tidbits are absolutely useful (like when I can look forward to a new version.)

Tuesday, May 13, 2008

Praise for Oracle BI Publisher

I have been working on a small scale data warehouse project for a few months along with many other tasks. Following Kimball's ideas on dimensional modeling and by keeping it simple I had more or less an idea on how to proceed with the construction of the database, data model and how to create the reports (but only in SQL). One guy in management actually wanted to learn SQL and went ahead with SQL Developer (fantastic product, so easy to use that anybody familiar with the layout of the keyboard can use it). But I did not expect the same attitude from the rest. I have to say that I've seen many products for making glorified reports, but all of them gave me the impression of being cumbersome to install and not exactly bug free. I did not know any product very well and neither had I the time to start testing them. Earlier I've played with Application Express and thought some reports could be created in ApEx, but I had a nagging feeling this would not cover all our needs.

After a presentation delivered by a guy from Bicon at OUGN's (Oracle User Group Norway) yearly conference I decided to try out Oracle BI Publisher (formerly called XML Publisher). BIP is clearly an option for us. After installation which is plain sailing, you define a few simple reports for immediate publishing. BIP comes with its own web server and can be installed on any windows box lying around (which you then baptize The Application Server). Anybody with a net browser can indulge themselves with the reports. The security model is intuitive with roles and users; I can easily write a report and decide who will have access to it. The BIP Desktop add-on for Windows does a good job with defining a layout. The ability to export the report in different formats guarantees that reports can be published in different ways and even processed further elsewhere (e.g. Excel). BIP has a scheduler (requires access to an oracle database for storing a repository) so that long-running reports (i.e. the sofar-not-converted batch job reports that used to run on the OLTP database) can be run during the night and be ready for the early morning bird at 07 hs.

If you are a DBA with limited experience outside the world of sqlplus and alike, I recommend checking out BIP. Later on when demand for more analyzing tools increases I will dig into some other BI tools, but for a while I will spend time creating more reports in BIP.

Monday, May 5, 2008


Just a short post on this error code. Happened last time I recreated a physical standby. I could not find more than the reference note on this error on Metalink (a note that is just a copy of the output from command oerr ora 16047):

Cause: The DB_UNIQUE_NAME specified for the destination does not match the DB_UNIQUE_NAME at the destination.
Action: Make sure the DB_UNIQUE_NAME specified in the LOG_ARCHIVE_DEST_n parameter defined for the destination matches the DB_UNIQUE_NAME parameter defined at the destination.

The error message was misleading in my case; my bad was that the log_archive_config on primary and the standby did not agree (actually it was nothing on standby). This error may show up in alert-log (didn't every time with me), and will also show up in v$archive_dest:

select dest_id,status,error
from v$archive_dest;

If this is your case, check log_archive_config on all nodes.