ORA-00338的错误提示，问题原因及如何解决
ORA-00338: log <Log_Number> of thread <thread_number> is more recent than control file
ORA-00312: online log <Log_Number> thread <thread_number>: 'redo.log'
oerr ora 338 00338, 00000, "log %s of thread %s is more recent than control file" // *Cause: The control file change sequence number in the log file is // greater than the number in the control file. This implies that // the wrong control file is being used. Note that repeatedly causing // this error can make it stop happening without correcting the real // problem. Every attempt to open the database will advance the // control file change sequence number until it is great enough. // *Action: Use the current control file or do backup control file recovery to // make the control file current. Be sure to follow all restrictions // on doing a backup control file recovery.
alert日志部分内容
ORACLE_BASE from environment = C:\app Mon Jun 27 13:29:51 2016 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 3585184207 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Mon Jun 27 13:31:19 2016 db_recovery_file_dest_size of 3852 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Mon Jun 27 13:31:55 2016 ALTER DATABASE RECOVER database Media Recovery Start started logmerger process Mon Jun 27 13:31:56 2016 Media Recovery failed with error 264 Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database ... Mon Jun 27 13:32:10 2016 alter database open Mon Jun 27 13:32:11 2016 Errors in file c:\app\diag\rdbms\trace\hyd_lgwr_7944.trc: ORA-00338: log 3 of thread 1 is more recent than control file ORA-00312: online log 3 thread 1: 'D:\APP\ORADATA\REDO03.LOG' Errors in file c:\app\diag\rdbms\trace\hyd_lgwr_7944.trc: ORA-00338: log 3 of thread 1 is more recent than control file ORA-00312: online log 3 thread 1: 'D:\APP\ORADATA\REDO03.LOG' Errors in file c:\app\diag\rdbms\trace\hyd_ora_6880.trc: ORA-00338: log 1 of thread is more recent than control file ORA-00312: online log 3 thread 1: 'D:\APP\ORADATA\REDO03.LOG' USER (ospid: 6880): terminating the instance due to error 338 Instance terminated by USER, pid = 6880
从日志中我们发现，数据库在mount后，在做恢复时意外终止退出。经过和客户沟通，得知由于昨天非正常关机，今天才出现这种情况，数据文件和控制文件都未动过。出现这种问题，正常的情况下我们可能需要通过备份的控制文件或重建控制文件再进行恢复，本例通过清除日志文件就正常打开数据库，比较特别，特记录于此。
SQL> startup mount
ORACLE instance started.
Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 343934376 bytes
Database Buffers 427819008 bytes
Redo Buffers 5259264 bytes
Database mounted.
SQL> col member for a60
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC
---------- -------------- -------------- ------------------------------------------------------------ ------
3 ONLINE D:\APP\ORADATA\REDO03.LOG NO
2 ONLINE D:\APP\ORADATA\REDO02.LOG NO
1 ONLINE D:\APP\ORADATA\REDO01.LOG NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- ------ ---------------- ------------- ------------ ------------
NEXT_TIME
------------
1 1 67 52428800 512 1 NO INACTIVE 2719251 23-JUN-16 2758086
23-JUN-16
3 1 66 52428800 512 1 NO INACTIVE 2687384 22-JUN-16 2719251
23-JUN-16
2 1 68 52428800 512 1 NO CURRENT 2758086 23-JUN-16 2.8147E+14
SQL> select status, checkpoint_change#,
2 to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
3 count(*)
4 from v$datafile_header
5 group by status, checkpoint_change#, checkpoint_time
6 order by status, checkpoint_change#, checkpoint_time;
STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
-------------- ------------------ ---------------------------------------------------------- ----------
ONLINE 2784716 23-JUN-2016 17:30:41 5
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select LF.member, L.group#, L.thread#, L.sequence#, L.status,
2 L.first_change#, L.first_time, DF.min_checkpoint_change#
3 from v$log L, v$logfile LF,
4 (select min(checkpoint_change#) min_checkpoint_change#
5 from v$datafile_header
6 where status='ONLINE') DF
7 where LF.group# = L.group#
8 and L.first_change# >= DF.min_checkpoint_change#;
no rows selected
SQL>select * from v$recover_file;
no rows selected
SQL>
备份控制文件，system表空间，redo03.log，尝试清除redo03.log，数据库正常打开
SQL>alter database clear logfile group 3;
SQL>alter database open;Completed: ALTER DATABASE MOUNT Mon Jun 27 13:41:25 2016 alter database clear logfile group 3 Clearing online log 3 of thread 1 sequence number 66 Completed: alter database clear logfile group 3 Mon Jun 27 13:41:36 2016 alter database open Mon Jun 27 13:41:37 2016 Thread 1 advanced to log sequence 69 (thread open) Thread 1 opened at log sequence 69 Current log# 3 seq# 69 mem# 0: D:\APP\ORADATA\REDO03.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Jun 27 13:41:37 2016 SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Mon Jun 27 13:41:45 2016 QMNC started with pid=19, OS id=7340 Mon Jun 27 13:41:50 2016 Completed: alter database open Mon Jun 27 13:41:54 2016 Starting background process CJQ0 Mon Jun 27 13:41:54 2016 CJQ0 started with pid=23, OS id=4952 Mon Jun 27 13:41:57 2016
小结：不论在什么情况下，都要认真对待数据库，你对她好，她也就对你好，关爱是相互的。有时换一种方法可能会更好
