Monitor Logs From Primary and Standby Databases

Query 1:  - Primary

SQL> select thread#, max(sequence#) "Last Primary Seq Generated"

  2  from v$archived_log val, v$database vdb

  3  where val.resetlogs_change# = vdb.resetlogs_change#

  4  group by thread# order by 1;

   THREAD# Last Primary Seq Generated

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

         1                      21893

         2                      16200

         3                      14331

         4                      16682

Query2:  - Physical Standby:

SQL> select thread#, max(sequence#) "Last Standby Seq Received"

  2  from v$archived_log val, v$database vdb

  3  where val.resetlogs_change# = vdb.resetlogs_change#

  4  group by thread# order by 1;

   THREAD# Last Standby Seq Received

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

         1                     21893

         2                     16200

         3                     14331

         4                     16682

Compare the values from Query 1 and Query 2, if there is a difference between the values of "Last Primary Seq Generated" and "Last Standby Seq Received”, the Primary site has isses of shipping logs to the Standby server.

Query 3:  - Physical Standby:

SQL> select thread#, max(sequence#) "Last Standby Seq Applied"

  2  from v$archived_log val, v$database vdb

  3  where val.resetlogs_change# = vdb.resetlogs_change#

  4  and val.applied='YES'

  5  group by thread# order by 1;

   THREAD# Last Standby Seq Applied

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

         1                    21892

         2                    16199

         3                    14330

         4                    16682

Compare the values from Query 2 and Query 3, if there is a difference between the values of "Last Standby Seq Received" and "Last Standby Seq Applied", the standby has issue or logs have not yet applied.