undo损坏故障恢复（二）ORA

undo 故障诊断与恢复（二）

今天是2013-09-01，目前困扰我将近一周的问题，终于解决了，我非常感谢帮助我的朋友，也非常感谢管我要钱然后替我解决问题的朋友（我没采用）。这更激发了我一定要解决这个问题的斗志。我写这篇笔记，就是为了帮助那些遇到同样困难的朋友，我觉得‘闻道有先后，术业有专攻’，没有人是无法被取代的。如果有朋友搜到我这篇文章，希望我能给你带来问题的解决办法。先描述一下，我模拟的这种问题的情景： 1）数据处于open状态,非归档，无备份 2）直接rm掉undo表空间的所有数据文件 3）直接使用shutdown abort 关闭数据

首先使用一般方法进行恢复：

*.undo_management='MANUAL' *.undo_tablespace='UNDOTBS1' _corrupted_rollback_segments=(_SYSSMU10_2912317599$,_SYSSMU1_2568952200$,_SYSSMU2_3407863847$,_SYSSMU3_3709763726$,_SYSSMU4_1190056585$,_SYSSMU5_1631096226$,_SYSSMU6_1599787359$,_SYSSMU7_1113629270$,_SYSSMU8_2926058019$,_SYSSMU9_890209428$) "pfile.ora" 177L, 6491C written [oracle@oracle-one ~]$ sqlplsu / as sysdba -bash: sqlplsu: command not found [oracle@oracle-one ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:19:27 2013

Connected to an idle instance.

SQL> startup mount pfile='/home/oracle/pfile.ora'; ORACLE instance started.

Database altered.

SQL> alter database open;

Database altered.

SQL>

无论采用重新重建undo或是加入隐含参数._corrupted_rollback_segments，_offline_rollback_segments都无济于事可以尝试采用如下步骤。步骤如下： [root@oracle-one ~]# su - oracle [oracle@oracle-one ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:09:31 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

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

1）首先启动数据库

SQL> startup ORACLE instance started.

Total System Global Area 405020672 bytes Fixed Size 2213816 bytes Variable Size 251660360 bytes Database Buffers 146800640 bytes Redo Buffers 4345856 bytes Database mounted. Database opened. SQL> SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oracle-one ~]$ pwd /home/oracle [oracle@oracle-one ~]$ cd /opt/app/oracle/RHYS/ [oracle@oracle-one RHYS]$ ls control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf test.dbf undotbs01.dbf users01.dbf

2）删除所有undo数据文件

[oracle@oracle-one RHYS]$ rm undotbs01.dbf [oracle@oracle-one RHYS]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:10:25 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

3）关闭数据库然后启动mount状态使用10046事件跟踪数据库启动过程来诊断问题

SQL> shutdown abort; ORACLE instance shut down. SQL> startup mount; ORACLE instance started.

System altered.

SQL> alter database open; alter database open * ERROR at line 1: ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf'

SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oracle-one RHYS]$ ls control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf test.dbf users01.dbf

查看alert日志如下：

[oracle@oracle-one trace]$ vi alert_RHYS.log Sun Aug 04 19:36:35 2013 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Shared memory segment for instance monitoring created Picked latch-free SCN scheme 3 Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST Autotune of undo retention is turned on. IMODE=BR ILAT =27 LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options. Using parameter settings in client-side pfile /opt/app/oracle/admin/RHYS/pfile/init.ora on machine oracle-one System parameters with non-default values: processes = 150 memory_target = 448M control_files = "/opt/app/oracle/RHYS/control01.ctl" control_files = "/opt/app/oracle/flash_recovery_area/RHYS/control02.ctl" db_block_size = 8192 compatible = "11.2.0.0.0" db_recovery_file_dest = "/opt/app/oracle/flash_recovery_area" db_recovery_file_dest_size= 4977M undo_tablespace = "UNDOTBS1" remote_login_passwordfile= "EXCLUSIVE" db_domain = "ORACLE" dispatchers = "(PROTOCOL=TCP) (SERVICE=RHYSXDB)" audit_file_dest = "/opt/app/oracle/admin/RHYS/adump" audit_trail = "DB" db_name = "RHYS" open_cursors = 300 diagnostic_dest = "/opt/app/oracle" Sun Aug 04 19:36:36 2013 PMON started with pid=2, OS id=8068 Sun Aug 04 19:36:36 2013 VKTM started with pid=3, OS id=8070 at elevated priority VKTM running at (10)millisec precision with DBRM quantum (100)ms Sun Aug 04 19:36:37 2013 GEN0 started with pid=4, OS id=8074 Sun Aug 04 19:36:37 2013 DIAG started with pid=5, OS id=8076 Sun Aug 04 19:36:37 2013 Wed Aug 14 21:10:40 2013 PSP0 started with pid=7, OS id=3670 Wed Aug 14 21:10:40 2013 DIA0 started with pid=8, OS id=3672 Wed Aug 14 21:10:40 2013 MMAN started with pid=9, OS id=3674 Wed Aug 14 21:10:40 2013 DBW0 started with pid=10, OS id=3676 Wed Aug 14 21:10:40 2013 LGWR started with pid=11, OS id=3678 Wed Aug 14 21:10:40 2013 CKPT started with pid=12, OS id=3680 Wed Aug 14 21:10:40 2013 SMON started with pid=13, OS id=3682 Wed Aug 14 21:10:40 2013 RECO started with pid=14, OS id=3684 Wed Aug 14 21:10:40 2013 MMON started with pid=15, OS id=3686 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Wed Aug 14 21:10:40 2013 MMNL started with pid=16, OS id=3688 starting up 1 shared server(s) ... ORACLE_BASE from environment = /opt/app/oracle Wed Aug 14 21:10:40 2013 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 2735932048 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Wed Aug 14 21:11:03 2013 OS Pid: 3698 executed alter system set events '10046 trace name context forever,level 12' alter database open Wed Aug 14 21:11:07 2013 Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_dbw0_3676.trc: ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3698.trc: ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' ORA-1157 signalled during: alter database open... Wed Aug 14 21:11:07 2013

查看open跟踪文件如下：

[oracle@oracle-one trace]$ vi RHYS_ora_3698.trc BINDS #5: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f43fee70e80 bln=22 avl=03 flg=05 value=1138 EXEC #5:c=1000,e=1080,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=2203911306,tim=1376486190407372 WAIT #5: nam='db file sequential read' ela= 37 file#=1 block#=705 blocks=1 obj#=79 tim=1376486190407651 WAIT #5: nam='db file sequential read' ela= 88 file#=1 block#=665 blocks=1 obj#=74 tim=1376486190407836 WAIT #5: nam='control file sequential read' ela= 8 file#=0 block#=1 blocks=1 obj#=74 tim=1376486190407905 WAIT #5: nam='control file sequential read' ela= 6 file#=0 block#=15 blocks=1 obj#=74 tim=1376486190407939 WAIT #5: nam='control file sequential read' ela= 4 file#=0 block#=17 blocks=1 obj#=74 tim=1376486190407955 WAIT #5: nam='control file sequential read' ela= 5 file#=0 block#=23 blocks=1 obj#=74 tim=1376486190407972 WAIT #5: nam='control file sequential read' ela= 5 file#=0 block#=1 blocks=1 obj#=74 tim=1376486190407995 WAIT #5: nam='control file sequential read' ela= 4 file#=0 block#=15 blocks=1 obj#=74 tim=1376486190408011 WAIT #5: nam='control file sequential read' ela= 3 file#=0 block#=17 blocks=1 obj#=74 tim=1376486190408025 WAIT #5: nam='control file sequential read' ela= 5 file#=0 block#=31 blocks=1 obj#=74 tim=1376486190408041 DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident) ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' FETCH #5:c=0,e=687,p=2,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=2203911306,tim=1376486190408156 STAT #5 id=1 cnt=0 pid=0 pos=1 obj=74 op='TABLE ACCESS BY INDEX ROWID SEQ$ (cr=0 pr=0 pw=0 time=0 us)' STAT #5 id=2 cnt=1 pid=1 pos=1 obj=79 op='INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=1 pw=0 time=0 us)' ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf'

*** 2013-08-14 21:16:30.408 USER (ospid: 3772): terminating the instance due to error 604 EXEC #1:c=552916,e=1017603,p=143,cr=4074,cu=36,mis=0,r=0,dep=0,og=1,plh=0,tim=1376486190558831

尝试采用一般办法进行恢复过程如下：

[oracle@oracle-one trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:12:38 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database create datafile '/opt/app/oracle/RHYS/undotbs01.dbf';

Database altered.

SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 3 needs media recovery ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf'

SQL> recover datafile 3; ORA-00279: change 2829 generated at 08/04/2013 19:37:47 needed for thread 1 ORA-00289: suggestion : /opt/app/oracle/flash_recovery_area/RHYS/archivelog/2013_08_14/o1_mf_1_1_%u_.arc ORA-00280: change 2829 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/opt/app/oracle/flash_recovery_area/RHYS/archivelog/2013_08_14/o1_mf_1_1_%u_.ar c' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3

ORA-00308: cannot open archived log '/opt/app/oracle/flash_recovery_area/RHYS/archivelog/2013_08_14/o1_mf_1_1_%u_.ar c' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3

SQL> alter database datafile '/opt/app/oracle/undotbs01.dbf' offline; alter database datafile '/opt/app/oracle/undotbs01.dbf' offline * ERROR at line 1: ORA-01516: nonexistent log file, data file, or temporary file "/opt/app/oracle/undotbs01.dbf"

SQL> alter database datafile '/opt/app/oracle/RHYS/undotbs01.dbf' offline drop;

Database altered.

SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' Process ID: 3714 Session ID: 1 Serial number: 7

SQL> exit

至此数据已经无法打开了。在此进行问题定位，跟踪事件。

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oracle-one trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:15:48 2013

Connected to an idle instance.

SQL> startup mount; ORACLE instance started.

System altered.

SQL> sexit SP2-0042: unknown command "sexit" - rest of line ignored. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oracle-one trace]$ ls -ltr total 10556

-rw-r-----. 1 oracle oinstall 296879 Aug 14 21:16 alert_RHYS.log

查看数据库alert日志如下：

[oracle@oracle-one trace]$ vi alert_RHYS.log ORACLE_BASE from environment = /opt/app/oracle Wed Aug 14 21:10:40 2013 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 2735932048 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Wed Aug 14 21:11:03 2013 OS Pid: 3698 executed alter system set events '10046 trace name context forever,level 12' alter database open Wed Aug 14 21:11:07 2013 Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_dbw0_3676.trc: ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3698.trc: ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' ORA-1157 signalled during: alter database open... Wed Aug 14 21:11:07 2013 Checker run found 1 new persistent data failures Wed Aug 14 21:13:55 2013 alter database create datafile '/opt/app/oracle/RHYS/undotbs01.dbf' Wed Aug 14 21:14:07 2013 Completed: alter database create datafile '/opt/app/oracle/RHYS/undotbs01.dbf' alter database open Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3714.trc: ORA-01113: file 3 needs media recovery ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' ORA-1113 signalled during: alter database open... Wed Aug 14 21:14:13 2013 Checker run found 1 new persistent data failures Wed Aug 14 21:14:26 2013 ALTER DATABASE RECOVER datafile 3 Media Recovery Start Serial Media Recovery started ORA-279 signalled during: ALTER DATABASE RECOVER datafile 3 ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /opt/app/oracle/flash_recovery_area/RHYS/archivelog/2013_08_14/o1_mf_1_1_%u_.arc Errors with log /opt/app/oracle/flash_recovery_area/RHYS/archivelog/2013_08_14/o1_mf_1_1_%u_.arc ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Lost write protection disabled Completed: ALTER DATABASE MOUNT Wed Aug 14 21:16:20 2013 OS Pid: 3772 executed alter system set events '10046 trace name context forever,level 12' alter database open Beginning crash recovery of 1 threads Started redo scan Completed redo scan read 18 KB redo, 3 data blocks need recovery Started redo application at Thread 1: logseq 91, block 3, scn 9934235 Recovery of Online Redo Log: Thread 1 Group 1 Seq 91 Reading mem 0 Mem# 0: /opt/app/oracle/RHYS/redo01.log Completed redo application of 0.01MB Completed crash recovery at Thread 1: logseq 91, block 39, scn 9954267 3 data blocks read, 3 data blocks written, 18 redo k-bytes read Wed Aug 14 21:16:29 2013 Thread 1 advanced to log sequence 92 (thread open) Thread 1 opened at log sequence 92 Current log# 3 seq# 92 mem# 0: /opt/app/oracle/RHYS/redo03.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Wed Aug 14 21:16:29 2013 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 WE8MSWIN1252 Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3772.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3772.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' Error 604 happened during db open, shutting down database USER (ospid: 3772): terminating the instance due to error 604 Instance terminated by USER, pid = 3772 ORA-1092 signalled during: alter database open... opiodr aborting process unknown ospid (3772) as a result of ORA-1092 Wed Aug 14 21:16:30 2013

查看数据库open日志如下：

[oracle@oracle-one trace]$ vi RHYS_ora_3772.trc Trace file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3772.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1 System name: Linux Node name: oracle-one Release: 2.6.39-400.17.1.el6uek.x86_64 Version: #1 SMP Fri Feb 22 18:16:18 PST 2013 Machine: x86_64 Instance name: RHYS Redo thread mounted by this instance: 1 Oracle process number: 19 Unix process pid: 3772, image: oracle@oracle-one (TNS V1-V3)

*** 2013-08-14 21:16:20.678 *** SESSION ID:(1.5) 2013-08-14 21:16:20.678 *** CLIENT ID:() 2013-08-14 21:16:20.678 *** SERVICE NAME:() 2013-08-14 21:16:20.678 *** MODULE NAME:(sqlplus@oracle-one (TNS V1-V3)) 2013-08-14 21:16:20.678 *** ACTION NAME:() 2013-08-14 21:16:20.678

BINDS #5: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f43fee70e80 bln=22 avl=03 flg=05 value=1138 EXEC #5:c=1000,e=1080,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=2203911306,tim=1376486190407372 WAIT #5: nam='db file sequential read' ela= 37 file#=1 block#=705 blocks=1 obj#=79 tim=1376486190407651 WAIT #5: nam='db file sequential read' ela= 88 file#=1 block#=665 blocks=1 obj#=74 tim=1376486190407836 WAIT #5: nam='control file sequential read' ela= 8 file#=0 block#=1 blocks=1 obj#=74 tim=1376486190407905 WAIT #5: nam='control file sequential read' ela= 6 file#=0 block#=15 blocks=1 obj#=74 tim=1376486190407939 WAIT #5: nam='control file sequential read' ela= 4 file#=0 block#=17 blocks=1 obj#=74 tim=1376486190407955 WAIT #5: nam='control file sequential read' ela= 5 file#=0 block#=23 blocks=1 obj#=74 tim=1376486190407972 WAIT #5: nam='control file sequential read' ela= 5 file#=0 block#=1 blocks=1 obj#=74 tim=1376486190407995 WAIT #5: nam='control file sequential read' ela= 4 file#=0 block#=15 blocks=1 obj#=74 tim=1376486190408011 WAIT #5: nam='control file sequential read' ela= 3 file#=0 block#=17 blocks=1 obj#=74 tim=1376486190408025 WAIT #5: nam='control file sequential read' ela= 5 file#=0 block#=31 blocks=1 obj#=74 tim=1376486190408041 DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident) ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' FETCH #5:c=0,e=687,p=2,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=2203911306,tim=1376486190408156 STAT #5 id=1 cnt=0 pid=0 pos=1 obj=74 op='TABLE ACCESS BY INDEX ROWID SEQ$ (cr=0 pr=0 pw=0 time=0 us)' STAT #5 id=2 cnt=1 pid=1 pos=1 obj=79 op='INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=1 pw=0 time=0 us)' ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf' ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf'

*** 2013-08-14 21:16:30.408 USER (ospid: 3772): terminating the instance due to error 604 EXEC #1:c=552916,e=1017603,p=143,cr=4074,cu=36,mis=0,r=0,dep=0,og=1,plh=0,tim=1376486190558831

从启动数据库中看到有两个block，存在问题，然后先转储查看状态：如下：

[oracle@oracle-one trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:17:06 2013

Connected to an idle instance.

SQL> startup mount; ORACLE instance started.

System altered.

SQL> exit

查看转储文件如下：

[oracle@oracle-one trace]$ vi RHYS_ora_3828.trc Trace file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3828.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1 System name: Linux Node name: oracle-one Release: 2.6.39-400.17.1.el6uek.x86_64 Version: #1 SMP Fri Feb 22 18:16:18 PST 2013 Machine: x86_64 Instance name: RHYS Redo thread mounted by this instance: 1 Oracle process number: 19 Unix process pid: 3828, image: oracle@oracle-one (TNS V1-V3)

*** 2013-08-14 21:17:47.329 *** SESSION ID:(1.5) 2013-08-14 21:17:47.329 *** CLIENT ID:() 2013-08-14 21:17:47.329 *** SERVICE NAME:() 2013-08-14 21:17:47.329 *** MODULE NAME:(sqlplus@oracle-one (TNS V1-V3)) 2013-08-14 21:17:47.329 *** ACTION NAME:() 2013-08-14 21:17:47.329

Start dump data block from file /opt/app/oracle/RHYS/system01.dbf minblk 705 maxblk 705 File Number=1, Blksiz=8192, File Type=3 DATA

Block header dump: 0x004002c1 Object id on Block? Y seg/obj: 0x4f csc: 0x00.ce0c2 itc: 2 flg: - typ: 2 - INDEX fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0003.007.000002f5 0x00c020f7.0092.11 --U- 1 fsc 0x0000.000ce0c3 转储另一个块进行查看：如下：

[oracle@oracle-one trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:19:00 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system dump datafile '/opt/app/oracle/RHYS/system01.dbf' block 665;

System altered.

SQL> exit

[oracle@oracle-one trace]$ vi RHYS_ora_3834.trc Trace file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3834.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1 System name: Linux Node name: oracle-one Release: 2.6.39-400.17.1.el6uek.x86_64 Version: #1 SMP Fri Feb 22 18:16:18 PST 2013 Machine: x86_64 Instance name: RHYS Redo thread mounted by this instance: 1 Oracle process number: 19 Unix process pid: 3834, image: oracle@oracle-one (TNS V1-V3)

*** 2013-08-14 21:19:21.633 *** SESSION ID:(1.7) 2013-08-14 21:19:21.633 *** CLIENT ID:() 2013-08-14 21:19:21.633 *** SERVICE NAME:() 2013-08-14 21:19:21.633 *** MODULE NAME:(sqlplus@oracle-one (TNS V1-V3)) 2013-08-14 21:19:21.633 *** ACTION NAME:() 2013-08-14 21:19:21.633

Start dump data block from file /opt/app/oracle/RHYS/system01.dbf minblk 665 maxblk 665 File Number=1, Blksiz=8192, File Type=3 DATA

Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0003.012.0000030c 0x00c0208f.0096.29 ---- 1 fsc 0x0000.00000000 0x02 0x000a.007.000002fd 0x00c0029f.00a0.02 ---- 1 fsc 0x0000.00000000 bdba: 0x00400299 data_block_dump,data header at 0x7f7c2e1b0a5c =============== tsiz: 0x1fa0 hsiz: 0xd6 pbl: 0x7f7c2e1b0a5c 76543210 flag=-------- ntab=1 nrow=98 frre=-1 fsbo=0xd6 fseo=0x226 avsp=0x350 tosp=0x350 0xe:pti[0] nrow=98 offs=0 [oracle@oracle-one trace]$ cd [oracle@oracle-one ~]$ cd study/bbed/ [oracle@oracle-one bbed]$ ls bbed.par bbed.txt bifile.bbd log.bbd

至此，可以明白，seg$属于bootrap$过程中的内部表，但是现在查看665和705这两个块，存在没有commit的事务，因此数据库无法启动。对于bootstrap$是数据开始初始化的过程，可以参考其他资料进行研究。目前要做的就是绕过，不让该数据块进行事务恢复，因为目前已经没发恢复了。需要修改这两个块的itl使其flag为c状态。 (摘录于maclean的blog中的一段话） FLAG – status of the txn, ie., commit, uncommitted etc., . If the cleanout was performed; that is, the block was modified: The block is flagged as containing delayed-logging cleanout (the flag KTBFUPB in ktbitflg indicates in this case a cleanout at commit time) Flag – Transaction flag ---- = Uncommitted -B-- = The UBA (Undo Block Address) contains undo for this ITL --U- = Committed by fast commits & delayed block cleanout has not occurred ---T = Transaction active at block cleanout SCN -C-U- = Block cleaned by delayed block cleanout, and rollback segment info is overwritten.

必须使用块修改工具BBED来修改存在问题的数据块将ITL事务槽的FLAG从U修改为C(Commit)。 TRANSACTION_COMMITED = 0×08; TRANSACTION_UPBOUND = 0×02; TRANSACTION_ACTIVE = 0×01; Flag= –U- 即TRANSACTION_UPBOUND时flag所占字节为0×02，需要将该字节修改为TRANSACTION_COMMITED = 0×08;

在little endian 下 0x8000 其实是 0x08 0x00 ，即 C---

[oracle@oracle-one bbed]$ bbed parfile=./bbed.par Password:

BBED: Release 2.0.0.0.0 - Limited Production on Wed Aug 14 21:24:27 2013

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 1,665 DBA 0x00400299 (4194969 1,665)

BBED> map File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 665 Dba:0x00400299 ------------------------------------------------------------ KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 72 bytes @20

struct kdbh, 14 bytes @92

struct kdbt[1], 4 bytes @106

sb2 kdbr[98] @110

ub1 freespace[336] @306

ub1 rowdata[7546] @642

ub4 tailchk @8188

BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000004a ub4 ktbbhod1 @24 0x0000004a struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x0097474c ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x02 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x00000000 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0003 ub2 kxidslt @46 0x0012 ub4 kxidsqn @48 0x0000030c struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c0208f ub2 kubaseq @56 0x0096 ub1 kubarec @58 0x29 ub2 ktbitflg @60 0x0001 (NONE) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x00000000 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x000a ub2 kxidslt @70 0x0007 ub4 kxidsqn @72 0x000002fd struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c0029f ub2 kubaseq @80 0x00a0 ub1 kubarec @82 0x02 ub2 ktbitflg @84 0x0001 (NONE) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000

BBED> map File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 665 Dba:0x00400299 ------------------------------------------------------------ KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 72 bytes @20

struct kdbh, 14 bytes @92

struct kdbt[1], 4 bytes @106

sb2 kdbr[98] @110

ub1 freespace[336] @306

ub1 rowdata[7546] @642

ub4 tailchk @8188

BBED> p kdbh struct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) sb1 kdbhntab @93 1 sb2 kdbhnrow @94 98 sb2 kdbhfrre @96 -1 sb2 kdbhfsbo @98 214 sb2 kdbhfseo @100 550 sb2 kdbhavsp @102 848 sb2 kdbhtosp @104 848

BBED> map File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 665 Dba:0x00400299 ------------------------------------------------------------ KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 72 bytes @20

struct kdbh, 14 bytes @92

struct kdbt[1], 4 bytes @106

sb2 kdbr[98] @110

ub1 freespace[336] @306

ub1 rowdata[7546] @642

ub4 tailchk @8188

BBED> p kcbh struct kcbh, 20 bytes @0 ub1 type_kcbh @0 0x06 ub1 frmt_kcbh @1 0xa2 ub1 spare1_kcbh @2 0x00 ub1 spare2_kcbh @3 0x00 ub4 rdba_kcbh @4 0x00400299 ub4 bas_kcbh @8 0x0097475c ub2 wrp_kcbh @12 0x0000 ub1 seq_kcbh @14 0x01 ub1 flg_kcbh @15 0x04 (KCBHFCKV) ub2 chkval_kcbh @16 0xcca6 ub2 spare3_kcbh @18 0x0000

BBED> map File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 665 Dba:0x00400299 ------------------------------------------------------------ KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 72 bytes @20

struct kdbh, 14 bytes @92

struct kdbt[1], 4 bytes @106

sb2 kdbr[98] @110

ub1 freespace[336] @306

ub1 rowdata[7546] @642

ub4 tailchk @8188

BBED> modify /x 0x0080 ktbbh.ktbbhitl[0].ktbitflg Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 665 Offsets: 60 to 571 Dba:0x00400299 ------------------------------------------------------------------------ 00800000 00000000 0a000700 fd020000 9f02c000 a0000200 01000000 00000000 00016200 ffffd600 26025003 50030000 6200571f 191fd11e 8f1e4c1e 021ede03 6a1d2b04 1f1dd51c 8b1c411c f71bad1b 6c1b2b1b e91a9f1a 541a131a d2198919 4019f718 ad186318 1a18d017 86173d17 fb16b516 75162f16 e5159b15 51150715 ba147914 3814ed13 a2135713 0c13ca12 80123612 eb11a011 55110b11 c1107710 2d10e30f 990f4f0f 050fc40e 830e420e 010ec00d 9c03390d f00c5103 5c0c120c c80b7102 2602eb0a a20a580a 120ad109 90094f09 0a09c108 77082d08 e3079907 4f070507 bb067106 2706dd05 93054905 ff04b504 6b040000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 2c000a03 c2035b02 c10202c1 0205c405 1e324402 c1020180 02c30204 c3020102 202d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d01802c 000a03c2 324602c1 0202c102 0fce6464 64646464 64646464 64646464 01800180 02c20203 c2020220 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 01802c00 0a03c232 4402c102 02c1020f ce646464 64646464 64646464 64646401 80018002 c20203c2 0202202d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d01

BBED> modify /x 0x0080 ktbbh.ktbbhitl[1].ktbitflg File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 665 Offsets: 84 to 595 Dba:0x00400299 ------------------------------------------------------------------------ 00800000 00000000 00016200 ffffd600 26025003 50030000 6200571f 191fd11e 8f1e4c1e 021ede03 6a1d2b04 1f1dd51c 8b1c411c f71bad1b 6c1b2b1b e91a9f1a 541a131a d2198919 4019f718 ad186318 1a18d017 86173d17 fb16b516 75162f16 e5159b15 51150715 ba147914 3814ed13 a2135713 0c13ca12 80123612 eb11a011 55110b11 c1107710 2d10e30f 990f4f0f 050fc40e 830e420e 010ec00d 9c03390d f00c5103 5c0c120c c80b7102 2602eb0a a20a580a 120ad109 90094f09 0a09c108 77082d08 e3079907 4f070507 bb067106 2706dd05 93054905 ff04b504 6b040000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 2c000a03 c2035b02 c10202c1 0205c405 1e324402 c1020180 02c30204 c3020102 202d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d01802c 000a03c2 324602c1 0202c102 0fce6464 64646464 64646464 64646464 01800180 02c20203 c2020220 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 01802c00 0a03c232 4402c102 02c1020f ce646464 64646464 64646464 64646401 80018002 c20203c2 0202202d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d01 802c000a 03c23243 02c10202 c1020fce 64646464 64646464

BBED> map File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 665 Dba:0x00400299 ------------------------------------------------------------ KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 72 bytes @20

struct kdbh, 14 bytes @92

struct kdbt[1], 4 bytes @106

sb2 kdbr[98] @110

ub1 freespace[336] @306

ub1 rowdata[7546] @642

ub4 tailchk @8188

BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000004a ub4 ktbbhod1 @24 0x0000004a struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x0097474c ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x02 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x00000000 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0003 ub2 kxidslt @46 0x0012 ub4 kxidsqn @48 0x0000030c struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c0208f ub2 kubaseq @56 0x0096 ub1 kubarec @58 0x29 ub2 ktbitflg @60 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x00000000 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x000a ub2 kxidslt @70 0x0007 ub4 kxidsqn @72 0x000002fd struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c0029f ub2 kubaseq @80 0x00a0 ub1 kubarec @82 0x02 ub2 ktbitflg @84 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000

BBED> sum apply Check value for File 1, Block 665: current = 0xcca6, required = 0xcca6

BBED> exit

[oracle@oracle-one bbed]$ bbed parfile=./bbed.par Password:

BBED: Release 2.0.0.0.0 - Limited Production on Wed Aug 14 21:28:17 2013

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 1,705 DBA 0x004002c1 (4195009 1,705)

BBED> map File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 705 Dba:0x004002c1 ------------------------------------------------------------ KTB Data Block (Index Leaf)

struct kcbh, 20 bytes @0

struct ktbbh, 72 bytes @20

struct kdxle, 32 bytes @92

sb2 kd_off[222] @124

ub1 freespace[4477] @568

ub1 rowdata[3075] @5045

ub4 tailchk @8188

BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x02 (KDDBTINDEX) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000004f ub4 ktbbhod1 @24 0x0000004f struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000ce0c2 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x02 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x00000000 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0000 ub2 kxidslt @46 0x0000 ub4 kxidsqn @48 0x00000000 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00000000 ub2 kubaseq @56 0x0000 ub1 kubarec @58 0x00 ub2 ktbitflg @60 0x0000 (NONE) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x00000000 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0003 ub2 kxidslt @70 0x0007 ub4 kxidsqn @72 0x000002f5 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c020f7 ub2 kubaseq @80 0x0092 ub1 kubarec @82 0x11 ub2 ktbitflg @84 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x000ce0c3

BBED> modify /x 0x0080 ktbbh.ktbbhitl[0].ktbitflg Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 705 Offsets: 60 to 571 Dba:0x004002c1 ------------------------------------------------------------------------ 00800000 00000000 03000700 f5020000 f720c000 92001100 01200000 c3e00c00 00008001 00000000 de00e001 5d13a712 00000000 00000000 00000000 06000000 601f0000 551f491f 3d1f311f 251f191f 0d1f011f f51ee91e dd1ed11e c51eb91e ad1ea11e 951e891e 7d1e711e 651e591e 4d1e411e 351e291e 1d1e111e 051ef91d ed1de11d d51dc91d bd1db11d a51d991d 8d1d811d 751d691d 5d1d511d 451d391d 2d1d211d 151d091d fd1cf11c e51cd91c cd1cc11c b51ca91c 9d1c911c 851c791c 6d1c611c 551c491c 3d1c311c 251c191c 0d1c011c f51be91b dd1bd11b c51bb91b ad1ba11b 951b891b 7d1b711b 651b591b 4d1b411b 351b291b 1d1b111b 051bf91a ed1ae11a d51ac91a bd1ab11a a51a991a 8d1a811a 751a691a 5c1a4f1a 421a351a 281a1b1a 0e1a011a f419e719 da19cd19 c019b319 a6199919 8c197f19 72196519 58194b19 3e193119 24191719 0a19fd18 f018d618 c918bc18 af18a218 88187b18 6e186118 54184718 3a182d18 20181318 0718fa17 ed179f17 92178517 78176b17 5f175217 45173817 2b171e17 11170417 f716ea16 dd16b616 a9169c16 8f168216 75166816 5b164e16 41163416 27161a16 0d160016 f315e615 d915cc15 bf15b215 a5159815 8b157e15 71156415 57154a15 3d153015 23151615 0915fc14 ef14e214 d514c814 bb14ae14 a1149414 87147a14 6d146014 53144614 39142c14 6a135d13

BBED> modify /x 0x0080 ktbbh.ktbbhitl[1].ktbitflg File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 705 Offsets: 84 to 595 Dba:0x004002c1 ------------------------------------------------------------------------ 00800000 c3e00c00 00008001 00000000 de00e001 5d13a712 00000000 00000000 00000000 06000000 601f0000 551f491f 3d1f311f 251f191f 0d1f011f f51ee91e dd1ed11e c51eb91e ad1ea11e 951e891e 7d1e711e 651e591e 4d1e411e 351e291e 1d1e111e 051ef91d ed1de11d d51dc91d bd1db11d a51d991d 8d1d811d 751d691d 5d1d511d 451d391d 2d1d211d 151d091d fd1cf11c e51cd91c cd1cc11c b51ca91c 9d1c911c 851c791c 6d1c611c 551c491c 3d1c311c 251c191c 0d1c011c f51be91b dd1bd11b c51bb91b ad1ba11b 951b891b 7d1b711b 651b591b 4d1b411b 351b291b 1d1b111b 051bf91a ed1ae11a d51ac91a bd1ab11a a51a991a 8d1a811a 751a691a 5c1a4f1a 421a351a 281a1b1a 0e1a011a f419e719 da19cd19 c019b319 a6199919 8c197f19 72196519 58194b19 3e193119 24191719 0a19fd18 f018d618 c918bc18 af18a218 88187b18 6e186118 54184718 3a182d18 20181318 0718fa17 ed179f17 92178517 78176b17 5f175217 45173817 2b171e17 11170417 f716ea16 dd16b616 a9169c16 8f168216 75166816 5b164e16 41163416 27161a16 0d160016 f315e615 d915cc15 bf15b215 a5159815 8b157e15 71156415 57154a15 3d153015 23151615 0915fc14 ef14e214 d514c814 bb14ae14 a1149414 87147a14 6d146014 53144614 39142c14 6a135d13 00000000 00000000 00000000 00000000 00000000 00000000

BBED> map File: /opt/app/oracle/RHYS/system01.dbf (1) Block: 705 Dba:0x004002c1 ------------------------------------------------------------ KTB Data Block (Index Leaf)

struct kcbh, 20 bytes @0

struct ktbbh, 72 bytes @20

struct kdxle, 32 bytes @92

sb2 kd_off[222] @124

ub1 freespace[4477] @568

ub1 rowdata[3075] @5045

ub4 tailchk @8188

BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x02 (KDDBTINDEX) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000004f ub4 ktbbhod1 @24 0x0000004f struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000ce0c2 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x02 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x00000000 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0000 ub2 kxidslt @46 0x0000 ub4 kxidsqn @48 0x00000000 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00000000 ub2 kubaseq @56 0x0000 ub1 kubarec @58 0x00 ub2 ktbitflg @60 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x00000000 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0003 ub2 kxidslt @70 0x0007 ub4 kxidsqn @72 0x000002f5 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c020f7 ub2 kubaseq @80 0x0092 ub1 kubarec @82 0x11 ub2 ktbitflg @84 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x000ce0c3

BBED> sum apply Check value for File 1, Block 705: current = 0x8989, required = 0x8989

这时候验证的时候会出现错，暂且忽略。

BBED> verify DBVERIFY - Verification starting FILE = /opt/app/oracle/RHYS/system01.dbf BLOCK = 705

Block Checking: DBA = 4195009, Block Type = KTB-managed data block **** actual rows locked by itl 2 = 1 != # in trans. header = 0 ---- end index block validation Block 705 failed with check code 6401

DBVERIFY - Verification complete

Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 1 Total Blocks Failing (Index): 1 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED

BBED> set dba 1,655 DBA 0x0040028f (4194959 1,655)

BBED> verify DBVERIFY - Verification starting FILE = /opt/app/oracle/RHYS/system01.dbf BLOCK = 655

DBVERIFY - Verification complete

Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 1 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED

BBED> set dba 1,665 DBA 0x00400299 (4194969 1,665)

BBED> verify DBVERIFY - Verification starting FILE = /opt/app/oracle/RHYS/system01.dbf BLOCK = 665

Block Checking: DBA = 4194969, Block Type = KTB-managed data block data header at 0x1a2aa5c kdbchk: row locked by non-existent transaction table=0 slot=72 lockid=2 ktbbhitc=2 Block 665 failed with check code 6101

DBVERIFY - Verification complete

Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED

至此数据块的itl修改完毕，然后再重新转储一下每个块信息，查看一下状态。

[oracle@oracle-one bbed]$ cd [oracle@oracle-one ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:46:11 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE -------------------- MOUNTED

SQL> alter system dump datafile '/opt/app/oracle/RHYS/system01.dbf' block 665;

System altered.

SQL> !

[oracle@oracle-one trace]$ vi RHYS_ora_3913.trc Trace file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3913.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1 System name: Linux Node name: oracle-one Release: 2.6.39-400.17.1.el6uek.x86_64 Version: #1 SMP Fri Feb 22 18:16:18 PST 2013 Machine: x86_64 Instance name: RHYS Redo thread mounted by this instance: 1 Oracle process number: 19 Unix process pid: 3913, image: oracle@oracle-one (TNS V1-V3)

*** 2013-08-14 21:46:28.499 *** SESSION ID:(1.11) 2013-08-14 21:46:28.500 *** CLIENT ID:() 2013-08-14 21:46:28.500 *** SERVICE NAME:() 2013-08-14 21:46:28.500 *** MODULE NAME:(sqlplus@oracle-one (TNS V1-V3)) 2013-08-14 21:46:28.500 *** ACTION NAME:() 2013-08-14 21:46:28.500

Error: alter system dump datafile/tempfile: file 1 not readable

*** 2013-08-14 21:48:49.495 Start dump data block from file /opt/app/oracle/RHYS/system01.dbf minblk 665 maxblk 665

Block header dump: 0x00400299 Object id on Block? Y seg/obj: 0x4a csc: 0x00.97474c itc: 2 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0003.012.0000030c 0x00c0208f.0096.29 C--- 0 scn 0x0000.00000000 0x02 0x000a.007.000002fd 0x00c0029f.00a0.02 C--- 0 scn 0x0000.00000000 bdba: 0x00400299 data_block_dump,data header at 0x7fe2e3c93a5c =============== tsiz: 0x1fa0 hsiz: 0xd6 pbl: 0x7fe2e3c93a5c 76543210 flag=-------- ntab=1 nrow=98 frre=-1 fsbo=0xd6 fseo=0x226 avsp=0x350 tosp=0x350 0xe:pti[0] nrow=98 offs=0 0x12:pri[0] offs=0x1f57 "RHYS_ora_3913.trc" 2047L, 76502C written [oracle@oracle-one trace]$ exit exit SQL> alter system dump datafile '/opt/app/oracle/RHYS/system01.dbf' block 705;

System altered.

[oracle@oracle-one trace]$ vi RHYS_ora_3913.trc fsl: 0 fnx: 0x0 ver: 0x01

SQL> alter system dump datafile '/opt/app/oracle/RHYS/system01.dbf' block 705;

System altered.

SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oracle-one trace]$ vi RHYS_ora_4026.trc Trace file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_4026.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1 System name: Linux Node name: oracle-one Release: 2.6.39-400.17.1.el6uek.x86_64 Version: #1 SMP Fri Feb 22 18:16:18 PST 2013 Machine: x86_64 Instance name: RHYS Redo thread mounted by this instance: 1 Oracle process number: 19 Unix process pid: 4026, image: oracle@oracle-one (TNS V1-V3)

*** 2013-08-14 21:52:18.547 *** SESSION ID:(1.13) 2013-08-14 21:52:18.548 *** CLIENT ID:() 2013-08-14 21:52:18.548 *** SERVICE NAME:() 2013-08-14 21:52:18.548 *** MODULE NAME:(sqlplus@oracle-one (TNS V1-V3)) 2013-08-14 21:52:18.548 *** ACTION NAME:() 2013-08-14 21:52:18.548

Start dump data block from file /opt/app/oracle/RHYS/system01.dbf minblk 705 maxblk 705 File Number=1, Blksiz=8192, File Type=3 DATA

Block header dump: 0x004002c1 Object id on Block? Y seg/obj: 0x4f csc: 0x00.ce0c2 itc: 2 flg: - typ: 2 - INDEX fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000 0x02 0x0003.007.000002f5 0x00c020f7.0092.11 C--- 0 scn 0x0000.000ce0c3 Leaf block dump =============== header address 139841897286236=0x7f2f7a9cca5c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 222 kdxcofbo 480=0x1e0 kdxcofeo 4957=0x135d kdxcoavs 4775 kdxlespl 0 kdxlende 0 [oracle@oracle-one trace]$ cd

重新启动数据库，查看状态：如下：

[oracle@oracle-one trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:53:55 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE -------------------- MOUNTED

SQL> shutdown immediate ORA-01109: database not open

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

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _optimizer_undo_cost_change string 11.2.0.1 undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1

这个时候，数据库已经打开了，但是目前存在一个问题是无论是创建表空间还是删除undo表空间，都会报如下错误。据我个人认为，该错误其实就是说名数据库元数据已经存在不一致的问题，那么如下需要做的就是先exp数据，然后再创建实例，重新导入数据。

SQL> create undo tablespace undotbs2 datafile '/opt/app/oracle/RHYS/undotbs2.dbf' size 200M autoextend off; create undo tablespace undotbs2 datafile '/opt/app/oracle/RHYS/undotbs2.dbf' size 200M autoextend off * ERROR at line 1: ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [], [], [], [], []

如有朋友，在读完之后，存在其他思路，希望你告诉我。谢谢。

云奕文章网

undo损坏故障恢复（二）ORA

相关推荐：