v$dataguard_stats 的apply finish time 为空

本文用于查询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