ORA-16028 while setting log_archive_dest_n parameter

If you are trying to unset log_archive_dest_n parameter and you face error ORA-16028, it means that you need to have at least one (or as set in LOG_ARCHIVE_MIN_SUCCEED_DEST) log archive destinations. I noticed in one of my RAC databases which had 2 archive destinations set, log_archive_dest_1 and log_archive_dest_3.

SQL> select inst_id,value from gv$parameter where name='log_archive_dest_1'; INST_ID                      VALUE ------------------------------------------------------- 1                                  LOCATION=+ARCH 2                                  LOCATION=+ARCH 3                                  LOCATION=+ARCH SQL> select inst_id, value from gv$parameter where name='log_archive_dest_3';   INST_ID                      VALUE ------------------------------------------------------- 1                                  LOCATION=+DATA 2                                  LOCATION=+DATA 3                                  LOCATION=+DATA

 Archived logs were being created under dest_3, whereas I wanted to have archives to be created under dest_1. So I tried to unset dest_3, but ORA-16028 was returned.

SQL> alter system set log_archive_dest_3='' scope=both sid='*'; alter system set log_archive_dest_3='' scope=both sid='*' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-16028: new LOG_ARCHIVE_DEST_3 causes less destinations than LOG_ARCHIVE_MIN_SUCCEED_DEST requires

 The reason for this error was that dest_1 destination was set to DEFER, and it needed to be set to ENABL before I could unset dest_3 (or set it DEFER).

SQL> select inst_id,value from gv$parameter where name='log_archive_dest_state_1'; INST_ID               VALUE ---------------------------------- 1                            DEFER 2                            DEFER 3                            DEFER

If you face this kind of situation, you need to make sure that at least one destination’s state is set to ENABLE before one or more other destinations could be unset or set to DEFER.

SQL> alter system set log_archive_dest_state_1=enable scope=both sid='*'; System altered.

 After that, dest_3 could be set to DEFER or could be unset.

SQL> alter system set log_archive_dest_state_3=defer scope=both sid='*'; System altered.