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.