We need to access diagnostic data about the Data Guard configuration, especially when there's a problem in the Redo Apply or redo transport services. After the first diagnosis of the problem, it's possible to decide whether to search for detailed information in the primary database or in the standby database. If the issue is about sending redo, it's more likely that the necessary information can be found on the primary site. However, if it's about Redo Apply, it's better to search for detailed information on the standby site.
No matter where we search for diagnostic data, we need to know where to search for the related logfiles and how to query diagnostic data in the database. The most commonly referenced files in a Data Guard issue are the primary and standby alert log files. If the Data Guard broker is used in the configuration, Data Guard Monitor (DMON) logfiles can also be helpful for troubleshooting. If necessary, we can also query Data Guard-related dynamic performance views to get information about the issue.
Now let's look at the details of using these methods to access diagnostic data in a Data Guard configuration.
Alert log files are the first step to start investigating an Oracle Database issue. It's also the same for Data Guard. We can find the directory that contains the alert log and the trace files using the following query:
SQL> SELECT NAME,VALUE FROM V$DIAG_INFO WHERE NAME LIKE 'Diag%'; NAME VALUE --------------- ----------------------------------------------------- Diag Enabled TRUE Diag Trace /u01/app/oracle2/diag/rdbms/india_ps/INDIAPS/trace Diag Alert /u01/app/oracle2/diag/rdbms/india_ps/INDIAPS/alert Diag Incident /u01/app/oracle2/diag/rdbms/india_ps/INDIAPS/incident Diag Cdump /u01/app/oracle2/diag/rdbms/india_ps/INDIAPS/cdump
Here, the Diag Trace directory is the location of the background process trace files, the server process trace files, the SQL trace files, and the text-formatted version of the alert log file. The Diag Alert directory keeps the XML-formatted version of the alert log. Incident logfiles are under the Diag Incident directory, and the core dump files are under the Diag Cdump directory.
Let's go to the Diag Trace directory and list the alert log file; we can use the tail command to see the last lines of the file:
$ cd /u01/app/oracle2/diag/rdbms/india_ps/INDIAPS/trace $ ls –al alert* -rw-r----- 1 oracle dba 2533843 Dec 20 01:56 alert_INDIAPS.log $ tail -100 alert_INDIAPS.log . . . Media Recovery Waiting for thread 1 sequence 3273 (in transit) Recovery of Online Redo Log: Thread 1 Group 5 Seq 3273 Reading mem 0 Mem# 0: /u01/app/oracle2/datafile/ORCL/std5.log RFS[20]: Selected log 4 for thread 1 sequence 3274 dbid 1319333016 branch 791552282 Thu Dec 20 01:48:19 2012 Archived Log entry 450 added for thread 1 sequence 3273 ID 0x4eea7a49 dest 1: Media Recovery Waiting for thread 1 sequence 3274 (in transit) Recovery of Online Redo Log: Thread 1 Group 4 Seq 3274 Reading mem 0 Mem# 0: /u01/app/oracle2/datafile/ORCL/std4.log Thu Dec 20 01:56:08 2012 RFS[20]: Selected log 5 for thread 1 sequence 3275 dbid 1319333016 branch 791552282 Thu Dec 20 01:56:08 2012 Archived Log entry 451 added for thread 1 sequence 3274 ID 0x4eea7a49 dest 1: Thu Dec 20 01:56:09 2012 Media Recovery Waiting for thread 1 sequence 3275 (in transit) Recovery of Online Redo Log: Thread 1 Group 5 Seq 3275 Reading mem 0 Mem# 0: /u01/app/oracle2/datafile/ORCL/std5.log
Another method to monitor the alert log is the ADRCI command-line tool, which is an Oracle Database 11g feature to manage Oracle Database diagnostic data. Using ADRCI, it's possible to manage the entire alert log and trace files in the diagnostic directories (database, ASM, listener alert log files, and so on), view the health monitor reports, and zip incident and problem information into a file to send to Oracle Support.