undo异常 – Database SOS

模拟19c数据库root pdb undo异常恢复

对于19c在pdb情况下三种常见故障进行了模拟测试:
模拟19c数据库redo异常恢复
 模拟19c数据库pdb undo异常恢复
 模拟19c数据库root pdb undo异常恢复
模拟oracle 19c cdb模式下root pdb中undo丢失故障恢复
会话1,pdb中插入大量数据,未提交

SQL> alter session set container=pdb; 

Session altered.

SQL> alter database open;

Database altered.

SQL> create user xff identified by oracle default tablespace users;
grant dba to xff;
conn xff/oracle@127.0.0.1/pdb
create table t_xifenfei as select * from dba_objects;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;

User created.

SQL> 
Grant succeeded.

SQL> Connected.
SQL> 

Table created.

SQL> 
72351 rows created.

SQL> 
144702 rows created.

SQL> 
289404 rows created.

SQL> 
578808 rows created.

SQL> 

1157616 rows created.

SQL> SQL> SQL>

会话2中root pdb模拟事务

[oracle@localhost ~]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:56:01 2020
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> 
SQL> 
SQL> conn system/oracle
Connected.
SQL> create table t_xifenfei tablespace users as select * from dba_objects;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;

Table created.

SQL> 
72380 rows created.

SQL> 
144760 rows created.

SQL> 
289520 rows created.

SQL> 
579040 rows created.

SQL> 

1158080 rows created.

SQL> SQL>

会话3 abort库并删除root pdb中undo文件

[oracle@localhost ~]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:56:55 2020
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> shutdown abort;
ORACLE instance shut down.
SQL> 

[oracle@localhost oradata]$ cd ORA19C
[oracle@localhost ORA19C]$ ls
control01.ctl  control02.ctl  pdb  pdbseed  redo01.log  redo02.log  redo03.log  sysaux01.dbf
system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@localhost ORA19C]$ rm -rf undotbs01.dbf

启动数据库报ORA-01157 ORA-01110错误

SQL> alter database datafile 4 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
SQL> alter session set container=pdb;

Session altered.

SQL> alter database open;

Database altered.

SQL> conn / as sysdba
Connected.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
 
SQL> SQL> select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ --------------------------------
SYSTEM                         SYSTEM                         ONLINE
UNDOTBS1                       _SYSSMU1_1261223759$           NEEDS RECOVERY
UNDOTBS1                       _SYSSMU2_27624015$             NEEDS RECOVERY
UNDOTBS1                       _SYSSMU3_2421748942$           NEEDS RECOVERY
UNDOTBS1                       _SYSSMU4_625702278$            NEEDS RECOVERY
UNDOTBS1                       _SYSSMU5_2101348960$           NEEDS RECOVERY
UNDOTBS1                       _SYSSMU6_813816332$            NEEDS RECOVERY
UNDOTBS1                       _SYSSMU7_2329891355$           NEEDS RECOVERY
UNDOTBS1                       _SYSSMU8_399776867$            NEEDS RECOVERY
UNDOTBS1                       _SYSSMU9_1692468413$           NEEDS RECOVERY
UNDOTBS1                       _SYSSMU10_930580995$           NEEDS RECOVERY

本次测试比较幸运,虽然undo段状态为NEEDS RECOVERY,但是数据库直接open成功.实际生产情况,可能比这个要复杂很多

Oracle 12c undo异常处理—root pdb undo异常

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 12c undo异常处理—root pdb undo异常

在12c pdb环境中如果root pdb的undo文件异常，数据库该如何恢复呢？这篇文章模拟undo丢失的情况下进行恢复
模拟环境
三个会话，其中第一个会话对pdb1中的表进行操作，并且有事务未提交,第二个会话对pdb2进行操作，也未提交事务；第三个会话直接abort库，模拟突然库异常，然后删除root pdb下面的undo文件

--会话1
[oracle@ora1221 oradata]$ sqlplus  / as sysdba
SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:24:20 2016
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
SQL> alter session set container=pdb1;
Session altered.
SQL> alter database open;
Database altered.
SQL>  create user chf identified by oracle;
User created.
SQL> grant dba to chf;
Grant succeeded.
SQL> create table chf.t_xifenfei_p1 as
  2  select * from dba_objects;
Table created.
SQL> insert into chf.t_xifenfei_p1
  2  select * from dba_objects;
72427 rows created.
SQL> select count(*) from chf.t_xifenfei_p1;
  COUNT(*)
----------
    144853
--会话2
[oracle@ora1221 ~]$ ss
SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:34:01 2016
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
SQL> alter session set container=pdb2;
Session altered.
SQL> alter database open;
Database altered.
SQL>  create user chf identified by oracle;
User created.
SQL> grant dba to chf;
Grant succeeded.
SQL>  create table chf.t_xifenfei_p2
  2  as select * from dba_objects;
Table created.
SQL> delete from chf.t_xifenfei_p2;
72426 rows deleted.
SQL> select count(*) from chf.t_xifenfei_p2;
  COUNT(*)
----------
    0
--会话3
[oracle@ora1221 ~]$ ss
SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:36:16 2016
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
SQL> shutdown abort
ORACLE instance shut down.
--删除cdb undo文件
[oracle@ora1221 orcl12c2]$ ls -ltr
total 2040912
drwxr-x---. 2 oracle oinstall      4096 Jun 16 18:26 pdbseed
drwxr-x---. 2 oracle oinstall      4096 Jun 16 18:27 pdb2
drwxr-x---. 2 oracle oinstall      4096 Jun 16 18:28 pdb1
-rw-r-----. 1 oracle oinstall 209715712 Jun 16 22:24 redo03.log
-rw-r-----. 1 oracle oinstall   5251072 Jun 16 22:24 users01.dbf
-rw-r-----. 1 oracle oinstall  34611200 Jun 16 22:25 temp01.dbf
-rw-r-----. 1 oracle oinstall 849354752 Jun 16 22:35 system01.dbf
-rw-r-----. 1 oracle oinstall  73408512 Jun 16 22:35 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 492838912 Jun 16 22:35 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 209715712 Jun 16 22:36 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Jun 16 22:36 redo01.log
-rw-r-----. 1 oracle oinstall  18726912 Jun 16 22:36 control02.ctl
-rw-r-----. 1 oracle oinstall  18726912 Jun 16 22:36 control01.ctl
[oracle@ora1221 orcl12c2]$ rm undotbs01.dbf
[oracle@ora1221 orcl12c2]$ ls -l un*
ls: cannot access un*: No such file or directory

启动数据库
由于有undo文件丢失数据库在启动的时候检测到文件丢失(ORA-01157)，无法open,offline文件后依旧无法启动(ORA-00376)

[oracle@ora1221 orcl12c2]$ ss
SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:51:21 2016
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf'
offline 数据文件
SQL> alter database datafile 4 offline ;
alter database datafile 4 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL>  alter database datafile 4 offline drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf'
Process ID: 7547
Session ID: 16 Serial number: 56234

把undo_management修改为manual后启动库，依旧报ORA-00376

SQL> startup pfile='/tmp/pfile' mount;
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
Database mounted.
SQL> show parameter undo_management;
NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
undo_management                      string
MANUAL
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf'
Process ID: 7981
Session ID: 16 Serial number: 56572

设置_corrupted_rollback_segments参数

SQL> startup pfile='/tmp/pfile' mount;
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
Database mounted.
SQL> show parameter _corrupted_rollback_segments;
NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
_corrupted_rollback_segments         string
_SYSSMU1_3200770482$, _SYSSMU2
_3597554035$, _SYSSMU3_2898427
493$, _SYSSMU4_670955920$, _SY
SSMU5_1233449977$, _SYSSMU6_32
67641983$, _SYSSMU7_2822479342
$, _SYSSMU8_1645196706$, _SYSS
MU9_3032014485$, _SYSSMU10_474
465626$
SQL> alter database open;
Database altered.

通过设置_corrupted_rollback_segments参数之后，数据库正常启动，下面继续其他pdb

open pdb1

SQL> alter session set container=pdb1;
Session altered.
SQL> alter database open;
Database altered.
SQL> select count(*) from chf.t_xifenfei_p1;
  COUNT(*)
----------
     72426

pdb2 open

SQL> alter session set container=pdb2;
Session altered.
SQL> alter database open;
Database altered.
SQL> select count(*) from chf.t_xifenfei_p2;
  COUNT(*)
----------
     72426

至此数据库基本上恢复完成，但是看到的pdb里面两个测试表的数据和我们预测的有一定的偏差,看来cdb中的undo和pdb中的undo还是有一定的依赖关系.同时也说明了root的undo异常对于其他pdb的open最少在恢复上面影响不大.下一篇测试业务pdb中undo异常处理

记录一次系统回滚段坏块恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：记录一次系统回滚段坏块恢复

在上一篇中深入分析一次ORA-00314错误的数据库继续恢复,出现file 1 block 403和404等坏块,使得后面的恢复进一步写入了复杂境地.更加麻烦的是,这个库里面有一张核心表有40个字段,包括long,nvarchar2等一些字段,但是使用aul,dul,odu挖取都出现异常(表的行数正确,但是有些列的数据不能正常被挖出来),原因是该表中有特殊字段数据,被逼无赖只能继续拉库
数据库启动报错

SQL> startup mount pfile='c:\pfile.txt'
ORACLE 例程已经启动。
Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             201329808 bytes
Database Buffers          243269632 bytes
Redo Buffers                7094272 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

查看alert日志

SMON: enabling cache recovery
Fri May 16 22:49:53 2014
Hex dump of (file 1, block 404) in trace file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2788.trc
Corrupt block relative dba: 0x00400194 (file 1, block 404)
Fractured block found during buffer read
Data in bad block:
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x0
 block checksum disabled
Reread of rdba: 0x00400194 (file 1, block 404) found same corrupted data
Fri May 16 22:49:55 2014
Errors in file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2788.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 404)
ORA-01110: data file 1: 'C:\ORADATA\INTERLIB\SYSTEM01.DBF'
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604

跟踪数据库启动过程
这里可以清晰的看到是因为block 404有坏块,数据库递归sql访问到该坏块报错,从而数据库无法继续open,对数据库启动过程做10046跟踪

PARSING IN CURSOR #2 len=148 dep=1 uid=0 oct=6 lid=0 tim=80533759 hv=3540833987 ad='2a6ce1ec'
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
END OF STMT
PARSE #2:c=62500,e=211766,p=14,cr=85,cu=0,mis=1,r=0,dep=1,og=4,tim=80533755
BINDS #2:
kkscoacd
 Bind#0
  oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=2a6ceac2  bln=32  avl=09  flg=09
  value="_SYSSMU1$"
 Bind#1
  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=099b9b7c  bln=24  avl=02  flg=05
  value=2
 Bind#2
  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=099b9b58  bln=24  avl=02  flg=05
  value=9
 Bind#3
  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=099b9b34  bln=24  avl=02  flg=05
  value=2
 Bind#4
  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=099b9b10  bln=24  avl=02  flg=05
  value=1
 Bind#5
  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=099b9aec  bln=24  avl=04  flg=05
  value=13332
 Bind#6
  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=099b9ac8  bln=24  avl=04  flg=05
  value=24672
 Bind#7
  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=099b9aa4  bln=24  avl=05  flg=05
  value=47727002
 Bind#8
  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=099b9a80  bln=24  avl=01  flg=05
  value=0
 Bind#9
  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=099b9a5c  bln=24  avl=01  flg=05
  value=0
 Bind#10
  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=099b9a38  bln=24  avl=02  flg=05
  value=1
 Bind#11
  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=099b9a14  bln=24  avl=02  flg=05
  value=1
 Bind#12
  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=099b9ba0  bln=22  avl=02  flg=05
  value=1
WAIT #2: nam='db file sequential read' ela= 10794 file#=1 block#=404 blocks=1 obj#=-1 tim=80546515
Hex dump of (file 1, block 404)
Dump of memory from 0x1FB9C000 to 0x1FB9E000
1FB9C000 00000000 00000000 00000000 00000000  [................]
        Repeat 286 times
1FB9D1F0 00000000 00000000 00000000 00000001  [................]
1FB9D200 0000A200 0001D831 00000000 05010000  [....1...........]
1FB9D210 00007F30 00000000 00000000 00000000  [0...............]
1FB9D220 00000000 00000000 00000000 00000000  [................]
  Repeat 221 times
Corrupt block relative dba: 0x00400194 (file 1, block 404)
Fractured block found during buffer read
Data in bad block:
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x0
 block checksum disabled
Reread of rdba: 0x00400194 (file 1, block 404) found same corrupted data
EXEC #2:c=0,e=45015,p=1,cr=1,cu=3,mis=1,r=0,dep=1,og=4,tim=80578919
ERROR #2:err=1578 tim=811723
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 404)
ORA-01110: data file 1: 'C:\ORADATA\INTERLIB\SYSTEM01.DBF'
EXEC #1:c=328125,e=3468857,p=52,cr=619,cu=10,mis=0,r=0,dep=0,og=1,tim=82331842
ERROR #1:err=1092 tim=811898

dbv检测坏块

C:\oracle\product\10.2.0\db_1\BIN>dbv  FILE = C:\ORADATA\INTERLIB\SYSTEM01.DBF
DBVERIFY: Release 10.2.0.3.0 - Production on 星期一 5月 19 15:22:41 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - 开始验证: FILE = C:\ORADATA\INTERLIB\SYSTEM01.DBF
页 403 标记为损坏
Corrupt block relative dba: 0x00400193 (file 1, block 403)
Bad header found during dbv:
Data in bad block:
 type: 70 format: 1 rdba: 0x00030030
 last change scn: 0x0000.3a6c2e79 seq: 0x0 flg: 0x00
 spare1: 0x4c spare2: 0x45 spare3: 0x2
 consistency value in tail: 0x00070000
 check value in block header: 0x1
 block checksum disabled
页 404 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00400194 (file 1, block 404)
Fractured block found during dbv:
Data in bad block:
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x0
 block checksum disabled
页 498 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x004001f2 (file 1, block 498)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x004001f2
 last change scn: 0x0000.02d7eb9d seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x37290601
 check value in block header: 0x6c5e
 computed block checksum: 0xc2b5
页 61078 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0040ee96 (file 1, block 61078)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0040ee96
 last change scn: 0x0000.02d5cf11 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xa6e30601
 check value in block header: 0x51d4
 computed block checksum: 0xf572
页 61147 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0040eedb (file 1, block 61147)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0040eedb
 last change scn: 0x0000.02d7f7e6 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x6ed80601
 check value in block header: 0x4ae8
 computed block checksum: 0x893f
页 61502 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0040f03e (file 1, block 61502)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0040f03e
 last change scn: 0x0000.02d810dd seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xaf4c0601
 check value in block header: 0xd99b
 computed block checksum: 0xbf91
页 61989 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0040f225 (file 1, block 61989)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0040f225
 last change scn: 0x0000.02d80f65 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xdff70601
 check value in block header: 0x4e2a
 computed block checksum: 0xd092
DBVERIFY - 验证完成
检查的页总数: 62720
处理的页总数 (数据): 37740
失败的页总数 (数据): 0
处理的页总数 (索引): 7021
失败的页总数 (索引): 0
处理的页总数 (其它): 1784
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 16169
标记为损坏的总页数: 7
流入的页总数: 5
最高块 SCN            : 316431787 (0.316431787)
C:\oracle\product\10.2.0\db_1\BIN>

根据我们的经验,对数据库启动影响很大的,主要是403,404,498三个坏块,在10201的库中查询得出结论(403,404是rollback,498是seq$).那现在我们可以理解在数据库启动过程中需要undo$表中相关列信息,但是由于rollback对应的block有坏块,使得数据库无法操作update操作,从而无法正常启动.

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
输入 file_id 的值:  1
原值    3:  WHERE FILE_ID = &FILE_ID
新值    3:  WHERE FILE_ID = 1
输入 block_id 的值:  404
原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
新值    4:    AND 404 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ------------------------------
SYS
SYSTEM
ROLLBACK           SYSTEM

常用方法
设置event跳过坏块,使用隐含参数屏蔽回滚段

  _corrupted_rollback_segments= _SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$, _SYSSMU11$, _SYSSMU12$, _SYSSMU13$, _SYSSMU14$, _SYSSMU15$, _SYSSMU16$, _SYSSMU17$, _SYSSMU18$, _SYSSMU19$, _SYSSMU20$
  undo_management          = MANUAL
  event                    = 10231 trace name context forever, level 10, 10232 trace name context forever, level 10, 10233 trace name context forever, level 10
SQL> startup mount pfile='c:\pfile.txt'
ORACLE 例程已经启动。
Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             201329808 bytes
Database Buffers          243269632 bytes
Redo Buffers                7094272 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

这里可以看出来,数据库在update undo$的时候因为rollback异常无法通过屏蔽回滚段和跳过坏块的方法来解决。因为是system undo系统块,想法就是从别的相同版本库中拷贝一个相同位置块过来试试看。

拷贝数据块

C:\oracle\product\10.2.0\db_1\BIN>bbed listfile=c:\bbed.txt
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon May 19 10:47:14 2014
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set blocksize 8192
        BLOCKSIZE       8192
BBED> set mode edit
        MODE            Edit
BBED> copy file 2 block 405 to file 1 block 405
 File: C:\ORADATA\INTERLIB\SYSTEM01.DBF (1)
 Block: 405              Offsets:    0 to  511           Dba:0x00400195
------------------------------------------------------------------------
 02a20000 94014000 d4c50800 00000104 60890000 00000500 36000000 66004545
 0000e81f 401fc81e 201ea01d 481da01c f81b501b e81a801a d8197019 08196018
 f8179017 e8168016 1816c815 68152815 d8145814 f8137813 18139812 3812f811
 a8116811 18119810 3810b80f 580fd80e 780e380e e80da80d 580d180d c80c480c
 f00b480b e00a380a d0092809 c0087008 1008d007 80074007 f006b006 60062006
 d0052805 a8046004 2004d003 d802b401 6001e000 00000000 0a001800 0c001400
 1300ff1d 78100000 78100000 02000000 00000000 0a165f45 00002e00 0201e71e
 94014000 4c004300 03020100 2b05c000 5e15c000 0c001800 0c002000 29000100
 82230000 82230000 02000000 00000000 0a163347 00003200 02014500 94014000
 58004600 03022100 f30ec000 f50ec000 00000000 00000000 03000000 0d000e00
 0e000e00 06c52008 4b5a2705 c51e3337 5f06c520 084b5a27 06c51f4c 28393906
 c520084b 5a2706c5 273e0355 0d000000 ff165f43 0a001800 0c001400 0d004000
 82230000 82230000 02000000 00000000 0a163346 00003200 02014500 94014000
 58004500 03020100 f30ec000 f80ec000 00000000 00000000 06c52008 4b5a2705
 c4201c60 5f000000 0c001800 0c002000 2a000100 82230000 82230000 02000000
 00000000 0a163345 00003200 02014500 94014000 58004400 03022100 f30ec000
 <32 bytes per line>
BBED> sum apply
Check value for File 1, Block 405:
current = 0x8960, required = 0x8960

重新启动数据库

SQL> startup mount pfile='c:\pfile.txt'
ORACLE 例程已经启动。
Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             201329808 bytes
Database Buffers          243269632 bytes
Redo Buffers                7094272 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

分析alert日志

Mon May 19 10:54:05 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 13 Reading mem 0
  Mem# 0: C:\ORADATA\INTERLIB\REDO03.LOG
Block recovery stopped at EOT rba 13.5.16
Block recovery completed at rba 13.5.16, scn 0.316366922
Doing block recovery for file 1 block 9
Block recovery from logseq 13, block 3 to scn 316366921
Mon May 19 10:54:05 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 13 Reading mem 0
  Mem# 0: C:\ORADATA\INTERLIB\REDO03.LOG
Block recovery completed at rba 13.5.16, scn 0.316366922
Mon May 19 10:54:07 2014
Errors in file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_1208.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00607: 当更改数据块时出现内部错误
ORA-00600: 内部错误代码, 参数: [4193], [102], [58], [], [], [], [], []
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604

这里是因为我们拷贝了一个其他库的undo段过来，然后数据库启动的时候首先使用到该undo块和rollback segment header 不匹配,所以通过通过修改undo header 来修复,使用bbed修改段头信息,因为在以前的文章中描述过,在此不再重复,具体参考:使用bbed解决ORA-00607/ORA-00600[4194]故障启动数据库

SQL> startup mount pfile='c:\pfile.txt'
ORACLE 例程已经启动。
Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             201329808 bytes
Database Buffers          243269632 bytes
Redo Buffers                7094272 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open upgrade;
数据库已更改。

但是因为seq$有坏块,数据库启动后,如果使用非系统认证登录数据库会报如下错误

C:\oracle\product\10.2.0\db_1\BIN>sqlplus interlib/oracle
SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 5月 19 16:29:29 2014
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
ERROR:
ORA-00600: 内部错误代码, 参数: [6807], [AUDSES$], [144], [], [], [], [], []
请输入用户名:
C:\oracle\product\10.2.0\db_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 5月 19 16:30:08 2014
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select object_type from dba_objects where object_name='AUDSES$';
OBJECT_TYPE
-------------------
SEQUENCE

因为seq$有坏块导致该问题，因为该数据库需要重建,使用exp导出来数据，然后重建完成相关工作

undo异常总结和恢复思路

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：undo异常总结和恢复思路

UNDO异常报错千奇百怪,针对本人遇到的比较常见的undo异常报错进行汇总，仅供参考，数据库恢复过程是千奇百怪的，不能照搬硬套.
ORA-00704/ORA-00376
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’
Error 704 happened during db open, shutting down database
USER (ospid: 17864): terminating the instance due to error 704
Instance terminated by USER, pid = 17864
ORA-1092 signalled during: alter database open…
opiodr aborting process unknown ospid (17864) as a result of ORA-1092

ORA-00600[4097]
Fri Aug 31 23:14:10 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Fri Aug 31 23:14:12 2012
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.

ORA-01595/ORA-00600[4194]
Fri Aug 31 23:14:14 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-01595: error freeing extent (2) of rollback segment (4))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], []

0RA-00600[4193]
Tue Feb 14 09:35:34 2012
Errors in file d:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2824.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4193], [2005], [2008], [], [], [], [], []

ORA-00600[kcfrbd_3]
Wed Dec 05 10:26:35 2012
SMON: enabling tx recovery
Wed Dec 05 10:26:35 2012
Database Characterset is ZHS16GBK
Wed Dec 05 10:26:35 2012
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_548.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [2279045], [1], [2277120], [2277120], [], []
SMON: terminating instance due to error 474

ORA-00600[4137]
Fri Jul 6 18:00:40 2012
SMON: ignoring slave err,downgrading to serial rollback
Fri Jul 6 18:00:41 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_smon_16636.trc:
ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []
ORACLE Instance techdb (pid = 8) – Error 600 encountered while recovering transaction (3, 17).

ORA-01595/ORA-01594
Sat May 12 21:54:17 2012
Errors in file /oracle/app/admin/prmdb/bdump/prmdb2_smon_483522.trc:
ORA-01595: error freeing extent (2) of rollback segment (19))
ORA-01594: attempt to wrap into rollback segment (19) extent (2) which is being freed

ORA-00704/ORA-01555
Fri May 4 21:04:21 2012
select ctime, mtime, stime from obj$ where obj# = :1
Fri May 4 21:04:21 2012
Errors in file /oracle/admin/standdb/udump/perfdb_ora_1286288.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 40 with name “_SYSSMU40$” too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 1286288
ORA-1092 signalled during: alter database open resetlogs…

ORA-00607/ORA-00600[4194]
Block recovery completed at rba 3994.5.16, scn 0.89979533
Thu Jul 26 13:21:11 2012
Errors in file /orasvr/admin/mispdata/udump/mispdata_ora_2865.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [31], [2], [], [], [], [], []
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 2865
ORA-1092 signalled during: ALTER DATABASE OPEN…

ORA-00704/ORA-00600[4000]
Thu Feb 28 19:29:13 2013
Errors in file /u1/PROD/prodora/db/tech_st/10.2.0/admin/PROD_oracle/udump/prod_ora_20989.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [50], [], [], [], [], [], []
Thu Feb 28 19:29:13 2013
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 20989
ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS…

undo异常恢复处理思路
除了极少数undo坏块,undo文件丢失外，大部分undo异常是因为redo未被正常进行前滚，从而导致undo回滚异常数据库无法open，解决此类问题，需要结合一般需要结合redo异常处理技巧在其中，一般undo异常处理思路
1.切换undo_management= MANUAL尝试启动数据库，如果不成功进入2
2.设置10513 等event尝试启动数据库，如果不成功进入3
3.使用_offline_rollback_segments/_corrupted_rollback_segments屏蔽回滚段
4.如果依然不能open数据库,考虑使用bbed工具提交事务，修改回滚段状态等操作
5.如果依然还不能open数据库，考虑使用dul

如果您按照上述步骤还不能解决,请联系我们,将为您提供专业数据库技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

姊妹篇
ORACLE REDO各种异常恢复
 ORACLE丢失各种文件导致数据库不能OPEN恢复

重建控制文件丢失undo异常恢复—ORA-600 25025模拟与恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：重建控制文件丢失undo异常恢复—ORA-600 25025模拟与恢复

在现实的情况中，有些人因为某种原因重建控制文件(丢失undo[有意或者无意])，然后又resetlogs库尝试恢复,这样的操作可能导致各种比较麻烦的恢复,这里模拟ORA-600[25025]异常恢复
模拟ORA-600[25025]错误

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> alter database backup controlfile to trace as '/tmp/ctl';
Database altered.
SQL> create table chf.t_xifenfei_www as select * from dba_objects where 1=0;
Table created.
SQL> insert into chf.t_xifenfei_www select * from dba_objects;
74749 rows created.
--另外一个会话abort
SQL> shutdown abort;
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes
SQL> !vi /tmp/ctl.sql
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oracle/oradata/ora11g/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/oracle/oradata/ora11g/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oracle/oradata/ora11g/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/oracle/oradata/ora11g/system01.dbf',
  '/u01/oracle/oradata/ora11g/sysaux01.dbf',
 -- '/u01/oracle/oradata/ora11g/undo02.dbf',
  '/u01/oracle/oradata/ora11g/users01.dbf',
  '/u01/oracle/oradata/ora11g/dbfs01.dbf',
  '/u01/oracle/oradata/ora11g/tts_xifenfei02.dbf',
  '/u01/oracle/oradata/ora11g/tts_xifenfei01.dbf',
  '/u01/oracle/oradata/ora11g/system02.dbf',
  '/u01/oracle/oradata/ora11g/czum01.dbf',
  '/u01/oracle/oradata/ora11g/undotbs02.dbf',
  '/u01/oracle/oradata/sp2008',
  '/u01/oracle/oradata/sp_2009',
  '/u01/oracle/oradata/sp_2010',
  '/u01/oracle/oradata/sp_2011',
  '/u01/oracle/oradata/sp_2012',
  '/u01/oracle/oradata/sp_2013',
  '/u01/oracle/oradata/sp_2014',
  '/u01/oracle/oradata/sp_2015',
  '/u01/oracle/oradata/sp_2016',
  '/u01/oracle/oradata/sp_2017',
  '/u01/oracle/oradata/sp_2018',
  '/u01/oracle/oradata/sp_2019',
  '/u01/oracle/oradata/sp_2020',
  '/u01/oracle/oradata/sp_2021',
  '/u01/oracle/oradata/sp_2022',
  '/u01/oracle/oradata/sp_2023',
  '/u01/oracle/oradata/sp_2024',
  '/u01/oracle/oradata/sp_2025',
  '/u01/oracle/oradata/sp_20max'
CHARACTER SET ZHS16GBK
;
"/tmp/ctl.sql" [New] 43L, 1519C written
SQL> @/tmp/ctl.sql
Control file created.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12696930343864 generated at 05/18/2013 01:17:54 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_38_805394597.dbf
ORA-00280: change 12696930343864 for thread 1 is in sequence #38
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo01.log
ORA-00310: archived log contains sequence 37; sequence 38 required
ORA-00334: archived log: '/u01/oracle/oradata/ora11g/redo01.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL>  recover database using backup controlfile until cancel;
ORA-00279: change 12696930343864 generated at 05/18/2013 01:17:54 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_38_805394597.dbf
ORA-00280: change 12696930343864 for thread 1 is in sequence #38
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo03.log
ORA-00310: archived log contains sequence 39; sequence 38 required
ORA-00334: archived log: '/u01/oracle/oradata/ora11g/redo03.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12696930343864 generated at 05/18/2013 01:17:54 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_38_805394597.dbf
ORA-00280: change 12696930343864 for thread 1 is in sequence #38
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo02.log
ORA-00279: change 12696930370956 generated at 08/26/2013 13:00:25 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_39_805394597.dbf
ORA-00280: change 12696930370956 for thread 1 is in sequence #39
ORA-00278: log file '/u01/oracle/oradata/ora11g/redo02.log' no longer needed
for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [],
[], [], [], [], []
Process ID: 12603
Session ID: 125 Serial number: 3

alert日志信息

ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup controlfile until cancel  ...
ALTER DATABASE RECOVER    LOGFILE '/u01/oracle/oradata/ora11g/redo02.log'
Media Recovery Log /u01/oracle/oradata/ora11g/redo02.log
Mon Aug 26 13:05:00 2013
ORA-279 signalled during: ALTER DATABASE RECOVER    LOGFILE '/u01/oracle/oradata/ora11g/redo02.log'  ...
Mon Aug 26 13:05:10 2013
ALTER DATABASE RECOVER    LOGFILE '/u01/oracle/oradata/ora11g/redo03.log'
Media Recovery Log /u01/oracle/oradata/ora11g/redo03.log
Mon Aug 26 13:05:10 2013
Incomplete recovery applied all redo ever generated.
Recovery completed through change 12696930370973 time 08/26/2013 13:00:56
Media Recovery Complete (ora11g)
Completed: ALTER DATABASE RECOVER    LOGFILE '/u01/oracle/oradata/ora11g/redo03.log'
alter database open resetlogs
RESETLOGS after complete recovery through change 12696930370973
Archived Log entry 1 added for thread 1 sequence 37 ID 0xfa6fa6cb dest 1:
Archived Log entry 2 added for thread 1 sequence 38 ID 0xfa6fa6cb dest 1:
Archived Log entry 3 added for thread 1 sequence 39 ID 0xfa6fa6cb dest 1:
Clearing online redo logfile 1 /u01/oracle/oradata/ora11g/redo01.log
Clearing online log 1 of thread 1 sequence number 37
Mon Aug 26 13:05:22 2013
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/oracle/oradata/ora11g/redo02.log
Clearing online log 2 of thread 1 sequence number 38
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/oracle/oradata/ora11g/redo03.log
Clearing online log 3 of thread 1 sequence number 39
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 4201621195 (0xfa6fa6cb)
Online log /u01/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared
Mon Aug 26 13:05:33 2013
Setting recovery target incarnation to 2
Mon Aug 26 13:05:33 2013
Using SCN growth rate of 16384 per second
Mon Aug 26 13:05:33 2013
Assigning activation ID 4220644150 (0xfb91eb36)
LGWR: STARTING ARCH PROCESSES
Mon Aug 26 13:05:33 2013
ARC0 started with pid=20, OS id=12679
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Aug 26 13:05:35 2013
ARC1 started with pid=21, OS id=12683
Mon Aug 26 13:05:35 2013
ARC2 started with pid=22, OS id=12687
Mon Aug 26 13:05:36 2013
ARC3 started with pid=24, OS id=12691
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/oracle/oradata/ora11g/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Aug 26 13:05:36 2013
SMON: enabling cache recovery
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_12603.trc  (incident=146705):
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_146705/ora11g_ora_12603_i146705.trc
Mon Aug 26 13:05:45 2013
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Undo initialization errored: err:600 serial:0 start:57601994 end:57610584 diff:8590 (85 seconds)
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_12603.trc:
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_12603.trc:
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 12603): terminating the instance due to error 600
Instance terminated by USER, pid = 12603
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (12603) as a result of ORA-1092
Mon Aug 26 13:05:47 2013
ORA-1092 : opitsk aborting process

trace文件

*** 2013-08-26 13:05:38.945
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=7j16t46cacjt9) -----
alter database open resetlogs
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+41        call     kgdsdst()            BF8A34E4 ? 2 ?
ksedst1()+77         call     skdstdst()           BF8A34E4 ? 0 ? 1 ? 8592C38 ?
                                                   8592AB6 ? 8592C38 ?
ksedst()+33          call     ksedst1()            0 ? 1 ?
dbkedDefDump()+2704  call     ksedst()             0 ? BF8A40E7 ? 47EF1FF4 ?
                                                   BF8A3D66 ? 0 ? BF8A3BF4 ?
ksedmp()+47          call     dbkedDefDump()       3 ? 2 ?
ksfdmp()+59          call     ksedmp()             3EB ? BF8A5458 ? F1DADED ?
                                                   116056E0 ? 3EB ? 116056E0 ?
dbgexPhaseII()+1725  call     00000000             116056E0 ? 3EB ?
dbgexProcessError()  call     dbgexPhaseII()       B6C515AC ? B6A0C890 ?
+2550                                              BF8A8D30 ?
dbgeExecuteForError  call     dbgexProcessError()  B6C515AC ? B6A0C890 ? 1 ? 0 ?
()+65                                              B6C515AC ? B6A0C890 ?
dbgePostErrorKGE()+  call     dbgeExecuteForError  B6C515AC ? B6A0C890 ? 0 ? 1 ?
1794                          ()                   0 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   116056E0 ? B6C330D4 ? 258 ?
50
kgeade()+324         call     00000000             116056E0 ? B6C330D4 ? 258 ?
kgeriv_int()+107     call     kgeade()             116056E0 ? 11605808 ?
                                                   B6C330D4 ? 258 ? 0 ? 61C1 ?
kgeriv()+25          call     kgeriv_int()         116056E0 ? B6C330D4 ? 61C1 ?
                                                   0 ? 1 ? BF8A96B8 ?
kgesiv()+98          call     kgeriv()             116056E0 ? B6C330D4 ? 61C1 ?
                                                   1 ? BF8A96B8 ?
ksesic1()+48         call     kgesiv()             116056E0 ? B6C330D4 ? 61C1 ?
                                                   1 ? BF8A96B8 ? 61C1 ? 1 ?krta2rfn()+78
                                                   call     ksesic1()            61C1 ? 0 ? 3 ? 0 ?
kttsinfo()+496       call     krta2rfn()           3 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ktusmout_online_ut(  call     kttsinfo()           9 ? 0 ? 0 ? BF8A9948 ?
)+810
ktusmiut_init_ut()+  call     ktusmout_online_ut(  B000 ? 89E57F8 ? 9 ?
                                                   BF8A96B8 ?
348                           )
ktuini()+518         call     ktusmiut_init_ut()   0 ? 0 ? 0 ? 0 ? 1 ? 0 ?
adbdrv()+46345       call     ktuini()             0 ? BF8A9DE0 ? 1004BF42 ?
                                                   BF8AA258 ? B6A0BFFC ?
                                                   10EA1F20 ?
opiexe()+16835       call     adbdrv()             25C8F1F8 ? 0 ? 0 ? 2A34F528 ?
                                                   2A0400C8 ? BF8AF07C ?
opiosq0()+3437       call     opiexe()             4 ? 0 ? BF8AFE8C ?
kpooprx()+239        call     opiosq0()            3 ? E ? BF8B0184 ? A4 ? 0 ?
kpoal8()+607         call     kpooprx()            BF8B2D6C ? BF8B10AC ? 1D ?
                                                   1 ? 0 ? A4 ?
opiodr()+962         call     00000000             5E ? 1C ? BF8B2D68 ?
ttcpip()+1930        call     00000000             5E ? 1C ? BF8B2D68 ? 0 ?
opitsk()+1355        call     ttcpip()             11616580 ? 5E ? BF8B2D68 ?
                                                   0 ? BF8B29F8 ? BF8B2E90 ?
                                                   FDEBA80 ? 0 ? BF8B2EBC ?
opiino()+827         call     opitsk()             0 ? 0 ?
opiodr()+962         call     00000000             3C ? 4 ? BF8B3E2C ?
opidrv()+479         call     opiodr()             3C ? 4 ? BF8B3E2C ? 0 ?
sou2o()+80           call     opidrv()             3C ? 4 ? BF8B3E2C ?
opimai_real()+109    call     sou2o()              BF8B3E10 ? 3C ? 4 ?
                                                   BF8B3E2C ?
ssthrdmain()+212     call     00000000             2 ? BF8B3F58 ? 0 ? 47DA6F14 ?
                                                   BF8B3F14 ? 47D9A670 ?
main()+147           call     ssthrdmain()         2 ? BF8B3F58 ?
__libc_start_main()  call     00000000             2 ? BF8B4054 ? BF8B4060 ?
+220                                               47D9A828 ? 0 ? 1 ?
_start()+33          call     __libc_start_main()  856F3B4 ? 2 ? BF8B4054 ?
                                                   BCC1EA0 ? BCC1E90 ?
                                                   47D8C790 ?
--------------------- Binary Stack Dump ---------------------

MOS中有类似描述ORA-600 [25025] [25] While Opening the Clone Database in Resetlog Mode (Doc ID 603100.1),该解决方案是重建控制文件增加所有数据文件，在本次测试中，我就是人为除掉了undo，模拟undo丢失[其实数据库已经resetlogs过了，就算加入undo重建控制文件也不会成功(人工修改undo文件头除外)]，又做了不正确的重建控制文件操作的故障，我提供解决方案如下

解决办法

--参数文件修改
undo_management='manual'
--尝试open数据库
recover database;
alter database open;
--新建undo
create undo tablespace undo_new datafile '' size 100m autoextend on next 10m maxsize 30G;
--屏蔽需要恢复回滚段
select tablespace_name,segment_name,status from dba_rollback_segs;
_corrupted_rollback_segments
--重启数据库使得_corrupted_rollback_segments生效
shutdown immediate;
startup
--删除老undo
drop tablespace old_undo
--修改参数
shutdonw immediate
undo_management='auto'
undo_tablespace='unod_new'
--启动数据库
startup
--导出数据，导入新库

姊妹篇:重建控制文件丢失undo异常恢复—ORA-01173模拟与恢复

标签归档：undo异常