ORA-02097 and ORA-16018

1) Issues ARCHIVE LOG LIST to see destination.



SQL> archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217



2)Check your current archival location by issuing, show parameter DB_RECOVERY_FILE_DEST


SQL> show parameter DB_RECOVERY_FILE_DEST


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2

db_recovery_file_dest_size big integer 30G



3) Now you want to set log_archive_dest.



SQL> alter system set log_archive_dest='/oradata1';

alter system set log_archive_dest='/oradata1'

*
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



4)If you want to set log_archive_dest first reset DB_RECOVERY_FILE_DEST and then set .


SQL> alter system set DB_RECOVERY_FILE_DEST='';

System altered.


SQL> alter system set log_archive_dest='/oradata1';

System altered.


SQL> archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata1
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217



5)Also if you now want to set DB_RECOVERY_FILE_DEST ORA-16019: will occur.



SQL> alter system set DB_RECOVERY_FILE_DEST='/oradata2';



alter system set DB_RECOVERY_FILE_DEST='/oradata2'

*

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



6)To set DB_RECOVERY_FILE_DEST first reset LOG_ARCHIVE_DEST.

SQL> alter system set log_archive_dest='';

System altered.


SQL> alter system set DB_RECOVERY_FILE_DEST='/oradata2';

System altered.


SQL> archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217



7)To set multiple location of archival destination set another log_archive_dest_n parameter like,



SQL> alter system set log_archive_dest_3='LOCATION=/oradata2';

System altered.