本文用于查询active DataGuard 的延迟查询
查询standby延迟时，发现
apply finish time为空
SQL> select name,value from v$dataguard_stats;
NAME VALUE
-------------------------------- --------------------
transport lag +00 00:00:00
apply lag +00 00:00:00
apply finish time
estimated startup time 14
这是个BUG，适用于所有平台的
12.1.0.2 (Server Patch Set)
11.2.0.4
Bug 20468490 - In V$DATAGUARD_STATS the statistic with NAME "apply finish time" may be Null under the VALUE column (文档 ID 20468490.8)
Bug 20468490 : APPLY FINISH TIME IS ALWAYS NULL
中提到：
DIAGNOSTIC ANALYSIS:
--------------------
There are no disabled threads
Standby redo log files will be used
This is the case with realtime apply and normal apply
In v$recovery_progress everythink has a valid value
Standby redo log files have been recreated
Standby controlfiles have been recreated
那么怎么看apply是不是正常的？
其他的查询方法：
col TYPE for a20
col UNITS for a20
col COMMENTS for a20
select * from v$recovery_progress;
1
SQL> /
2
3
START_TIM TYPE ITEM UNITS SOFAR TOTAL TIMESTAMP COMMENTS
4
--------- -------------------- -------------------------------- -------------------- ---------- ---------- --------- --------------------
5
06-AUG-17 Media Recovery Log Files Files 32 0
6
06-AUG-17 Media Recovery Average Apply Rate KB/sec 893 0
7
06-AUG-17 Media Recovery Redo Applied Megabytes 72896 0
8
06-AUG-17 Media Recovery Last Applied Redo SCN+Time 0 0 07-AUG-17 SCN: 58937171789
9
06-AUG-17 Media Recovery Apply Time per Log Seconds 1550 0
10
06-AUG-17 Media Recovery Checkpoint Time per Log Seconds 0 0
11
06-AUG-17 Media Recovery Elapsed Time Seconds 83532 0
12
06-AUG-17 Media Recovery Standby Apply Lag Seconds 0 0
13
14
8 rows selected.
多切换几次，看看值的变化情况
首先通过dbms_flashback.get_system_change_number 可以获得系统当前的SCN值：
SQL> col scn for 9999999999999
SQL> select dbms_flashback.get_system_change_number scn from dual;
SCN
--------------
8908390522972
通过scn_to_timestamp函数可以将SCN转换为时间戳:
SQL> select scn_to_timestamp(8908390522972) scn from dual;
SCN
---------------------------------------------------------------------------
05-JAN-07 10.56.30.000000000 AM
再通过timestamp_to_scn可以将时间戳转换为SCN:
SQL> select timestamp_to_scn(scn_to_timestamp(8908390522972)) scn from dual;
SCN
--------------
8908390522972
