Archive destination full automatic switch location in Oracle

Archive destination full automatic switch location in Oracle

Errors
If our Archive destination is full then Oracle Database is come in hang or stuck stage.
Alternate archiving destination to be used when the original destination fails.
Following error generated:

0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 1934 not archived, no available destinations

Cause
Archive Destination is full.

Solution
Oracle provide us to choose an alternate path for archive destination if its full before our database comes in hang stage.
You need to set the following parameter with alternative location to overcome the common error in Oracle.

Feature ALTERNATIVE Archive Location enable by following settings:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\archive MANDATORY MAX_FAILURE=1 ALTERNATE=LOG_ARCHIVE_DEST_2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=E:\archive MANDATORY ALTERNATE=LOG_ARCHIVE_DEST_1';

Note:
1. If you use alternate destination then transmission of an on-line redo log from the primary site to the standby site fails.
2. If you use REOPEN then it should be 0 or NOREOPEN then it will switch to ALTERNATE location.
3. If REOPEN is non zero value and you specify the MAX_FAILURE is set if it threshold reached then it will switch ALTERNATE location.
4. ALTERNATE attribute does not conflict with a nonzero REOPEN attribute value.

Example with NOREOPEN

alter system set log_archive_dest_1='location=use_db_recovery_file_dest noreopen alternate=log_archive_dest_2' scope=both;
alter system set log_archive_dest_2='location=D:\archive' scope=both;
alter system set log_archive_dest_state_2='ALTERNATE' scope=both;

Check the Status which destination is used

select dest_id, dest_name, status from v$archive_dest_status where status 'INACTIVE';

DEST_ID   DEST_NAME          STATUS
--------- ------------------ ---------
1         LOG_ARCHIVE_DEST_1 VALID
2         LOG_ARCHIVE_DEST_2 UNKNOWN

If primary is destination full, it switch to alternative location using ALTERNATE then you need to enable the first destination it marked as disabled in some situation:

select dest_id, dest_name, status from v$archive_dest_status where status 'INACTIVE';

DEST_ID   DEST_NAME          STATUS
--------- ------------------ ---------
1         LOG_ARCHIVE_DEST_1 DISABLED
2         LOG_ARCHIVE_DEST_2 VALID

-- Enable the first destination
SQL> alter system set log_archive_dest_state_1=enable;

Advertisements