Solution for Different Oracle Archive Log Sequence on Primary to Standby

Few days ago for a difficult reasons to explain i lost a production an Oracle database and i had to restore it for a tape backup.(we lost few hours of data). I had standby database attached to the production database that was in sync and it got corrupted as well  meaning that i had to rebuild my standby from scratch. Well so far so good i put the production database back online and duplicated it do a standby as it was before but in my monitoring dashboard(i am using Nagios as an alternative monitoring tool) the Primary database sequence was way ahead of my Standby database sequence even thou in my Standby database alert.log the recovery process was doing correct recovery and the status of it reached the "transit" state. What the hell is going on ? As my database is replicating the data in the correct manner but still i get this error !  Let's start digging to see why this happens and how to fix it !

  • First i will check my Production Sequence# 
 select max(SEQUENCE#) from v$archived_log;

MAX(SEQUENCE#)
--------------

          3909
  • Next i will check my Standby last applied  Sequence# 
 SELECT max(SEQUENCE#)  FROM V$ARCHIVED_LOG where APPLIED='YES';

MAX(SEQUENCE#)
--------------

           263
 Hmmmm ... how come ?
  •  Let's see now the the archive logs list on my Production database
 archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     263
Next log sequence to archive   265
Current log sequence           265
Well here is the problem ! My logs sequence have been reinitialized(reset), and this was my mistake  when i restored the database.
  •  So to make sure this was the case we will check the RESETLOGS_CHANGE# value stored in the v$archived_log table.
 select distinct(to_char(RESETLOGS_CHANGE#)),RESETLOGS_TIME from v$archived_log;

(TO_CHAR(RESETLOGS_CHANGE#))             RESETLOGS
---------------------------------------- ---------

48539962706                              18-DEC-14
51072706835                              26-FEB-15 --my restore date
It is exactly what happened !!! My old incarnation sequence numbers are still registered in my v$archived_log table. (we can fix this by re-creating the controlfile but i don't want any downtime right now) So now that i know that the Standby is getting all the updates and is in sync with my production database i need to fix my monitoring system so that he can recognize that. Here is the script that my Nagios is using to monitor the replication. 
select max(SEQUENCE#) from v$archived_log;
-it uses the result of the query and matches with the result from the standby. This is not going to work, i need to write a query that will recognize the most recent incarnation. So here the query i managed to come up with after doing some goggling... 
set heading off trimspool on
select maxSCN AS PRIMARY
from (select  max(sequence#) almax
           from v\$archived_log
           where resetlogs_change#=(select resetlogs_change# from v\$database where THREAD#=1)
           ) al,
          (select max(sequence#) maxSCN
           from v\$log_history
           where first_time=(select max(first_time) from v\$log_history where THREAD#=1)
           ) lh;
This the query as per Nagios usage(notice the escape \ signs), you can remove the \ and test is inside your Production database. Now my monitoring dashboard is green and my manager is happy .

Copyright 2020. All Rights Reserved