Delete Archive logs in primary database and then sync primary and standby databases.
Introduction:
This article delves into the careful process of deleting archive logs in a primary database and subsequently synchronizing the primary and standby databases. In a synchronized primary-standby database configuration, such as Oracle Data Guard, archive logs play a pivotal role in ensuring data consistency and facilitating disaster recovery. However, there are scenarios where archive logs need to be managed, including their deletion from the primary database.
Scenario:
Delete archivelogs in primary and then try to make sync between primary and standby.
Solution:
Step 1: Remove archivelogs in primary database.
o1_mf_1_28_jwljqc1w_.arc o1_mf_1_30_jwlk2jp7_.arc o1_mf_1_32_jwlocnmc_.arc o1_mf_1_34_jwlpgcbk_.arc o1_mf_1_36_jwlpqs81_.arc
o1_mf_1_29_jwljzbgw_.arc o1_mf_1_31_jwlnxpbx_.arc o1_mf_1_33_jwlp8lor_.arc o1_mf_1_35_jwlpgck0_.arc o1_mf_1_37_jwlpqv2w_.arc
[oracle@oracle 2021_12_27]$ rm *
Step 2: Check archive log list and switch the log files in primary database.
[oracle@oracle 2021_12_27]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Dec 27 14:23:52 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 38
Next log sequence to archive 40
Current log sequence 40
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 40
Next log sequence to archive 42
Current log sequence 42
Step 3: In standby database it has no effect .
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”,
2 APPL.SEQUENCE# “Last Sequence Applied”,
(ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
3 4 5 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; 6 7 8 9 10
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 37 37 0
Step 4: Now in Primary database change the log archive dest parameter state as defer and then enable .
Switch some log file.
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system switch logfile;
System altered.
Step 5: Stop and Start the MRP process in standby database and check the log sync it get’s synced.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
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 5 6 7 8 9 10
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 40 41 -1
SQL> /
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 42 42 0
Conclusion:
The process of deleting archive logs from a primary database necessitates an understanding of the potential impacts on synchronization with standby databases. While striving to optimize storage, one must tread carefully to ensure that vital data for disaster recovery is preserved.
