ALTERNATE Archive log destination configuration that can help you – at least it helped me =)

Hi all,

I was working on a database that was waiting on a backup policy to be configured on the Netbackup side, so basically it had no backups. No news here right =)

The dev team did not wait for much to hammer down the database which caused the archive log generation spiked high.

They were planning to work all weekend long and I needed to make sure that the database was available.

I had a spare diskgroup that I could use when the FRA got full but I did not wanted to monitor the space usage along the weekend to change the archive log destination, also did not want set it up ahead of it causing to double the archive log being sent to both locations wasting space.

So, one active location at a particular time and if /when the 1st location filled up, it would switch to the secondary location.

The configuration that helped me with was the ALTERNATE parameter in the log_archive_dest_n.

The parameter “Specifies an alternate archiving destination to be used when the original destination fails.”

If you want you can read the all details of the parameter here 

Most of the configurations using this parameter are related to the Dataguard standby configurations but it works on this scenario as well.

This is how I used it:
*I used the noreopen configuration as I knew that the space issue would not be resolved during the weekend

alter system set log_archive_dest_1='location=use_db_recovery_file_dest noreopen alternate=log_archive_dest_2' scope=both sid'*';
alter system set log_archive_dest_2='+NEW_FLASH´ sid='*';
alter system set log_archive_dest_state_2= 'ALTERNATE' scope=both sid='*';

On the database, you can see the status of the archive destinations:

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

I saw this error in the alert when the space on the 1st destination exhausted (also some hiccups on the database´s services):

ORA-17502: ksfdcre:4 Failed to create file +FLASH
ORA-15041: diskgroup "+FLASH" space exhausted
ORA-16038: log 8 sequence# 1059 cannot be archived
ORA-19504: failed to create file ""

Checking the log_archive_dest parameter status, the 1st one got disabled and only the 2nd was is valid, the hickups were baralley noticed andthe database kept working all the time.

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

When the space issue was resolved, all I needed to do was to enable the 1st location and set the 2nd one again to alternate.

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

Thanks and hope it helps

Elisson Almeida