Data Guard相关的动态性能视图
Data Guard相关的动态性能视图
1.查看进程的活动状况---v$managed_standby
该视图就是专为显示standby数据库相关进程的当前状态信息，例如：
[html]
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。 www.2cto.com
2.确认redo应用进度---v$archive_dest_status
该视图显示归档文件路径配置信息及redo的应用情况等，例如：
[html]
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数据库归档文件的一些附加信息，比如文件创建时间啦，创建进程啦，归档序号啦，是否被应用啦之类，例如：
[html]
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库中所有已被应用的归档文件信息(不论该归档文件是否还存在)，例如：
[html]
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信息。
www.2cto.com
例如，查询数据库角色，保护模式，保护级别等：
[html]
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，例如：
[html]
SQL> select recovery_mode from v$archive_dest_status where dest_id=2;
RECOVERY_MODE
-----------------------
MANAGED
www.2cto.com
7.Data guard事件---v$dataguard_status
该视图显示那些被自动触发写入alert.log或服务器trace文件的事件。通常是在你不便访问到服务器查询alert.log时，可以临时访问本视图查看一些与dataguard相关的信息，例如：
[html]
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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23490154/viewspace-1061860/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
dg 主库丢失归档 解决方案
请登录后发表评论
登录
全部评论
