ORA-16019 和 ORA-16018 错误的处理方法

原创

tianlesoftware 2022-09-14 20:09:19 ©著作权

文章标签 archive file oracle string 数据库 文章分类 虚拟化云计算

©著作权归作者所有：来自51CTO博客作者tianlesoftware的原创作品，请联系作者获取转载授权，否则将追究法律责任

一． ORA-16019 和 ORA-16018 错误产生描述

同事在修改归档目录，一不小心把参数设置错误了，他设置的是log_archive_dest参数。log_archive_dest_n 和 DB_RECOVERY_FILE_DEST是冲突的。也就是说，只能用一个。先看相关的代码来说明这个问题。

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

这是同事修改之后的结果，我们来修改一下DB_RECOVERY_FILE_DEST参数：

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

报错，不能修改，在修改log_archive_dest_1 参数：

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

还是报错，不能修改，在修改log_archive_dest 参数：

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

依旧报错，至此，我们不能修改归档的任何目录。他们他们是不兼容的，如果我们在没有清空其他目录的条件下来修改，就会造成如上的死锁。既谁不能修改谁。他们之间互相锁住了。

解决方法：

在这种情况下，我们只能通过修改pfile文件来删除掉冲突的参数，在用修改之后的pfile启动数据库。

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

删除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.

二．关于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 was in use when a LOG_ARCHIVE_DEST_n (n = 1...10) parameter was encountered while fetching initialization parameters.

2) An ALTER SYSTEM ARCHIVE LOG START TO command was in effect when a LOG_ARCHIVE_DEST_n parameter was encountered while fetching initialization parameters.

3) A LOG_ARCHIVE_DEST_n parameter was in use when an ALTER SYSTEM command was used to define a value for either the LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST parameter.

4) Parameter DB_RECOVERY_FILE_DEST was in use when an attempt was made to use an 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 was in use when a LOG_ARCHIVE_DEST_n (n = 1...10) parameter was encountered while fetching initialization parameters.

2) An ALTER SYSTEM ARCHIVE LOG START TO command was in effect when a LOG_ARCHIVE_DEST_n parameter was encountered while fetching initialization parameters.

3) A LOG_ARCHIVE_DEST_n parameter was in use when an ALTER SYSTEM command was used to define a value for either the LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST parameter.

4) Parameter DB_RECOVERY_FILE_DEST was in use when an attempt was made to use an 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.

关于log_archive_dest 参数，我们参考Oracle 连接文档：

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 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 have to be complete file or directory specifiers themselves; they only need to form a valid file path after the variables are substituted into LOG_ARCHIVE_FORMAT and the two parameters are concatenated together.

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

三．如何正确设置参数

3.1 如果我们想设置log_archive_dest 这个参数的时候，我们应该如何操作：

3.1.1清空db_recover_file_dest 和 log_archive_dest_n 参数，因为这2个参数和log_archive_dest参数冲突：

SQL> alter system set db_recovery_file_dest='';

System altered.

SQL> alter system set log_archive_dest_1='';

System altered.

3.1.2修改log_archive_dest参数，注意这里写的是直接路径，没有location选项。因为我们之前清空了冲突的两个参数，所以可以随便修改，不会报错了。

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';