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#
Next i will check my Standby last applied Sequence#
Hmmmm ... how come ?
Let's see now the the archive logs list on my Production database
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.
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.
-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...
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 .