Oracle数据字典表和动态性能视图学习之1：V$DATAGUARD

本文通过介绍V$DATAGUARD_STATS这一动态性能视图来获取关于Physical standby database的相关信息。在一套Dataguard环境下，如果需要做failover Role Transition的话，建议先在备库上通过查询V$DATAGUARD_STATS视图来估算failover切换需要的时间(failover time=apply finish time+estimated startup time)。

首先来看两个查询：

查询1，来源于10g Physical standby database ：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select protection_mode,database_role,open_mode from v$database;

PROTECTION_MODE     DATABASE_ROLE     OPEN_MODE
------------------- ----------------- ---------
MAXIMUM PERFORMANCE PHYSICAL STANDBY  MOUNTED

SQL> select * from v$dataguard_stats;

NAME                    VALUE          UNIT                           TIME_COMPUTED
----------------------- -------------- ------------------------------ --------------------
apply finish time       +00 00:00:00.1 day(2) to second(1) interval   20-FEB-2012 14:05:18
apply lag               +00 00:00:15   day(2) to second(0) interval   20-FEB-2012 14:05:18
estimated startup time  161            second                         20-FEB-2012 14:05:18
standby has been open   Y                                             20-FEB-2012 14:05:18
transport lag           +00 00:00:07   day(2) to second(0) interval   20-FEB-2012 14:05:18

SQL> 查询2，来源于11g Physical standby database：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select protection_mode,database_role,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
MAXIMUM PERFORMANCE  PHYSICAL STANDBY READ ONLY WITH APPLY

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  02/20/2012 14:07:37  02/20/2012 14:07:36
apply lag               +00 00:00:00     day(2) to second(0) interval  02/20/2012 14:07:37  02/20/2012 14:07:36
apply finish time       +00 00:00:00.000 day(2) to second(3) interval  02/20/2012 14:07:37
estimated startup time  36               second                        02/20/2012 14:07:37

SQL> 然后，依据上面的输出结果来简单介绍一下V$DATAGUARD_STATS这一动态性能视图的相关信息：

在官方文档上，关于V$DATAGUARD_STATS是这样描述的：该动态性能视图显示出在主库上产生了多少重做日志数据，但是还没有被备库所应用。所以，通过查询该视图可以基本确定如果万一主库出现崩溃的话，备库上将丢失多少重做日志数据。我们可以在一套Dataguard环境下的任一备库的实例上从该视图里获取相关信息，然而，在主库的实例上查询该视图返回的信息都将是空。也就是说，只可以从备库的实例上查询V$DATAGUARD_STATS，从主库实例上是看不到任何有用信息的。

接下来，解释一下各个字段的值信息：

NAME：

•apply lag,该值表示在通过在备库上应用主库传递过来的重做日志与出库同步所延迟的时间。“APPLY LAG: Amount of time that the application of redo data on the standby database lags behind the primary database.”从查询中可以看到第1个延迟15秒，第2个延迟0秒。说明该11g的备库应用重做日志已经与该主库完全同步了。
•transport lag,该值表示在单位时间内主库上产生的重做日志还没有传输到备库上，或者主库上产生的重做日志还没有被备库所应用。从查询中看到第1个10g备库上的日志传输延迟7秒，而第2个11g备库的日志传输延迟为0。
•apply finish time,该值表示在备库上完成应用重做日志所需要的时间。从第1个查询中看到完成应用重做日志还需要0.1秒，第2个查询中则为0，因为已经完全同步。
•estimated startup time,该值表示启动和打开物理备库所需要的时间，该字段不是适用于逻辑备库。 “An estimate of the time needed to start and open the database.”
•standby has been open,该值表示物理备库自从上次启动以来，是否以OPEN READ ONLY方式打开过？该参数值如果是Y，现在需要做FAILOVER，那么就需要先将该物理备库shutdown然后以OPEN READ WRITE方式打开。从第1个查询中，看到该物理备库如果做FAILOVER，那么就需要shutdown--->startup open read write；第2个查询中则没有该记录，因为11g的dataguard可以一边OPEN READ ONLY，一边执行redo apply,也就是11g 的ACTIVE Dataguard。
VALUE：给出各个参数的值。如第1个查询中的，apply finish time值为+00 00:00:00.1，说明该物理备库需要0.1秒的时间来完成应用剩余的重做日志数据。

UNIT：各个参数的时间单元。

TIME_COMPUTED：物理备库上估算各个参数的本地时间。

DATUM_TIME：在物理备库上获取元数据来估算  APPLY LAG 和 TRANSPORT LAG 这两个参数值的本地时间。如果从多次查询中看到该时间值对应的APPLY LAG 和 TRANSPORT LAG 这两个参数值保持不变的话，那么就说明该物理备库已经停止从主库接收到重做数据！该字段是11g中新出现的。

最后，这是在学习dataguard时，新了解和学习的动态性能视图，个人觉得比较有用，就根据自己的理解简单记录之。

 

附录如下网址：http://docs.oracle.com/cd/E18283_01/server.112/e17110/dynviews_1103.htm 的官方说明：

V$DATAGUARD_STATSV$DATAGUARD_STATS displays information about Data Guard metrics when queried on a standby database. No rows are returned when queried on a primary database.

Column Datatype Description
NAME VARCHAR2(32) Name of the metric:
•APPLY FINISH TIME - An estimate of the time needed to apply all received, but unapplied redo from the primary database. If there are one or more redo gaps on the standby database, an estimate of the time needed to apply all received, but unapplied redo up to the end of the last archived redo log before the beginning of the earliest redo gap.

•APPLY LAG - Apply lag is a measure of the degree to which the data in a standby database lags behind the data in the primary database, due to delays in propagating and applying redo to the standby database.

•TRANSPORT LAG - Transport lag is a measure of the degree to which the transport of redo to the standby database lags behind the generation of redo on the primary database. If there are one or more redo gaps on the standby database, the transport lag is calculated as if no redo has been received after the beginning of the earliest redo gap.

•ESTIMATED STARTUP TIME - An estimate of the time needed to start and open the database.
 
VALUE VARCHAR2(64) Value of the metric
UNIT VARCHAR2(30) Unit of measurement
TIME_COMPUTED VARCHAR2(30) Local time at the standby database when the metric was computed
DATUM_TIME VARCHAR2(30) Local time at the standby database when the datum used to compute the metric was received
The APPLY LAG and TRANSPORT LAG metrics are computed based on data that is periodically received from the primary database. An unchanging value in this column across multiple queries indicates that the standby database is not receiving data from the primary database.