Reset “log_archive_dest_n” does not revert the Archive destination to “db_recovery_file_dest”

This is a really annoying behavior I saw on both Oracle 12.1 and Oracle 19c. It is not (yet?) a bug, but..

The scenario

We got a database Oracle EE 19c in archivelog mode. We have set db_recovery_file_dest to a diskgroup (+FRA01) and we don’t have any log_archive_dest_n set (nor we have log_archive_dest). So:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 3 08:15:38 2020
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.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     18
Next log sequence to archive   20
Current log sequence           20

SQL> show parameter db_recovery

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
+FRA01
db_recovery_file_dest_size           big integer
30000M

Now we set log_archive_dest_1 to a valid location, within the same diskgroup:

SQL> alter system set log_archive_dest_1='location=+FRA01' scope=both sid='*';

System altered.

Checking again the Archive destination, we see this:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA01
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

So, nothing strange, all good. We perform a logfile switch and archive current logs command to double check the destination is ok, and all was good.

The strange behavior

Now, chances were we want to revert to the previous configuration: removing the log_archive_dest_1 current value and use the “USE_DB_RECOVERY_FILE_DEST” as per db_recovery_file_dest parameter.

SQL> alter system set log_archive_dest_1=' ' scope=both sid='*';

System altered.

(This works for both Oracle 12.1 and Oracle 19c. In Oracle 19c you can also issue the reset command.)

After nullfing the parameter, if we try to archive current log, it won’t work, because:

SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16014: log 2 sequence# 20 not archived, no available destinations
ORA-00312: online log 2 thread 1:
'+REDO01/YADLAB19/ONLINELOG/group_2.262.1058121547'
ORA-00312: online log 2 thread 1:
'+REDO02/YADLAB19/ONLINELOG/group_2.262.1058121547'

If we check the current value of the Archive destination via the “archive log list” command, we can read the following:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            ?#/dbs/arch
Oldest online log sequence     19
Next log sequence to archive   20
Current log sequence           21

Moreover, the db_recovery_file_dest parameter is still there:

SQL> show parameter db_recovery_file

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
+FRA01
db_recovery_file_dest_size           big integer
30000M

It seems that there is no automatic fallback to db_recovery_file_dest if no valid log_archive_dest_n exist.

This become really weird when you found that you can fix it by simply set db_recovery_file_dest to the same value it actually contains:

SQL> show parameter db_recovery_file

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
+FRA01
db_recovery_file_dest_size           big integer
30000M
SQL> alter system set db_recovery_file_dest='+FRA01' scope=both sid='*';

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     19
Next log sequence to archive   20
Current log sequence           21
SQL> alter system archive log current;

System altered.

Pitfall!

Since we tried to switch the logfiles with an invalid destination, even if we fixed the parameter with a valid destination (and performed a switch!), our 19.9 Grid Infrastructure will not be able to understand our database is now unstuck.. leaving the database resource status to INTERMEDIATE and ALL our services down!

The only way I found to restore the resources back fully online, was to restart the database.

Conclusions

I know that when changing archivelog destinations we are expected to double check everything, but to me this is really odd. I expect that no parameters can have an inconsistent values between each others, ever.

This is something as weird as the pga_aggregate_limit enforced behavior that came out since 19.6 RU. I hope some default fallback, alert or fix will be implemented.

Cheers!