Data Guard相关的动态性能视图

1.查看进程的活动状况---v$managed_standby

该视图就是专为显示standby数据库相关进程的当前状态信息，例如：

SQL> select process,client_process,sequence#,status from v$managed_standby;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH             39 CLOSING
ARCH      ARCH             40 CLOSING
RFS       LGWR             41 IDLE
RFS       ARCH              0 IDLE
MRP0      N/A              41 WAIT_FOR_LOG
RFS       N/A               0 IDLE

6 rows selected.

通过上述查询可以得知primary开了两个归档进程，使用lgwr同步传输方式与standby通信，已经接收完40的日志，正等待41。

 

2.确认redo应用进度---v$archive_dest_status

该视图显示归档文件路径配置信息及redo的应用情况等，例如：

SQL> select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name  from v$archive_dest_status where status='VALID';

DEST_NAME            ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# DB_UNIQUE_NAME
-------------------- ---------------- ------------- --------------- ------------ ------------------------------
LOG_ARCHIVE_DEST_1                  1            40               0            0 10gstandby
LOG_ARCHIVE_DEST_2                  0             0               0            0 10gpri
STANDBY_ARCHIVE_DEST                1            39               1           39 NONE

 

3.检查归档文件路径及创建信息---v$archived_log

该视图查询standby数据库归档文件的一些附加信息，比如文件创建时间啦，创建进程啦，归档序号啦，是否被应用啦之类，例如：

SQL> select name,creator,sequence#,applied,completion_time from v$archived_log;

NAME                                                                   CREATOR  SEQUENCE# APP COMPLETIO
---------------------------------------------------------------------- ------- ---------- --- ---------
/u01/app/oracle/oradata/orcl/archivelog/1_13_793805797.dbf             ARCH            13 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_14_793805797.dbf             ARCH            14 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_15_793805797.dbf             ARCH            15 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_16_793805797.dbf             ARCH            16 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_17_793805797.dbf             ARCH            17 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_18_793805797.dbf             ARCH            18 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_19_793805797.dbf             ARCH            19 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_20_793805797.dbf             ARCH            20 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_21_793805797.dbf             ARCH            21 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_22_793805797.dbf             ARCH            22 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_23_793805797.dbf             ARCH            23 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_24_793805797.dbf             ARCH            24 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_25_793805797.dbf             ARCH            25 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_26_793805797.dbf             ARCH            26 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_27_793805797.dbf             ARCH            27 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_28_793805797.dbf             ARCH            28 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_29_793805797.dbf             ARCH            29 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_30_793805797.dbf             ARCH            30 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_31_793805797.dbf             ARCH            31 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_32_793805797.dbf             ARCH            32 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_33_793805797.dbf             ARCH            33 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_34_793805797.dbf             ARCH            34 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_35_793805797.dbf             ARCH            35 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_36_793805797.dbf             ARCH            36 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_37_793805797.dbf             ARCH            37 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_38_793805797.dbf             ARCH            38 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_39_793805797.dbf             ARCH            39 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_40_793805797.dbf             ARCH            40 YES 18-JAN-13

28 rows selected.

4.查询归档历史---v$log_history

该视图查询standby库中所有已被应用的归档文件信息(不论该归档文件是否还存在)，例如：

 

SQL> select first_time,first_change#,next_change#,sequence# from v$log_history;

FIRST_TIM FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#
--------- ------------- ------------ ----------
12-SEP-12        446075       477821          1
12-SEP-12        477821       500567          2
20-SEP-12        500567       500632          3
20-SEP-12        500632       500635          4
20-SEP-12        500635       501761          5
20-SEP-12        501761       501943          6
20-SEP-12        501943       507264          7
20-SEP-12        507264       509553          8
20-SEP-12        509553       510248          9
20-SEP-12        510248       510707         10
20-SEP-12        510707       510708         11
20-SEP-12        510708       511515         12
20-SEP-12        511515       531605         13
20-SEP-12        531605       532507         14
20-SEP-12        532507       532606         15
20-SEP-12        532606       532667         16
20-SEP-12        532667       532717         17
20-SEP-12        532717       532730         18
20-SEP-12        532730       532969         19
20-SEP-12        532969       534190         20
20-SEP-12        534190       534200         21
20-SEP-12        534200       534324         22
20-SEP-12        534324       534724         23
17-JAN-13        534724       536874         24
17-JAN-13        536874       539504         25
17-JAN-13        539504       539620         26
17-JAN-13        539620       539739         27
17-JAN-13        539739       539821         28
17-JAN-13        539821       539884         29
17-JAN-13        539884       541323         30
17-JAN-13        541323       541324         31
17-JAN-13        541324       543867         32
17-JAN-13        543867       546890         33
17-JAN-13        546890       546902         34
17-JAN-13        546902       546917         35
17-JAN-13        546917       546992         36
17-JAN-13        546992       546993         37
17-JAN-13        546993       547203         38
17-JAN-13        547203       547225         39
17-JAN-13        547225       585657         40

40 rows selected.

 

5.查询当前数据的基本信息---v$database信息。

例如，查询数据库角色，保护模式，保护级别等：

 

SQL> select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;

DATABASE_ROLE    DB_UNIQUE_NAME                 OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------------------------ ---------- -------------------- -------------------- --------------------
PHYSICAL STANDBY 10gstandby                     MOUNTED    MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY NOT ALLOWED

6.检查应用模式(是否启用了实时应用)---v$archive_dest_status

查询v$archive_dest_status视图，如果打开了实时应用，则recovery_mode会显示为：MANAGED REAL TIME APPLY，例如：

SQL> select recovery_mode from v$archive_dest_status where dest_id=2;

RECOVERY_MODE
-----------------------
MANAGED

 

7.Data guard事件---v$dataguard_status

该视图显示那些被自动触发写入alert.log或服务器trace文件的事件。通常是在你不便访问到服务器查询alert.log时，可以临时访问本视图查看一些与dataguard相关的信息，例如：

SQL> select message from v$dataguard_status;

MESSAGE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 23222
RFS[1]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to MAXIMUM AVAILABILITY mode
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 23224
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/u01/app/oracle/oradata/orcl/redo04.log'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 23226
RFS[3]: Identified database type as 'physical standby'
RFS[3]: Successfully opened standby log 5: '/u01/app/oracle/oradata/orcl/redo05.log'
Media Recovery Start: Managed Standby Recovery
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /u01/app/oracle/oradata/orcl/archivelog/1_37_793805797.dbf
Media Recovery Waiting for thread 1 sequence 38 (in transit)
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 23232
RFS[4]: Identified database type as 'physical standby'
Attempt to start background Managed Standby Recovery process
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 5: '/u01/app/oracle/oradata/orcl/redo05.log'
Media Recovery Log /u01/app/oracle/oradata/orcl/archivelog/1_38_793805797.dbf
Media Recovery Waiting for thread 1 sequence 39 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/u01/app/oracle/oradata/orcl/redo04.log'
Media Recovery Log /u01/app/oracle/oradata/orcl/archivelog/1_39_793805797.dbf
Media Recovery Waiting for thread 1 sequence 40 (in transit)
Managed Standby Recovery Canceled
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 /u01/app/oracle/oradata/orcl/redo01.log
Clearing online redo logfile 1 complete
Media Recovery Waiting for thread 1 sequence 40 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 5: '/u01/app/oracle/oradata/orcl/redo05.log'
Media Recovery Log /u01/app/oracle/oradata/orcl/archivelog/1_40_793805797.dbf
Media Recovery Waiting for thread 1 sequence 41 (in transit)

56 rows selected.