Wednesday, July 3, 2013

Monitoring Primary and Physical Standby Databases

Before we go and run script to find out the primary or standby status, below is the list of processes involved in Primary and standby databases that need to understood

There are a number of Oracle background processes that play a key role, first the primary database
  • LGWR - log writer process flushes from the SGA to the ORL files
  • LNS - LogWriter Network Service reads redo being flushed from the redo buffers by the LGWR and performs a network send of the redo to the standby
  • ARCH - archives the ORL files to archive logs, that also used to fulfill gap resolution requests, one ARCH processes is dedicated to local redo log activity only and never communicates with a standby database
The standby database will also have key processes

  • RFS - Remote File Server process performs a network receive of redo transmitted from the primary and writes the network redo to the standby redo log (SRL) files.
  • ARCH - performs the same as the primary but on the standby
  • MRP - Managed Recover Process coordinates media recovery management, recall that a physical standby is in perpetual recovery mode
  • LSP - Logical Standby Process coordinates SQL apply, this process only runs in a logical standby
  • PR0x - recovery server process reads redo from the SRL or archive log files and apply this redo to the standby database.
the above mentioned process can be verified using the SQL script below. Corresponding process related to primary and standby will be displayed depends where this SQL is executed i.e. Primary or Standby

SELECT process,
       status,
       client_process,
       sequence#,
       block#,
       active_agents,
       known_agents
  FROM v$managed_standby;
Monitor Log shipping on Primary database

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12

SELECT DB_NAME,
       HOSTNAME,
       LOG_ARCHIVED,
       LOG_APPLIED,
       APPLIED_TIME,
       LOG_ARCHIVED - LOG_APPLIED LOG_GAP
  FROM (SELECT NAME DB_NAME FROM V$DATABASE),
       (SELECT UPPER (
                  SUBSTR (
                     HOST_NAME,
                     1,
                     (DECODE (INSTR (HOST_NAME, '.'),
                              0, LENGTH (HOST_NAME),
                              (INSTR (HOST_NAME, '.') - 1)))))
                  HOSTNAME
          FROM V$INSTANCE),
       (SELECT MAX (SEQUENCE#) LOG_ARCHIVED
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 1 AND ARCHIVED = 'YES'),
       (SELECT MAX (SEQUENCE#) LOG_APPLIED
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2 AND APPLIED = 'YES'),
       (SELECT TO_CHAR (MAX (COMPLETION_TIME), 'DD-MON/HH24:MI') APPLIED_TIME
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2 AND APPLIED = 'YES');

see if the MRP process is running on standby

SELECT PROCESS from V$MANAGED_STANDBY where PROCESS like 'MRP%';
last log applied and received along with log time on Standby database

SELECT 'Last Applied  : ' Logs,
       TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
  FROM v$archived_log
 WHERE sequence# = (SELECT MAX (sequence#)
                      FROM v$archived_log
                     WHERE applied = 'YES')
UNION
SELECT 'Last Received : ' Logs,
       TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
  FROM v$archived_log
 WHERE sequence# = (SELECT MAX (sequence#) FROM v$archived_log);

Last sequence # received and applied on the standby database

SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
  FROM (  SELECT thread# thrd, MAX (sequence#) almax
            FROM v$archived_log
           WHERE resetlogs_change# = (  SELECT resetlogs_change# FROM v$database)
        GROUP BY thread#) al,
       (  SELECT thread# thrd, MAX (sequence#) lhmax
            FROM v$log_history
           WHERE first_time = (  SELECT MAX (first_time) FROM v$log_history)
        GROUP BY thread#) lh
 WHERE al.thrd = lh.thrd;

Apply rate: To find out the speed of media recovery in a standby database, you can use this query:



set lines 200
col type format a30
col ITEM format a20
col comments format a20

select * from v$recovery_progress;

Archive Lag Histogram: The  V$STANDBY_EVENT_HISTOGRAM view came with 11gR2 and shows the historical occurance of archive lags in terms of seconds.

SQL> col name format a10

SQL> select * from  V$STANDBY_EVENT_HISTOGRAM;

Verify from Primary if the Real-time apply is being used

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

DEST_ID RECOVERY_MODE
------- ------------------------
2 MANAGED REAL TIME APPLY

further reading:

BASH-DBA: Managing a Physical Standby Database
BASH-DBA: Snapshot standby database
BASH-DBA: Open Physical Standby For Read Write Testing and ...
BASH-DBA: Oracle Data Guard : Synchronous vs. Asynchronous ...

No comments:

Post a Comment