由于服务器突然掉电,重启机器后发现数据库无法启动。 数据库版本 oracle10201,OS REDHAT AS4 数据库无归档,无备份
一、查看错误 查看报警日志错误如下: Errors in file /u01/app/oracle/admin/monitor/bdump/monitorbj6_p001_9216.trc: ORA-00600: internal error code, arguments: [3020], [5], [145802], [21117322], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 5, block# 145802) ORA-10564: tablespace BWMONITOR ORA-01110: data file 5: '/data/oradata/monitor/bwmonitor/bwmonitor01.dbf' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
[root@CCN-BJ-6-579 ~]# su - oracle [oracle@CCN-BJ-6-579 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 4 10:30:04 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> select open_mode from v$database;
OPEN_MODE ---------- MOUNTED
尝试打开数据库 SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 2 needs media recovery ORA-01110: data file 2: '/data/oradata/monitor/undotbs01.dbf'
SQL> recover datafile 2; Media recovery complete. SQL> alter database open; alter database open * ERROR at line 1: ORA-01172: recovery of thread 1 stuck at block 145802 of file 5 ORA-01151: use media recovery to recover block, restore backup if needed
SQL> select file#,name from v$datafile where file#=5;
FILE# NAME ------------------------------------------------------------
5 /data/oradata/monitor/bwmonitor/bwmonitor01.dbf
1 rows selected.
SQL> recover datafile 5; ORA-00283: recovery session canceled due to errors ORA-12801: error signaled in parallel query server P001 ORA-00600: internal error code, arguments: [3020], [5], [145802], [21117322], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 5, block# 145802) ORA-10564: tablespace BWMONITOR ORA-01110: data file 5: '/data/oradata/monitor/bwmonitor/bwmonitor01.dbf' ORA-10560: block type 'FIRST L
使用dbv查看坏块 [oracle@CCN-BJ-6-579 bdump]$ dbv file=/data/oradata/monitor/bwmonitor/bwmonitor01.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Sun Nov 4 10:47:56 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oradata/monitor/bwmonitor/bwmonitor01.dbf Page 128843 is influx - most likely media corrupt Corrupt block relative dba: 0x0141f74b (file 5, block 128843) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0141f74b last change scn: 0x0000.14e1187e seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xbc660601 check value in block header: 0x7cba computed block checksum: 0x54ae
Page 128851 is influx - most likely media corrupt Corrupt block relative dba: 0x0141f753 (file 5, block 128851) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0141f753 last change scn: 0x0000.14e0bc66 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x187e0601 check value in block header: 0x1e80 computed block checksum: 0x1d10
DBV-00200: Block, dba 21108455, already marked corrupted
DBV-00200: Block, dba 21108463, already marked corrupted
DBV-00200: Block, dba 21108471, already marked corrupted
DBV-00200: Block, dba 21108479, already marked corrupted Page 136967 is influx - most likely media corrupt Corrupt block relative dba: 0x01421707 (file 5, block 136967) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x01421707 last change scn: 0x0000.14e0f557 seq: 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x2b38061b check value in block header: 0x4aaf computed block checksum: 0x3984
DBV-00200: Block, dba 21113354, already marked corrupted
DBV-00200: Block, dba 21113418, already marked corrupted Block Checking: DBA = 21113419, Block Type = KTB-managed data block data header at 0xb7f27264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141899 failed with check code 6101 Block Checking: DBA = 21113420, Block Type = KTB-managed data block data header at 0xb7f29264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141900 failed with check code 6101 Block Checking: DBA = 21113421, Block Type = KTB-managed data block data header at 0xb7f2b264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141901 failed with check code 6101 Block Checking: DBA = 21113422, Block Type = KTB-managed data block data header at 0xb7f2d264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=1 ktbbhitc=2 Page 141902 failed with check code 6101 Block Checking: DBA = 21113423, Block Type = KTB-managed data block data header at 0xb7f2f264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141903 failed with check code 6101 Block Checking: DBA = 21113424, Block Type = KTB-managed data block data header at 0xb7f31264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141904 failed with check code 6101 Block Checking: DBA = 21113425, Block Type = KTB-managed data block data header at 0xb7f33264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141905 failed with check code 6101
DBV-00200: Block, dba 21113426, already marked corrupted
DBV-00200: Block, dba 21113434, already marked corrupted Block Checking: DBA = 21113435, Block Type = KTB-managed data block data header at 0xb7f47264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141915 failed with check code 6101 Block Checking: DBA = 21113436, Block Type = KTB-managed data block data header at 0xb7f49264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141916 failed with check code 6101 Block Checking: DBA = 21113437, Block Type = KTB-managed data block data header at 0xb7f4b264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141917 failed with check code 6101 Block Checking: DBA = 21113438, Block Type = KTB-managed data block data header at 0xb7f4d264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141918 failed with check code 6101 Block Checking: DBA = 21113439, Block Type = KTB-managed data block data header at 0xb7f4f264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=1 ktbbhitc=2 Page 141919 failed with check code 6101 Block Checking: DBA = 21113440, Block Type = KTB-managed data block data header at 0xb7f51264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141920 failed with check code 6101 Block Checking: DBA = 21113441, Block Type = KTB-managed data block data header at 0xb7f53264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141921 failed with check code 6101
DBV-00200: Block, dba 21113442, already marked corrupted
DBV-00200: Block, dba 21113450, already marked corrupted Block Checking: DBA = 21113451, Block Type = KTB-managed data block data header at 0xb7f67264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=1 ktbbhitc=2 Page 141931 failed with check code 6101 Block Checking: DBA = 21113452, Block Type = KTB-managed data block data header at 0xb7f69264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141932 failed with check code 6101 Block Checking: DBA = 21113453, Block Type = KTB-managed data block data header at 0xb7f6b264 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=2 Page 141933 failed with check code 6101
DBVERIFY - Verification complete
Total Pages Examined : 384000 Total Pages Processed (Data) : 118235 Total Pages Failing (Data) : 17 Total Pages Processed (Index): 37073 Total Pages Failing (Index): 0 Total Pages Processed (Other): 1431 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 227258 Total Pages Marked Corrupt : 13 Total Pages Influx : 3 Highest block SCN : 350302023 (0.350302023) [oracle@CCN-BJ-6-579 bdump]$
Alter Database Backup Controlfile to Trace;
p=130:14:8927158941743069727::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,361172.1,1,1,1,helvetica
Subject: Resolving ORA-600[3020] Raised During Recovery Doc ID: Note:361172.1
由于是无归档,无备份,只好死马当活马医。 SQL> recover database test; ORA-10586: Test recovery had to corrupt 1 data block in order to proceed ORA-10573: Test recovery tested redo from change 350296383 to 350302447 ORA-10570: Test recovery complete
SQL> recover database allow 1 corruption; Media recovery complete. SQL> recover database test; ORA-10572: Test recovery canceled due to errors ORA-00264: no recovery required
SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel
SQL> conn /as sysdba Connected to an idle instance. SQL> alter database open; alter database open * ERROR at line 1: ORA-01034: ORACLE not available
SQL> startup ORACLE instance started.
Total System Global Area 1241513984 bytes Fixed Size 1219136 bytes Variable Size 134219200 bytes Database Buffers 1090519040 bytes Redo Buffers 15556608 bytes Database mounted. ORA-03113: end-of-file on communication channel 三、修复错误 ORA-600 [3705] 查看报警日志,错误变为 Errors in file /u01/app/oracle/admin/monitor/bdump/monitorbj6_lgwr_9651.trc: ORA-00600: internal error code, arguments: [3705], [1], [2], [2], [2], [], [], []
p=130:14:8927158941743069727::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,1054592.6,1,1,1,helvetica
Subject: ORA-449 AND ORA-600[3705] WHEN TRYING TO OPEN DATABASE Doc ID: Note:1054592.6
SQL> conn /as sysdba Connected to an idle instance. SQL> startup mount; ORACLE instance started.
Total System Global Area 1241513984 bytes Fixed Size 1219136 bytes Variable Size 134219200 bytes Database Buffers 1090519040 bytes Redo Buffers 15556608 bytes Database mounted. 创建控制文件备份 SQL> Alter Database Backup Controlfile to Trace;
Database altered.
SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' || 2 p.SPID || '.trc' 3 from (select p.SPID 4 from v$mystat m, v$session s, v$process p 5 where m.STATISTIC# = 1 6 and s.SID = m.SID 7 and p.ADDR = s.PADDR) p, 8 (select t.INSTANCE 9 from v$thread t, v$parameter v 10 where v.name = 'thread' 11 and (v.VALUE = 0 or t.THREAD# = to_number(v.VALUE))) i, 12 (select value from v$parameter where name = 'user_dump_dest') d 13 ;
D.VALUE||'/'||LOWER(RTRIM(I.INSTANCE,CHR(0)))||'_ORA_'||P.SPID||'.TRC' -------------------------------------------------------------------------------- /u01/app/oracle/admin/monitor/udump/monitorbj6_ora_10203.trc
创建pfile文件 SQL> create pfile='/tmp/aa.txt' from spfile;
File created.
SQL> shutdown immediate ORA-01109: database not open
Database dismounted. ORACLE instance shut down. 以pfile方式启动到nomount状态 SQL> startup nomount pfile='/tmp/aa.txt'; ORACLE instance started.
Total System Global Area 1241513984 bytes Fixed Size 1219136 bytes Variable Size 134219200 bytes Database Buffers 1090519040 bytes Redo Buffers 15556608 bytes 创建控制文件 SQL> CREATE CONTROLFILE REUSE DATABASE "MONITOR" NORESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 18688 7 LOGFILE 8 GROUP 1 '/data/oradata/monitor/redo01.log' SIZE 50M, 9 GROUP 2 '/data/oradata/monitor/redo02.log' SIZE 50M, 10 GROUP 3 '/data/oradata/monitor/redo03.log' SIZE 50M 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/data/oradata/monitor/system01.dbf', 14 '/data/oradata/monitor/undotbs01.dbf', 15 '/data/oradata/monitor/sysaux01.dbf', 16 '/data/oradata/monitor/users01.dbf', 17 '/data/oradata/monitor/bwmonitor/bwmonitor01.dbf', 18 '/data/oradata/monitor/bwmonitor/bw_m_c_1.dbf', 19 '/data/oradata/monitor/bwmonitor/bw_m_c_2.dbf', 20 '/data/oradata/monitor/bwmonitor/bw_m_c_3.dbf', 21 '/data/oradata/monitor/bwmonitor/bw_m_c_4.dbf' 22 CHARACTER SET ZHS16GBK 23 ;
Control file created.
SQL> select open_mode from v$database;
OPEN_MODE ---------- MOUNTED
打开数据库,提示需要进行介质恢复 SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/data/oradata/monitor/system01.dbf'
执行介质恢复。 SQL> recover database; Media recovery complete. 打开数据库 SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE ---------- READ WRITE
SQL> 最后数据库成功启动。 注意数据库启动后要添加临时表空间的数据文件。 |