I have received several emails asking help with why oracle does not write information into the alert log files during database startup failures.. Due to practice we tend to look for instance or database related information in our standard log directories such as $ORACLE_BASE/admin/…/bdump or $ORACLE_HOME/network/log directories. This causes panic and anxiety, searching Google, open entries on OTN forums or open an SR with Oracle support. The Oracle documentation has also not done a good job in this area.
Entries are not found in the alert log because the database/instance was not started using SQL*Plus entries are not added to the db alert log. Depending on what we are trying to look for, what area of the stack is being examined, or at what state of the application is running under, there are different kinds and flavors and locations where you could find logs.
Note: This is not a complete list, but a start for the beginners to navigate their way through the troubleshooting process.
1. During installation:
- When the installation is started until such time that the screen where the orainventory location is specified the logs are written to the /tmp directory of the server from where the installer is executed. Subsequently logs get written to $ORACLE_BASE/oraInventory/logs location.
- When creating a database using dbca or configuring network using netca the logs are generated to $ORACLE_HOME/cfgtoollogs/. Depending on the configuration assistant used the logs are created under specific directories.
[oracle@] cfgtoollogs]$ ls -ltr
drwxr-x— 2 oracle dba 4096 Feb 16 16:50 oui
drwxr-x— 3 oracle dba 4096 Feb 18 13:45 emca <===== EMCA (enterprise Manager) log files
drwxr-xr-x 2 oracle dba 4096 Feb 22 22:22 catbundle
drwxr-x— 3 oracle dba 4096 Feb 23 13:13 dbca <===== DBCA log files
drwxr-xr-x 2 oracle dba 4096 Feb 23 13:22 netca <===== NETCA log files
drwxr-xr-x 3 oracle dba 4096 Mar 18 16:47 opatch <===== opatch log files
2. Clusterware startup
- On server start or reboot, the information pertaining the various devices are written to the system logs files directory. The location of these log files can vary depending on the OS, on Linux servers they are located in /var/log/messages.
- When the OS starts the clusterware, messages are written to two locations, (a) to the alert log located at $ORA_CRS_HOME/log/< >alert<nodename>.log and (b) for the various clusterware daemon processes to the respective daemon directories.
- After the clusterware demons are up and running, the clusterware attempts to start the resources configured and managed by the CRS. This is done by reading the configuration settings from the OCR file. During this process, the CRS generates log files for each resource.
- For the nodeapps which includes VIP, ONS and GRD the log files are located in $ORA_CRS_HOME/log/racg directory.
- The logs related to the other resources are located under the respective home directories. For example the ASM startup log file is located in the $ASM_HOME/log/<nodename>/racg/ directory and the RBDMS database related resources will be located in the $ORACLE_HOME/log/<nodename>/racg directory.
[oracle@prddb1]$ ls -ltr $ORA_CRS_HOME/log/prddb1
drwxr-x— 2 oracle dba 4096 Feb 16 14:41 admin <===
drwxr-x— 2 root dba 4096 Feb 16 14:44 crsd <=== cluster ready services deamon log files
drwxr-x— 2 oracle dba 4096 Mar 18 09:17 evmd <=== event manager deamon log files
drwxr-x— 4 oracle dba 4096 Mar 23 13:12 cssd <=== cluster synchronization services deamon log files
-rw-rw-r– 1 root dba 48915 Mar 29 02:44 alertprddb1.log <=== clusterware alert log
drwxrwxr-t 5 oracle dba 4096 Mar 29 11:03 racg <=== all clusterware nodeapps such as VIP, ONS, etc
drwxr-x— 2 oracle dba 98304 Mar 31 15:57 client <=== all clusterware client log files
[oracle@prddb1 racg]$ ls -ltr $ASM_HOME/log/prddb1/racg
drwxr-xr-t 2 oracle dba 4096 Feb 16 15:53 racgmain
drwxr-xr-t 2 oracle dba 4096 Feb 16 15:53 racgeut
drwxr-xr-t 2 oracle dba 4096 Feb 16 15:53 racgmdb
drwxr-xr-t 2 oracle dba 4096 Feb 16 15:53 racgimon
-rw-r–r– 1 oracle dba 2256 Mar 23 13:04 imon.log
-rw-r–r– 1 oracle dba 22423 Mar 25 10:06 ora.prddb1.LISTENER_PRDDB1.lsnr.log <== ASM listener log
-rw-r–r– 1 oracle dba 2617 Mar 25 10:06 mdb.log
-rw-r–r– 1 oracle dba 17696 Mar 25 10:06 ora.prddb1.ASM1.asm.log <== asm log generated by CRS during resource startup
[oracle@prddb1 racg]$ ls -ltr $ORACLE_HOME/log/prddb1/racg/
-rw-r–r– 1 oracle dba 43669 Mar 25 10:06 ora.prddb1.RACPROD_PRDDB1.lsnr.log <== database listener log
-rw-r–r– 1 oracle dba 6155 Mar 29 11:00 ora.RACPROD.RMANBKUP.RACPROD1.srv.log <== database service log
-rw-r–r– 1 oracle dba 15474 Mar 29 11:01 ora.RACPROD.RMANBKUP.cs.log <== database composite service log
-rw-r–r– 1 oracle dba 11606 Mar 29 11:16 ora.RACPROD.RACPROD1.inst.log <== database instance startup log
3. Database logs
Once the database is up and running then the log entries are written to their normal destinations… for example the database logs will be written to $ORACLE_BASE/admin/<database name>/bdump/alert_<db instancename>.log
Note: Only the errors during startup are logged under $ORACLE_HOME/log/<nodename>/racg location. If the startup is clean the log at this location only has a success entry and the remaining startup entries are written to the alert_<instancename>.log on the respective nodes for the respective instances.
In 11gR2 this is again different.. we will discuss that in a different blog at a later time.