Logs in 10gR2 RAC..


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
total 40

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
total 168
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
total 68
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/
total 940

-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.


Sangam10 :: call for papers is on


Interested in presenting at the Oracle users group conference in India?  AIOUG has started accepting papers for their second users conference to be held on 3rd and 4th September.

Check out www.aioug.org/sangam10.php for more details.

10gR2 RAC – POC – additional VIP to isolate network traffic

In a RAC configuration we traditional have a  VIP associated to the public address for every node in the cluster.  Since this is the only public network to the database server, all traffic is directed through this VIP.

Note:  Multiple networks in the cluster are not officially  supported for Oracle 10.2 RAC.    So this entire venture of multiple networks was at our risk.

We had a requirement to isolate application traffic received from the application tier from the regular public/user/third party application traffic.  For this an additional public IP was configured for each server in the cluster and an additional VIP was defined on this new public IP.   Since the intention was to use this new VIP only for the application tier, we called it the middle tier (mt) VIP.     So this note discuss the process followed and the outcome

Steps to add the new VIP

There are two methods of adding a VIP to CSR from the command prompt as discussed below or by creating a ‘cap’ file that contains the profile definitions.  This is a 3 node cluster, the nodes are ebspocdb1, ebspocdb2, ebspocdb3

1. Create an user VIP using the following command as user ‘oracle’

/app/oracle/product/crs/bin/crs_profile -create ora.ebspocdb1-mt.vip -t application -d ‘Mid Tier application VIP’ -a /app/oracle/product/crs/bin/usrvip -h ebspocdb1 -p favored -o as=1,ap=1,ra=0,oi=eth3,ov=,on=
/app/oracle/product/crs/bin/crs_profile -create ora.ebspocdb2-mt.vip -t application -d ‘Mid Tier application VIP’ -a /app/oracle/product/crs/bin/usrvip -h ebspocdb2 -p favored -o as=1,ap=1,ra=0,oi=eth3,ov=,on=
/app/oracle/product/crs/bin/crs_profile -create ora.ebspocdb3-mt.vip -t application -d ‘Mid Tier application VIP’ -a /app/oracle/product/crs/bin/usrvip -h ebspocdb3 -p favored -o as=1,ap=1,ra=0,oi=eth3,ov=,on=

2. Once the profile has been created, register the resource with CRS..

/app/oracle/product/crs/bin/crs_register ora.ebspocdb1-mt.vip
/app/oracle/product/crs/bin/crs_register ora.ebspocdb2-mt.vip
/app/oracle/product/crs/bin/crs_register ora.ebspocdb3-mt.vip

3. This resource should be owned by ‘root’.  As user ‘root’ change the ownership of the resource and then give ‘oracle’ user execute privilege for this resource.   You can execute these commands from any node in the cluster.

/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb1-mt.vip -o root
/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb1-mt.vip -u user:oracle:r-x

/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb2-mt.vip -o root
/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb2-mt.vip -u user:oracle:r-x

/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb3-mt.vip -o root
/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb3-mt.vip -u user:oracle:r-x

4. Start the resource as user ‘oracle’

/app/oracle/product/crs/bin/crs_start -c ebspocdb1 ora.ebspocdb1-mt.vip
/app/oracle/product/crs/bin/crs_start -c ebspocdb2 ora.ebspocdb2-mt.vip
/app/oracle/product/crs/bin/crs_start -c ebspocdb3 ora.ebspocdb3-mt.vip

5.  Verify if VIP has been configured and started

[oracle@ebspocdb1 ~]$ crsstat | grep .vip
ora.ebspocdb1.vip           ONLINE     ONLINE on ebspocdb1
ora.ebspocdb1-mt.vip        ONLINE     ONLINE on ebspocdb1
ora.ebspocdb2.vip           ONLINE     ONLINE on ebspocdb2
ora.ebspocdb2-mt.vip        ONLINE     ONLINE on ebspocdb2
ora.ebspocdb3.vip           ONLINE     ONLINE on ebspocdb3
ora.ebspocdb3-mt.vip        ONLINE     ONLINE on ebspocdb3

NOTE:  if you need to modify this resource for any reason you could use the crs_profile update command.  However you have to first stop the resource using crs_stop command and then execute the update command. For example..
crs_profile -update ora.ebspocdb1-mt.vip -t application -a /app/oracle/product/crs/bin/usrvip -h ebspocdb1 -p favored -o oi=eth3,ov=,on=
After executing the update command, repeat steps 2 thru 4 to implement this change.

Create second  database listener

Create a second listener on the new mt.vip  using netca on a different port.  Select the new mt VIP as the network.  This will also add the new listener to CRS.  After configuring the listener verify the status.

[oracle@ebspocdb1 ~]$ crsstat | grep .lsnr
ora.ebspocdb1.LISTENERMT_EBSPOCDB1.lsnr    ONLINE     ONLINE on ebspocdb1
ora.ebspocdb1.LISTENER_EBSPOCDB1.lsnr      ONLINE     ONLINE on ebspocdb1
ora.ebspocdb2.LISTENERMT_EBSPOCDB2.lsnr    ONLINE     ONLINE on ebspocdb2
ora.ebspocdb2.LISTENER_EBSPOCDB2.lsnr      ONLINE     ONLINE on ebspocdb2
ora.ebspocdb3.LISTENERMT_EBSPOCDB3.lsnr    ONLINE     ONLINE on ebspocdb3
ora.ebspocdb3.LISTENER_EBSPOCDB3.lsnr      ONLINE     ONLINE on ebspocdb3

Update the LOCAL_LISTENER and REMOTE_LISTENER definitions in the tnsnames.ora file.

Update the LOCAL_LISTENER and REMOTE_LISTENER definitions in the tnsnames.ora file to include the new LISTENERMT_EBSPOCDB1 listener definitions.  Once this is complete and the listener is recycled, the database services are dynamically registered with the listener.

The Results

While the default VIP was defined in the DNS server, the private VIP was only visible to the app tier. On a load test, we noticed that not all connections was established with success.  Actually every other connection failed.

The intention of having a dedicated VIP was to isolate connections from the app tier to the database tier using this private VIP.  However once the LOCAL_LISTENER parameter is configured, database services are registered with both listeners,  what’s wrong with that? When the app tier makes a connection request to the database tier using the new VIP, and when the database tier had to return the session handle back to the requestor, it could not pin it back to the same listener that received the request and almost every other time the session handle was routed to the other listener causing session death.

What would have been nice, is if the services could be mapped to a specific listener, which would allow sessions to be pinned back to the service to the listener to the requestor.


Its obvious why Oracle does not support multiple public networks for the database tier, since VIPs are assigned to public NIC, additional VIP did not help in this isolation.

DGB::ORA-16608:one or more sites have warnings

I am at a customer site trying to help them with a physical standby implementation in a RAC environment. The primary database is a three node RAC cluster but the standby environment is a single instance RAC database.

During setup and configuration of the dataguard broker, we had the following error . ” ORA-16608: one or more sites have warnings”. Like it says one or more errors? Even if there is only one error with the configuration or setup the misleading message can make you wondering.. Lets take at analyzing one such scenario..

1. From the command line interface of the broker when you check the configuration status using show configuration, the broker would give you the current setup at the end of the show configuration output, the broker tries to query the current status from all servers involved, this includes all instances in the RAC configuration on both the primary and physical standby side.

DGMGRL> show configuration verbose

Name: AT_DGB
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED

ATDB – Primary database
ATSB – Physical standby database

Current status for “AT_DGB”:
Warning: ORA-16608: one or more databases have warnings

2. Which database in the DGB configuration has this error? SHOW DATABASE VERBOSE <database name> would tell us which database has this error. The error above really does not tell which database has the error. So we may have to check the details against both the databases. Checking the status of the Primary database we observed “ORA-16809: multiple warnings detected for the database” another error? again not one specific error but potentially many.. that what the error description tells us..


Current status for “ATDB”:
Warning: ORA-16809: multiple warnings detected for the database

InitialConnectIdentifier = ‘atdb’
ObserverConnectIdentifier = ”
LogXptMode = ‘SYNC’
Dependency = ”
DelayMins = ‘0’
Binding = ‘OPTIONAL’
MaxFailure = ‘0’
MaxConnections = ‘1’
ReopenSecs = ‘300’
NetTimeout = ‘180’
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘auto’
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ’30’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ”
LogFileNameConvert = ”
FastStartFailoverTarget = ”
StatusReport = ‘(monitor)’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’

Checking the details of the other database in the configuration reveled no errors.

3. Time to drill down further to get more details. Checking the DMON process log files under ‘bdump’ destination directory we noticed the following.. (during initial configuration it easier if we tail -f on the DMON log file on both the primary and standby servers to monitor its activity)..

for example: /oracle/admin/atdb/bdump/$ tail -f drcatdb1.log

DG 2009-09-30-14:21:34 0 2 0 RSM Warning: Property ‘StandbyFileManagement’ has inconsistent values:METADATA=’auto’, SPFILE=”, DATABASE=’auto’
DG 2009-09-30-14:21:34 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property StandbyFileManagement is inconsistent with the database setting
DG 2009-09-30-14:21:34 0 2 0 RSM Warning: Property ‘ArchiveLagTarget’ has inconsistent values:METADATA=’0′, SPFILE=”, DATABASE=’0′
DG 2009-09-30-14:21:34 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
DG 2009-09-30-14:21:34 0 2 0 RSM Warning: Property ‘LogArchiveMinSucceedDest’ has inconsistent values:METADATA=’1′, SPFILE=”, DATABASE=’1′
DG 2009-09-30-14:21:34 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
DG 2009-09-30-14:21:35 0 2 698930610 Operation MON_VERIFY continuing with warning, status = ORA-1679

4. There are definitely so many errors in the DMON log file all related to property mismatch between dataguard broker and the database. “HEALTH CHECK WARNING: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting” and all these properties have a mismatch?

Checking the values on both the DGB and the instance really could not find any mismatch. After considerable searching on metalink and google had no luck with any specific reasons why this could be wrong or showing as a mismatch.

5. If you recollect one of the requirements for the DGB is to have an SPFILE configuration, the customer did have an SPFILE however instead of keeping the file on shared location, maintained three copies (one on each instance) of the SPFILE in the $ORACLE_HOME/dbs directory..

6. We created a new pfile from the spfile, disabled the init<SID>.ora file and created an spfile file on ASM.

SQL>create spfile=’+data_01_dg/atdb/spfileatdb.ora’ from pfile=’/home/oracle/pfileATB.ora

This did not solve the problem. Apparently initatdb1.ora located in $ORACLE_HOME/dbs directory was not rightly pointing to the ASM spfile. We changed this to look like the output below..

$ORACLE_HOME/dbs/cat initatdb1.ora

7. Starting the instance either using SQLplus or SRVCTL utility brought the database instance up successfully however the instance did not use the right spfile that was created on ASM but used the spfile located in the $ORACLE_HOME/dbs directory.

8. We had to add the spfile using the srvctl utility to the OCR file. Where is the SPFILE information stored in the OCR file? Every database managed by on the RAC cluster could keep the SPFILE file (optionally) in the OCR file. Lets analyze the OCR file, to read the contents of the OCR file, we take a dump of the contents into ASCII readable format.

Not seen such a behavior before.  We are continuing to investigate why this is happening?

$ORA_CRS_HOME/bin/ocrdump will create an output called OCRDUMP in the default directory where the command was executed from. Edit the OCRDUMP file (vi OCRDUMP) and search for SPFILE, against the database ATDB database the output was blank. You would think that if the database was created using DBCA that wrote the database and instance related entries to the OCR file


Using SRVCTL utility we added the SPFILE information to the OCR file and this fixed the startup issue to use the right SPFILE.

$ /oracle/crs/bin/srvctl modify database -d atdb -p +DATA_01_DG/ATDB/spfileatdb.ora

To make sure that the entry was in the OCR we did another dump of the OCR file and checked the entry for the SPFILE for the ATDB database. Yes it was there..

ORATEXT : +SHARED_DATA_01_DG/ARM00TDB/spfilearm00tdb.ora

9. This really fixed the issue, it was the DGB not able to synchronize the value of these various parameters/properties between the various instances in the cluster. BTW, in a RAC environment its a best practice to maintain the SPFILE in a shared location such as ASM storage.

The SHOW CONFIGURATION command from the DGMGRL returned error free.


Name: AT_DGB
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED

ATDB – Primary database
ATSB – Physical standby database

Current status for “AT_DGB”: