DB version: 19c
OS : RHEL 7.9
My shop has archive overflow configured like below.
log_archive_dest_1 => LOCATION=/oradata/fra/CDB_MKS/archivelog alternate=log_archive_dest_3 max_failure=1 reopen=300
log_archive_dest_3 => LOCATION=/dbdata_nfs/archoverflow alternate=log_archive_dest_1 valid_for=(all_logfiles,all_roles)
In one my DBs, during the initial configuration, somebody forgot to add "alternate=log_archive_dest_1" bit when setting log_archive_dest_3.
Because of this, when the space was freed up in log_archive_dest_1, Oracle did not automatically revert archiving back to LOG_ARCHIVE_DEST_1.
So, I manually reverted it back using:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ALTERNATE ;
System altered.
-- Now, the following query accurately shows the status of the archive destinations
SQL> 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
3 LOG_ARCHIVE_DEST_3 ALTERNATE
And the redo logs are correctly getting archived to LOG_ARCHIVE_DEST_1.
But, ARCHIVE LOG LIST command still shows the alternate location even 2 hours after running "ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ALTERNATE ;" . Why is this ?
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dbdata_nfs/archoverflow
Oldest online log sequence 2367
Next log sequence to archive 2370
Current log sequence 2370
SQL>