联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
朋友和我说，他的数据库ORACLE 8.0.5出现ORA-01207,进行了尝试恢复但是别未成功,让我协助其完成恢复
数据库版本
SVRMGR> select * from v$version; BANNER ---------------------------------------------------------------- Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production CORE Version 4.0.5.0.0 - Production TNS for 32-bit Windows: Version 8.0.5.0.0 - Production NLSRTL Version 3.3.2.0.0 - Production 5 rows selected.
open数据库报ORA-01207错误
SVRMGR> alter database open; alter database open * ORA-01122: database file 1 failed verification check ORA-01110: data file 1: 'D:\ORANT\DATABASE\SYS1ORCL.ORA' ORA-01207: file is more recent than controlfile - old controlfile
出现该错误的原因是因为控制文件里面的scn或者checkpoint_time>数据文件中的对应值,从而出现该错误,解决方法重建控制文件或者执行recover using backup controlfile 之类命令
重建控制文件,并open报ORA-600[4147]
SVRMGR> alter database backup controlfile to trace;
Statement processed.
SVRMGR> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 15077376 bytes
Fixed Size 49152 bytes
Variable Size 12906496 bytes
Database Buffers 2048000 bytes
Redo Buffers 73728 bytes
SVRMGR> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2> MAXLOGFILES 32
3> MAXLOGMEMBERS 2
4> MAXDATAFILES 32
5> MAXINSTANCES 16
6> MAXLOGHISTORY 3260
7> LOGFILE
8> GROUP 1 'D:\ORANT\DATABASE\LOG4ORCL.ORA' SIZE 1M,
9> GROUP 2 'D:\ORANT\DATABASE\LOG3ORCL.ORA' SIZE 1M,
10> GROUP 3 'D:\ORANT\DATABASE\LOG2ORCL.ORA' SIZE 1M,
11> GROUP 4 'D:\ORANT\DATABASE\LOG1ORCL.ORA' SIZE 1M
12> DATAFILE
13> 'D:\ORANT\DATABASE\SYS1ORCL.ORA',
14> 'D:\ORANT\DATABASE\USR1ORCL.ORA',
15> 'D:\ORANT\DATABASE\RBS1ORCL.ORA',
16> 'D:\ORANT\DATABASE\TMP1ORCL.ORA'
17> ;
Statement processed.
SVRMGR> recover database using backup controlfile;
ORA-00279: change 46960617 generated at 01/31/14 18:51:49 needed for thread 1
ORA-00289: suggestion : D:\ORANT\RDBMS80\ARC12900.1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORANT\DATABASE\LOG3ORCL.ORA
Log applied.
Media recovery complete.
SVRMGR> alter database open;
alter database open
*
ORA-00600: internal error code, arguments: [4147], [16], [1], [], [], [], [], []
The ORA-600[4147] basically indicates some kind of corruption with the UNDO (rollback segment)block, most probably due to a lost write to the rollback segment.
ORA-600[4147]是因为回滚段坏块导致(具体是因为undoblock的scn无效),解决方法是用dul找出来回滚段,并屏蔽之
继续恢复报ORA-00600[3668]
SVRMGR> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SVRMGR> startup ORACLE instance started. Total System Global Area 15077376 bytes Fixed Size 49152 bytes Variable Size 12906496 bytes Database Buffers 2048000 bytes Redo Buffers 73728 bytes Database mounted. ORA-00600: internal error code, arguments: [3668], [1], [2], [17232], [17232], [4], [], []
ORA-00600[3668]是因为在ORACLE 7.0到9.2的版本中The FIRST time an attempt has been made to start an instance after a CREATE CONTROLFILE command has been issued.
At least one data file needs MEDIA RECOVERY.在9.2.0.x及其以后版本报:ORA-1113: file
通过重建控制文件,执行recover database,再open数据库恢复成功
ORA-600 [3668] “Media Recovery Required After CREATE CONTROLFILE” (Doc ID 93665.1)
ERROR: ORA-600 [3668] [a] [b] [c] [d] [e] VERSIONS: versions 7.0 to 9.2 DESCRIPTION: This is the FIRST time an attempt has been made to start an instance after a CREATE CONTROLFILE command has been issued. At least one data file needs MEDIA RECOVERY. The error has been externalised in Oracle 9.2.x as : ORA-1113: file <name> needs media recovery. ARGUMENTS: Arg [a] File number needing media recovery Arg [b] Control file status Arg [c] Control file checkpoint count Arg [d] File header checkpoint count Arg [e] File header status FUNCTIONALITY: CACHE RECOVERY COMPONENT IMPACT: INSTANCE FAILURE - Usually unable to start the instance when receiving this error. SUGGESTIONS: This should only ever be signalled if a CREATE CONTROLFILE has just been performed and one or more files need media recovery. In this case it is best to: o recreate the controlfile again. Prior to doing this, check that the create controlfile statement is accurate in all respects. For example, check that it contains the correct number of datafiles for each tablespace and that these files exist. Check that it contains the correct number of redo log groups. o Perform the appropriate media recovery The type of recovery to be performed depends on the circumstances. Doing a complete media recovery : RECOVER DATABASE (if create controlfile was NORESETLOGS) ALTER DATABASE OPEN; Doing an incomplete recovery (for example time based, change based or until CANCEL) : RECOVER DATABASE USING BACKUP CONTROLFILE (if using RESETLOGS) ALTER DATABASE OPEN RESETLOGS; You may need to supply the names of the ONLINE logs to complete the 'BACKUP CONTROLFILE' recovery.ORA-600[4147][a][b] it’s a corruption in the undo segment. Block not new enough (Undo) Sequences match but count wrong. [a] Record number from the UBA we want to look at. [b] Count on the Undo block header. This error is raised when we are looking at a data block, and need to read the undo pointed at by the UBA(eg. for CR) but the record number from the UBA we want to look at doesn’t exist in the undo block header. The UNDO block we are looking at is dumped to the trace file before the ORA-600[4147] and call stack. E.g. in 10.1.0.5 The undo record shows: 2007-07-10 10:48:48.467 *** SERVICE NAME:(SYS$USERS) 2007-07-10 10:48:48.466 *** SESSION ID:(281.26013) 2007-07-10 10:48:48.466 Dump of buffer cache at level 3 BH (0x0x72fcbfe0) file#: 2 rdba: 0x00802fdc (2/12252) class: 42 ba: 0x0x72618000 set: 6 blksize: 8192 bsi: 0 set_flg: 0 pwbcnt: 0 dbwrid: 0 obj: -1 objn: 0 tsn: 1 afn: 2 hash: [53faf870,ac20da50] lru: [53fd885c,57fb57ec] ckptq: [60fe6408,53fd8830] fileq: [77fed448,68fe2cf0] use: [NULL] wait: [NULL] st: XCURRENT md: NULL tch: 2 txn: 0xac793324 flags 0x2002009: buffer_dirty private gotten_in_current_mode redo_since_read LRBA: [0xba99.1bd30.0] HSCN: [0x0.132c41a1] HSUB: [1] buffer tsn: 1 rdba: 0x00802fdc (2/12252) scn: 0x0000.132c41a1 seq: 0x06 flg: 0x00 tail: 0x41a10206 frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK ******************************************************************************** UNDO BLK: xid: 0x000d.024.00004a96 seq: 0x13ae cnt: 0xa irb: 0xa icl: 0x0 flg: 0x0000 --> cnt: 0xa = 10 The problem undo segment is usn#=13(d in hex) --> cnt: 0xa = 10 means this TX has only 10 chained records *** 2007-07-10 10:50:13.794 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [4147], [12], [10], [], [], [], [], Current SQL statement for this session: UPDATE POTENTIAL_VIOS .... Block header dump: 0x09004664 Object id on Block? Y seg/obj: 0x17adb csc: 0x00.132c4258 itc: 2 flg: O typ: 1 - DATA fsl: 0 fnx: 0x9004665 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000d.024.00004a96 0x00802fdc.13ae.0c ---- 1 fsc 0x0000.00000000 0x02 0x000d.020.00004a96 0x00802fdc.13ae.02 C--- 0 scn 0x0000.132c4193 We need to update a record that is part of an uncommitted TX and the changes are in Uba: 0x00802fdc.13ae.0c. Uba is the UndoBlockAddress = dba.seq#.rec# The Uba shows rec#= 0c -> the change is in rec chain 12, however the undo header shows this TX has only 10 chain records. Because of this mismatch we raise the ora-600[4147[12][10]