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

 ORA-02097, ORA-16028 occurs when you change the LOG_ARCHIVE_DEST_n parameter value twice.

 

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

Change the LOG_ARCHIVE_DEST_N parameter state to ENABLE.

[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 )

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *