Misconception of Archive Log Sequences in Data Guard/Different Oracle Archive Log Sequence on Primary to Standby
Misconception of Archive Log Sequences in Data Guard/
Different Oracle Archive Log Sequence on Primary to Standby
*************************
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…
1
2
3
4
5
6
7
8
9
10
11
12
|
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;
|
===================================
We will go in detail to understand why sequence number is showing higher value(Ex: 50) in “v$archived_log” even though the current log sequence(SQL> archive log list) is very low Ex: 5-6.
This behavior because of registering the old incarnation sequences with the recent control file, If you are using FRA or try to register the archive log location when there are old incarnation sequences exist in the same location. The old incarnation sequences can persist if we haven’t cleaned up after several Drills(Fail-over/Switchover). I did several drills before writing this small article how the log sequences will be updated with control file. By thus you cannot estimate or use views to check the synchronization between primary and standby database(s). So consider to cleanup them by several procedures. We will discuss available scenarios.
Now lets check what is the maximum sequence archived on primary with the first query and second query output shows what is the log sequence applied on standby with the GAP.
PRIMARY
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
56
SQL>
DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
---------- -------------- ------------ ----------- -------------- -------
ORCL ORACLE-PRIMARY 56 48 03-FEB/19:17 8
So the maximum archived sequence on primary is “56” and log applied on standby is “48” from primary and the GAP stated as “8”. Initially we start troubleshoot to fix the archive gaps of “8” and after all the troubleshooting part found that there is no archive with “56” on primary and the current log sequences is just “7” from the below output.
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7 SQL>
Here the problem is only from the primary database and you can see from below output what is the maximum sequence applied on the standby database.
STANDBY
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
6
SQL>
So what’s wrong with the view “v$archived_log” ? Of course if you check the “RESETLOGS_CHANGE#” from the “v$archived_log” then you can see multiple “RESETLOGS_CHANGE#” this is because of previous fail-overs the old incarnation sequence numbers are still registered too. So how to cleanup those archives? You have several ways
1) Re-create Control file
— This is not always possible because you need downtime of primary/production database.
2) Uncatalog archive log files.
— By this procedure you can uncatalog all of the archives and after removing old incarnation archives again we can register them as below steps.
RMAN> change archivelog sequence 1 uncatalog;
RMAN> change archivelog all uncatalog;
RMAN> catalog start with ‘archive log location’;
RMAN> change archivelog all uncatalog;
RMAN> catalog start with ‘archive log location’;
3) Clear the section in the controlfile which contains data referencing v$archived_log, May be in view “v$archived_log” it can contain information of destinations 1 to 30, This below procedure describes process of only keeping entries from one distinct location. The package “dbms_backup_restore.resetCfileSection” refers to different sections such as cleanup of “v$archived_log” and cleanup of backups so on.
Now gather information to verify the number of sequences after executing the package.
SQL> select count(*) from v$archived_log; COUNT(*) ---------- 237 SQL>
Run the below DBMS package from “SYS” user.
SQL> execute sys.dbms_backup_restore.resetCfileSection( 11);
PL/SQL procedure successfully completed.
SQL>
Check again how many entries exist in v$archived_log?
SQL> select count(*) from v$archived_log; COUNT(*) ---------- 0 SQL> select sequence#,applied from v$archived_log; no rows selected SQL>
Register existing archive log files with Control file as below. Before this step ensure there are no archive log sequences of old incarnation.
RMAN> catalog start with ‘Archive Log Location’;
RMAN> catalog start with ‘Archive Log Location’;
Note:- Prior to this entire operation, Recommended to take Full backup.
After registering the archive sequences of current “RESETLOGS_CHANGE#” then you can check the sequence numbers of primary and standby database(s) and also synchronization between primary and standby databases.
=======================================================
This comment has been removed by the author.
ReplyDeleteSo far out of all the blogs,I personally feel this blog is just awesome.There are soo many information provided here in this blog. Therefore It is totally amazing...
ReplyDeleteSoftware Testing Services
Software Testing Company
Software Testing Companies
QA Testing Services
Functional Testing Services
Test Automation Services
Functional Testing Company
Performance Testing Services
Security Testing Services
API Testing Services
Thanks for the content
ReplyDeleteI really appreciate this post and I like this very much. I am waiting for new post here and Please keep it up in future..
ReplyDeleteSoftware Testing Services USA
Software Testing Company USA
Functional Testing Services
QA Automation Testing Services
eCommerce Testing Services
Performance Testing Services
Security Testing Services
API Testing Services
Regression Testing Services
Mobile App Testing Services
Enjoyed reading the article above, really explains everything in detail, the article is very inter sting and effective. Thank you and good luck for the upcoming articles soa Online Training
ReplyDeleteI am very happy after interacting with this blog. It's wonderful. You described all the business issues to find the right human resource, including finding Mobile app developers near me. But, freelance portals are a boon for getting out of the complications. I think I must also make a special mention of my trusted partner Eiliana.com who helped my projects get into the right hands with their services.
ReplyDelete