Data Guard相关的动态性能视图

来源：互联网发布：电脑店铺收银软件编辑：程序博客网时间：2024/06/06 00:08

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

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

SQL> select process,client_process,sequence#,status from v$managed_standby;PROCESS   CLIENT_P  SEQUENCE# STATUS--------- -------- ---------- ------------ARCH      ARCH             39 CLOSINGARCH      ARCH             40 CLOSINGRFS       LGWR             41 IDLERFS       ARCH              0 IDLEMRP0      N/A              41 WAIT_FOR_LOGRFS       N/A               0 IDLE6 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 10gstandbyLOG_ARCHIVE_DEST_2                  0             0               0            0 10gpriSTANDBY_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-1328 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          112-SEP-12        477821       500567          220-SEP-12        500567       500632          320-SEP-12        500632       500635          420-SEP-12        500635       501761          520-SEP-12        501761       501943          620-SEP-12        501943       507264          720-SEP-12        507264       509553          820-SEP-12        509553       510248          920-SEP-12        510248       510707         1020-SEP-12        510707       510708         1120-SEP-12        510708       511515         1220-SEP-12        511515       531605         1320-SEP-12        531605       532507         1420-SEP-12        532507       532606         1520-SEP-12        532606       532667         1620-SEP-12        532667       532717         1720-SEP-12        532717       532730         1820-SEP-12        532730       532969         1920-SEP-12        532969       534190         2020-SEP-12        534190       534200         2120-SEP-12        534200       534324         2220-SEP-12        534324       534724         2317-JAN-13        534724       536874         2417-JAN-13        536874       539504         2517-JAN-13        539504       539620         2617-JAN-13        539620       539739         2717-JAN-13        539739       539821         2817-JAN-13        539821       539884         2917-JAN-13        539884       541323         3017-JAN-13        541323       541324         3117-JAN-13        541324       543867         3217-JAN-13        543867       546890         3317-JAN-13        546890       546902         3417-JAN-13        546902       546917         3517-JAN-13        546917       546992         3617-JAN-13        546992       546993         3717-JAN-13        546993       547203         3817-JAN-13        547203       547225         3917-JAN-13        547225       585657         4040 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 startedARC1: Archival startedARC0: Becoming the 'no FAL' ARCHARC0: Becoming the 'no SRL' ARCHARC1: Becoming the heartbeat ARCHRedo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[1]: Assigned to RFS process 23222RFS[1]: Identified database type as 'physical standby'Primary database is in MAXIMUM AVAILABILITY modeChanging standby controlfile to MAXIMUM AVAILABILITY modeRedo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[2]: Assigned to RFS process 23224RFS[2]: Identified database type as 'physical standby'Primary database is in MAXIMUM AVAILABILITY modeStandby controlfile consistent with primaryPrimary database is in MAXIMUM AVAILABILITY modeStandby controlfile consistent with primaryRFS[2]: Successfully opened standby log 4: '/u01/app/oracle/oradata/orcl/redo04.log'Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[3]: Assigned to RFS process 23226RFS[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 RecoveryManaged Standby Recovery not using Real Time ApplyMedia Recovery Log /u01/app/oracle/oradata/orcl/archivelog/1_37_793805797.dbfMedia Recovery Waiting for thread 1 sequence 38 (in transit)Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[4]: Assigned to RFS process 23232RFS[4]: Identified database type as 'physical standby'Attempt to start background Managed Standby Recovery processPrimary database is in MAXIMUM AVAILABILITY modeStandby controlfile consistent with primaryRFS[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.dbfMedia Recovery Waiting for thread 1 sequence 39 (in transit)Primary database is in MAXIMUM AVAILABILITY modeStandby controlfile consistent with primaryRFS[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.dbfMedia Recovery Waiting for thread 1 sequence 40 (in transit)Managed Standby Recovery CanceledAttempt to start background Managed Standby Recovery processMRP0: Background Managed Standby Recovery process startedManaged Standby Recovery not using Real Time ApplyClearing online redo logfile 1 /u01/app/oracle/oradata/orcl/redo01.logClearing online redo logfile 1 completeMedia Recovery Waiting for thread 1 sequence 40 (in transit)Primary database is in MAXIMUM AVAILABILITY modeStandby controlfile consistent with primaryRFS[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.dbfMedia Recovery Waiting for thread 1 sequence 41 (in transit)56 rows selected.