Secondary archive destinations in Oracle
5 min readIntroduction:
Archiving Oracle databases facilitates long-term data retention and storage. However, if space becomes insufficient, it raises concerns about the continuity of data archiving processes.
When the archive destination reaches its capacity, your Oracle Database may encounter issues such as hanging or becoming unresponsive, accompanied by error messages like the following:
ORA-00257: Archiver Error, connect internal only until freed
ORA-16014: Log X Sequence# X not archived, no available destinations
In such scenarios, having an alternate archiving destination becomes imperative to mitigate potential failures in the primary archive destination. The alternate destination serves as a backup option, enabling seamless continuation of archiving operations in case of space constraints, hardware failures, or other issues with the primary destination.
Let’s engage in a brief demonstration to grasp its functionality.
STEP 1,
Let’s start by configuring the primary archive destination to /u01/primary_dest. Currently, it’s at 99% capacity. We’ll replicate this setup for your test.
Then, run the following commands:
SQL> select version from v$instance;
VERSION
-----------------
12.1.0.2.0
SQL> select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS from v$archive_dest where status!='INACTIVE';
DEST_NAME TARGET DESTINATION VALID_ROLE STATUS
--------------------- ---------- -------------------- ------------ ---------
LOG_ARCHIVE_DEST_1 PRIMARY /u01/primary_dest ALL_ROLES VALID
STEP 2,
We’ll establish an alternate archive destination at /u02/secondary_dest to serve as a backup in case the primary destination becomes inaccessible.
oracle@localhost$: df -h /u02/secondary_dest
Filesystem Size Used Avail Use% Mounted on
/dev/ddg2 1004M 18M 936M 2% /u04
SQL> alter system set log_archive_dest_2='LOCATION=/u02/secondary_dest' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2=ALTERNATE scope=both;
System altered.
SQL> select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS,ALTERNATE from v$archive_dest where status!='INACTIVE';
DEST_NAME TARGET DESTINATION VALID_ROLE STATUS ALTERNATE
--------- ----------- ------------------- ------------ ----------- ---------
LOG_ARCHIVE_DEST_1 PRIMARY /u01/primary_dest ALL_ROLES VALID NONE
LOG_ARCHIVE_DEST_2 PRIMARY /u02/secondary_dest ALL_ROLES ALTERNATE NONE
STEP 3,
The primary and alternate archive destinations can be linked, enabling Oracle to switch to the alternate location when the primary reaches full capacity. However, it’s important to note that Oracle won’t automatically revert back to the primary destination once it becomes available again. To enable failback functionality, it’s necessary to properly configure the primary archive destination.
For the LOG_ARCHIVE_DEST_1, let’s designate it as the alternate for the alternate archive destination.
We’ll designate LOG_ARCHIVE_DEST_2 to serve as the alternate archive destination, as illustrated in the following example.
SQL> alter system set log_archive_dest_2='LOCATION=/u02/secondary_dest NOREOPEN
ALTERNATE=LOG_ARCHIVE_DEST_1' scope=both;
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=/u01/primary_dest
NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' scope=both;
System altered.
SQL> select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS,ALTERNATE from v$archive_dest where status!='INACTIVE';
DEST_NAME TARGET DESTINATION VALID_ROLE STATUS ALTERNATE
---------- ------- ----------------- ------------ ---------- ------------------
LOG_ARCHIVE_DEST_1 PRIMARY /u01/primary_dest ALL_ROLES VALID LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_2 PRIMARY /u02/secondary_dest ALL_ROLES ALTERNATE LOG_ARCHIVE_DEST_1
After configuring LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2, we’ll verify that the linkage between the primary and alternate archive destinations is established. This verification ensures that Oracle can successfully fail back to the primary destination once it has available space.
STEP 4,
Following the DML execution, the archive destination has reached 100% usage, as indicated in the provided output. This scenario triggers the failover to the alternate archive destination.
Thread 1 is advanced to log sequence 20 (LGWR switch)
Current log# 2 seq# 20 mem# 0: /u01/app/oracle/ORCL/redo02.log
2023-12-05T04:44:29.216275+05:30
ARC0: Encountered disk I/O error 19502
2023-12-05T04:44:29.216575+05:30
ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1 '/u01/primary_dest/1_19_1058325294.dbf' (error 19502) (ORCL)
2023-12-05T04:44:29.217417+05:30
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_arc0_23015.trc:
ORA-27072: File I/O error
Additional information: 4
Additional information: 350208
Additional information: 446464
ORA-19502: write error on file "/u01/primary_dest/1_19_1267236278.dbf", block number 350208 (block size=512)
2023-12-05T04:44:32.583182+05:30
ORCLP(3):Resize operation completed for file# 11, old size 1172480K, new size 1182720K
2023-12-05T04:44:33.183355+05:30
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_arc0_23015.trc:
ORA-19502: write error on file "/u01/primary_dest/1_19_1267236278.dbf", block number 350208 (block size=512)
ORA-27072: File I/O error
Additional information: 4
Additional information: 350208
Additional information: 446464
ORA-19502: write error on file "/u01/primary_dest/1_19_1267236278.dbf", block number 350208 (block size=512)
STEP 5,
In Oracle version 12c, the archiving process consistently directs data to the primary destination, even successfully facilitating automatic failback to this primary location. Following cleanup and restoration of the primary destination for archiving purposes, Oracle seamlessly resumes archiving redo logs to this destination without any error notifications or failback messages recorded in the alert log file. This smooth continuity is exemplified in the subsequent example derived from the query of v$archived_log.
SQL> select thread#,sequence#,name from v$archive_log;:
THREAD# SEQUENCE# NAME
-------- ---------- -----------------------------------------------
1 2 /u01/primary_dest/1_2_1267236278.dbf
1 3 /u01/primary_dest/1_3_1267236278.dbf
1 4 /u01/primary_dest/1_4_1267236278.dbf
1 5 /u01/primary_dest/1_5_1267236278.dbf
1 6 /u02/secondary_dest/1_6_1267236278.dbf
1 7 /u02/secondary_dest/1_7_1267236278.dbf
1 8 /u02/secondary_dest/1_8_1267236278.dbf
1 9 /u02/secondary_dest/1_9_1267236278.dbf
1 10 /u02/secondary_dest/1_10_1267236278.dbf
1 11 /u02/secondary_dest/1_11_1267236278.dbf
1 12 /u02/secondary_dest/1_12_1267236278.dbf
1 13 /u02/secondary_dest/1_13_1267236278.dbf
1 14 /u02/secondary_dest/1_14_1267236278.dbf
1 15 /u02/secondary_dest/1_15_1267236278.dbf
1 16 /u02/secondary_dest/1_16_1267236278.dbf
1 17 /u02/secondary_dest/1_17_1267236278.dbf
1 18 /u02/secondary_dest/1_18_1267236278.dbf
1 19 /u02/secondary_dest/1_19_1267236278.dbf
1 20 /u02/secondary_dest/1_20_1267236278.dbf
1 21 /u02/secondary_dest/1_21_1267236278.dbf
1 22 /u02/secondary_dest/1_22_1267236278.dbf
1 23 /u02/secondary_dest/1_23_1267236278.dbf
1 24 /u02/secondary_dest/1_24_1267236278.dbf
1 25 /u02/secondary_dest/1_25_1267236278.dbf
1 26 /u02/secondary_dest/1_26_1267236278.dbf
1 27 /u02/secondary_dest/1_27_1267236278.dbf
1 28 /u02/secondary_dest/1_28_1267236278.dbf
1 29 /u01/primary_dest/1_29_1267236278.dbf
1 30 /u01/primary_dest/1_30_1267236278.dbf
1 31 /u01/primary_dest/1_31_1267236278.dbf
1 32 /u01/primary_dest/1_32_1267236278.dbf
31 rows selected.
Conclusion
Oracle has significantly enhanced its ability to automatically manage the switching of archive destinations between the primary and alternate destinations. I trust that my demonstration will enable you to leverage this functionality effectively.
While having an alternate archive destination is beneficial, it’s important to note that Oracle may sporadically log error messages in the alert log file regarding the failure of the primary destination until it becomes operational again.
