Archived Log FileFast Recovery Area

LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST are Mutually Exclusive

LOG_ARCHIVE_DEST

Got an error “ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST” when I tried to set the parameter like this:

SQL> alter system set log_archive_dest='/backup08/archivelog';
alter system set log_archive_dest='/backup08/archivelog'
*
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

Rationale

This is because LOG_ARCHIVE_DEST and DB_RECOVERY_FILE_DEST or LOG_ARCHIVE_DEST_n are mutually exclusive. They can’t be set values at the same time. That is to say, if DB_RECOVERY_FILE_DEST has a valid and true value, then LOG_ARCHIVE_DEST can’t have any value, or vice versa.

Solutions

However, if you insist to use LOG_ARCHIVE_DEST, you have to reset (null out) the value of DB_RECOVERY_FILE_DEST first. Let’s see what value is in it now.

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/recovery_area
db_recovery_file_dest_size           big integer 4032M

Now, let’s do a reset within spfile scope.

SQL> alter system reset db_recovery_file_dest scope=spfile;

System altered.

Then bounce the database instance.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2321612800 bytes
Fixed Size                  2228640 bytes
Variable Size            1325403744 bytes
Database Buffers          973078528 bytes
Redo Buffers               20901888 bytes
Database mounted.
Database opened.

Check current archived log destination. Right now, it’s back to the very default location: $ORACLE_HOME/dbs/arch.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     12
Next log sequence to archive   14
Current log sequence           14

Let’s mount the database and set LOG_ARCHIVE_DEST again.

SQL> alter system set log_archive_dest='/backup08/archivelog';

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /backup08/archivelog
Oldest online log sequence     12
Next log sequence to archive   14
Current log sequence           14
SQL>

This time, we succeed.

Leave a Reply

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