Monitoring Log Apply Services on Physical Standby Databases

1 12

1 13 1 14

The preceding example shows the log files that were not received by standby destination 2.

8.5.4 Monitoring Log Apply Services on Physical Standby Databases

To monitor the status of log apply services on a physical standby database, query the fixed views described in this section. You can also monitor the standby database using the Oracle Enterprise Manager GUI.

This section contains the following topics:

■ Accessing the V$DATABASE View

■ Accessing the V$MANAGED_STANDBY Fixed View

■ Accessing the V$ARCHIVE_DEST_STATUS Fixed View

■ Accessing the V$ARCHIVED_LOG Fixed View

■ Accessing the V$LOG_HISTORY Fixed View

■ Accessing the V$DATAGUARD_STATUS Fixed View

Also, see Oracle Database Reference for complete reference information about views.

8.5.4.1 Accessing the V$DATABASE View

Issue the following query to show information about the protection mode, the protection level, the role of the database, and switchover status:

SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

Issue the following query to show information about fast-start failover:

SQL> SELECT FS_FAILOVER_STATUS FSFO_STATUS, FS_FAILOVER_CURRENT_TARGET TARGET_STANDBY, FS_FAILOVER_THRESHOLD THRESHOLD,

FS_FAILOVER_OBSERVER_PRESENT OBS_PRES - FROM V$DATABASE;

8.5.4.2 Accessing the V$MANAGED_STANDBY Fixed View

Query the physical standby database to monitor Redo Apply and redo transport services activity at the standby site.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS 2> FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --- --

---Monitoring the Primary and Standby Databases

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

8.5.4.3 Accessing the V$ARCHIVE_DEST_STATUS Fixed View

To quickly determine the level of synchronization for the standby database, issue the following query on the physical standby database:

SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#

2> FROM V$ARCHIVE_DEST_STATUS;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#

---- - --- ---1 947 ---1 945

The previous query output shows that the standby database is two archived redo log files behind the primary database.

To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view. It will contain the value MANAGED REAL TIME APPLY when real-time apply is enabled, as shown in the following example:

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ; RECOVERY_MODE

---MANAGED REAL TIME APPLY

8.5.4.4 Accessing the V$ARCHIVED_LOG Fixed View

The V$ARCHIVED_LOG fixed view on the physical standby database shows all the archived redo log files received from the primary database. This view is only useful after the standby site starts receiving redo data; before that time, the view is populated by old archived redo log records generated from the primary control file.

For example, you can execute the following SQL*Plus statement:

SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#, 2> NEXT_CHANGE# FROM V$ARCHIVED_LOG;

REGISTRAR CREATOR THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

--- --- --- --- - ---RFS ARCH 1 945 74651 74739 RFS ARCH 1 946 74739 74772 RFS ARCH 1 947 74772 74774

The previous query output shows three archived redo log files received from the primary database.

8.5.4.5 Accessing the V$LOG_HISTORY Fixed View

Query the V$LOG_HISTORY fixed view on the physical standby database to show all the archived redo log files that were applied:

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#

2> FROM V$LOG_HISTORY;

THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

--- --- -

---Monitoring the Primary and Standby Databases

1 945 74651 74739

The previous query output shows that the most recently applied archived redo log file was sequence number 945.

8.5.4.6 Accessing the V$DATAGUARD_STATUS Fixed View

The V$DATAGUARD_STATUS fixed view displays events that would typically be triggered by any message to the alert log or server process trace files.

The following example shows output from the V$DATAGUARD_STATUS view on a primary database:

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

MESSAGE

---ARC0: Archival started

ARC1: Archival started

Archivelog destination LOG_ARCHIVE_DEST_2 validated for no-data-loss recovery

Creating archive destination LOG_ARCHIVE_DEST_2: 'dest2' ARCH: Transmitting activation ID 0

LGWR: Completed archiving log 3 thread 1 sequence 11 Creating archive destination LOG_ARCHIVE_DEST_2: 'dest2' LGWR: Transmitting activation ID 6877c1fe

LGWR: Beginning to archive log 4 thread 1 sequence 12 ARC0: Evaluating archive log 3 thread 1 sequence 11

ARC0: Archive destination LOG_ARCHIVE_DEST_2: Previously completed ARC0: Beginning to archive log 3 thread 1 sequence 11

Creating archive destination LOG_ARCHIVE_DEST_1:

'/oracle/arch/arch_1_11.arc'

ARC0: Completed archiving log 3 thread 1 sequence 11 ARC1: Transmitting activation ID 6877c1fe

15 rows selected.

The following example shows the contents of the V$DATAGUARD_STATUS view on a physical standby database:

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

MESSAGE

---ARC0: Archival started

ARC1: Archival started

RFS: Successfully opened standby logfile 6: '/oracle/dbs/sorl2.log' ARC1: Evaluating archive log 6 thread 1 sequence 11

ARC1: Beginning to archive log 6 thread 1 sequence 11 Creating archive destination LOG_ARCHIVE_DEST_1:

'/oracle/arch/arch_1_11.arc'

ARC1: Completed archiving log 6 thread 1 sequence 11

RFS: Successfully opened standby logfile 5: '/oracle/dbs/sorl1.log' Attempt to start background Managed Standby Recovery process Media Recovery Log /oracle/arch/arch_1_9.arc

10 rows selected.

Tuning the Log Apply Rate for a Physical Standby Database