Dataguard: Tracing in dataguard

A note on tracing when you have dataguard environment,

Use the following useful SQL

• SELECT group#, member FROM v$logfile WHERE type = 'STANDBY';
• SELECT group#, dbid, archived, status FROM v$standby_log;
• SELECT dest_id,valid_type,valid_role,valid_now FROM v$archive_dest;
• SELECT process, status, group#, thread#, sequence# FROM v$managed_standby order by process, group#, thread#, sequence#;
• SELECT name, value, time_computed FROM v$dataguard_stats;
• SELECT timestamp, facility, dest_id, message_num, error_code, message FROM v$dataguard_status ORDER by timestamp;
• SELECT primary_xid, type, mining_status, apply_status FROM v$logstdby_transaction;

Use LOG_ARCHIVE_TRACE for tracing various redo propagation and apply processes/tasks

• ALTER SYSTEM SET LOG_ARCHIVE_TRACE=<trace level>

The trace level can be as follows

• 0 Disables archived redo log tracing (default setting)
• 1 Tracks archiving of redo log file
• 2 Tracks archival status per archived redo log destination
• 4 Tracks archival operational phase
• 8 Tracks archived redo log destination activity
• 16 Tracks detailed archived redo log destination activity
• 32 Tracks archived redo log destination parameter modifications
• 64 Tracks ARCn process state activity
• 128 Tracks FAL server process activity
• 256 Reserved for future use
• 512 Tracks asynchronous LGWR activity
• 1024 Tracks RFS physical client
• 2048 Tracks ARCn or RFS heartbeat
• 4096 Tracks real-time apply activity
• 8192 Tracks Redo Apply activity (media recovery or physical standby)

If you configured DG broker the trace can be enabled as follows and the files located as.

Trace files for DMON can be found in the bdump directory and is called drc<sid>.log

/** Tracing primary site **/

DGMGRL> edit database 'primary_db_name' set property LogArchiveTrace='1′;

/** Tracing standby site **/

DGMGRL> edit database 'standby_db_name' set property LogArchiveTrace='1′;

/** Directly from the dgmgrl command prompt **/

host#> dgmgrl -debug sys/password@<instance>

Enterprise manage can be used to monitor, manage and trigger alerts for DG configurations

Use the following commands from within DGMGRL

• SHOW DATABASE <'db name'> 'StatusReport'
• SHOW DATABASE <'db name'> 'LogXptStatus'
• SHOW DATABASE <'db name'> 'InconsistentProperties'
• SHOW DATABASE <'db name'> 'InconsistentLogXptProps'
• SHOW CONFIGURATION
• SHOW DATABASE <db name>
• debug
• logfile

Thanks

Geek DBA