Wednesday, 15 October 2014

Tracing in Oracle Data Guard

Sometime to do additional analysis and troubleshooting in oracle databases or even to provide additional information to Oracle Support we need to enable tracing, and when it comes to oracle data guard we have several tracing levels as well, like other oracle events.

Gathering trace data for database workload there are lot of ways which is also shown in my another article,  however today we will see special type of tracing which is for log archive and log apply services. To gather and generate trace data for archiving you can enable tracing on the Primary and Standby Databases. Archive Tracing can be enabled via the Initialization Parameter ‘log_archive_trace’.

There are several Levels available. To enable different levels of tracing for oracle data guard archiving set the ‘log_archive_trace’ to the Sum of the desired Levels. Remember that setting this parameter back to ‘0’ disables the Tracing. Relevant and common Levels for Log Transport Services are:

For the Primary Database:

1:            Tracks archiving of log files
2:            Tracks archive status by archive log file destination
64:         Tracks ARCn process state activity
128:       Tracks FAL server process activity
512:       Tracks LGWR redo shipping network activity
2048:     Tracks RFS/ARCn ping heartbeat

For the Standby Database:

1:            Tracks archiving of log files
2:            Tracks archive status by archive log file destination
64:          Tracks ARCn process state activity
256:       Tracks RFS Logical Client
1024:     Tracks RFS physical client
2048:     Tracks RFS/ARCn ping heartbeat

There are some more Levels which are concerning local Archiving and Log Apply Services which can be set as well if required. See below

4096 Tracks real-time apply activity

8192 Tracks Redo Apply activity (media recovery or physical standby)

16384 Tracks archive I/O buffers

32768 Tracks LogMiner dictionary archiving

You can combine tracing levels by setting the value of the LOG_ARCHIVE_TRACE parameter to the sum of the individual levels. For example, setting the parameter to 6 generates level 2 and level 4 trace output.

Note: The level numbers do not appear in the actual trace output; they are shown here for clarification only.

You can read oracle documentation for further information from following link of doc

Reference :-

No comments:

Post a Comment