ORA-16018
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.
Actually, LOG_ARCHIVE_DEST has been deprecated in favor of the LOG_ARCHIVE_DEST_n
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.
Since Oracle adopts Fast Recovery Area (FRA), we tend to use DB_RECOVERY_FILE_DEST for the destination of archived logs and backup sets.