Scripts to Monitor a Data Guard Environment:

Scripts to Monitor a Data Guard Environment:

 At the end of this topic I have also mentioned some script which you can run in your environment.

View	Database	Description
DBA_LOGSTDBY_EVENTS	Logical only	Contains information about the activity of a logical standby database. It can be used to determine the cause of failures that occur when SQL Apply is applying redo to a logical standby database.
DBA_LOGSTDBY_HISTORY	Logical only	Displays the history of switchovers and failovers for logical standby databases in a Data Guard configuration. It does this by showing the complete sequence of redo log streams processed or created on the local system, across all role transitions. (After a role transition, a new log stream is started and the log stream sequence number is incremented by the new primary database.)
DBA_LOGSTDBY_LOG	Logical only	Shows the log files registered for logical standby databases.
DBA_LOGSTDBY_NOT_UNIQUE	Logical only	Identifies tables that have no primary and no non-null unique indexes.
DBA_LOGSTDBY_PARAMETERS	Logical only	Contains the list of parameters used by SQL Apply.
DBA_LOGSTDBY_SKIP	Logical only	Lists the tables that will be skipped by SQL Apply.
DBA_LOGSTDBY_SKIP_TRANSACTION	Logical only	Lists the skip settings chosen.
DBA_LOGSTDBY_UNSUPPORTED	Logical only	Identifies the schemas and tables (and columns in those tables) that contain unsupported data types. Use this view when you are preparing to create a logical standby database.
V$ARCHIVE_DEST	Primary, physical, snapshot, and logical	Describes all of the destinations in the Data Guard configuration, including each destination's current value, mode, and status. Note: The information in this view does not persist across an instance shutdown.
V$ARCHIVE_DEST_STATUS	Primary, physical, snapshot, and logical	Displays runtime and configuration information for the archived redo log destinations. Note: The information in this view does not persist across an instance shutdown.
V$ARCHIVE_GAP	Physical, snapshot, and logical	Displays information to help you identify a gap in the archived redo log files.
V$ARCHIVED_LOG	Primary, physical, snapshot, and logical	Displays archive redo log information from the control file, including names of the archived redo log files.
V$DATABASE	Primary, physical, snapshot, and logical	Provides database information from the control file. Includes information about fast-start failover (available only with the Data Guard broker).
V$DATABASE_INCARNATION	Primary, physical, snapshot, and logical	Displays information about all database incarnations. Oracle Database creates a new incarnation whenever a database is opened with the RESETLOGS option. Records about the current and the previous incarnation are also contained in the V$DATABASE view.
V$DATAFILE	Primary, physical, snapshot, and logical	Provides datafile information from the control file.
V$DATAGUARD_CONFIG	Primary, physical, snapshot, and logical	Lists the unique database names defined with the DB_UNIQUE_NAME and LOG_ARCHIVE_CONFIGinitialization parameters.
V$DATAGUARD_STATS	Primary, physical, snapshot, and logical	Displays various Data Guard statistics, including apply lag and transport lag. This view can be queried on any instance of a standby database. No rows are returned if queried on a primary database. n.
V$DATAGUARD_STATUS	Primary, physical, snapshot, and logical	Displays and records events that would typically be triggered by any message to the alert log or server process trace files.
V$FS_FAILOVER_STATS	Primary	Displays statistics about fast-start failover occurring on the system.
V$LOG	Primary, physical, snapshot, and logical	Contains log file information from the online redo log files.
V$LOGFILE	Primary, physical, snapshot, and logical	Contains information about the online redo log files and standby redo log files.
V$LOG_HISTORY	Primary, physical, snapshot, and logical	Contains log history information from the control file.
V$LOGSTDBY_PROCESS	Logical only	Provides dynamic information about what is happening with SQL Apply. This view is very helpful when you are diagnosing performance problems during SQL Apply on the logical standby database, and it can be helpful for other problems.
V$LOGSTDBY_PROGRESS	Logical only	Displays the progress of SQL Apply on the logical standby database.
V$LOGSTDBY_STATE	Logical only	Consolidates information from the V$LOGSTDBY_PROCESS and V$LOGSTDBY_STATS views about the running state of SQL Apply and the logical standby database.
V$LOGSTDBY_STATS	Logical only	Displays LogMiner statistics, current state, and status information for a logical standby database during SQL Apply. If SQL Apply is not running, the values for the statistics are cleared.
V$LOGSTDBY_TRANSACTION	Logical only	Displays information about all active transactions being processed by SQL Apply on the logical standby database.
V$MANAGED_STANDBY	Physical and snapshot	Displays current status information for Oracle database processes related to physical standby databases. Note: The information in this view does not persist across an instance shutdown.
V$REDO_DEST_RESP_HISTOGRAM	Primary	Contains the response time information for destinations that are configured for SYNC transport. Note: The information in this view does not persist across an instance shutdown.
V$STANDBY_EVENT_HISTOGRAM	Physical	Contains a histogram of apply lag values for the physical standby. An entry is made in the corresponding apply lag bucket by the Redo Apply process every second. (This view returns rows only on a physical standby database that has been open in real-time query mode.) Note: The information in this view does not persist across an instance shutdown.
V$STANDBY_LOG	Physical, snapshot, and logical	Contains log file information from the standby redo log files.

Run the following queries in your standby database in order to know details about your Data Guard environment.

1) In order to know when your log last applied as well as last received log time issue following query:

select 'Last Applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
  from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
  union
   select 'Last Received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
  from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log);

Example output:

SQL> select 'Last Applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
  2             from v$archived_log
  3                     where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
  4             union
  5                     select 'Last Received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
  6             from v$archived_log
  7                     where sequence# = (select max(sequence#) from v$archived_log);

LOGS             TIME
---------------- ------------------
Last Applied  :  08-MAR-11:19:18:30
Last Received :  22-MAR-11:14:20:28

2) Verify the last sequence# received and the last sequence# applied to standby database by following query:

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

Example output:

SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
  2  from (select thread# thrd, max(sequence#) almax
  3  from v$archived_log
  4  where resetlogs_change#=(select resetlogs_change# from v$database)
  5  group by thread#) al,
  6  (select thread# thrd, max(sequence#) lhmax
  7  from v$log_history
  8  where first_time=(select max(first_time) from v$log_history)
  9  group by thread#) lh
 10  where al.thrd = lh.thrd;

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1             33978             2074

3)In order to know about transport lag time, apply lag and apply finish time issue,

set lines 180
   col name for a40
   col value for a40
   col unit for a40
   select
    NAME,
    VALUE,
    UNIT 
    from v$dataguard_stats
    union
    select null,null,' ' from dual
    union
    select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
   from v$dataguard_stats;

Example output:

SQL>    set lines 180
SQL>    col name for a40
SQL>    col value for a40
SQL>    col unit for a40
SQL>    select
  2      NAME,
  3      VALUE,
  4      UNIT
  5      from v$dataguard_stats
  6      union
  7      select null,null,' ' from dual
  8      union
  9      select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
 10     from v$dataguard_stats;

NAME                                     VALUE                                    UNIT
---------------------------------------- ---------------------------------------- ----------------------------------------
apply finish time                                                                 day(2) to second(3) interval
apply lag                                +13 16:29:57                             day(2) to second(0) interval
estimated startup time                   45                                       second
transport lag                                                                     day(2) to second(0) interval

                                                                                  Time Computed: 03/22/2011 14:24:26

6 rows selected.

4)In order to know the status of the processes involved in Data Guard configuration issue following query,

select process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;

Example output:

SQL> select process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;  

PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ------------- ------------
ARCH      CLOSING      ARCH          33979      61441             0            0
ARCH      CLOSING      ARCH          33976      61441             0            0
ARCH      CONNECTED    ARCH              0          0             0            0
ARCH      CLOSING      ARCH          33977      61441             0            0
ARCH      CLOSING      ARCH          33978      61441             0            0
RFS       IDLE         LGWR          33980      42566             0            0
MRP0      WAIT_FOR_LOG N/A           24128          0            25           25
RFS       IDLE         UNKNOWN           0          0             0            0

8 rows selected.

Where the types of PROCESS may be,
- RFS - Remote file server
- MRP0 - Detached recovery server process
- MR(fg) - Foreground recovery session
- ARCH - Archiver process
- FGRD
- LGWR
- RFS(FAL)
- RFS(NEXP)
- LNS - Network server process

The process status may be,
UNUSED - No active process
ALLOCATED - Process is active but not currently connected to a primary database
CONNECTED - Network connection established to a primary database
ATTACHED - Process is actively attached and communicating to a primary database
IDLE - Process is not performing any activities
ERROR - Process has failed
OPENING - Process is opening the archived redo log
CLOSING - Process has completed archival and is closing the archived redo log
WRITING - Process is actively writing redo data to the archived redo log
RECEIVING - Process is receiving network communication
ANNOUNCING - Process is announcing the existence of a potential dependent archived redo log
REGISTERING - Process is registering the existence of a completed dependent archived redo log
WAIT_FOR_LOG - Process is waiting for the archived redo log to be completed
WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved
APPLYING_LOG - Process is actively applying the archived redo log to the standby database

The client process may be,
Archival - Foreground (manual) archival process (SQL)
ARCH - Background ARCn process
LGWR - Background LGWR process

5) In the primary database ensure that everything is fine by issuing following commands. In case of RAC database ensure that these output is ok across all the instances.

select status,error from v$archive_dest where status <>'INACTIVE';
select * from v$archive_processes where status <> 'STOPPED';
show parameter dest_2
show parameter state_2
alter system set log_archive_dest_state_2=enable scope=both sid='*';
alter system switch logfile;
alter system switch logfile;
select status,error from v$archive_dest where status <>'INACTIVE';