Troubleshoot Fix ORA-16018 ORA-16019 ORA-02097 Oracle Archival Errors

Problem Description:
alter system set log_archive_dest command fails with either
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST
Or
ORA-16019: cannot use db_recovery_file_dest with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST

Error Description
You cannot have both LOG_ARCHIVE_DEST and DB_RECOVERY_FILE_DEST destinations are active. Either one of it must be a null value. You have to check which one is set / active for now using archive log list command. 

ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST
In this case DB_RECOVERY_FILE_DEST is set already and you are trying to set the log_archive_dest

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Current log sequence           9
SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 4032M
SQL> alter system set log_archive_dest='/u01/app/oracle/fast_recovery_area';
alter system set log_archive_dest='/u01/app/oracle/fast_recovery_area'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST
To resolve this you can approach this method. First DB_RECOVERY_FILE_DEST to null value and set  the value for log_archive_dest.

SQL> alter system set DB_RECOVERY_FILE_DEST='';

System altered.

SQL>  alter system set log_archive_dest='/u01/app/oracle/fast_recovery_area';

System altered.

ORA-16019: cannot use db_recovery_file_dest with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 4032M
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/fast_recovery_area
Oldest online log sequence     7
Current log sequence           9
SQL> alter system set DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area';
alter system set DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use db_recovery_file_dest with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST


SQL> alter system set log_archive_dest='';

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area';

System altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Current log sequence           9
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google