In Oracle : Monitor a Standby Database (Oracle 10g)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Replication

-> Monitor a Standby Database (Oracle 10g)

 

 Monitor a Standby Database (Oracle 10g)

 

 

1. Determine the status of redo log files

2. Determine the most recent archived redo log file

3. Determine the most recent archived redo log file at each destination

4. Find out if archived redo log files have been received

5. Monitoring the Performance of Redo Transport Services

6. Dynamic Performance Views for Standby database

1. Determine the status of redo log files

SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;

 

 

2. Determine the most recent archived redo log file

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

 

 

3. Determine the most recent archived redo log file at each destination

SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
             FROM V$ARCHIVE_DEST_STATUS
           WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

 

 

4. Find out if archived redo log files have been received

 

You can issue a query at the primary database to find out if an archived redo log file was not received at a particular site. Each destination has an ID number associated with it. You can query the DEST_ID column of the V$ARCHIVE_DEST fixed view on the primary database to identify each destination's ID number.

Assume the current local destination is 1, and one of the remote standby destination IDs is 2. To identify which log files are missing at the standby destination, issue the following query:

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE#

            FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL 
                         WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# 

                                                                                        FROM V$ARCHIVED_LOG 
                                                                                      WHERE DEST_ID=2 
                                                                                          AND THREAD# = LOCAL.THREAD#);

For details about monitoring the archiving status of the primary database click here.

 

 

5. Monitoring the Performance of Redo Transport Services

 

The waits related to a standby database can be found in V$SYSTEM_EVENT

 

Events for Destinations Configured with the ARCH Attribute

Wait Event

Monitors the Amount of Time Spent By . . .

ARCH wait on ATTACH

All ARCn processes to spawn an RFS connection.

ARCH wait on SENDREQ

All ARCn processes to write the received redo data to disk as well as open and close the remote archived redo log files.

ARCH wait on DETACH

All ARCn processes to delete an RFS connection.


Wait Events for Destinations Configured with the LGWR SYNC Attributes

Wait Event

Monitors the Amount of Time Spent By . . .

LGWR wait on LNS

The LGWR process waiting to receive messages from the LNSn process.

LNS wait on ATTACH

All network servers to spawn an RFS connection.

LNS wait on SENDREQ

All network servers to write the received redo data to disk as well as open and close the remote archived redo log files.

LNS wait on DETACH

All network servers to delete an RFS connection.

 

Wait Events for Destinations Configured with the LGWR ASYNC Attributes

Wait Event

Monitors the Amount of Time Spent By . . .

LNS wait on DETACH

All network servers to delete an RFS connection.

LNS wait on ATTACH

All network servers to spawn an RFS connection.

LNS wait on SENDREQ

All network servers to write the received redo data to disk as well as open and close the remote archived redo log files.

True ASYNC Control FileTXN Wait

The LNSn process to get hold of the control file transaction during its lifetime.

True ASYNC Wait for ARCH log

The LNSn process waiting to see the archived redo log (if the LNSn process is archiving a current log file and the log is switched out).

Waiting for ASYNC dest activation

The LNSn process waiting for an inactive destination to become active.

True ASYNC log-end-of-file wait

The LNSn process waiting for the next bit of redo after it has reached the logical end of file.

 

6. Dynamic Performance Views for Standby database

 

Several dynamic performance views contain useful information about archived redo logs, as summarized in the following table.

 

Dynamic Performance View

Description

V$DATABASE

Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.

V$ARCHIVED_LOG

Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST

Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES

Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG

Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.

V$LOG

Displays all redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY

Contains log history information such as which logs have been archived and the SCN range for each archived log.


 

In-Oracle.com  -> Oracle DBA -> Oracle Replication

-> Monitor a Standby Database (Oracle 10g)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.