I'm still amazed that after hundreds of installs you can still hit a new error. In this case the oraInst.loc file, which points to the central inventory location, was not setup correctly. There was an old location which they commented out, but they forgot to specify a new one.

What is the Central Inventory? The Central Inventory stores information for all Oracle products installed on the server. Inside the inventory location there is an xml file (inventory.xml) which lists all of the ORACLE_HOMEs.

Ex. <Central Inventory Path>/ContentsXML/inventory.xml (In this example, using the path from the oraInst.loc file above you would look in /home/oracle/oraInventory/ContentsXML/inventory.xml)

As you can see, this server has both 9i and 10g database software and an application server. Inside each of these ORACLE_HOMEs there is a Local Inventory. The local inventory stores the list of components, patchsets and interim patches installed in that particular ORACLE_HOME. The file in this case is named comps.xml

ex. <Local Inventory Location>/ContentsXML/comps.xml (This file is rather large so just a small clip. This is from the 920_64bit ORACLE_HOME.)

By default Oracle looks for the oraInst.loc file in /var/opt/oracle or /etc depending on your platform. But you can change the location if you wish, you'll just need to remember to specify the -invPtrLoc parameter with opatch and the runInstaller.

Thursday

In a normal oracle environment you can set the FAST_START_MTTR_TARGET parameter which controls the amount of time (seconds) required to recover from an instance or server failure. 10g has a new feature Automatic Checkpoint tuning which utilizes periods of low I/O to advance checkpoints. This feature requires that FAST_START_MTTR_TARGET is set to a non-zero value.

FAST_START_MTTR_TARGET includes both instance startup and crash recovery. If you have a RAC environment and there is a failure the surviving instances will perform the recovery. Therefore including the instance startup time in the mean time to recover calculation isn't accurate. So in the case of RAC environments you may choose to set the _FAST_START_INSTANCE_RECOVERY_TARGET parameter which determines the amount of the time for instance recovery. Note: If multiple instances fail, recovery may take longer than the value you specified for this parameter.

So in summary, FAST_START_MTTR_TARGET includes the time for startup, mount, crash recovery, and open. _FAST_START_INSTANCE_RECOVERY_TARGET includes the time for instance recovery only.

Changing any parameter which affects checkpointing should be throughly tested to make sure you don't thrash your IO subsystem. Each of these parameters will increase I/O.

Wednesday

A standard practice for any Oracle database should be to enable passwords on your listeners. EBS is no exception and recently Oracle published a metalink note: 386374.1 on how to do this for 11.5.x environments.

The note has detailed steps on how to set the password at the application and database tiers.

In my opinion, just as important as setting a password, is to monitor for invalid attempts after. Otherwise whats the point? At best you will only slow down a determined intruder. Once you have set a password, any attempt to execute a command via lsnrctl (without setting your password first) will result in the following error showing up in your listener log:

TNS-01169: The listener has not recognized the password

Each DBA has his preferred language for scripts. Some prefer shell, others perl. Myself, I prefer perl. Below is a perl script I have used to monitor the listener log for the above error.

$interval=60; # How many seconds before we check to see if data has been written to the logfile;$email_threshold=5; # How many errors within the interval before an email gets sent;$hostname=$ARGV[0];$file=$ARGV[1];open(filePtr, $file) or die "Can't find $file\n";

for (;;) { @errors=("Subject: Listener Password Errors for $hostname\n"); $currTime = localtime(time); push(@errors,"Here are some errors found at $currTime for $hostname.\n");

A couple of key things you may want to customize in the above script are the $interval and $email_threshold variables. As well, there is an email address towards the bottom where alerts will be sent.

Now, I am by no means a perl expert but the above works for me. I'm sure there are some more efficient ways to monitor the log file. Feel free to post a comment with your preferred method. If you cycle your logfiles keep in mind you'll need to restart this script. I've monitored resource consumption and its pretty minimal but I know some DBA's will prefer not having a process run constantly.

Note: I've only tested this script on a 9i listener log. Its on my todo list to test it on 8.0.6.

UPDATE: Thanks to Howard Rogers for informing me of the 10g changes. Security has been enhanced such that the TNS listener administrator has to be a member of the local dba group. Unless you plan on administering your listener remotely then you don't need a password. Please see the comments and Metalink note: 340009.1

Most of our databases are still 9i but i'll be sure to remove the listener passwords as part of the upgrades.