ORA-16018解决办法

因为11g归档日志默认路径是：DB_RECOVERY_FILE_DEST  空间大小是2G 导致日志撑满，数据库hang住了

修改归档日志报ORA-16018错误：

先创建归档日志存放目录：

orahost:root:[/oracle]#mkdir arch_log

orahost:root:[/oracle]#chown –R oracle:oinstall ./arch_log

orahost:root:[/oracle]#chmod 775 ./arch_log

orahost:root:[/oracle/arch_log]#su – oracle

然后修改路径：

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

[oracle@bsqkpi dbs]$ oerr ora 16018
16018, 00000, "cannot use %s 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...31) 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.

提示有冲突

进入pfile把DB_recovery_FILE_DEST=……***掉

然后

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 2505338880 bytes
Fixed Size                  2230952 bytes
Variable Size             620758360 bytes
Database Buffers         1862270976 bytes
Redo Buffers               20078592 bytes
Database mounted.
Database opened.
SQL> alter system set log_archive_dest='/arch_log';------修改成功

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /arch_log
Oldest online log sequence     136
Next log sequence to archive   138
Current log sequence           138
SQL>