ORA-16028: new db_recovery_file_dest causes less destinations than LOG_ARCHIVE_MIN_SUCCEED_DEST requires

I got “ORA-16028: new db_recovery_file_dest causes less destinations than LOG_ARCHIVE_MIN_SUCCEED_DEST requires  | ORA-02097: parameter cannot be modified because specified value is invalid ” error in Oracle database.

ORA-16028: new db_recovery_file_dest causes less destinations than LOG_ARCHIVE_MIN_SUCCEED_DEST requires

You are performing a Data Pump export (expdp), and it fails with the following errors. Details of error are as follows.

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


SQL>

ALTER SYSTEM SET LOG_ARCHIVE_DEST_n fails with ORA-02097, ORA-16028.

[Example]
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/tmp/a1 NOREOPEN ALTERNATE=log_archive_dest_2' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/tmp/a2 NOREOPEN ALTERNATE=log_archive_dest_2' SCOPE=BOTH;

[Error]

SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/tmp/a1 NOREOPEN ALTERNATE=log_archive_dest_2' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/tmp/a2 NOREOPEN ALTERNATE=log_archive_dest_2' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/tmp/a2 NOREOPEN ALTERNATE=log_archive_dest_2' SCOPE=BOTH
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16028: new LOG_ARCHIVE_DEST_1 causes less destinations than
LOG_ARCHIVE_MIN_SUCCEED_DEST requires

This problem is caused by Bug 16746404.

BUG 16746404 – INCONSISTENT STATUS OF V$ARCHIVE_DEST AFTER BROKER RE-ENABLES A MEMBER

The LOG_ARCHIVE_DEST_N state changes to UNKNOWN in the 1st ALTER statement, and parameter values can not be changed in the 2nd ALTER statement.

SQL> select dest_name,valid_now from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_1';

DEST_NAME                      VALID_NOW
------------------------------ ----------------
LOG_ARCHIVE_DEST_1             UNKNOWN

[Example]
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/tmp/a1 NOREOPEN ALTERNATE=log_archive_dest_2' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_1=enable SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/tmp/a2 NOREOPEN ALTERNATE=log_archive_dest_2' SCOPE=BOTH;

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )