Viewing Data Guard Diagnostic Information
The Data Guard broker records activity information in the Oracle database alert log file and in the Data Guard broker log files. The broker log files are named drc.log and are located in the same directory as the alert log file.
You can obtain information about the health of the database (referred to as the database status) by issuing the SHOW DATABASE DGMGRL command.
DGMGRL> SHOW DATABASE london 'RecvQEntries';
The following monitorable database properties can be used to query the database status:
- StatusReport: Lists all problems detected by the broker during a database health check
- Log pX tStatus: Lists all log p py transport errors detected on all instances of the primary database
- InconsistentProperties: Lists all properties that have inconsistent values between the broker configuration file and the database settings
- InconsistentLogXptProps: Lists all redo transport–related properties of standby databases that have inconsistent values between the broker configuration file and the redo transport settings
- LsbyFailedTxnInfo: Identifies a failed transaction that caused log apply service to stop
- LsbyParameters: Identifies the value of MAX_SGA (maximum system global area) and MAX_SERVERS (maximum number of parallel query servers) specifically reserved for log apply services
- RecvQEntries: Returns a table indicating all log files that were received by the standby database but have not yet been applied
- SendQEntries: Returns a table that shows all log files on the primary database that were not successfully archived to one or more standby databases
- TopWaitEvents: Specifies the 5 events with the longest waiting time in the specified instance
Using Monitorable Database Properties to Identify a Failure to Identify a Failure
You can use the SHOW CONFIGURATION, SHOW DATABASE, and SHOW FAR_SYNC commands along with additional monitorable database properties to identify and determine an appropriate resolution for a failure in your Database Guard configuration.
1. Use the SHOW CONFIGURATION command to check the status of the configuration.The status is an aggregated status of all databases and instances in the broker configuration. If everything is working properly in the configuration, the output of this command with respect to status is “SUCCESS.” If there is a problem in the configuration,you receive an error message and it will indicate which databases are in warning or error states.
DGMGRL> SHOW CONFIGURATION;
2. If you receive an error message when you execute the If you receive an error message when you execute the SHOW CONFIGURATION CONFIGURATION command, execute the SHOW DATABASE command for each database or the SHOW FAR_SYNC command for each Far Sync to view a partial list of the warnings and errors for the database.
DGMGRL> SHOW DATABASE london;
DGMGRL> SHOW FAR_SYNC ' ostonFS'
3. After viewing the StatusReport output, you can view the other monitorable database properties such as InconsistentProperties, LogXptStatus, and InconsistentLogXptProps.
DGMGRL> SHOW DATABASE london 'InconsistentProperties';
Using the SHOW CONFIGURATION DGMGRL Command to Monitor the Configuration Command to Monitor the Configuration
The SHOW CONFIGURATION DGMGRL command provides a brief description of the configuration, including the state of the configuration, the protection mode, and the state of fast-start failover. The display also lists the databases that are part of the configuration.
DGMGRL> show configuration Configuration – DRSolution Protection Mode: MaxPerformance Databases: boston - Primary database WARNING: ORA-16809: multiple warnings detected for this database bostonFS - Far Sync london - Physical standby database Physical standby database london2 - Logical standby database londonFS - Far Sync (inactive) Fast-Start Failover: DISABLED Configuration Status: WARNING
In addition, the current status of the configuration is provided; if there is a problem with the configuration, a warning message appears. Indentation is used in the database listing to show redo sources and redo destinations.
Using the SHOW DATABASE VERBOSE DGMGRL Command to Monitor the Configuration
Use the SHOW DATABASE VERBOSE DGMGRL command to view a brief summary and the properties of the specified database. An example of the output:
DGMGRL> show database verbose london
Database - london
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag 0 seconds(computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 1.52 MByte/s
Real Time Query: OFF
Instance(s):
london
Properties:
DGConnectIdentifier = 'london'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = '(Local:londonFS SYNC)'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '10'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'boston, london'
LogFileNameConvert = 'boston, london'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host03)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=london_DGMGRL.example.com)(INSTANCE_NAME=london)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arch_%t_%s_%r.log'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
Viewing Standby Redo Log Information in V$LOGFILE
Obtain information about the standby redo log by querying V$LOGFILE.
SQL> SELECT group#, member 2 FROM v$logfile 3 WHERE type = 'STANDBY'; GROUP# STATUS MEMBER ------ ------ ---------------------------------- 4 +DATA/boston/onlinelog/group_4.278.711989145 5 +DATA/boston/onlinelog/group_5.279.711989151 6 +DATA/boston/onlinelog/group_6.280.711989159 7 +DATA/boston/onlinelog/group_7.281.711989165
The STATUS column contains the following possible values:
- INVALID: The file is inaccessible.
- STALE: The file contents are incomplete.
- DELETED: The file is no longer used.
- Null: The file is in use.
The CON_ID column of this view provides information useful in a multitenant architecture.Possible values for the CON_ID column include:
- 0: This value is used for rows containing data that pertain to the entire CDB This value : This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
- 1: This value is used for rows containing data that pertain to only the root
- n: Where n is the applicable container ID for the rows containing data
Viewing Standby Redo Log Information in V$STANDBY LOG V$STANDBY_LOG
Query V$STANDBY_LOG to obtain information about the standby redo log.
SQL> SELECT group#, dbid, archived, status
2 FROM v$standby_log;
GROUP# DBID ARC STATUS
---------- -------------- --- ----------
4 UNASSIGNED NO UNASSIGNED
5 3303427449 YES ACTIVE
6 UNASSIGNED YES UNASSIGNED
7 UNASSIGNED YES UNASSIGNED
Columns of interest include:
- GROUP#: Log group number
- DBID: Database ID of the primary database to which the standby redo log file is assigned. If the standby redo log file is unassigned, the value UNASSIGNED is displayed.
- ARCHIVED: Contains a value of YES or NO. See STATUS for additional information.
- STATUS: Contains a value of UNASSIGNED or ACTIVE:
- UNASSIGNED: If the value of ARCHIVED is NO, the standby redo log was archived and is again available. If the value of ARCHIVED is YES, yg the standby redo log was never used and is available.
- ACTIVE: If the value of ARCHIVED is NO, the standby redo log is complete and waiting to be archived. If the value of ARCHIVED is YES, the standby redo log is currently being written to and is not ready to be archived.
The CON_ID column of this view provides information useful in a multitenant architecture.
Identifying Destination Settings
The VALID_NOW column in V$ARCHIVE_DEST indicates whether the archive log destination is used.
SQL> SELECT dest_id,valid_type,valid_role,valid_now 2 FROM v$archive_dest; DEST_ID VALID_TYPE VALID_ROLE VALID_NOW ------- --------------- ------------ -------------- 1 ALL LOGFILES ALL ROLES YES 2 STANDBY_LOGFILE STANDBY_ROLE WRONG VALID_TYPE 3 ONLINE_LOGFILE STANDBY_ROLE WRONG VALID_ROLE 4 ALL_LOGFILES ALL_ROLES UNKNOWN 5 ALL_LOGFILES ALL_ROLES UNKNOWN 6 ALL_LOGFILES ALL_ROLES UNKNOWN 7 ALL LOGFILES ALL ROLES UNKNOWN … 9 ALL_LOGFILES ALL_ROLES UNKNOWN 30 ALL_LOGFILES ALL_ROLES UNKNOWN 31 ALL_LOGFILES ALL_ROLES YES 31 rows selected.
The column contains the following values:
- YES: The archive log destination is appropriately defined for the current database role
- WRONG VALID_TYPE: The archive log destination is appropriately defined for the current database role but cannot be used. For example, LOG_ARCHIVE_DEST_2 is set to (STANDBY_LOGFILES,STANDBY_ROLE), but WRONG VALID_TYPE is returned because this standby destination does not have an implemented standby redo log.
- WRONG VALID_ROLE: The archive log destination is not appropriately defined for the current database role. For example, LOG_ARCHIVE_DEST_3 is set to (ONLINE LOGFILES ONLINE_LOGFILES,STANDBY ROLE STANDBY_ROLE)but WRONG VALID ROLE VALID_ROLE i t db s returned because this destination is currently running in the primary database role
- UNKNOWN: The archive log destination is not defined.
The VALID_TYPE and VALID_ROLE columns are the values from the VALID_FOR attribute that is specified for each archive log destination.
Setting the LOG_ARCHIVE_TRACE Initialization Parameter
Set this parameter to trace the transmission of redo data to the standby system. To enable, disable, or modify the LOG_ARCHIVE_TRACE parameter in a primary database,issue the ALTER SYSTEM SET LOG ARCHIVE TRACE=trace level statement while the database is open or mounted. If you change the value of this parameter dynamically with an ALTER SYSTEM statement, the changes take effect at the start of the next archive operation.
Set this parameter to an integer value to see the progression of redo log archiving to the standby system.
– On the primary database, processes write an audit trail of the archived logs sent to the standby system into a trace file and information into the alert log.
– On the standby database, processes write an audit trail of the archived logs received from the primary database into a trace file and information into the alert log.
The integer values for the LOG_ARCHIVE_TRACE parameter represent levels of tracing data.In general, a higher level indicates more detailed information. 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.
The following integer levels are available:
| Level | Meaning |
|---|---|
| 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 | Tracks RFS logical client |
| 512 | Tracks LGWR redo shipping network activity |
| 1024 | Tracks RFS physical client |
| 2048 | Tracks ARCn or RFS ping heartbeat |
| 4096 | Tracks real-time apply activity |
| 8192 | Tracks redo apply activity (media recovery or physical standby) |
| 16384 | Tracks redo transport buffer management |
| 32768 | Tracks LogMiner dictionary |
Viewing Redo Transport Errors by Querying by Querying V$ARCHIVED DEST V$ARCHIVED_DEST
If the standby site is not receiving redo data, query the V$ARCHIVE_DEST view and check for error messages.
SQL> SELECT DEST_ID "ID", STATUS "DB_status",
DESTINATION "Archive_dest", ERROR "Error" FROM
V$ARCHIVE_DEST WHERE DEST_ID <=5;
ID DB status Archive dest Error
-- --------- -------------------- ----------------------
1 VALID /u01/app/oracle/
fast_recovery_area/
london
2 ERROR standby1 ORA-16012: Archivelog
standby database
identifier mismatch
3 INACTIVE
4 INACTIVE
5 INACTIVE
5 rows selected.
If the output of the query does not help you, check the following list of possible issues. If any of the following conditions exist, redo transport services will fail to transmit redo data to the standby database:
- The service name for the standby instance is not configured correctly in the tnsnames.ora file for the primary database.
- The Oracle Net service name specified by the LOG_ARCHIVE_DEST_n parameter for the primary database is incorrect.
- The LOG_ARCHIVE_DEST_STATE_n parameter for the standby database is not set to the value ENABLE.
- The listener.ora file has not been configured correctly for the standby database.
- The listener is not started at the standby site.
- The standby instance is not started.
- You have added a standby archiving destination to the primary SPFILE or text initialization parameter file, but have not yet enabled the change.
- Redo transport authentication has not been configured properly.
Evaluating Redo Data by Querying by Querying V$DATAGUARD STATS
Query V$DATAGUARD_STATS to evaluate each standby database in terms of the currency of the data in the standby database and the time it takes to perform a role transition if all available redo data is applied to the standby database.
SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ---------------------- ------------ -------------------- transport lag +00 00:00:00 10/08/2013 20:07:05 apply lag +00 00:00:00 10/08/2013 20:07:05 apply finish time +00 00:00:00 10/08/2013 20:07:05 estimated startup time 10 10/08/2013 20:07:05
V$DATAGUARD_STATS displays the amount of redo data generated by the primary database that is not yet available on the standby database. This information enables you to determine how much redo data would be lost if the primary database were to crash when you queried this view.
Viewing Data Guard Status Information by Querying by Querying V$DATAGUARD STATUS
Query V$DATAGUARD_STATUS to view messages that were recently written to the alert log or server process trace files that concern physical standby databases or redo transport services for all standby database types.
SQL> SELECT timestamp, facility, message FROM v$dataguard_status ORDER by timestamp; TIMESTAMP FACILITY --------- ------------------------ MESSAGE -------------------------------------------------------- … ARC9: Beginning to archive thread 1 sequence 122 (5336651-5404240) 08-OCT-13 Log Apply Services Media Recovery Waiting for thread 1 sequence 123 (in transit) 08-OCT-13 Log Transport Services ARC9: Completed archiving thread 1 sequence 122 (0-0)
Monitoring Redo Apply by Querying by Querying V$MANAGED STANDBY
Query V$MANAGED_STANDBY to view information about Redo Apply and redo transport status on a physical standby database.
SQL> SELECT process, status, group#, thread#, sequence# 2 FROM v$managed_standby 3 order by process, group#, thread#, sequence#; PROCESS STATUS GROUP# THREAD# SEQUENCE# --------- ------------ ---------- ---------- ---------- ARCH CLOSING 4 1 142 ARCH CLOSING 4 1 146 ARCH CLOSING 4 1 148 ARCH CLOSING 5 1 141 ARCH CLOSING 5 1 147 MRP0 APPLYING_LOG N/A 1 149 RFS IDLE 2 1 149 RFS IDLE N/A 0 0 RFS IDLE N/A 0 0
Monitoring SQL Apply by Querying by Querying V$LOGSTDBY TRANSACTION
Query V$LOGSTDBY_TRANSACTION to view information about the transactions that are actively being processed by SQL Apply.
SQL> SELECT primary_xid, type, 2 mining_status, apply_status 3 FROM v$logstdby_transaction;
The transaction identifiers shown in this view correspond to transaction identifiers assigned at the primary database and do not correspond to the transactions that are active at the logical standby database. Query the V$TRANSACTION view on the logical standby database for information about transactions that are active in the logical standby database, including those that were created as part of SQL Apply.