DG - dataguard trouble shooting的相关视图

•V$DATAGUARD_STATS：显示dataguard统计信息

备库
SQL> select * from v$dataguard_stats;
NAME
VALUE
UNIT
TIME_COMPUTED
DATUM_TIME
-------------------------------- ------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag
+00 00:00:00
day(2) to second(0) interval
04/17/2016 06:35:53
04/17/2016 06:35:52
apply lag
+00 00:00:00
day(2) to second(0) interval
04/17/2016 06:35:53
04/17/2016 06:35:52
apply finish time
day(2) to second(3) interval
04/17/2016 06:35:53
estimated startup time
18
second
04/17/2016 06:35:53
SQL>

 

•v$managed_standby：显示physical standby数据库后台进程的当前状态

主库
SQL> select * from v$managed_standby ;
PROCESS
PID STATUS
CLIENT_PROCESS
CLIENT_PID
CLIENT_DBID
GROUP#
RESETLOG_ID
THREAD#
SEQUENCE#
BLOCK#
BLOCKS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS
--------- ---------- -------------- ---------------- ------------ ---------------- ------------ ----------- ---------- ---------- ---------- ---------- ---------- ------------ -------------
ARCH
2912 CLOSING
ARCH
2912
1390893779
1
863113045
1
7032
63488
606
0
0
0
ARCH
2916 CONNECTED
ARCH
2916
1390893779
N/A
0
0
0
0
0
0
0
0
ARCH
2920 CLOSING
ARCH
2920
1390893779
2
863113045
1
7033
53248
1229
0
0
0
ARCH
2924 CLOSING
ARCH
2924
1390893779
3
863113045
1
7031
61440
69
0
0
0
LNS
2928 WRITING
LNS
2928
1390893779
3
863113045
1
7034
29477
1
0
0
0
SQL>
备库
SQL> select * from v$managed_standby ;
PROCESS
PID STATUS
CLIENT_PROCESS
CLIENT_PID CLIENT_DBID
GROUP#
RESETLOG_ID
THREAD#
SEQUENCE#
BLOCK#
BLOCKS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS
--------- --------- --------------- ---------------- ---------- ----------------- ----------- ----------- ---------- ---------- ---------- ---------- ---------- ------------ -------------
ARCH
3044 CLOSING
ARCH
3044
1390893779
6
863113045
1
7030
79872
63
0
0
0
ARCH
2612 CLOSING
ARCH
2612
1390893779
6
863113045
1
7031
61440
69
0
0
0
ARCH
1348 CONNECTED
ARCH
1348
1390893779
N/A
0
0
0
0
0
0
0
0
ARCH
2736 CLOSING
ARCH
2736
1390893779
7
863113045
1
7032
63488
606
0
0
0
ARCH
816 CLOSING
ARCH
816
1390893779
6
863113045
1
7033
53248
1229
0
0
0
RFS
680 IDLE
UNKNOWN
2916
1390893779
N/A
0
0
0
0
0
0
0
0
RFS
1620 IDLE
UNKNOWN
2924
1390893779
N/A
0
0
0
0
0
0
0
0
MRP0
800 WAIT_FOR_LOG
N/A
N/A
N/A
N/A
863113045
1
7034
0
0
0
9
9
RFS
484 IDLE
LGWR
2928
1390893779
3
863113045
1
7034
29050
1
0
0
0
RFS
900 IDLE
UNKNOWN
2912
1390893779
N/A
0
0
0
0
0
0
0
0
已选择10行。
SQL>

 

•v$standby_apply_snapshot：显示当前应用重做数据的速度

SQL> select to_char(snapshot_time,'dd-mon-rr hh24:mi:ss') snapshot_time, thread#, sequence#, applied_scn,apply_rate from V$standby_apply_snapshot;
SNAPSHOT_TIME
THREAD#
SEQUENCE#
APPLIED_SCN
APPLY_RATE
--------------------- --------- ----------- --------------- ----------
15-05-08 15:45:08
1
31527
3273334169433
68234
15-05-08 15:45:08
2
23346
3273334169449
68234
SQL>

 

•v$recovery_progress：跟踪database recovery操作，并评估当前完成当前正在执行的操作所需的时间

SQL> select * from V$RECOVERY_PROGRESS;
START_TIM TYPE
ITEM
UNITS
SOFAR
TOTAL TIMESTAMP COMMENTS
--------- ------------------------- -------------------------------- -------------------- ---------- ---------- --------- ------------------------------
17-APR-16 Media Recovery
Log Files
Files
2
0
17-APR-16 Media Recovery
Active Apply Rate
KB/sec
0
0
17-APR-16 Media Recovery
Average Apply Rate
KB/sec
3
0
17-APR-16 Media Recovery
Maximum Apply Rate
KB/sec
0
0
17-APR-16 Media Recovery
Redo Applied
Megabytes
13
0
17-APR-16 Media Recovery
Last Applied Redo
SCN+Time
0
0 17-APR-16 SCN: 1036677
17-APR-16 Media Recovery
Active Time
Seconds
13
0
17-APR-16 Media Recovery
Apply Time per Log
Seconds
0
0
17-APR-16 Media Recovery
Checkpoint Time per Log
Seconds
0
0
17-APR-16 Media Recovery
Elapsed Time
Seconds
3656
0
10 rows selected.
SQL>

•v$log_history：显示log history，该信息来自控制文件

•v$archived_gap：显示备库的archive gaps信息

•v$asm_diskgroup

•v$database

•v$archive_dest