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 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.