May 2008 Archives

Well it has taken me sometime to get around to it, but finally I've added the Google WEB 2.0 Search API to this site. So now you can directly continue your search for anything Oracle specific or otherwise from here. As a default you get anything current in the way of Oracle news from other sites and blogs.

If you are interested in this facility supplied by Google then check out the documentation on their website. There is nothing to install but you do have to register with Google by opening an account to get a site specific key but that is pretty painless.

If you are looking for a comfortable way of efficiently logging information within your Oracle database or you want a seamless logging from within your Java application which already uses log4j and uses an Oracle database - look no further. It is part of the Apache project and can be freely used within production solutions. So take a look here for details on log4plsql.

If you encounter an error "ORA-39700: database must be opened with UPGRADE option" you probably have a database instance which hasn't yet been converted. That is to say, you have more than one instance running on the same database but the instance you want to start hasn't yet been converted to the correct version. This can happen when you restore an old backup to your database server. The steps needed to complete this are very simple:

1. make sure your ORACLE_SID points to the instance you want to convert

2. start sqlplus / nolog

3. connect / as sysdba

3. use "startup upgrade" to boot the instance in resticted upgrade mode

4. run "catupgrd.sql" stored in "$ORACLE_HOME/rdbms/admin"

5. shutdown the instance "shutdown immediate"

6. restart "startup"

7. finally run "utlrp.sql" to compile any invalid objects

... and that should be it!

When tuning a database you have to contend with a vast number of possibilities as to how to go about it. Whilst anything Oracle supplies in the way of tools and techniques can be regarded as a valid means of correcting some unwanted behavior there are some tools which should be avoided in production.

The use of "Hints" in particular should regarded as an exploratory method for diagnosing what could be done to improve things or what might happen when a database gets loaded with large amounts of data and performance starts to degrade. Using hints in a production environment should only be done as a last resort and as a temporary work-around until some more robust correction can be installed. This article 'Rules for Hinting' by Jonathan Lewis demonstrates why that is.

So be warned - there are almost always better solutions, which could be used instead of hints.