Oracle 11G归档格式修改错误不能open的解决办法
1、以下内容是自己测试总结，也许有错误：log_archive_format知识：%slog sequence number%Slog sequence number, zero filled （日志序列号，左边补0）%t thread number%Tthread number, zero filled （归档?号，好像不是进程号，左边补0）%aactivation ID%ddatabase ID （不是数据库名，是一串数字）%rresetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database其中%s/%t/%r 必须都包含，缺少任何一个都会报错，大小写不能同时存在。
2、自己环境下：Linux默认归档格式：%t_%s_%r.dbf-rw-r-----. 1 oracle oinstall 3584 May 11 15:53 1_12_879293371.dbfwindows 默认归档格式：ARC%S_%R.%T
3、修改错误：SQL> alter system set log_archive_format='$ORACLE_SID_%t_鬈熵痼霄%s_%r.dbf' scope=spfile;SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.---正常关闭SQL> startup;ORACLE instance started.Total System Global Area 881037312 bytesFixed Size 2218392 bytesVariable Size 515901032 bytesDatabase Buffers 356515840 bytesRedo Buffers 6402048 bytesDatabase mounted.Database opened. -----正常打开SQL> show parameter log_archive_format;SQL> alter system switch logfile;System altered.SQL> alter system checkpoint;System altered.SQL> alter system switch logfile; ^Calter system switch logfile *ERROR at line 1:ORA-01013: user requested cancel of current operation----------因为执行没反应，所以ctrl+c取消操作SQL> alter system archive log current;alter system archive log current*ERROR at line 1:ORA-16038: log 1 sequence# 41 cannot be archived ------------不能归档ORA-00312: online log 1 thread 1: '/data/orcl/redo01.log'ORA-00312: online log 1 thread 1: '/data/orcl/redo01b.log'ORA-07217: sltln: environment variable cannot be evaluated.（此时数据库仍处于打开状态，只是不能归档，重新执行第一条语句修改归档格式为正确格式，然后重启即可。）
4、如果上一步直接重启了，数据库会打不开：SQL> startup force;ORACLE instance started.Total System Global Area 881037312 bytesFixed Size 2218392 bytesVariable Size 515901032 bytesDatabase Buffers 356515840 bytesRedo Buffers 6402048 bytesDatabase mounted. ----数据库已挂载ORA-03113: end-of-file on communication channel （数据库不能打开）Process ID: 26859Session ID: 191 Serial number: 3查看报警日志：tail -50 $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log日志：........alter database openFri Jun 26 11:12:38 2015LGWR: STARTING ARCH PROCESSESFri Jun 26 11:12:38 2015ARC0 started with pid=20, OS id=27066ARC0: Archival startedLGWR: STARTING ARCH PROCESSES COMPLETEARC0: STARTING ARCH PROCESSESErrors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26985.trc:ORA-07217: sltln: environment variable cannot be evaluated.Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26985.trc:ORA-16038: log 4 sequence# 36 cannot be archivedORA-00312: online log 4 thread 1: '/data/orcl/redo04a.log'ORA-00312: online log 4 thread 1: '/data/orcl/redo04b.log'ORA-07217: sltln: environment variable cannot be evaluated.USER (ospid: 26985): terminating the instance due to error 16038-------提示log 4 sequence# 36 cannot be archived不能归档
5、解决办法：因为归档格式是写入参数文件的，所以修改参数文件中的log_archive_format为正确格式就能正常打开了。SQL> create pfile from spfile;File created.SQL> exit[oracle@192dbs]$viinitorcl.ora *.log_archive_format='orcl_%t_%s_%r.dbf'（格式修改为正确格式就行了）[oracle@192dbs]$sqlplus/assysdbaSQL> startup pfile=$ORACLE_HOME/dbs/initorcl.ora;数据正常打开SQL> show parameter log_archive_format;NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_format string orcl_%t_%s_%r.dbf[oracle@192arch]$ll$ORACLE_HOME/dbs/arch--格式正确,问题解决-rw-r-----. 1 oracle oinstall 6144 Jun 26 13:23 orcl_1_38_879293371.dbf让后需要把pfile中的内容写回spfileSQL>create spfile from pfile;SQL>startup force;--OK同样修改参数文件也可以用如下方式：SQL> create pfile from spfile;File created.SQL> exit[oracle@192dbs]$viinitorcl.ora *.log_archive_format='orcl_%t_%s_%r.dbf'（格式修改为正确格式就行了）[oracle@192dbs]$sqlplus/assysdbaSQL> create spfile from pfile;SQL>startup;--OK