Archive

Posts Tagged ‘protection mode’

Monitoring Standby Databases

March 23, 2013 Leave a comment

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. select process,client_process,sequence#,status from v$managed_standby ;

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 LNSnprocess.
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

View Database Description
DBA_LOGSTDBY_EVENTS Logical only Contains information about the activity of a logical standby database. It can be used to determine the cause of failures that occur when SQL Apply is applying redo to a logical standby database.
DBA_LOGSTDBY_HISTORY Logical only Displays the history of switchovers and failovers for logical standby databases in a Data Guard configuration. It does this by showing the complete sequence of redo log streams processed or created on the local system, across all role transitions. (After a role transition, a new log stream is started and the log stream sequence number is incremented by the new primary database.)
DBA_LOGSTDBY_LOG Logical only Shows the log files registered for logical standby databases.
DBA_LOGSTDBY_NOT_UNIQUE Logical only Identifies tables that have no primary and no non-null unique indexes.
DBA_LOGSTDBY_PARAMETERS Logical only Contains the list of parameters used by SQL Apply.
DBA_LOGSTDBY_SKIP Logical only Lists the tables that will be skipped by SQL Apply.
DBA_LOGSTDBY_SKIP_TRANSACTION Logical only Lists the skip settings chosen.
DBA_LOGSTDBY_UNSUPPORTED Logical only Identifies the schemas and tables (and columns in those tables) that contain unsupported data types. Use this view when you are preparing to create a logical standby database.
V$ARCHIVE_DEST Primary, physical, and logical Describes all of the destinations in the Data Guard configuration, including each destination’s current value, mode, and status.Note: The information in this view does not persist across an instance shutdown.
V$ARCHIVE_DEST_STATUS Primary, physical, and logical Displays runtime and configuration information for the archived redo log destinations.Note: The information in this view does not persist across an instance shutdown.
V$ARCHIVE_GAP Physical and logical Displays information to help you identify a gap in the archived redo log files.
V$ARCHIVED_LOG Primary, physical, and logical Displays archive redo log information from the control file, including names of the archived redo log files.
V$DATABASE Primary, physical, and logical Provides database information from the control file. Includes information about fast-start failover (available only with the Data Guard broker).
V$DATABASE_INCARNATION Primary, physical, and logical Displays information about all database incarnations. Oracle Database creates a new incarnation whenever a database is opened with the RESETLOGS option. Records about the current and the previous incarnation are also contained in the V$DATABASE view.
V$DATAFILE Primary, physical, and logical Provides datafile information from the control file.
V$DATAGUARD_CONFIG Primary, physical, and logical Lists the unique database names defined with the DB_UNIQUE_NAME and LOG_ARCHIVE_CONFIGinitialization parameters.
V$DATAGUARD_STATS Primary, physical, and logical Displays how much redo data generated by the primary database is not yet available on the standby database, showing how much redo data could be lost if the primary database were to crash at the time you queried this view. You can query this view on any instance of a standby database in a Data Guard configuration. If you query this view on a primary database, then the column values are cleared.
V$DATAGUARD_STATUS Primary, physical, and logical Displays and records events that would typically be triggered by any message to the alert log or server process trace files.
V$LOG Primary, physical, and logical Contains log file information from the online redo log files.
V$LOGFILE Primary, physical, and logical Contains information about the online redo log files and standby redo log files.
V$LOG_HISTORY Primary, physical, and logical Contains log history information from the control file.
V$LOGSTDBY_PROCESS Logical only Provides dynamic information about what is happening with SQL Apply. This view is very helpful when you are diagnosing performance problems during SQL Apply on the logical standby database, and it can be helpful for other problems.
V$LOGSTDBY_PROGRESS Logical only Displays the progress of SQL Apply on the logical standby database.
V$LOGSTDBY_STATE Logical only Consolidates information from the V$LOGSTDBY_PROCESS and V$LOGSTDBY_STATS views about the running state of SQL Apply and the logical standby database.
V$LOGSTDBY_STATS Logical only Displays LogMiner statistics, current state, and status information for a logical standby database during SQL Apply. If SQL Apply is not running, the values for the statistics are cleared.
V$LOGSTDBY_TRANSACTION Logical only Displays information about all active transactions being processed by SQL Apply on the logical standby database.
V$MANAGED_STANDBY Physical only Displays current status information for Oracle database processes related to physical standby databases.Note: The information in this view does not persist across an instance shutdown.
V$STANDBY_LOG Physical and logical Contains log file information from the standby redo log files.
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, theRC_BACKUP_REDOLOG contains similar information.

6 .Monitoring Apply Lag in a Real-time Query Environment

SQL> SELECT name, value, datum_time, time_computed 
          FROM V$DATAGUARD_STATS WHERE name like 'apply lag';

7. To obtain a histogram that shows the history of apply lag values since the standby instance was last started

SQL>SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag'
                                                          AND COUNT > 0;

8. Using V$DATABASE

The following query displays the data protection mode, data protection level, database role, and switchover status for a primary, physical standby or snapshot standby database:

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE ROLE, 
                                      SWITCHOVER_STATUS FROM V$DATABASE;

The following query displays fast-start failover status:

SQL> SELECT FS_FAILOVER_STATUS "FSFO STATUS", FS_FAILOVER_CURRENT_TARGET TARGET,
     FS_FAILOVER_THRESHOLD THRESHOLD,
     FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT" 
     FROM V$DATABASE;

9. Using V$MANAGED_STANDBY

The following query displays Redo Apply and redo transport status on a physical standby database:

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS 
                                             FROM V$MANAGED_STANDBY;
PROCESS  STATUS   THREAD# SEQUENCE# BLOCK# BLOCKS
-------  --------   ---- ---------- ------   ----
RFS       ATTACHED    1      947      72     72
MRP0 APPLYING_LOG 1 946 10 72

The sample output shows that a RFS process completed archiving a redo log file with a sequence number of 947 and that Redo Apply is actively applying an archived redo log file with a sequence number of 946. Redo Apply is currently recovering block number 10 of the 72-block archived redo log file.

10. Querying V$ARCHIVED_LOG

The following query displays information about archived redo log files that have been received by a physical or snapshot standby database from a primary database:

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# 
                                             FROM V$ARCHIVED_LOG;
THREAD#    SEQUENCE#   FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1            945         74651         74739
1            946         74739         74772
1            947         74772         74795

The sample output shows that three archived redo log files have been received from the primary database.

11. V$LOG_HISTORY

The following query displays archived log history information:

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# 
                                               FROM V$LOG_HISTORY;

12. V$DATAGUARD_STATUS

The following query displays messages generated by Data Guard events that caused a message to be written to the alert log or to a server process trace file:

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

13. V$ARCHIVE_DEST

The following query shows the status of each redo transport destination, and for redo transport destinations that are standby databases, the SCN of the last primary database redo applied at that standby database:

SQL> SELECT DEST_ID, APPLIED_SCN FROM V$ARCHIVE_DEST WHERE 
                                              TARGET='STANDBY';
DEST_ID      STATUS   APPLIED_SCN
---------- ---------   -----------
2            VALID      439054
3            VALID      439054
Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

StartupForce

Where all the Action Is !!!!

Pavan DBA's Blog

The DBA Knowledge Store

ORACLE-BASE - Latest Articles

Where all the Action Is !!!!