Friday, April 20, 2012

ORA-16018 ORA-16019

Cause : 
These two errors come whenever LOG_ARCHIVE_DEST is set as archival location and you want to set DB_RECOVERY_FILE_DEST


Solution : 

1- You need to see Archive log destination First .
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-You Can Check Archive Location  By Check Database Parameter

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata
 db_recovery_file_dest_size big integer 10G

 3-Set New Archive Log Location

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

System altered.
 SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01
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='/u02';
alter system set DB_RECOVERY_FILE_DEST='/u02'
*
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='/u02';

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

To set multiple location of archival destination set another log_archive_dest_n parameter like,
SQL> alter system set log_archive_dest_3='LOCATION=/u02';
System altered.


Thank you
Osama mustafa
 

2 comments:

  1. I have resolved "ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
    DB_RECOVERY_FILE_DEST" issue using below method

    SQL> alter system set log_archive_dest='/u01/app/oracle/archives' scope=both;

    System altered.

    -RAJUKR

    ReplyDelete
  2. how you do it, its not possible i think so
    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    spfile string /opt/ora/oracle/tesdb/11.1.0/
    dbs/spfilePROD.ora
    SQL> alter system set log_archive_dest='/opt/d02/oracle/proddb/11.1.0/dbs/arch' scope=both;
    alter system set log_archive_dest='/opt/d02/oracle/proddb/11.1.0/dbs/arch' scope=both
    *
    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

    ReplyDelete