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

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

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

SQL>showparameterlog_archive_dest

NAMETYPEVALUE

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

log_archive_deststring/u01/oracle/archive_test/test1

log_archive_dest_1string

log_archive_dest_10string

log_archive_dest_2string

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

SQL>altersystemsetDB_RECOVERY_FILE_DEST='';

altersystemsetDB_RECOVERY_FILE_DEST=''

*

ERRORatline1:

ORA-02097:parametercannotbemodifiedbecausespecifiedvalueisinvalid

ORA-16019:cannotusedb_recovery_file_destwithLOG_ARCHIVE_DESTorLOG_ARCHIVE_DUPLEX_DEST

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

SQL>altersystemsetlog_archive_dest_1="location=/u01/oracle/archive_test/test1";

altersystemsetlog_archive_dest_1="location=/u01/oracle/archive_test/test1"

*

ERRORatline1:

ORA-02097:parametercannotbemodifiedbecausespecifiedvalueisinvalid

ORA-16019:cannotuseLOG_ARCHIVE_DEST_1withLOG_ARCHIVE_DESTorLOG_ARCHIVE_DUPLEX_DEST

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

SQL>altersystemsetlog_archive_dest='location=/u01/oracle/archive_test/test1';

altersystemsetlog_archive_dest='location=/u01/oracle/archive_test/test1'

*

ERRORatline1:

ORA-02097:parametercannotbemodifiedbecausespecifiedvalueisinvalid

ORA-16018:cannotuseLOG_ARCHIVE_DESTwithLOG_ARCHIVE_DEST_norDB_RECOVERY_FILE_DEST

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

解决方法：

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

SQL>createpfilefromspfile;

Filecreated.

SQL>shutdownimmediate

Databaseclosed.

Databasedismounted.

ORACLEinstanceshutdown.

删除pfile里的相关冲突参数

SQL>conn/assysdba

Connectedtoanidleinstance.

SQL>startuppfile=?/dbs/initorcl.ora

ORACLEinstancestarted.

TotalSystemGlobalArea281018368bytes

FixedSize1218968bytes

VariableSize109053544bytes

DatabaseBuffers167772160bytes

RedoBuffers2973696bytes

Databasemounted.

Databaseopened.

二．关于log_archive_dest,ORA-16019,ORA-16018的说明

ORA-16018:cannotusestringwithLOG_ARCHIVE_DEST_norDB_RECOVERY_FILE_DEST

Cause:Oneofthefollowingeventscausedanincompatibility:

1)ParameterLOG_ARCHIVE_DESTorLOG_ARCHIVE_DUPLEX_DESTwasinusewhenaLOG_ARCHIVE_DEST_n(n=1...10)parameterwasencounteredwhilefetchinginitializationparameters.

2)AnALTERSYSTEMARCHIVELOGSTARTTOcommandwasineffectwhenaLOG_ARCHIVE_DEST_nparameterwasencounteredwhilefetchinginitializationparameters.

3)ALOG_ARCHIVE_DEST_nparameterwasinusewhenanALTERSYSTEMcommandwasusedtodefineavalueforeithertheLOG_ARCHIVE_DESTorLOG_ARCHIVE_DUPLEX_DESTparameter.

4)ParameterDB_RECOVERY_FILE_DESTwasinusewhenanattemptwasmadetouseanALTERSYSTEMorALTERSESSIONcommandtodefineavalueforLOG_ARCHIVE_DESTorLOG_ARCHIVE_DUPLEX_DEST.

Action:Eliminateanyincompatibleparameterdefinitions.

ORA-16018:cannotusestringwithLOG_ARCHIVE_DEST_norDB_RECOVERY_FILE_DEST

Cause:Oneofthefollowingeventscausedanincompatibility:

1)ParameterLOG_ARCHIVE_DESTorLOG_ARCHIVE_DUPLEX_DESTwasinusewhenaLOG_ARCHIVE_DEST_n(n=1...10)parameterwasencounteredwhilefetchinginitializationparameters.

2)AnALTERSYSTEMARCHIVELOGSTARTTOcommandwasineffectwhenaLOG_ARCHIVE_DEST_nparameterwasencounteredwhilefetchinginitializationparameters.

3)ALOG_ARCHIVE_DEST_nparameterwasinusewhenanALTERSYSTEMcommandwasusedtodefineavalueforeithertheLOG_ARCHIVE_DESTorLOG_ARCHIVE_DUPLEX_DESTparameter.

4)ParameterDB_RECOVERY_FILE_DESTwasinusewhenanattemptwasmadetouseanALTERSYSTEMorALTERSESSIONcommandtodefineavalueforLOG_ARCHIVE_DESTorLOG_ARCHIVE_DUPLEX_DEST.

Action:Eliminateanyincompatibleparameterdefinitions.

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

LOG_ARCHIVE_DESTisapplicableonlyifyouarerunningthedatabaseinARCHIVELOGmodeorarerecoveringadatabasefromarchivedredologs.LOG_ARCHIVE_DESTisincompatiblewiththeLOG_ARCHIVE_DEST_nparameters,andmustbedefinedasthenullstring("")or('')whenanyLOG_ARCHIVE_DEST_nparameterhasavalueotherthananullstring.Useatextstringtospecifythedefaultlocationandrootofthediskfileortapedevicewhenarchivingredologfiles.(Archivingtotapeisnotsupportedonalloperatingsystems.Thevaluecannotbearawpartition.

IfLOG_ARCHIVE_DESTisnotexplicitlydefinedandalltheLOG_ARCHIVE_DEST_nparametershavenullstringvalues,LOG_ARCHIVE_DESTissettoanoperatingsystem-specificdefaultvalueoninstancestartup.

Tooverridethedestinationthatthisparameterspecifies,eitherspecifyadifferentdestinationformanualarchivingorusetheSQL*PlusstatementARCHIVELOGSTARTfilespecforautomaticarchiving,wherefilespecisthenewarchivedestination.Topermanentlychangethedestination,usethestatementALTERSYSTEMSETLOG_ARCHIVE_DEST=filespec,wherefilespecisthenewarchivedestination.

NeitherLOG_ARCHIVE_DESTnorLOG_ARCHIVE_FORMAThavetobecompletefileordirectoryspecifiersthemselves;theyonlyneedtoformavalidfilepathafterthevariablesaresubstitutedintoLOG_ARCHIVE_FORMATandthetwoparametersareconcatenatedtogether.

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

Systemaltered.

SQL>altersystemsetlog_archive_dest_1='';

Systemaltered.

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

SQL>altersystemsetlog_archive_dest='/u01/oracle/archive_test/test1';

Systemaltered.

SQL>altersystemsetlog_archive_dest='/u01/oracle/archive_test';

Systemaltered.

SQL>altersystemsetlog_archive_dest='/u01/oracle/archive_test/test1';

Systemaltered.

3.2如果我们想从log_archive_dest这个参数切换回log_archive_dest_1参数，操作也是一样，

3.2.1先清空该参数

SQL>altersystemsetlog_archive_dest='';

Systemaltered.

3.2.2修改相应的log_archive_dest_1参数：

SQL>altersystemsetlog_archive_dest_1="location=/u01/oracle/archive_test/test1";

Systemaltered.

SQL>altersystemsetDB_RECOVERY_FILE_DEST='/u01/oracle/archive_test/test1';

Systemaltered.

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

Blog：http://blog.csdn.net/tianlesoftware

网上资源：http://tianlesoftware.download.csdn.net

相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1群：62697716(满);DBA2群：62697977

DBA3群：63306533;聊天群：40132017

<!--EndFragment-->