Dataguard Health Check Query's

To check dataguard_status.

  select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database ;

DATABASE_ROLE     DB_UNIQUE_NAME     OPEN_MODE                        PROTECTION_MODE                  PROTECTION_LEVEL                   SWITCHOVER_STATUS

 ----------------             --------------------             -------------------------------       ---------------------------------          -----------------------------------        --- ------------------------

PHYSICAL STANDBY        STANDBY                READ ONLY WITH APPLY     MAXIMUM PERFORMANCE      MAXIMUM PERFORMANCE      NOT ALLOWED

 To check last applied log in dataguard.

  select THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

THREAD#     LAST_APPLIED_LOG

   ----------       ----------------

      1                  294 

To check the log file of database.

  select message from v$dataguard_status;

RFS[193]: Assigned to RFS process 7498

RFS[193]: No standby redo logfiles created for thread 1

RFS[194]: Assigned to RFS process 7504

Media Recovery Log /archive/hubprod/PROD/arch/1_294_896369591.dbf

Media Recovery Waiting for thread 1 sequence 295 (in transit)

To check weather all archive's are applied or not.

  select sequence#,applied from v$archived_log order by sequence#;

  SEQUENCE#       APPLIED

     ----------            ---------

      284                  YES

      285                  YES

      286                  YES

      287                  YES

      288                  YES

      289                  YES

      290                  YES

      291                  YES

      292                  YES

      293                  YES

      294                  YES

To verify the error in dataguard:--

  select error_code,MESSAGE from v$dataguard_status

ERROR_CODE                    MESSAGE

----------                               ----------------------------------------

       0                                       1 sequence 295

       0                                       0 ARC3: Beginning to archive thread 1

       0                                       sequence 294 (5967959586253-5967959586467)

       0                                       ARC3: Completed archiving thread 1

       0                                       sequence 294 (5967959586253-5967959586467)