Standby Out Off sync
Description:
In this blog, we are going to see standby log out off sync how to check and sync the primary and standby logs.
Standby Log Sync:
The primary database logs are synced with the standby database in case any issue delay happens in log sync we restart the MRP process and defer enable the log_dest_2 location in the primary database.
Overall Steps:-
1. Bring Down the standby database.
2. Create a user and do multiple log switches in the primary database.
3. Start the standby database, and check the log difference.
4. primary database defers and enables the dest 2.
5. Restart the MRP process in the standby database.
6. Check the log Sync status.
Step 1:- Bring down the standby database.
SQL> shut immediate
ORA-01109: database not openDatabase dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Step 2:- Create user and do multiple log switches in the primary database.
Check current archive log status:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
Create a user and insert the table.
SQL> create user agent identified by agent;
SQL> alter user agent default tablespace users quota unlimited on users;
SQL> grant connect, resource to agent;
SQL> conn agent/agent
Connected.
SQL> show user
USER is “AGENT”
SQL> create table agent1 as select * from user_tables;
SQL> conn / as sysdba
Connected.
Do multiple log switches.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Check the archive log status.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SQL>
Step 3:- Start the standby database, and check the log difference.
Startup standby database.
SQL> startup
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 738197504 bytes
Database Buffers 503316480 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
Check the log sync different.
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Step 4:-Primary database defer and enable the dest 2.
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
Step 5:- Restart the MRP process in the standby database.
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
Step 6:- Check the log sync status.
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM(SELECT THREAD# , SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#, FIRST_TIME ) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# , SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#, FIRST_TIME ) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 2 3 4
**********************************************************************************
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
