ORA-01558: out of transaction ID’s in rollback segment SYSTEM

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORA-01558: out of transaction ID’s in rollback segment SYSTEM

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户数据库启动报ORA-01558: out of transaction ID’s in rollback segment SYSTorcl无法open成功,而且对应的备库也无法正常open(报同样错误)
ORA-01558

对ORA-01558错误分析

[oracle@iZbp1hx0enix3hix1kvyrxZ ~]$  oerr ora 1558
01558, 00000, "out of transaction ID's in rollback segment %s"
// *Cause: All the available transaction id's have been used
// *Action: Shutdown the instance and restart using other rollback segment(s),
//          then drop the rollback segment that has no more transaction id's.

官方对于该错误的解释为由于回滚段的事务ID被使用完,无法再分配从而报ORA-01558,解决方法是重启库使用其他回滚段,并删除这个异常回滚段.对于此次故障的报错,这个方法原则上行不通,因为异常的是systorcl里面的rollback回滚段(也就是我们通常说的系统回滚段).通过alert日志分析客户这个故障的前因后果。
正常运行的库,突然报该错误

Fri Jun 03 19:37:36 2022
Thread 1 advanced to log sequence 2766146 (LGWR switch)
  Current log# 3 seq# 2766146 morcl# 0: /oradata/orclr/group_3.310.962631159
Fri Jun 03 19:37:36 2022
LNS: Standby redo logfile selected for thread 1 sequence 2766146 for destination LOG_ARCHIVE_DEST_2
Fri Jun 03 19:37:37 2022
Archived Log entry 3560157 added for thread 1 sequence 2766145 ID 0xefc56f40 dest 1:
Fri Jun 03 19:37:58 2022
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Fri Jun 03 19:38:13 2022
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl

这个错误一直报了很长时间,等到有时间窗口,客户安排重启操作

Mon Sep 26 14:10:12 2022
Shutting down instance (immediate)
Shutting down instance: further logons disabled
License high water mark = 205
All dispatchers and shared servers shutdown
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
ALTER DATABASE CLOSE NORMAL
ORA-1558 signalled during: ALTER DATABASE CLOSE NORMAL...
Mon Sep 26 14:10:22 2022
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Mon Sep 26 14:10:33 2022
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Mon Sep 26 14:10:43 2022
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Mon Sep 26 14:10:53 2022
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Mon Sep 26 14:10:55 2022
Shutting down instance (abort)
License high water mark = 205
USER (ospid: 25049): terminating the instance
Instance terminated by USER, pid = 25049

正常shutdown immediate执行失败,直接abort方式关闭库,然后尝试重启库

Mon Sep 26 14:12:16 2022
ARC3 started with pid=44, OS id=25978 
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Thread 1 opened at log sequence 2782827
  Current log# 1 seq# 2782827 morcl# 0: /oradata/orclr/group_1.296.962631151
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Sep 26 14:12:16 2022
SMON: enabling cache recovery
Mon Sep 26 14:12:16 2022
NSA2 started with pid=46, OS id=25982 
Archived Log entry 3593518 added for thread 1 sequence 2782826 ID 0xefc56f40 dest 1:
ARC3: Standby redo logfile selected for thread 1 sequence 2782826 for destination LOG_ARCHIVE_DEST_2
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_ora_25879.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_ora_25879.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
Error 604 happened during db open, shutting down database
USER (ospid: 25879): terminating the instance due to error 604
Instance terminated by USER, pid = 25879
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (25879) as a result of ORA-1092
Mon Sep 26 14:12:17 2022
ORA-1092 : opitsk aborting process

数据库重启失败,报ORA-604和ORA-01558错误.
对于这种情况,对于数据库启动过程进行跟踪确认在UPDATE UNDO$的时候无法分配事务,导致无法继续

PARSING IN CURSOR #139696084476000 len=160 dep=1 uid=0 oct=6 lid=0 tim=1664353484223278 hv=1292341136 
ad='6ff1ffa8' sqlid='8vyjutx6hg3wh'
update /*+ rule */ 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 #139696084476000:c=5998,e=10708,p=7,cr=53,cu=0,mis=1,r=0,dep=1,og=3,plh=0,tim=1664353484223277
BINDS #139696084476000:
 Bind#0
  oacdty=01 mxl=32(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=6ff376da  bln=32  avl=21  flg=09
  value="_SYSSMU14_3733658264$"
 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=7f0d877d7a68  bln=24  avl=02  flg=05
  value=15
 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=7f0d877d7a38  bln=24  avl=03  flg=05
  value=336
 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=7f0d877d7a00  bln=24  avl=02  flg=05
  value=5
 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=7f0d877d79d0  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=7f0d877d79a0  bln=24  avl=03  flg=05
  value=5747
 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=7f0d877d7970  bln=24  avl=04  flg=05
  value=22103
 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=7f0d877d7938  bln=24  avl=06  flg=05
  value=1026171661
 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=7f0d877d7680  bln=24  avl=03  flg=05
  value=3399
 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=7f0d877d7650  bln=24  avl=02  flg=05
  value=2
 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=7f0d877d7620  bln=24  avl=02  flg=05
  value=5
 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=7f0d877d75f0  bln=24  avl=02  flg=05
  value=2
 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=7f0d877d7a98  bln=22  avl=02  flg=05
  value=14
EXEC #139696084476000:c=1000,e=1713,p=0,cr=1,cu=2,mis=1,r=0,dep=1,og=3,plh=3078630091,tim=1664353484225072
ERROR #139696084476000:err=1558 tim=1664353484225094
STAT #139696084476000 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  UNDO$ (cr=0 pr=0 pw=0 time=5 us)'
STAT #139696084476000 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=0 pw=0 time=6 us)'
KQRCMT: Write failed with error=604 po=0x6ff375d0 cid=3
diagnostics : cid=3 hash=f2114ab9 flag=2a
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTorcl

既然报了事务ID不足,那对block进行分析,确实几乎都达到了数据库设计的理论最大值

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0xfffe  0x0026  0x111f.4d598aae  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x01    9    0x00  0xfffe  0x0054  0x111f.4d598a63  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x02    9    0x00  0xfffe  0x001a  0x111f.4d598a77  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x03    9    0x00  0xfffe  0x005c  0x111f.4d598a7d  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x04    9    0x00  0xfffe  0x003d  0x111f.4d598a4d  0x00400185  0x0000.000.00000000  0x00000001    0x00000000
   0x05    9    0x00  0xfffe  0x0061  0x111f.4d598a74  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x06    9    0x00  0xfffe  0x0002  0x111f.4d598a76  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x07    9    0x00  0xfffe  0x001b  0x111f.4d598a90  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x08    9    0x00  0xfffe  0x0011  0x111f.4d598a8c  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x09    9    0x00  0xfffe  0x0042  0x111f.4d598a6e  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x0a    9    0x00  0xfffe  0x0003  0x111f.4d598a7c  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x0b    9    0x00  0xfffe  0x000a  0x111f.4d598a7b  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x0c    9    0x00  0xfffe  0x0005  0x111f.4d598a73  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x0d    9    0x00  0xfffe  0x005d  0x111f.4d598a87  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x0e    9    0x00  0xfffe  0x0050  0x111f.4d598a68  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x0f    9    0x00  0xfffe  0x0047  0x111f.4d598a6a  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x10    9    0x00  0xfffe  0x0033  0x111f.4d598a95  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x11    9    0x00  0xfffe  0x0031  0x111f.4d598a8d  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x12    9    0x00  0xfffe  0x0020  0x111f.4d598a81  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x13    9    0x00  0xfffe  0x0014  0x111f.4d598a85  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x14    9    0x00  0xfffe  0x000d  0x111f.4d598a86  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x15    9    0x00  0xfffe  0x0013  0x111f.4d598a83  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x16    9    0x00  0xfffe  0x003b  0x111f.4d598aa4  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x17    9    0x00  0xfffe  0x005b  0x111f.4d598a70  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x18    9    0x00  0xfffe  0x001f  0x111f.4d598a8a  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x19    9    0x00  0xfffe  0x0010  0x111f.4d598a94  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x1a    9    0x00  0xfffe  0x001c  0x111f.4d598a79  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x1b    9    0x00  0xfffe  0x0027  0x111f.4d598a91  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x1c    9    0x00  0xfffe  0x000b  0x111f.4d598a7a  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x1d    9    0x00  0xfffe  0x0012  0x111f.4d598a80  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x1e    9    0x00  0xfffe  0x0045  0x111f.4d598aba  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x1f    9    0x00  0xfffe  0x0008  0x111f.4d598a8b  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x20    9    0x00  0xfffe  0x0015  0x111f.4d598a82  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x21    9    0x00  0xfffe  0x0038  0x111f.4d598ab2  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x22    9    0x00  0xfffd  0x003e  0x111f.4d598ab4  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x23    9    0x00  0xfffe  0x0028  0x111f.4d598a9e  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x24    9    0x00  0xfffd  0x0060  0x111f.4d598a5e  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x25    9    0x00  0xfffe  0x0021  0x111f.4d598ab0  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x26    9    0x00  0xfffe  0x0025  0x111f.4d598aaf  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x27    9    0x00  0xfffe  0x0019  0x111f.4d598a93  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x28    9    0x00  0xfffe  0x0046  0x111f.4d598a9f  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x29    9    0x00  0xfffe  0x0023  0x111f.4d598a9d  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x2a    9    0x00  0xfffe  0x002c  0x111f.4d598a98  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x2b    9    0x00  0xfffe  0x0040  0x111f.4d598aa7  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x2c    9    0x00  0xfffe  0x0030  0x111f.4d598a99  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x2d    9    0x00  0xfffe  0x003c  0x111f.4d598aab  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x2e    9    0x00  0xfffe  0x0056  0x111f.4d598abf  0x00400189  0x0000.000.00000000  0x00000001    0x00000000
   0x2f    9    0x00  0xfffe  0x0037  0x111f.4d598aa2  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x30    9    0x00  0xfffe  0x0034  0x111f.4d598a9a  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x31    9    0x00  0xfffe  0x0007  0x111f.4d598a8f  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x32    9    0x00  0xfffe  0x001e  0x111f.4d598ab9  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x33    9    0x00  0xfffe  0x002a  0x111f.4d598a97  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x34    9    0x00  0xfffe  0x0029  0x111f.4d598a9c  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x35    9    0x00  0xfffd  0x005f  0x111f.4d598a5b  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x36    9    0x00  0xfffe  0x0049  0x111f.4d598ac6  0x00400189  0x0000.000.00000000  0x00000001    0x00000000
   0x37    9    0x00  0xfffe  0x0016  0x111f.4d598aa3  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x38    9    0x00  0xfffe  0x0022  0x111f.4d598ab3  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x39    9    0x00  0xfffd  0x0058  0x111f.4d598a52  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x3a    9    0x00  0xfffd  0x0048  0x111f.4d598a57  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x3b    9    0x00  0xfffe  0x002b  0x111f.4d598aa5  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x3c    9    0x00  0xfffe  0x0000  0x111f.4d598aac  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x3d    9    0x00  0xfffd  0x0043  0x111f.4d598a4f  0x00400185  0x0000.000.00000000  0x00000001    0x00000000
   0x3e    9    0x00  0xfffe  0x003f  0x111f.4d598ab6  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x3f    9    0x00  0xfffe  0x0032  0x111f.4d598ab7  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x40    9    0x00  0xfffe  0x005a  0x111f.4d598aa8  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x41    9    0x00  0xfffe  0x004b  0x111f.4d598abc  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x42    9    0x00  0xfffd  0x0017  0x111f.4d598a6f  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x43    9    0x00  0xfffd  0x004d  0x111f.4d598a50  0x00400185  0x0000.000.00000000  0x00000001    0x00000000
   0x44    9    0x00  0xfffe  0x005e  0x111f.4d598ac2  0x00400189  0x0000.000.00000000  0x00000001    0x00000000
   0x45    9    0x00  0xfffe  0x0041  0x111f.4d598abb  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x46    9    0x00  0xfffe  0x002f  0x111f.4d598aa0  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x47    9    0x00  0xfffd  0x0059  0x111f.4d598a6b  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x48    9    0x00  0xfffd  0x0052  0x111f.4d598a58  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x49    9    0x00  0xfffe  0xffff  0x111f.4d598ac7  0x00400189  0x0000.000.00000000  0x00000001    0x00000000
   0x4a    9    0x00  0xfffd  0x003a  0x111f.4d598a56  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x4b    9    0x00  0xfffe  0x002e  0x111f.4d598abe  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x4c    9    0x00  0xfffd  0x0024  0x111f.4d598a5d  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x4d    9    0x00  0xfffd  0x0039  0x111f.4d598a51  0x00400185  0x0000.000.00000000  0x00000001    0x00000000
   0x4e    9    0x00  0xfffd  0x0001  0x111f.4d598a62  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x4f    9    0x00  0xfffd  0x000e  0x111f.4d598a66  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x50    9    0x00  0xfffd  0x000f  0x111f.4d598a69  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x51    9    0x00  0xfffe  0x0036  0x111f.4d598ac5  0x00400189  0x0000.000.00000000  0x00000001    0x00000000
   0x52    9    0x00  0xfffd  0x0035  0x111f.4d598a59  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x53    9    0x00  0xfffd  0x004a  0x111f.4d598a55  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x54    9    0x00  0xfffd  0x0055  0x111f.4d598a64  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x55    9    0x00  0xfffd  0x004f  0x111f.4d598a65  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x56    9    0x00  0xfffe  0x0044  0x111f.4d598ac1  0x00400189  0x0000.000.00000000  0x00000001    0x00000000
   0x57    9    0x00  0xfffd  0x004e  0x111f.4d598a60  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x58    9    0x00  0xfffd  0x0053  0x111f.4d598a53  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x59    9    0x00  0xfffd  0x0009  0x111f.4d598a6c  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x5a    9    0x00  0xfffd  0x002d  0x111f.4d598aa9  0x00400188  0x0000.000.00000000  0x00000001    0x00000000
   0x5b    9    0x00  0xfffd  0x000c  0x111f.4d598a71  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x5c    9    0x00  0xfffd  0x001d  0x111f.4d598a7f  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x5d    9    0x00  0xfffd  0x0018  0x111f.4d598a88  0x00400187  0x0000.000.00000000  0x00000001    0x00000000
   0x5e    9    0x00  0xfffe  0x0051  0x111f.4d598ac3  0x00400189  0x0000.000.00000000  0x00000001    0x00000000
   0x5f    9    0x00  0xfffd  0x004c  0x111f.4d598a5c  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x60    9    0x00  0xfffd  0x0057  0x111f.4d598a5f  0x00400186  0x0000.000.00000000  0x00000001    0x00000000
   0x61    9    0x00  0xfffd  0x0006  0x111f.4d598a75  0x00400187  0x0000.000.00000000  0x00000001    0x00000000

通过bbed修改相关值,规避掉此类问题

m /x 6c000000 offset 6513
m /x 7c000000 offset 6752
m /x 8c000000 offset 7048

启动数据库成功
20220928211705

Oracle Recovery Tools实战批量坏块修复

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：Oracle Recovery Tools实战批量坏块修复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户数据库无法正常启动ORA-600 6711错误

SQL> startup mount pfile='d:/pfile.txt'
ORACLE instance started.

Total System Global Area 4294964032 bytes
Fixed Size                  9036608 bytes
Variable Size             889192448 bytes
Database Buffers         3388997632 bytes
Redo Buffers                7737344 bytes
Database mounted.
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-00600: internal error code, arguments: [6711], [4313028], [1], [4309898],
[0], [], [], [], [], [], [], []
Process ID: 22708
Session ID: 978 Serial number: 56675

alert日志报错

2022-06-26T12:34:41.855326+08:00
alter database open
2022-06-26T12:34:41.984974+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
Endian type of dictionary set to little
Undo initialization finished serial:0 start:313418906 end:313418906 diff:0 ms (0.0 seconds)
Database Characterset is ZHS16GBK
No Resource Manager plan active
2022-06-26T12:34:43.302315+08:00
Errors in file C:\APP\XFF\diag\rdbms\orcl\ora19c\trace\ora19c_ora_22708.trc  (incident=38629):
ORA-00600: internal error code, arguments: [6711], [4313028], [1], [4309898], [0], [], [], [], [], [], [], []
Incident details in: C:\APP\XFF\diag\rdbms\orcl\ora19c\incident\incdir_38629\ora19c_ora_22708_i38629.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2022-06-26T12:34:44.232115+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
2022-06-26T12:34:44.315431+08:00
Errors in file C:\APP\XFF\diag\rdbms\orcl\ora19c\trace\ora19c_ora_22708.trc:
ORA-00600: internal error code, arguments: [6711], [4313028], [1], [4309898], [0], [], [], [], [], [], [], []
2022-06-26T12:34:44.315431+08:00
Errors in file C:\APP\XFF\diag\rdbms\orcl\ora19c\trace\ora19c_ora_22708.trc:
ORA-00600: internal error code, arguments: [6711], [4313028], [1], [4309898], [0], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
Errors in file C:\APP\XFF\diag\rdbms\orcl\ora19c\trace\ora19c_ora_22708.trc  (incident=38630):
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [6711], [4313028], [1], [4309898], [0], [], [], [], [], [], [], []
Incident details in: C:\APP\XFF\diag\rdbms\orcl\ora19c\incident\incdir_38630\ora19c_ora_22708_i38630.trc
2022-06-26T12:34:45.266678+08:00
opiodr aborting process unknown ospid (22708) as a result of ORA-603
2022-06-26T12:34:45.274688+08:00
ORA-603 : opitsk aborting process
License high water mark = 1
USER (ospid: (prelim)): terminating the instance due to ORA error

通过分析trace文件进行分析，确认是由于histgrm$表异常导致,通过一些特殊处理,绕过该表相关sql,open数据库,并且尝试导出数据

SQL> startup mount pfile='d:/pfile.txt';
ORACLE instance started.

Total System Global Area 4294964032 bytes
Fixed Size                  9036608 bytes
Variable Size             889192448 bytes
Database Buffers         3388997632 bytes
Redo Buffers                7737344 bytes
Database mounted.
SQL>
SQL>
SQL> alter database open;

Database altered.

使用expdp导出数据报ORA-01578错
expdp-ora-1578

通过分析是由于system有坏块导致,dbv检查文件
dbv-huikuai
通过Oracle Recovery Tools工具批量坏块修复功能修复
20220626123245
20220626123343
通过工具修复大量主要坏块被修复,还有一些内部逻辑错误(后续工具继续完善),再次尝试逻辑导出数据,无任何报错,数据比较完美恢复
20220626160209

分享运气超级好的一次drop tablespace 数据恢复

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：分享运气超级好的一次drop tablespace 数据恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

分享一次运气超级好的恢复,本身是一个测试库,应用厂商今天准备把应用正式上线,操作流程是:先删除用户,然后删除表空间,在创建表空间导入数据正式上线,不知何种原因最终客户在测试业务中做了一些正式数据,结果是无情的被删除了,通过alert日志找到应用厂商的一些操作记录
2021年8月份创建了业务表空间

Wed Aug 18 09:49:03 2021
create tablespace xifenfei datafile 'D:\app\Administrator\oradata\xifenfei\xifenfei.dbf' size 10g
Wed Aug 18 09:52:28 2021
Completed: create tablespace xifenfei datafile 'D:\app\Administrator\oradata\xifenfei\xifenfei.dbf' size 10g

今天删除表空间

Tue Apr 12 11:15:02 2022
drop tablespace xifenfei including contents and datafiles
WARNING: Cannot delete file D:\APP\ADMINISTRATOR\ORADATA\xifenfei\xifenfei.DBF
Errors in file d:\app\administrator\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_4296.trc:
ORA-01265: 鏃犳硶鍒犻櫎 DATA D:\APP\ADMINISTRATOR\ORADATA\xifenfei\xifenfei.DBF
ORA-27056: 鏃犳硶鍒犻櫎鏂囦欢
OSD-04024: 无法删除文件。
O/S-Error: (OS 32) 另一个程序正在使用此文件，进程无法访问。
Completed: drop tablespace xifenfei including contents and datafiles

然后客户创建新表空间提示ORA-01119,然后人工删除掉该数据文件

Tue Apr 12 11:49:02 2022
create tablespace xifenfei datafile'D:\oracle\oradata\xifenfei\xifenfei.dbf'size 20480m
ORA-1119 signalled during: create tablespace xifenfei datafile'D:\oracle\oradata\xifenfei\xifenfei.dbf'size 20480m...
Tue Apr 12 11:49:16 2022
create tablespace xifenfei datafile'D:\oracle\oradata\xifenfei\xifenfei.dbf'size 20480m
ORA-1119 signalled during: create tablespace xifenfei datafile'D:\oracle\oradata\xifenfei\xifenfei.dbf'size 20480m...

创建新表空间成功，并增加数据文件

Tue Apr 12 12:08:43 2022
create tablespace xifenfei datafile'D:\app\Administrator\oradata\xifenfei\xifenfei.dbf'size 5120m
Tue Apr 12 12:10:25 2022
Completed: create tablespace xifenfei datafile'D:\app\Administrator\oradata\xifenfei\xifenfei.dbf'size 5120m
Tue Apr 12 12:11:19 2022
alter tablespace xifenfei add datafile'D:\app\Administrator\oradata\xifenfei\xifenfei1.dbf'size 5120m
Tue Apr 12 12:13:02 2022
Completed: alter tablespace xifenfei add datafile'D:\app\Administrator\oradata\xifenfei\xifenfei1.dbf'size 5120m
alter tablespace xifenfei add datafile'D:\app\Administrator\oradata\xifenfei\xifenfei2.dbf'size 5120m
Tue Apr 12 12:14:52 2022
Completed: alter tablespace xifenfei add datafile'D:\app\Administrator\oradata\xifenfei\xifenfei2.dbf'size 5120m

基本情况就是客户删除了一个10G的业务数据文件，然后创建了3个5G的业务数据文件,现在要恢复被以前的两个表的核心数据，需要做的就是把以前的10G的数据文件找出来,但是由于删除10G文件之后又写入了15G的数据文件(而且这里面有文件的file#和删除的文件一致),理论上无法直接做block层面扫描恢复,对于此类情况,尝试文件系统层面直接反删除恢复,不过没有任何记录,文件目录被覆盖,这条路走不通.通过block扫描,发现2个file# 5文件的起始位置（分别是block 2和block 0）,而且结束位置文件大小分别是10G和5G,根据经验这两个连续的磁盘分配空间很可能就是这两个file# 5的文件
20220415221719

通过winhex把数据拷贝出来,使用工具检测
20220407141224
除损坏的block 1之外（block 0 不统计在内）,其他block都正常,也就是说这个10G的被删除的数据文件,只是丢失一个文件头,业务数据全部再,后续通过dul恢复客户需要数据,完成这次数据恢复,类似这种文件丢失,文件系统损坏,文件大小为0kb等类似恢复,参见以前类似blog:
win文件系统损坏oracle恢复
dbca删除库和rm删库恢复
文件系统重新分区oracle恢复
restore database误操作恢复
文件系统损坏导致数据文件异常恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
rm -rf 删除数据文件恢复方法—文件系统反删除+oracle碎片重组

asm disk 磁盘部分被清空恢复

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：asm disk 磁盘部分被清空恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

由于未知原因导致磁盘组的一个磁盘前面1G被置空(data磁盘组一共5个1T磁盘,第一个盘未知原因置空了1G数据)

[root@oradb1 ~]#kfed read /dev/mapper/asm-disk1
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
7F539088C400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]
…………
[root@oradb1 ~]#kfed read /dev/mapper/asm-disk1 aun=999
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
7F8FEB795400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

通过分析disk 1和disk 2的前面1001MB数据,以及asm的数据分布特性,可以确认丢失的1000MB数据为39号文件的数据(和客户当时这个库是通过rman还原过来的操作有关系)
20220330224604
20220330225211

对于这样的情况,通过底层扫描,可以很好的恢复数据,参考以前类似文章
asm disk被加入vg恢复
asm disk header 彻底损坏恢复
asm磁盘dd破坏恢复
通过底层恢复,恢复结果如下
20220331133634
dbv检查数据文件
20220331134249
然后把数据库恢复出来数据文件中的数据恢复到新库中,至此完成该case恢复

110T oracle故障恢复

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：110T oracle故障恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户一套110T的数据库由于存储控制器故障导致数据库无法正常启动,启动报错如下：

Wed Feb  2 23:16:13 2022
Recovery of Online Redo Log: Thread 1 Group 7 Seq 1647469 Reading mem 0
  Mem# 0: /dev/vgredo6/rredo7b
  Mem# 1: /dev/vgredo4/rredo7a
Wed Feb  2 23:16:14 2022
Errors in file /opt/oracle/admin/xifenfei/udump/xifenfei_ora_26754.trc:
ORA-07445: exception encountered: core dump [_memcpy()+7040] [SIGSEGV] [Address not mapped to object] [] [] []
Wed Feb  2 23:16:15 2022
Errors in file /opt/oracle/admin/xifenfei/udump/xifenfei_ora_26754.trc:
ORA-07445: exception encountered: core dump [kcbzdh()+560] [SIGSEGV] [Address not mapped to object] [] [] []
ORA-07445: exception encountered: core dump [_memcpy()+7040] [SIGSEGV] [Address not mapped to object] [] [] []
Wed Feb  2 23:16:16 2022
Errors in file /opt/oracle/admin/xifenfei/udump/xifenfei_ora_26754.trc:
ORA-07445: exception encountered: core dump [kcbzdh()+560] [SIGSEGV] [Address not mapped to object] [] [] []
ORA-07445: exception encountered: core dump [kcbzdh()+560] [SIGSEGV] [Address not mapped to object] [] [] []
ORA-07445: exception encountered: core dump [_memcpy()+7040] [SIGSEGV] [Address not mapped to object] [] [] []
Wed Feb  2 23:16:16 2022
Errors in file /opt/oracle/admin/xifenfei/udump/xifenfei_ora_26754.trc:
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kcbzdh()+560] [SIGSEGV] [Address not mapped to object] [] [] []
ORA-07445: exception encountered: core dump [kcbzdh()+560] [SIGSEGV] [Address not mapped to object] [] [] []
ORA-07445: exception encountered: core dump [_memcpy()+7040] [SIGSEGV] [Address not mapped to object] [] [] []
Wed Feb  2 23:16:26 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_pmon_26722.trc:
ORA-07445: exception encountered: core dump [kcbzre1()+6593] [SIGSEGV] [Address not mapped to object] [] [] []
Wed Feb  2 23:16:27 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_pmon_26722.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+1200] [SIGSEGV][] []
ORA-07445: exception encountered: core dump [kcbzre1()+6593] [SIGSEGV] [Address not mapped to object] [] [] []
Wed Feb  2 23:16:27 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_pmon_26722.trc:
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+1200] [SIGSEGV][] []
ORA-07445: exception encountered: core dump [kcbzre1()+6593] [SIGSEGV] [Address not mapped to object] [] [] []
Wed Feb  2 23:17:11 2022
PSP0: terminating instance due to error 472
Instance terminated by PSP0, pid = 26724

该错误原因是由于redo信息和数据文件block信息不匹配导致无法正常应用日志,从而出现异常,在后续的recover 中还出现以下错误

Fri Feb 18 16:09:59 2022
ALTER DATABASE RECOVER  datafile 609,610,611,612,613,614,615,602,603,604,605,606,607,608  
Fri Feb 18 16:09:59 2022
Media Recovery Start
 parallel recovery started with 16 processes
Fri Feb 18 16:10:00 2022
Recovery of Online Redo Log: Thread 1 Group 7 Seq 1647469 Reading mem 0
  Mem# 0: /dev/vgredo6/rredo7b
  Mem# 1: /dev/vgredo4/rredo7a
Fri Feb 18 16:12:17 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_p000_22509.trc:
ORA-00600: internal error code, arguments: [6101], [0], [42], [96], [], [], [], []
Fri Feb 18 16:18:51 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_p000_22509.trc:
ORA-10562: Error occurred while applying redo to data block (file# 602, block# 1693691)
ORA-10564: tablespace DBS_DCDL_PT
ORA-01110: data file 602: '/dev/vgora12/rdbs_dcdl_pt0155'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 33682645
ORA-00600: internal error code, arguments: [6101], [0], [42], [96], [], [], [], []
Fri Feb 18 16:18:55 2022
Media Recovery failed with error 12801

Fri Feb 18 18:23:59 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_dbw1_22483.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+1200] [SIGSEGV][] []
ORA-07445: exception encountered: core dump [ksuitm()+2400] [SIGSEGV] [] [] [] []
ORA-00472: PMON  process terminated with error
Fri Feb 18 18:24:04 2022
DBW3: terminating instance due to error 472
Fri Feb 18 18:24:04 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_dbw3_22487.trc:
ORA-07445: exception encountered: core dump [ksuitm()+2400] [SIGSEGV] [] [] [] []
ORA-00472: PMON  process terminated with error
Fri Feb 18 18:24:04 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_dbw3_22487.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+1200] [SIGSEGV] [] []
ORA-07445: exception encountered: core dump [ksuitm()+2400] [SIGSEGV] [] [] [] []
ORA-00472: PMON  process terminated with error
Fri Feb 18 18:24:09 2022
LGWR: terminating instance due to error 472
Fri Feb 18 18:24:09 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_lgwr_22489.trc:
ORA-07445: exception encountered: core dump [ksuitm()+2400] [SIGSEGV] [] [] [] []
ORA-00472: PMON  process terminated with error

从sqlplus中看到类似一些报错

SQL>  recover datafile 601;
ORA-03113: end-of-file on communication channel

SQL> recover datafile 1066;
ORA-00283: recovery session canceled due to errors
ORA-12801: error signaled in parallel query server P015
ORA-00600: internal error code, arguments: [2037], [207064103], [207064103],
[162], [6], [1], [1833009883], [1130705717]


SQL> recover datafile 1065;
ORA-00283: recovery session canceled due to errors
ORA-12801: error signaled in parallel query server P004
ORA-00600: internal error code, arguments: [kcbzpb_1], [142189139], [3], [0],
[], [], [], []


SQL> recover datafile 2042;
ORA-00283: recovery session canceled due to errors
ORA-12801: error signaled in parallel query server P014
ORA-00600: internal error code, arguments: [3020], [627], [3234156],
[2633062764], [], [], [], []
ORA-10567: Redo is inconsistent with data block

通过屏蔽一致性,强制open库成功

Sun Feb 20 21:20:06 2022
SMON: enabling tx recovery
Sun Feb 20 21:20:06 2022
Database Characterset is ZHS16GBK
Sun Feb 20 21:20:07 2022
ORACLE Instance xifenfei (pid = 38) - Error 376 encountered while recovering transaction (74, 17) on object 34131051.
Sun Feb 20 21:20:07 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_smon_25140.trc:
ORA-00376: file 1416 cannot be read at this time
ORA-01110: data file 1416: '/dev/vgora14/rdbs_icdl_pt116'
Sun Feb 20 21:20:08 2022
Stopping background process MMNL
Sun Feb 20 21:20:09 2022
ORACLE Instance xifenfei (pid = 38) - Error 376 encountered while recovering transaction (88, 36) on object 33514955.
Sun Feb 20 21:20:09 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_smon_25140.trc:
ORA-00376: file 1264 cannot be read at this time
ORA-01110: data file 1264: '/dev/vgora14/rdbs_icdl_pt102'
Sun Feb 20 21:20:09 2022
Stopping background process MMON
Starting background process MMON
Starting background process MMNL
MMON started with pid=46, OS id=1482
Sun Feb 20 21:20:10 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_smon_25140.trc:
ORA-01578: ORACLE data block corrupted (file # 652, block # 3767844)
ORA-01110: data file 652: '/dev/vgora13/rdbs_dcdl_pt0205'
Sun Feb 20 21:20:10 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_smon_25140.trc:
ORA-01578: ORACLE data block corrupted (file # 652, block # 3767661)
ORA-01110: data file 652: '/dev/vgora13/rdbs_dcdl_pt0205'
replication_dependency_tracking turned off (no async multimaster replication found)
Sun Feb 20 21:20:11 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_smon_25140.trc:
ORA-01578: ORACLE data block corrupted (file # 652, block # 3767661)
ORA-01110: data file 652: '/dev/vgora13/rdbs_dcdl_pt0205'
Sun Feb 20 21:20:11 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_smon_25140.trc:
ORA-01578: ORACLE data block corrupted (file # 652, block # 3767661)
ORA-01110: data file 652: '/dev/vgora13/rdbs_dcdl_pt0205'
Sun Feb 20 21:20:11 2022
LOGSTDBY: Validating controlfile with logical metadata
Sun Feb 20 21:20:11 2022
LOGSTDBY: Validation complete
Sun Feb 20 21:20:11 2022
Errors in file /opt/oracle/admin/xifenfei/bdump/xifenfei_smon_25140.trc:
ORA-01578: ORACLE data block corrupted (file # 652, block # 3767661)
ORA-01110: data file 652: '/dev/vgora13/rdbs_dcdl_pt0205'
Completed: alter database open

对于异常undo进行处理,数据库正常open
20220220225006
20220220224004
由于客户短期无法迁移数据,先对于一些坏块进行修复，暂时运行数据库后续有时间窗口进行迁移.

删除分区 oracle asm disk 恢复

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：删除分区 oracle asm disk 恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

接到一个朋友数据库故障请求case.大概操作是这样的:有一个39T的lun,通过parted分了15个分区,给oracle asm使用创建磁盘组data4,然后分了4个分区做成data5(由于ausize写错误了),删除掉磁盘组和这四个分区.然后重新分配了6个分区,并且使用最后5个分区创建了data5磁盘组.使用了一段时间之后,由于oracle空间不足,检查的时候误以为这个lun就前面15个分区使用,人工把后面的6个分区给删除了,并且创建了4个新分区,然后发现数据库crash了,发现误删除了在使用的分区.然后又把新创建的4个分区给删除了.接手该故障的时候,这个39T lun的分区信息如下

[root@node1 linux64]# parted /dev/mapper/36000d31003d39e000000000000000004
GNU Parted 2.1
Using /dev/mapper/36000d31003d39e000000000000000004
Welcome to GNU Parted! Type 'help' to view a list of commands.
(parted) print                                                            
Model: Linux device-mapper (multipath) (dm)
Disk /dev/mapper/36000d31003d39e000000000000000004: 39.6TB
Sector size (logical/physical): 512B/4096B
Partition Table: gpt

Number  Start   End     Size    File system  Name         Flags
 1      2097kB  2000GB  2000GB               asmdata4-1
 2      2000GB  4000GB  2000GB               asmdata4-2
 3      4000GB  6000GB  2000GB               asmdata4-3
 4      6000GB  8000GB  2000GB               asmdata4-4
 5      8000GB  10.0TB  2000GB               asmdata4-5
 6      10.0TB  12.0TB  2000GB               asmdata4-6
 7      12.0TB  14.0TB  2000GB               asmdata4-7
 8      14.0TB  16.0TB  2000GB               asmdata4-8
 9      16.0TB  18.0TB  2000GB               asmdata4-9
10      18.0TB  20.0TB  2000GB               asmdata4-10
11      20.0TB  22.0TB  2000GB               asmdata4-11
12      22.0TB  24.0TB  2000GB               asmdata4-12
13      24.0TB  26.0TB  2000GB               asmdata4-13
14      26.0TB  28.0TB  2000GB               asmdata4-14
15      28.0TB  30.0TB  2000GB               asmdata4-15

客户正常使用情况下,这个lun上面相关分区的asm disk信息

 SQL> CREATE DISKGROUP DATA4 EXTERNAL REDUNDANCY  DISK 
'/dev/mapper/36000d31003d39e000000000000000004p1' SIZE 1907346M ,
 '/dev/mapper/36000d31003d39e000000000000000004p2' SIZE 1907350M ,
 '/dev/mapper/36000d31003d39e000000000000000004p3' SIZE 1907348M ,
 '/dev/mapper/36000d31003d39e000000000000000004p4' SIZE 1907348M ,
 '/dev/mapper/36000d31003d39e000000000000000004p5' SIZE 1907350M  
ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='8M' /* ASMCA */ 

SQL> ALTER DISKGROUP DATA4 ADD  DISK 
'/dev/mapper/36000d31003d39e000000000000000004p10' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p6' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p7' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p8' SIZE 1907350M ,
'/dev/mapper/36000d31003d39e000000000000000004p9' SIZE 1907348M /* ASMCA */ 

SQL> ALTER DISKGROUP DATA4 ADD  DISK 
'/dev/mapper/36000d31003d39e000000000000000004p11' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p12' SIZE 1907350M ,
'/dev/mapper/36000d31003d39e000000000000000004p13' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p14' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p15' SIZE 1907350M /* ASMCA */ 

SQL> CREATE DISKGROUP DATA5 EXTERNAL REDUNDANCY  DISK 
'/dev/mapper/36000d31003d39e000000000000000004p17' SIZE 1716614M ,
'/dev/mapper/36000d31003d39e000000000000000004p18' SIZE 1716614M ,
'/dev/mapper/36000d31003d39e000000000000000004p19' SIZE 1716614M ,
'/dev/mapper/36000d31003d39e000000000000000004p20' SIZE 1716614M ,
'/dev/mapper/36000d31003d39e000000000000000004p21' SIZE 1621246M  
ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='4M' /* ASMCA */

基于客户现在的情况,data4中的所有分区都正常,主要是要找出来data5中的5个分区的数据.因为客户不确定p16分区大小,导致后续的5个分区起始位置不好定位.从而使得恢复无法进行.通过shell脚本结合kfed尝试定位asm disk header信息

#!/bin/bash
j=xxxxxxxxxxx
for ((i=xxxxxx; i<=j; i++))
do
 echo "-----$i--------" >> /home/get_au1.txt
 kfed read /dev/mapper/36000d31003d39e000000000000000004 aun=$i |
 > grep  "kfdhdb.dskname:              DATA" >> /home/get_au.txt
done

结果发现无法获取到结果,通过分析发现这里由于lun过大，导致aun值过大,从而使得kfed溢出无法读取到正常值.根据parted的特性,人工dd部分block进行分析

[root@node1 bak]# kfed read xifenfei.dd aun=134|more
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  3357988283 ; 0x00c: 0xc826d5bb
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:              DATA5_0000 ; 0x028: length=10
kfdhdb.grpname:                   DATA5 ; 0x048: length=5
kfdhdb.fgname:               DATA5_0000 ; 0x068: length=10
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             33116450 ; 0x0a8: HOUR=0x2 DAYS=0x9 MNTH=0x4 YEAR=0x7e5
kfdhdb.crestmp.lo:            477378560 ; 0x0ac: USEC=0x0 MSEC=0x10e SECS=0x7 MINS=0x7
kfdhdb.mntstmp.hi:             33116450 ; 0x0b0: HOUR=0x2 DAYS=0x9 MNTH=0x4 YEAR=0x7e5
kfdhdb.mntstmp.lo:            486256640 ; 0x0b4: USEC=0x0 MSEC=0x2ec SECS=0xf MINS=0x7
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  4194304 ; 0x0bc: 0x00400000
kfdhdb.mfact:                    454272 ; 0x0c0: 0x0006ee80
kfdhdb.dsksize:                  429153 ; 0x0c4: 0x00068c61
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002

顺利找到了data5中的第一块磁盘,而且确定了起始位置,然后构造相关的dd语句把分区的数据dd到一个新磁盘中

dd if=/dev/mapper/36000d31003d39e000000000000000004 bs=4M skip=xxxxx count=429153 of=/dev/sdu

然后通过kfed查看数据
20210704160347

通过类似方法依次处理,最终把5块asm disk全部找到,并且顺利dd到新的磁盘中.尝试启动crs,并mount data5
20210704153634
20210704153548
data5 磁盘组mount成功之后,数据库顺利启动,实现lun中删除分区之后,asm磁盘组数据完美恢复
20210704153728
这次运气还不错,仅仅是对lun的分区使用了parted进行了删除和创建等操作,没有格式化文件系统和做成新的asm disk,不然数据会有一部分丢失.对于有部分破坏的分区,需要通过底层碎片的方法进行最大限度抢救数据.参考类似文档:
asm disk被加入vg恢复
又一例asm格式化文件系统恢复
文件系统损坏导致数据文件异常恢复
一次完美的asm disk被格式化ntfs恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
再一起asm disk被格式化成ext3文件系统故障恢复
oracle asm disk格式化恢复—格式化为ext4文件系统
oracle asm disk格式化恢复—格式化为ntfs文件系统
分享oracleasm createdisk重新创建asm disk后数据0丢失恢复案例

tab$异常被处理之后报ORA-600 13304故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：tab$异常被处理之后报ORA-600 13304故障处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

又一例数据库启动报ORA-600 16703 1403 20错误故障

Sun Jun 13 14:00:56 2021
NOTE: dependency between database xff and diskgroup resource ora.DG_ARCH_xff.dg is established
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_56340.trc  (incident=348265):
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/xff/xff1/incident/incdir_348265/xff1_ora_56340_i348265.trc
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_56340.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_56340.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 56340): terminating the instance due to error 704
Instance terminated by USER, pid = 56340

这个故障比较明显,根据我们之前的分析经验(警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703),应该是tab$被恶意破坏导致,通过分析安装程序,确认是该问题,客户通过互联网上的相关文章,dd方式进行处理,结果数据库报ORA-600 13304错误,无法继续,让我们提供技术支持

SMON: enabling tx recovery
Database Characterset is AL32UTF8
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_83843.trc  (incident=396265):
ORA-00600: internal error code, arguments: [13304], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/xff/xff1/incident/incdir_396265/xff1_ora_83843_i396265.trc
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_83843.trc:
ORA-00600: internal error code, arguments: [13304], [], [], [], [], [], [], [], [], [], [], []
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_83843.trc:
ORA-00600: internal error code, arguments: [13304], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 83843): terminating the instance due to error 600
Instance terminated by USER, pid = 83843

通过我们的技术对数据库进行一系列恢复之后,open过程报错

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 1
ORA-00904: "NAME": invalid identifier
Process ID: 23346
Session ID: 680 Serial number: 51933

通过跟踪启动过程分析

PARSE ERROR #140574232044112:len=45 dep=1 uid=0 oct=3 lid=0 tim=1623621695884944 err=904
select value$ from sys.props$ where name = :1
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "NAME": invalid identifier
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "NAME": invalid identifier

基本上可以确定是由于客户自行恢复导致props$表异常.通过进一步分析,确认是由于在对tab$处理不合适导致,进一步对tab$进行处理,数据库恢复正常,实现数据0丢失

文件系统重新分区oracle恢复

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：文件系统重新分区oracle恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

最近处理的一个恢复,算是这几年中的一个奇葩.
1. oracle dg 主备库raid同时损坏,找硬件恢复厂商软件重组raid,恢复厂商判断所有磁盘全部都是好的
2. 主库系统被重装,文件系统重新分区.备库在使用duplicate搭建dg的过程中(通过alert日志分析以前的dg是正常的,直接rm掉了所有文件,然后使用duplicate搭建),只是部分文件拷贝到了备库
3. 备份放在一台单独的存储上,但是当上去看是发现存储上面空空的,没有任何数据(通过对ctl的分析,确认存储上面只有一个月之前的备份记录,估计也被删除或者重新分区了(通过后续分析,判断应该是被重新分区了)
客户没有和我们说任何信息,就是说突然两个raid都损坏了,找硬件厂商进行恢复,硬件厂商开始也觉得这个会比较简单,直接通过raid模拟恢复出来lun,然后通过软件恢复出来一些数据文件(反馈给我的信息是少了redo,需要我们协助恢复),通过深入分析,发现少了大量数据文件,基于现在的恢复基本上没意义.然后通过低主库的raid模拟恢复,拷贝出来数据文件,结果发现恢复出来的文件大小,和文件头记录不匹配
20210607232818

这里显示文件大小应该是30G,但是实际拷贝的文件只有26G大小
20210607232731
通过底层进一步分析,发现任何大于4G的文件,按照4G为单位间隔损坏(4G好,4G损坏,4G好……)
20210605203719
20210605201235
出现这类情况,通过底层分析,判断是客户对磁盘进行了重新分区,引起底层问题导致
20210607214629
基于这样的情况,没有太多好的方法处理,直接使用底层碎片技术进行恢复
20210607233847
运气不错,顺利open数据库
20210607234450
本次恢复走了很多弯路,主要是客户不清楚客户那边处于什么原因,多次隐秘故障原因,没有如实的告知我们故障情况,一步步尝试,走了很多弯路,耽误了不少时间.如果可能请尽量告诉我们准确情况,便于我们准确做出判断,快速高效的恢复.
类似oracle 碎片层面恢复,我们进行了挺多的,类似：
dbca删除库和rm删库恢复
文件系统损坏导致数据文件异常恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
alter database create datafile 导致数据文件丢失恢复
rm -rf 删除数据文件恢复方法—文件系统反删除+oracle碎片重组

ORA-00600: internal error code, arguments: [16703], [1403], [4] 故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORA-00600: internal error code, arguments: [16703], [1403], [4] 故障处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有一个客户数据库遭遇ORA-600 16703错误,故障原因见:警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703,这种故障已经恢复比较多,在这次的恢复中遇到一个新错误,进行记录
接手给客户报错情况ORA-00600: internal error code, arguments: [16703], [1403], [20]
20210515011736

Thu May 13 22:36:11 2021
SMON: enabling cache recovery
Thu May 13 22:36:11 2021
NSA2 started with pid=61, OS id=6261 
Archived Log entry 90224 added for thread 1 sequence 50454 ID 0x19ae1c6c dest 1:
Errors in file /oracle/diag/rdbms/xff/xff/trace/xff_ora_5931.trc  (incident=741052):
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/xff/xff/incident/incdir_741052/xff_ora_5931_i741052.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /oracle/diag/rdbms/xff/xff/trace/xff_ora_5931.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Errors in file /oracle/diag/rdbms/xff/xff/trace/xff_ora_5931.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 5931): terminating the instance due to error 704
Instance terminated by USER, pid = 5931
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (5931) as a result of ORA-1092
Thu May 13 22:36:13 2021
ORA-1092 : opitsk aborting process

这种故障,由于是恶意脚本在数据库启动的时候清空tab$所致,使用bbed对tab$进行反向删除操作,实现数据库打开.
在这次的恢复过程中遇到ORA-600 16703 1403 4的错误

SQL> startup mount pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 7.0818E+10 bytes
Fixed Size                  2260448 bytes
Variable Size            1.3422E+10 bytes
Database Buffers         5.7177E+10 bytes
Redo Buffers              217030656 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-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [4], [], [], [],
[], [], [], [], [], []
Process ID: 51886
Session ID: 3497 Serial number: 3

根据ora-600 16703 1403 4基本上可以判断是由于tab$这个表中缺少obj#=4的对象导致,通过查询正常库,确认是该对象为tab$,也就是说由于tab$对象中少了tab$记录.通过bbed分析确认

SQL> SELECT a.OBJ#
  2        ,TAB#
  3        ,a.DATAOBJ#
  4        ,BOBJ#
  5        ,NAME
  6        ,DBMS_ROWID.ROWID_RELATIVE_FNO (a.ROWID) FILE_ID
  7        ,DBMS_ROWID.ROWID_BLOCK_NUMBER (a.ROWID) BLOCK_ID
  8    FROM TAB$ a, obj$ b
  9   WHERE     a.obj# = b.obj#
 10         AND A.OBJ# IN (4);

      OBJ#       TAB#   DATAOBJ#      BOBJ# NAME
---------- ---------- ---------- ---------- ------------------------------
   FILE_ID   BLOCK_ID
---------- ----------
         4          1          2          2 TAB$
         1        147

BBED> set dba 1,147
        DBA             0x00400093 (4194451 1,147)

BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct  *kdbr[14]
rowdata[6848]                               @7349
-------------
flag@7349: 0x20 (KDRHFH)
lock@7350: 0x02
cols@7351:    0
nrid@7352:0x00407b09.1
BBED> set dba 0x00407b09
        DBA             0x00407b09 (4225801 1,31497)

BBED> p kdbt[1]
struct kdbt[1], 4 bytes                     @110
   sb2 kdbtoffs                             @110      10
   sb2 kdbtnrow                             @112      2

BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct  *kdbr[11]
rowdata[815]                                @4436
------------
flag@4436: 0x5c (KDRHFL, KDRHFF, KDRHFD, KDRHFC)
lock@4437: 0x02
cols@4438:    0
ckix@4439:    8

BBED> x /rn  *kdbr[8]
rowdata[950]                                @4571
------------
flag@4571: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@4572: 0x00
cols@4573:    1
kref@4574:    1
hrid@4576:0x00400093.8
nrid@4582:0x00400094.0
col    0[2] @4590: 4

确认该记录发生了行迁移导致该问题,对其对应的block进行修复,数据库正常打开.

记录一次oracle现场故障处理经过

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：记录一次oracle现场故障处理经过

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

近期到现场进行了一个数据库恢复,我在恢复之前该库先由于硬件进行恢复,然后由其他人对其进行了一系列数据库恢复,但是未恢复成功,客户希望我们到现场进行处理(因为网络原因无法远程).接手库之后,处理第一个问题,是客户在进行现场备份的时候(把linux数据拷贝到win的过程中)发现有几个文件拷贝异常,这个错误很可能是由于当初的硬件故障修复之后留下的后遗症(由于io设备错误,无法运行此项请求),通过工具进行拷贝,恢复出来
20210403210131

DUL> copy file from  /oradata2/xifenfeidata.dbf to /oradata2/xifenfeidata.dbf

starting copy datafile '/oradata1/xifenfeidata.dbf' to '/oradata2/xifenfeidata.dbf'
read data error from file '/oradata1/xifenfeidata.dbf'.error message:Input/output error
read block# error: 560171
read data error from file '/oradata1/xifenfeidata.dbf'.error message:Input/output error
read block# error: 560179
datafile copy completed with 2 block error.

[oracle@localhost ~]$ dbv file=/oradata2/xifenfeidata.dbf blocksize=16384

DBVERIFY: Release 11.2.0.3.0 - Production on Mon Mar 29 17:28:17 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oradata2/xifenfeidata.dbf
Page 560171 is marked corrupt
Corrupt block relative dba: 0x3bc88c2b (file 239, block 560171)
Completely zero block found during dbv: 

Page 560179 is marked corrupt
Corrupt block relative dba: 0x3bc88c33 (file 239, block 560179)
Completely zero block found during dbv: 



DBVERIFY - Verification complete

Total Pages Examined         : 4194302
Total Pages Processed (Data) : 2230726
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1936953
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 26618
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 3
Total Pages Marked Corrupt   : 2
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 304929867 (106.304929867)

修复完相关无法拷贝文件之后,启动数据库报控制文件异常

Mon Mar 29 15:03:38 2021
alter database mount
USER (ospid: 29044): terminating the instance
Mon Mar 29 15:03:42 2021
System state dump requested by (instance=1, osid=29044), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/xff/xff/trace/xff_diag_28961.trc
Instance terminated by USER, pid = 29044

尝试重建ctl

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 29 17:40:17 2021

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 1.7704E+10 bytes
Fixed Size                  2235568 bytes
Variable Size            2348811088 bytes
Database Buffers         1.5301E+10 bytes
Redo Buffers               52580352 bytes
SQL> @/tmp/ctl.sql
CREATE CONTROLFILE REUSE DATABASE xff NORESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 249: '/oradata/xff/system03.dbf'

初步判断是由于对方之前恢复导致部分文件resetlogs scn异常,通过bbed进行判断确认

BBED> set file 1
        FILE#           1

BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116     
   ub4 kscnbas                              @116      0x00000001
   ub2 kscnwrp                              @120      0x0000

BBED> set file 249
        FILE#           249

BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116     
   ub4 kscnbas                              @116      0x00000001
   ub2 kscnwrp                              @120      0x0000

通过bbed修改相关值,然后重建控制文件成功,尝试resetlogs库，报ORA-01248错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01248: file 234 was created in the future of incomplete recovery
ORA-01110: data file 234: '/oradata1/xifenfeidata5.DBF'

关于ORA-01248的错误解释

01248, 00000, "file %s was created in the future of incomplete recovery"
// *Cause:  Attempting to do a RESETLOGS open with a file entry in the
//          control file that was originally created after the UNTIL time 
//          of the incomplete recovery.
//          Allowing such an entry may hide the version of the file that 
//          is needed at this time.  The file number may be in use for 
//          a different file which would be lost if the RESETLOGS was allowed.
// *Action: If more recovery is desired then apply redo until the creation
//          time of the file is reached. If the file is not wanted and the
//          same file number is not in use at the stop time of the recovery,
//          then the file can be taken offline with the FOR DROP option.
//          Otherwise a different control file is needed to allow the RESETLOGS.
//          Another backup can be restored and recovered, or a control file can
//          be created via CREATE CONTROLFILE.

大概的意思是文件的创建时间大于文件当前的scn,通过查询确实如此

SQL> select file#,CREATION_CHANGE#,CREATION_TIME from v$datafile_header where file#=234;

           FILE# CREATION_CHANGE# CREATION_
---------------- ---------------- ---------
             234     419298664864 02-AUG-19

SQL> SELECT status,  
  2  to_char(checkpoint_change#,'9999999999999999') "SCN",
  3  to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,
  4  count(*) ROW_NUM
  5  FROM v$datafile_header
  6  GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
  7  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  SCN               CHECKPOINT_TIME     FUZ          ROW_NUM
------- ----------------- ------------------- --- ----------------
ONLINE       417750848223 2021-02-23 23:50:46 YES                7
ONLINE       417750848223 2021-03-21 11:44:25 NO               396

通过对部分scn进行修改（比如减小创建时间的scn)，然后尝试resetlogs库

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
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 5 with name
"_SYSSMU5_2708889888$" too small
Process ID: 3182
Session ID: 1 Serial number: 3

这个错误比较简单,参考以前的部分文章:在数据库open过程中常遇到ORA-01555汇总数据库open过程遭遇ORA-1555对应sql语句补充,处理之后,数据库open成功

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.7704E+10 bytes
Fixed Size                  2235568 bytes
Variable Size            2348811088 bytes
Database Buffers         1.5301E+10 bytes
Redo Buffers               52580352 bytes
Database mounted.
SQL> alter database open;

Database altered.

本次数据库恢复基本上完成,已经最大限度恢复数据,导出数据到新库,完成恢复任务