ORA-16019 and ORA-16018 error handling methods

A. ORA-16019 and ORA-16018 error Generation description

The colleague is modifying the archive directory, accidentally set the parameter is wrong, he set the Log_archive_dest parameter. This parameter is conflicting with the default Log_archive_dest_n and Db_recovery_file_dest. In other words, only one can be used. Start by looking at the relevant code to illustrate the problem.

Sql> Show Parameter Log_archive_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Log_archive_dest String/u01/oracle/archive_test/test1

Log_archive_dest_1 string

Log_archive_dest_10 string

Log_archive_dest_2 string

This is the result of a colleague's modification, let's modify the Db_recovery_file_dest parameter:

Sql> alter system set db_recovery_file_dest= ";

Alter system set db_recovery_file_dest= '

*

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

Error, cannot be modified, in modify log_archive_dest_1 parameter:

Sql> alter system set log_archive_dest_1= "Location=/u01/oracle/archive_test/test1";

Alter system set log_archive_dest_1= "Location=/u01/oracle/archive_test/test1"

*

ERROR at line 1:

Ora-02097:parameter cannot be modified because specified value is invalid

Ora-16019:cannot use log_archive_dest_1 with Log_archive_dest or log_archive_duplex_dest

or error, can not be modified, in the modified log_archive_dest parameters:

Sql> alter system set log_archive_dest= ' Location=/u01/oracle/archive_test/test1 ';

Alter system set log_archive_dest= ' Location=/u01/oracle/archive_test/test1 '

*

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

Still error, so far, we can not modify the archive of any directory. They are incompatible, and if we modify the condition without emptying the other directory, it will cause a deadlock like above. Neither who can modify who. They locked each other up.

Workaround:

In this case, we can only modify the Pfile file to remove the conflicting parameters, and start the database with the modified pfile.

Sql> create Pfile from SPFile;

File created.

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Delete related conflict parameters in Pfile

Sql> Conn/as SYSDBA

Connected to an idle instance.

Sql> Startup Pfile=?/dbs/initorcl.ora

ORACLE instance started.

Total System Global area 281018368 bytes

Fixed Size 1218968 bytes

Variable Size 109053544 bytes

Database buffers 167772160 bytes

Redo buffers 2973696 bytes

Database mounted.

Database opened.

two. Notes on Log_archive_dest, ORA-16019 , ORA-16018

ORA-16018: cannot use string with Log_archive_dest_n or db_recovery_file_dest

Cause: One of the following events caused an incompatibility:

1) Parameter log_archive_dest or log_archive_duplex_dest is in use if a log_archive_dest_n (n = 1...10) Parameter was E Ncountered while fetching initialization parameters.

2) An ALTER SYSTEM ARCHIVE LOG START to command is in effect when a log_archive_dest_n parameter is encountered while FE Tching initialization parameters.

3) A Log_archive_dest_n parameter is in use if an ALTER SYSTEM command is used to define A value for either the Log_ar Chive_dest or log_archive_duplex_dest parameter.

4) Parameter Db_recovery_file_dest is in use if an attempt is made to a alter SYSTEM or alter SESSION command to Define a value for Log_archive_dest or log_archive_duplex_dest.

Action: Eliminate any incompatible parameter definitions.

ORA-16018: cannot use string with Log_archive_dest_n or db_recovery_file_dest

Cause: One of the following events caused an incompatibility:

1) Parameter log_archive_dest or log_archive_duplex_dest is in use if a log_archive_dest_n (n = 1...10) Parameter was E Ncountered while fetching initialization parameters.

2) An ALTER SYSTEM ARCHIVE LOG START to command is in effect when a log_archive_dest_n parameter is encountered while FE Tching initialization parameters.

3) A Log_archive_dest_n parameter is in use if an ALTER SYSTEM command is used to define A value for either the Log_ar Chive_dest or log_archive_duplex_dest parameter.

4) Parameter Db_recovery_file_dest is in use if an attempt is made to a alter SYSTEM or alter SESSION command to Define a value for Log_archive_dest or log_archive_duplex_dest.

Action: Eliminate any incompatible parameter definitions.

for log_archive_dest  parameters, we refer to the oracle  connection document:

log_archive_dest is applicable only if you are running the  Database in archivelog mode or are recovering a database from  archived redo logs. LOG_ARCHIVE_DEST is incompatible with the  log_archive_dest_n parameters, and must be defined as the null  string  ("")  or  ('   ')  when any log_archive_dest_n parameter has  a value other than a null string. use a text string  to specify the default location and root of the disk  file or tape device when archiving redo log files.  (archiving  to tape is not supported on all operating systems. the  value cannot be a&nBsp;raw partition.

if log_archive_dest is not explicitly defined and all  The log_archive_dest_n parameters have null string values, log_archive_dest  is set to an operating system-specific default value on  Instance startup.

to override the destination that this parameter specifies,  either specify a different destination for manual archiving or  use the SQL*Plus statement ARCHIVE LOG START filespec for  Automatic archiving, where filespec is the new archive destination.  to permanently change the destination, use the statement alter  system set log_archive_dest = filespec, where filespec is the  new archive destination.

Neither Log_archive_dest nor Log_archive_format has to be complete file or directory specifiers themselves; They need to form a valid file path after the variables is substituted into Log_archive_format and the both parameter S is concatenated together.

http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams118.htm#REFRN10085

three. How to set parameters correctly

3.1 if we want to set log_archive_dest This parameter, what we should do:

3.1.1 Empties the db_recover_file_dest and Log_archive_dest_n parameters because these 2 parameters conflict with the Log_archive_dest parameter:

Sql> alter system set db_recovery_file_dest= ";

System altered.

Sql> alter system set log_archive_dest_1= ";

System altered.

3.1.2 Modify the Log_archive_dest parameter, note that the direct path is written here, there is no location option. Because we have previously emptied the conflict of two parameters, so can be arbitrarily modified, no error.

Sql> alter system set log_archive_dest= '/u01/oracle/archive_test/test1 ';

System altered.

Sql> alter system set log_archive_dest= '/u01/oracle/archive_test ';

System altered.

Sql> alter system set log_archive_dest= '/u01/oracle/archive_test/test1 ';

System altered.

3.2 If we want to switch back to the log_archive_dest_1 parameter from the Log_archive_dest parameter, the operation is the same,

3.2.1 Clear the parameter first

Sql> alter system set log_archive_dest= ";

System altered.

3.2.2 Modify the corresponding log_archive_dest_1 parameters:

Sql> alter system set log_archive_dest_1= "Location=/u01/oracle/archive_test/test1";

System altered.

Sql> alter system set db_recovery_file_dest= '/u01/oracle/archive_test/test1 ';

System altered.

ORA-16019 and ORA-16018 Error handling methods (RPM)