[zt] Data guard monitor 和常用查詢

8.5.3.1 Monitoring the Process Activities

看程式狀態 

The V$MANAGED_STANDBY view on the standby database site shows you the activities performed by
both redo transport and Redo Apply processes in a Data Guard environment.
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

PROCESS CLIENT_P SEQUENCE# STATUS
-------------- -----------   ---------------- ------------
ARCH         ARCH         0                     CONNECTED
ARCH         ARCH         0                     CONNECTED
MRP0         N/A             204                 WAIT_FOR_LOG
RFS           LGWR        204                  WRITING
RFS           N/A             0                      RECEIVING

可能在這裡出現的程式：
RFS - Remote file server
MRP0 - Detached recovery server process
MR(fg) - Foreground recovery session
ARCH - Archiver process
FGRD
LGWR
RFS(FAL)
RFS(NEXP)

8.5.3.2 Determining the Progress of Redo Apply
The V$ARCHIVE_DEST_STATUS view on either a primary or standby database site provides you
information such as the online redo log files that were archived, the archived redo log
files that are applied, and the log sequence numbers of each. The following query output
shows the standby database is two archived redo log files behind in applying the redo data
received from the primary 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

8.5.3.3 Determining the Location and Creator of the Archived Redo Log Files
還有applied狀態：
SQL> SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME
2> FROM V$ARCHIVED_LOG;

NAME                                                                                      CREATOR SEQUENCE# APP COMPLETIO
-------------------------------------------------------------------------     -------      -------- ---    ---------
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00198.001 ARCH          198 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00199.001 ARCH          199 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00200.001 ARCH          200 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00201.001 LGWR          201 YES 30-MAY-02

8.5.3.5 Viewing the Archived Redo Log History
The V$LOG_HISTORY on the standby site shows you a complete history of the archived redo log,
including information such as the time of the first entry, the lowest SCN in the log,
the highest SCN in the log, and the sequence numbers for the archived redo log files.

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

FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
-------------- ---------------------- -------------------    ----------
02-JAN-08    440636                  463287                1
02-JAN-08   463287                  467566                2
。。。
。。。
07-JAN-08    543320                  545389                20
07-JAN-08    545389                  545428               21
07-JAN-08    545428                  547248                22

22 rows selected.

8.5.3.6 Determining Which Log Files Were Applied to the Standby Database
Standeby上最後applied的log:

Query the V$LOG_HISTORY view on the standby database, which records the latest log
sequence number that was applied. For example, issue the following query:

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG"
2> FROM V$LOG_HISTORY
3> GROUP BY THREAD#;

THREAD#   LAST_APPLIED_LOG
------------- ----------------
      1             967

8.5.3.7 Determining Which Log Files Were Not Received by the Standby Site
在primary server上查詢有哪些日誌沒有被傳輸到Standby:

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
3> WHERE LOCAL.SEQUENCE# NOT IN
5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
6> THREAD# = LOCAL.THREAD#);
THREAD# SEQUENCE#
----------       ----------
1               12
1               13
1               14

查詢DB 的狀態：

1) Primary Server
SQL> col instance format a10
SQL> set linesize 300
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,
2 PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS
3 FROM V$DATABASE;
DATABASE_ROLE    INSTANCE   OPEN_MODE PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ---------- ---------- -------------------- -------------------- --------------------
PRIMARY          dg1        READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY

2) Standby Server
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,
2 PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS
3 FROM V$DATABASE;

DATABASE_ROLE    INSTANCE   OPEN_MODE PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ---------- ---------- -------------------- -------------------- --------------------
PHYSICAL STANDBY dg2        MOUNTED    MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE SESSIONS ACTIVE

8.5.4.2 Accessing the V$MANAGED_STANDBY Fixed View
在Standby 端查詢redo apply和redo transport的狀態
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
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      WAIT_FOR_LOG          1         23          0          0
RFS       IDLE                  0          0          0          0

8.5.4.6 Accessing the V$DATAGUARD_STATUS Fixed View
這個對於troubleshooting很有用啊
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.

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

MESSAGE
----------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC0: Becoming the heartbeat ARCH
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Clearing online redo logfile 1 /u02/oradata/dg1/redo01.log
Clearing online redo logfile 1 complete
Media Recovery Waiting for thread 1 sequence 22

MESSAGE
----------------------------------------------------------------------
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 4374
RFS[1]: Identified database type as 'physical standby'
Media Recovery Log /u02/arch/1_22_642964606.dbf
Media Recovery Waiting for thread 1 sequence 23

17 rows selected.