1 查看进程的活动状态。
TESTDG>select process,status,thread#,sequence#,block#,blocks
2 from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 42 1 205
ARCH CLOSING 1 43 6145 1929
ARCH CLOSING 1 40 1 982
ARCH CLOSING 1 41 1 304
RFS IDLE 0 0 0 0
MRP0 WAIT_FOR_LOG 1 44 0 0
RFS IDLE 1 44 5205 2
RFS IDLE 0 0 0 0
已选择8行。
PROCESS：进程名 ARCH ,RFS,MRP0
CLIENT_PROCESS：对应的主库中的进程如 ARCH,LGWR
STATUS: 进程的当前状态
1)ALLOCATED: 正在准备连接主库
2)ATTACHED: 正在连接主库
3)CONNECTED:已经连接主库
4)IDLE:空闲
5)RECEIVING:归档日志接收中
6)OPENING:归档日志处理中
7)CLOSING:归档日志处理完，收尾中
8)WRITING:REDO数据库写向归档文件中
9)WAIT_FOR_LOG:等待新的REDO数据中
10)WAIT_FOR_GAP:归档有中断，正在等待中断的那部分REDO数据.
11)APPLYING_LOG: 应用REDO数据。
THREAD#进程号
SEQUENCE# :归档序列号
BLOCK# :？？
BLOCKS : ？？
TESTDG>col dest_name for a35
TESTDG>select dest_name,archived_thread#,archived_seq#,applied_thread#,
2 applied_seq#,db_unique_name
3 from v$archive_dest_status
4 where status='VALID';
DEST_NAME ARCH_THR# ARCH_SEQ# APP_THR# APP_SEQ# DB_UNIQUE_NAME
------------------ ---------------------------- ------------ ------------------------LOG_ARCHIVE_DEST_1 1 43 0 0 testdg
LOG_ARCHIVE_DEST_2 0 0 0 0 orcl
STANDBY_ARCHIVE_DEST 1 42 1 40 NONE
TESTDG>--检查归档文件路径和创建信息
TESTDG>col name for a35
TESTDG>select name,creator,sequence#,applied,completion_time from v$archived_log;
NAME CREATOR SEQUENCE# APP COMPLETION_TIM
----------------------------------- ------- ---------- --- --------------
D:\ORACLE\ARCHDG\ARC_1_33_728782665.ARC ARCH 33 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_31_728782665.ARC ARCH 31 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_32_728782665.ARC ARCH 32 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_34_728782665.ARC ARCH 34 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_35_728782665.ARC ARCH 35 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_36_728782665.ARC ARCH 36 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_37_728782665.ARC ARCH 37 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_38_728782665.ARC ARCH 38 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_39_728782665.ARC ARCH 39 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_40_728782665.ARC ARCH 40 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_41_728782665.ARC ARCH 41 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_42_728782665.ARC ARCH 42 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_43_728782665.ARC ARCH 43 YES 20-9月 -10
已选择13行。
--查看当前REDO 应用和REDO传输服务的活动状态。
查询物理standby的当前redo应用和redo传输服务的状态。
TESTDG>select process,status,thread#,sequence#,block#,blocks
2 from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 42 1 205
ARCH CLOSING 1 43 6145 1929
ARCH CLOSING 1 40 1 982
ARCH CLOSING 1 41 1 304
RFS IDLE 0 0 0 0
RFS IDLE 1 44 2 38
RFS IDLE 0 0 0 0
已选择7行。
TESTDG>--检查归档历史
TESTDG>select first_time,first_change#,next_change# ,sequence# from v$log_history;
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
-------------- ------------- ------------ ----------
03-9月 -10 886308 921211 1
03-9月 -10 921211 929925 2
03-9月 -10 929925 946708 3
03-9月 -10 946708 967419 4
03-9月 -10 967419 992343 5
04-9月 -10 992343 1015062 6
05-9月 -10 1015062 1057813 7
06-9月 -10 1057813 1088443 8
07-9月 -10 1088443 1095408 9
07-9月 -10 1095408 1123437 10
08-9月 -10 1123437 1152797 11
09-9月 -10 1152797 1178390 12
10-9月 -10 1178390 1178915 13
10-9月 -10 1178915 1178920 14
10-9月 -10 1178920 1213830 15
11-9月 -10 1213830 1252759 16
12-9月 -10 1252759 1284851 17
13-9月 -10 1284851 1324677 18
17-9月 -10 1324677 1359386 19
18-9月 -10 1359386 1386121 20
18-9月 -10 1386121 1411650 21
19-9月 -10 1411650 1432583 22
19-9月 -10 1432583 1454040 23
20-9月 -10 1454040 1454045 24
20-9月 -10 1454045 1459973 25
20-9月 -10 1459973 1462648 26
20-9月 -10 1462648 1468542 27
20-9月 -10 1468542 1496216 28
20-9月 -10 1496216 1498057 29
20-9月 -10 1498057 1504914 30
20-9月 -10 1504914 1511719 31
20-9月 -10 1511719 1516010 32
20-9月 -10 1516010 1524084 33
20-9月 -10 1524084 1526028 34
20-9月 -10 1526028 1527720 35
20-9月 -10 1527720 1530395 36
20-9月 -10 1530395 1530671 37
20-9月 -10 1530671 1531644 38
20-9月 -10 1531644 1532453 39
20-9月 -10 1532453 1533213 40
20-9月 -10 1533213 1533482 41
20-9月 -10 1533482 1533698 42
20-9月 -10 1533698 1538619 43
已选择43行。
--查询最后应用的归档文件
TESTDG>select thread#,max(sequence#) as LAST_APPLIED_LOG
2 FROM V$LOG_HISTORY
3 GROUP BY THREAD#;
THREAD# LAST_APPLIED_LOG
---------- ----------------
1 43
--也可以通过 v$archived_log的APP列获得相同的功能！
TESTDG>select thread#,sequence#,applied from v$archived_log;
THREAD# SEQUENCE# APP
---------- ---------- ---
1 33 YES
1 31 YES
1 32 YES
1 34 YES
1 35 YES
1 36 YES
1 37 YES
1 38 YES
1 39 YES
1 40 YES
1 41 YES
1 42 YES
1 43 YES
已选择13行。
ORCL>--在主库上查看未接收的日志
ORCL>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
4 (select sequence# from v$archived_log where dest_id=2 and thread#=local.thread#);
THREAD# SEQUENCE#
---------- ----------
1 17
1 18
1 19
1 20
1 21
1 22
1 23
1 24
1 25
1 26
1 27
1 28
1 29
1 30
注意：dest_id =N 就是LOG_ARCHIVE_DEST_N 中的那个N.
--检查应用模式(是否启用的实时应用)
TESTDG>select recovery_mode from v$archive_dest_status where dest_id=2;
RECOVERY_MODE
-----------------------
IDLE --没有启用，启用了实时应用则为：MANAGED READ TIME APPLY
---DATAGUARD 事件(v$dataguard_status).该视图显示alert.log和trace文件中记录的事件。
TESTDG>select message from v$dataguard_status;
MESSAGE
-------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: 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 8424
RFS[1]: Identified database type as 'physical standby'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 8308
RFS[2]: Identified database type as 'physical standby'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 8064
RFS[3]: Identified database type as 'physical standby'
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
MESSAGE
----------------------------------------------------------------
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_31_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_32_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_33_728782665.ARC
Media Recovery Waiting for thread 1 sequence 34
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 8656
RFS[4]: Identified database type as 'physical standby'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 8812
MESSAGE
--------------------------------------------------------
RFS[5]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 4: 'D:\ORACLE\TESTDGDATA\STANDBY01.LOG'
RFS[5]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_34_728782665.ARC
Media Recovery Waiting for thread 1 sequence 35 (in transit)
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[6]: Assigned to RFS process 7224
RFS[6]: Identified database type as 'physical standby'
MESSAGE
--------------------------------------------------------------------------------
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_35_728782665.ARC
Media Recovery Waiting for thread 1 sequence 36 (in transit)
MRP0: Background Media Recovery cancelled with status 16037
MRP0: Background Media Recovery process shutdown
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
Media Recovery Waiting for thread 1 sequence 36 (in transit)
MESSAGE
--------------------------------------------------------------------------------
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_36_728782665.ARC
Media Recovery Waiting for thread 1 sequence 37 (in transit)
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_37_728782665.ARC
Media Recovery Waiting for thread 1 sequence 38 (in transit)
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_38_728782665.ARC
MESSAGE
--------------------------------------------------------------------------------
MRP0: Background Media Recovery terminated with error 1274
MRP0: Background Media Recovery process shutdown
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[7]: Assigned to RFS process 10272
RFS[7]: Identified database type as 'physical standby'
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_38_728782665.ARC
Media Recovery Waiting for thread 1 sequence 39
MESSAGE
--------------------------------------------------------------------------------
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[8]: Assigned to RFS process 8292
RFS[8]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[9]: Assigned to RFS process 9672
RFS[9]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[8]: Successfully opened standby log 4: 'D:\ORACLE\TESTDGDATA\STANDBY01.LOG'
MESSAGE
--------------------------------------------------------------------------------
RFS[9]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_39_728782665.ARC
Media Recovery Waiting for thread 1 sequence 40 (in transit)
Primary database is in MAXIMUM PERFORMANCE mode
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_40_728782665.ARC
MRP0: Background Media Recovery terminated with error 1274
MRP0: Background Media Recovery process shutdown
Primary database is in MAXIMUM PERFORMANCE mode
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Primary database is in MAXIMUM PERFORMANCE mode
MESSAGE
--------------------------------------------------------------------------------
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_40_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_41_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_42_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_43_728782665.ARC
Media Recovery Waiting for thread 1 sequence 44 (in transit)
MESSAGE
--------------------------------------------------------------------------------
MRP0: Background Media Recovery cancelled with status 16037
MRP0: Background Media Recovery process shutdown
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
Media Recovery Waiting for thread 1 sequence 44 (in transit)
已选择117行。
--查询当前数据库的基本信息如数据库的角色和保护模式，保护级别。
TESTDG>select database_role,db_unique_name,
2 open_mode,protection_mode,
3 protection_level,switchover_status
4 from v$database;
DATABASE_ROLE DB_UNIQUE_ OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
------------- ---------- --------- --------------- ----------------
SWITCHOVER_STATUS
------------------
PHYSICAL STANDBY testdg READ ONLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
NOT ALLOWED
---查看failover后快速启动的信息。
TESTDG>select fs_failover_status ,
2 fs_failover_current_target,
3 fs_failover_threshold,
4 fs_failover_observer_present from v$database;
FS_FAIL_STATUS FS_FAIL_CUR_TARGET FS_FAIL_THRESHOLD FS_FAIL
-------------- ------------------- ------------------ ----
DISABLED 0