undo文件丢失
SQL> startup ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1344652 bytes Variable Size 218106740 bytes Database Buffers 88080384 bytes Redo Buffers 6328320 bytes Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' [root@xifenfei ~]# ls -l /u01/oracle/oradata/ora11g/undotbs01.dbf ls: /u01/oracle/oradata/ora11g/undotbs01.dbf: No such file or directory
尝试offline 数据文件
SQL> alter database datafile 3 offline; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Process ID: 16365 Session ID: 125 Serial number: 5
通过这里的错误提示可以看到因为datafile 3丢失并且offline,而在数据库启动的时候需要去使用该数据文件中的回滚段去回滚事务,但是因为该数据文件被offline,使得回滚不能进行从而出现该错误.这里出现ORA-00704和ORA-00604的错误,根据感觉不能轻易的使用屏蔽回滚段的方法实现,但是还是做一尝试.使用其他方法找到回滚段.
屏蔽回滚段后重启库
SQL> startup mount pfile='/tmp/pfile' force ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1344652 bytes Variable Size 218106740 bytes Database Buffers 88080384 bytes Redo Buffers 6328320 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Process ID: 16696 Session ID: 125 Serial number: 5
增加了合适的回滚段屏蔽,但是数据库还不能正常启动,而且依然报ORA-00704,决定对数据库启动过程做一个10046跟踪,来判断在哪一步出了问题
对数据库open过程做10046
SQL> conn / as sysdba Connected to an idle instance. SQL> startup pfile='/tmp/pfile' mount; ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1344652 bytes Variable Size 218106740 bytes Database Buffers 88080384 bytes Redo Buffers 6328320 bytes Database mounted. SQL> oradebug setmypid Statement processed. SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 Statement processed. SQL> oradebug TRACEFILE_NAME /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16869.trc SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Process ID: 16869 Session ID: 125 Serial number: 5 --trace中发现 PARSE ERROR #3063083528:len=60 dep=1 uid=0 oct=3 lid=0 tim=1342472283605146 err=604 SELECT NULL FROM PROPS$ WHERE NAME='BOOTSTRAP_UPGRADE_ERROR' ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'
通过这里可以看出来很可能是在SELECT PROPS$表的时候,需要使用到回滚段,这里有两种可能需要使用回滚段(1.有数据未提交[提交事务],2.数据块scn过大[提交事务/推进scn]).对于这个问题,我尝试着修改回滚段状态来解决这个问题
修改回滚段状态
[oracle@xifenfei ~]$ bbed listfile=listfile password=blockedit mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Tue Jul 17 09:10:01 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 1 block 225
FILE# 1
BLOCK# 225
BBED> map
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 225 Dba:0x004000e1
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 48 bytes @20
struct kdbh, 14 bytes @68
struct kdbt[1], 4 bytes @82
sb2 kdbr[21] @86
ub1 freespace[5357] @128
ub1 rowdata[2703] @5485
ub4 tailchk @8188
BBED> p *kdbr[1]
rowdata[1393]
-------------
ub1 rowdata[1393] @6878 0x2c
BBED> x /rncnnnnn
rowdata[1393] @6878
-------------
flag@6878: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6879: 0x00
cols@6880: 17
col 0[2] @6881: 1
col 1[20] @6884: _SYSSMU1_3138885392$
col 2[2] @6905: 1
col 3[2] @6908: 3
col 4[3] @6911: 128
col 5[4] @6915: 822624
col 6[1] @6920: 0
col 7[3] @6922: 498
col 8[2] @6926: 94
col 9[1] @6929: 0
col 10[2] @6931: 3
col 11[2] @6934: 2
col 12[0] @6937: *NULL*
col 13[0] @6938: *NULL*
col 14[0] @6939: *NULL*
col 15[0] @6940: *NULL*
col 16[2] @6941: 2
BBED> m /x 02 offset 6933
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 225 Offsets: 6933 to 7444 Dba:0x004000e1
------------------------------------------------------------------------
0202c103 ffffffff 02c1032c 001102c1 03145f53 5953534d 55325f34 32323832
33383232 322402c1 0202c104 03c2022d 04c3531b 16018003 c2075303 c2031401
8002c102 02c103ff ffffff02 c1032c00 1102c104 145f5359 53534d55 335f3232
31303734 32363432 2402c102 02c10403 c2023d04 c3531b17 018003c2 074903c2
02290180 02c10202 c103ffff ffff02c1 032c0011 02c10514 5f535953 534d5534
5f313435 35333138 30303624 02c10202 c10403c2 024d04c3 4f4f5101 8003c205
4502c164 018002c1 0402c103 ffffffff 02c1032c 001102c1 06145f53 5953534d
55355f33 37383736 32323331 362402c1 0202c104 03c2025d 04c3531b 1c018003
c2071603 c2021701 8002c102 02c103ff ffffff02 c1032c00 1102c107 145f5359
53534d55 365f3234 36303234 38303639 2402c102 02c10403 c2030904 c3531b1d
018003c2 075a03c2 02130180 02c10202 c103ffff ffff02c1 032c0011 02c10814
5f535953 534d5537 5f313932 34383833 30333724 02c10202 c10403c2 031904c3
531b2201 8003c207 5a03c202 1d018002 c10202c1 03ffffff ff02c103 2c001102
c109135f 53595353 4d55385f 32373630 33383233 372402c1 0202c104 03c20329
04c34f2e 34018003 c2071703 c2024a01 8002c102 02c103ff ffffff02 c1032c00
1102c10a 145f5359 53534d55 395f3335 39333435 30363135 2402c102 02c10403
<32 bytes per line>
…………类似方法修改其他值
BBED> sum apply
Check value for File 1, Block 225:
current = 0x4d51, required = 0x4d51
启动数据库并解决异常undo
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 218106740 bytes
Database Buffers 88080384 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified
SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified
SQL> select type# from seg$ where file#=3;
TYPE#
----------
10
10
10
10
10
10
10
10
10
10
10 rows selected.
SQL> update seg$ set type#=3 where file#=3;
10 rows updated.
SQL> commit;
Commit complete.
SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.
SQL> create undo tablespace undotbs1 datafile '/u01/oracle/oradata/ora11g/undotbs01.dbf' size 10m;
Tablespace created.
