Oracle备份恢复 – 第13页

有朋友找我，由于客户硬件故障,导致控制文件损坏，重建控制文件的时候,由于失误依赖了100多个数据文件,并且进行了resetlogs操作,然后通过bbed修改文件头,再进行恢复,数据库在open过程中报ORA-03113错误

SQL> recover database;
Media recovery complete.
SQL> alter database Open ;
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 11207284
Session ID: 4537 Serial number: 1

对应的alert日志信息

Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Dec 04 23:17:02 2021
SMON: enabling cache recovery
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Sat Dec 04 23:17:03 2021
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0x1604971A0][PC:0x1000E1764,kgegpa()+36][flags:0x0,count:1]
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0x1604971A0][PC:0x1000E1764,kgegpa()+36][flags:0x2,count:2]
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0x1604971A0][PC:0x1000E1764,kgegpa()+36][flags:0x2,count:2]
Sat Dec 04 23:17:07 2021
PMON (ospid: 10879866): terminating the instance due to error 397
Sat Dec 04 23:17:08 2021
System state dump requested by (instance=1, osid=10879866 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/oracle/diag/rdbms/xff/xff1/trace/xff1_diag_11076320.trc
Dumping diagnostic data in directory=[cdmp_20211204231708], requested by (instance=1, osid=10879866 (PMON)), 
summary=[abnormal instance termination].
Sat Dec 04 23:17:09 2021
ORA-1092 : opitsk aborting process

通过对其启动过程进行跟踪确认在update undo$ 语句中异常

PARSING IN CURSOR #11529215044940435280 len=148 dep=1 uid=0 oct=6 lid=0 tim=223080942765 
hv=3540833987 ad='c000000d67a42778' sqlid='5ansr7r9htpq3'
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 #11529215044940435280:c=10000,e=8182,p=6,cr=55,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=223080942764
BINDS #11529215044940435280:
 Bind#0
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=c000000d5fd299aa  bln=32  avl=20  flg=09
  value="_SYSSMU29_3935275865$"
 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=9fffffffbcc6e078  bln=24  avl=02  flg=05
  value=3
 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=9fffffffbcc6e048  bln=24  avl=03  flg=05
  value=128
 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=9fffffffbcc6e010  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=9fffffffbcc6dfe0  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=9fffffffbcc6dfb0  bln=24  avl=04  flg=05
  value=28921
 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=9fffffffbcc6df80  bln=24  avl=05  flg=05
  value=1245262
 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=9fffffffbcc6df48  bln=24  avl=06  flg=05
  value=1217986655
 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=9fffffffbcc6dc90  bln=24  avl=03  flg=05
  value=3621
 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=9fffffffbcc6dc60  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=9fffffffbcc6dc30  bln=24  avl=02  flg=05
  value=2
 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=9fffffffbcc6dc00  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=9fffffffbcc6e0a8  bln=22  avl=02  flg=05
  value=1
WAIT #4574659112: nam='db file sequential read' ela= 6835 file#=1 block#=842169 blocks=1 obj#=0 tim=90821816220340

通过分析,确认是由于undo事务异常,通过一系列处理之后,数据库启动报

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Process ID: 11206912
Session ID: 4537 Serial number: 1

对应alert日志报错

Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Dec 05 02:42:02 2021
SMON: enabling cache recovery
SUCCESS: diskgroup FRA was mounted
Archived Log entry 1 added for thread 1 sequence 1 ID 0x14ba19ad dest 1:
Sun Dec 05 02:42:03 2021
NOTE: dependency between database xff and diskgroup resource ora.FRA.dg is established
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_11206912.trc  (incident=5681243):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xff/xff1/incident/incdir_5681243/xff1_ora_11206912_i5681243.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Sun Dec 05 02:42:05 2021
Dumping diagnostic data in directory=[cdmp_20211205024205], requested by (instance=1, osid=11206912), 
summary=[incident=5681243].
Sun Dec 05 02:42:05 2021
Block recovery from logseq 2, block 3 to scn 13867049493730
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: +DATA/xff/onlinelog/group_2.log
Block recovery stopped at EOT rba 2.5.16
Block recovery completed at rba 2.5.16, scn 3228.2895062240
Block recovery from logseq 2, block 3 to scn 13867049493727
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: +DATA/xff/onlinelog/group_2.log
Block recovery completed at rba 2.5.16, scn 3228.2895062240
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_11206912.trc:
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_11206912.trc:
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 11206912): terminating the instance due to error 600
Instance terminated by USER, pid = 11206912
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (11206912) as a result of ORA-1092
Sun Dec 05 02:42:07 2021
ORA-1092 : opitsk aborting process

trace中关键信息和bbed对看到结果对应,通过bbed清空,数据库启动正常

Version: 0x01
  FREE BLOCK POOL::
    uba: 0x004cd9b9.02fb.0c ext: 0x19 spc: 0x1462
    uba: 0x00000000.02fb.02 ext: 0x19 spc: 0x1f06
    uba: 0x00000000.02fb.0d ext: 0x19 spc: 0x1970
    uba: 0x00000000.02fa.03 ext: 0x18 spc: 0x1e34
    uba: 0x00000000.02fa.03 ext: 0x18 spc: 0x1e34

   struct ktuxcfbp[0], 12 bytes             @4192    
      struct ktufbuba, 8 bytes              @4192    
         ub4 kubadba                        @4192     0x004cd9b9
         ub2 kubaseq                        @4196     0x02fb
         ub1 kubarec                        @4198     0x0c
      sb2 ktufbext                          @4200     25
      sb2 ktufbspc                          @4202     5218

以前有过类似恢复硬件恢复之后,数据库无法open故障恢复

校验代码为 6054 坏块故障修复

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

标题：校验代码为 6054 坏块故障修复

有客户反馈数据库操作报错,希望我们给予处理,报错信息如下:

通过对system01.dbf数据文件分析

C:\Users\XFF>dbv file=H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期六 12月 4 20:45:23 2021

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

DBVERIFY - 开始验证: FILE = H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF

DBV-00200: 块 DBA 4330012 已标记为损坏
csc(0x0000.1686df22) higher than block scn(0x0000.00000000)
页 135708 失败, 校验代码为 6054


DBVERIFY - 验证完成

检查的页总数: 184320
处理的页总数 (数据): 93105
失败的页总数 (数据): 0
处理的页总数 (索引): 31365
失败的页总数 (索引): 1
处理的页总数 (其他): 43622
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 16228
标记为损坏的总页数: 1
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 378591519 (0.378591519)

确认block 135708 有问题,确认起对象为I_OBJ5

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 的值:  135708
原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
新值    4:    AND 135708 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER                          SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------------------ --------------- ------------------ ------------------------------ -----------------
SYS                            I_OBJ5          INDEX
                    SYSTEM

这个对象为核心对象obj$的index,无法直接rebuild,当然可以通过参考以前blog文档进行重建:bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决,直接对该坏块进行修复.

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

BBED> m /x 23df8616
 File: H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF (0)
 Block: 135709           Offsets:    8 to  519           Dba:0x00000000
------------------------------------------------------------------------
 23df8616 00000104 44fe0000 02000000 28000000 22df8616 00000000 02000200
 00000000 0a001800 f7dc1b00 8002c000 bdd80100 00e00000 f331b114 0a001f00
 14081e00 4505c000 2c103b00 01200000 2adf8616 00008009 01000000 3f00a200
 35074010 00000000 e1b24100 56694100 06000000 641f0000 57119211 cd11d208
 97080812 43127e12 b912f412 2f130d09 e013bd09 1b14cc14 0715ab07 5c087d15
 b715f215 a3167007 3507de16 19175417 21088f17 ca170518 4018330a 1f0b7b18
 2c196719 a219dd19 181a531a 8e1a4709 031be607 f809791b b41bef1b 2a1c651c
 db1ce20d 8c1d310d c71d021e 3d1e781e b31e8209 291f291f 291f291f 291f291f
 291f291f 291f0000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

然后dbv报以下错误

C:\Users\XFF>dbv file=H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期六 12月 4 22:06:49 2021

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

DBVERIFY - 开始验证: FILE = H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF
itl[2] has higher commit scn(0x0000.1686df2a) than block scn (0x0000.1686df23)
页 135708 失败, 校验代码为 6056


DBVERIFY - 验证完成

检查的页总数: 184320
处理的页总数 (数据): 95152
失败的页总数 (数据): 0
处理的页总数 (索引): 34569
失败的页总数 (索引): 1
处理的页总数 (其他): 38386
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 16213
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 378683101 (0.378683101)

再次对其进行修复

BBED> m /x 22df8616
 File: H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF (0)
 Block: 135709           Offsets:   88 to  599           Dba:0x00000000
------------------------------------------------------------------------
 22df8616 00008009 01000000 3f00a200 35074010 00000000 e1b24100 56694100
 06000000 641f0000 57119211 cd11d208 97080812 43127e12 b912f412 2f130d09
 e013bd09 1b14cc14 0715ab07 5c087d15 b715f215 a3167007 3507de16 19175417
 21088f17 ca170518 4018330a 1f0b7b18 2c196719 a219dd19 181a531a 8e1a4709
 031be607 f809791b b41bef1b 2a1c651c db1ce20d 8c1d310d c71d021e 3d1e781e
 b31e8209 291f291f 291f291f 291f291f 291f291f 291f0000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

再次dbv检查修复成功

C:\Users\XFF>dbv file=H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期六 12月 4 22:30:14 2021

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

DBVERIFY - 开始验证: FILE = H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF


DBVERIFY - 验证完成

检查的页总数: 184320
处理的页总数 (数据): 95152
失败的页总数 (数据): 0
处理的页总数 (索引): 34569
失败的页总数 (索引): 0
处理的页总数 (其他): 38386
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 16213
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 378683101 (0.378683101)

对于这类的坏块,也可以通过我们的Oracle recovery tools进行快速恢复

win环境报ora-600 kokasgi1处理

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

标题：win环境报ora-600 kokasgi1处理

又一例数据库启动报ORA-600 kokasgi1错误的恢复请求

SMON: enabling tx recovery
Database Characterset is AL32UTF8
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3024.trc  (incident=44691):
ORA-00600: 内部错误代码, 参数: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: e:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_44691\orcl_ora_3024_i44691.trc
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3024.trc:
ORA-00600: 内部错误代码, 参数: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3024.trc:
ORA-00600: 内部错误代码, 参数: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 3024): terminating the instance due to error 600
Instance terminated by USER, pid = 3024
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (3024) as a result of ORA-1092

通过咨询是由于根据某厂商给出来的建议对对oracle的user$的系统默认用户update等操作

通过分析现在库的用户情况

通过一系列处理之后绕过kokasgi1错误,open数据库报ORA-12432错误

SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3268.trc:
ORA-12432: LBAC 错误: zllegbs:OCIStmtExecute
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3268.trc:
ORA-12432: LBAC 错误: zllegbs:OCIStmtExecute
Error 12432 happened during db open, shutting down database
USER (ospid: 3268): terminating the instance due to error 12432
Instance terminated by USER, pid = 3268
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (3268) as a result of ORA-1092

通过分析启动过程确认数据库在启动的时候访问lbacsys.lbac$pol表异常

PARSE ERROR #2:len=38 dep=1 uid=0 oct=3 lid=0 tim=37509085805630 err=942
select max(pol#) from lbacsys.lbac$pol
ORA-12432: LBAC 错误: zllegnp:OCIStmtExecute
ORA-12432: LBAC 错误: zllegnp:OCIStmtExecute

*** 2021-07-28 18:31:15.593
USER (ospid: 3512): terminating the instance due to error 12432

经过修改文件让数据库不在访问该表,数据库启动正常

SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Jul 28 19:52:59 2021
QMNC started with pid=32, OS id=2840 
Completed: alter database open

顺利导出数据,恢复完成

又一例存储cache丢失oracle数据库恢复

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

标题：又一例存储cache丢失oracle数据库恢复

10.2.0.5 hp unix rac,由于存储掉电导致cache丢失,数据库无法正常启动,客户要求我们介入处理
数据库mount报ORA-00600 kccpb_sanity_check_2错误

Thu Jul 22 14:52:06 EAT 2021
alter database mount
Thu Jul 22 14:52:10 EAT 2021
Errors in file /oracle/admin/xff/udump/xff1_ora_4611.trc:
ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [4697564], [4697561], [0x000000000], [], [], [], []

该错误是由于控制文件损坏,尝试重建控制文件报ORA-01163,ORA-01517

'/dev/oradata/rxff_ls94'
CHARACTER SET ZHS16GBK
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Thu Jul 22 14:54:02 EAT 2021
Errors in file /oracle/admin/xff/udump/xff1_ora_7283.trc:
ORA-01163: SIZE clause indicates 262144 (blocks), but should match header 204800
ORA-01517: log member: '/dev/oradata/rxff_redo1_1'
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "xff" NORESETLOGS  NOARCHIVELOG

由于redo大小错误导致该问题,设置正确的redo大小继续重建

'/dev/oradata/rxff_ls94'
CHARACTER SET ZHS16GBK
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Thu Jul 22 15:01:00 EAT 2021
Errors in file /oracle/admin/xff/udump/xff1_ora_14737.trc:
ORA-00600: internal error code, arguments: [kccsga_update_ckpt_4], [32], [8], [], [], [], [], []
Thu Jul 22 15:01:01 EAT 2021
Errors in file /oracle/admin/xff/udump/xff1_ora_14737.trc:
ORA-00600: internal error code, arguments: [kccsga_update_ckpt_4], [32], [8], [], [], [], [], []
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "xff" NORESETLOGS  NOARCHIVELOG

报ORA-00600 kccsga_update_ckpt_4错误,导致控制文件失败,处理该错误之后,重建控制文件成功,分析文件头信息和redo信息,确认只能强制库,尝试强制open库

Thu Jul 22 16:02:05 EAT 2021
SMON: enabling cache recovery
Thu Jul 22 16:02:05 EAT 2021
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0002.cdad19ed):
Thu Jul 22 16:02:05 EAT 2021
select ctime, mtime, stime from obj$ where obj# = :1
Thu Jul 22 16:02:05 EAT 2021
Errors in file /oracle/admin/xff/udump/xff1_ora_23219.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 19 with name "_SYSSMU19$" too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 23219
ORA-1092 signalled during: alter database open resetlogs...

这个问题比较常见:ORA-00704 ORA-00604 ORA-01555,参考类似文章:
在数据库open过程中常遇到ORA-01555汇总
 数据库open过程遭遇ORA-1555对应sql语句补充
数据库open成功但是报ORA-00600 4137

Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan 
Thu Jul 22 16:08:48 EAT 2021
Errors in file /oracle/admin/xff/bdump/xff1_smon_27436.trc:
ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=30, OS id=997
Thu Jul 22 16:08:49 EAT 2021
LOGSTDBY: Validating controlfile with logical metadata
Thu Jul 22 16:08:49 EAT 2021
ORACLE Instance xff1 (pid = 11) - Error 600 encountered while recovering transaction (1, 43).
Thu Jul 22 16:08:49 EAT 2021
Errors in file /oracle/admin/xff/bdump/xff1_smon_27436.trc:
ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []
Thu Jul 22 16:08:49 EAT 2021
Trace dumping is performing id=[cdmp_20210722160849]
Thu Jul 22 16:08:49 EAT 2021
LOGSTDBY: Validation complete
Completed: alter database open

该问题是由于undo异常,对undo进行处理,数据库无明显报错,安排导出数据

ORA-01092: ORACLE 例程终止故障恢复

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

标题：ORA-01092: ORACLE 例程终止故障恢复

数据库启动报ORA-01092: ORACLE 例程终止。强行断开连接错误

SQL> RECOVER DATABASE;
完成介质恢复。
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR 位于第 1 行:
ORA-01092: ORACLE 例程终止。强行断开连接

查看alert日志

Wed Jul 21 12:32:04 2021
SMON: enabling cache recovery
Wed Jul 21 12:32:04 2021
Errors in file c:\oracle\admin\dcpdm\udump\dcpdm_ora_3004.trc:
ORA-00600: ?????????: [4194], [34], [8], [], [], [], [], []

Wed Jul 21 12:32:05 2021
Recovery of Online Redo Log: Thread 1 Group 2 Seq 495 Reading mem 0
  Mem# 0 errs 0: C:\ORACLE\ORADATA\DCPDM\REDO02.LOG
Recovery of Online Redo Log: Thread 1 Group 2 Seq 495 Reading mem 0
  Mem# 0 errs 0: C:\ORACLE\ORADATA\DCPDM\REDO02.LOG
Wed Jul 21 12:32:05 2021
Errors in file c:\oracle\admin\dcpdm\udump\dcpdm_ora_3004.trc:
ORA-00604: ?? SQL ? 1 ????
ORA-00607: ?????????????
ORA-00600: ?????????: [4194], [34], [8], [], [], [], [], []

Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Wed Jul 21 12:32:05 2021
Errors in file c:\oracle\admin\dcpdm\bdump\dcpdm_pmon_13020.trc:
ORA-00604: error occurred at recursive SQL level 

Instance terminated by USER, pid = 3004
ORA-1092 signalled during: ALTER DATABASE OPEN...

trace文件信息

*** 2021-07-21 12:32:04.000
ksedmp: internal or fatal error
ORA-00600: ?????????: [4194], [34], [8], [], [], [], [], []
Current SQL statement for this session:
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
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_ksedmp+147          CALLrel  _ksedst+0            
_ksfdmp.108+e        CALLrel  _ksedmp+0            3
_kgeriv+89           CALLreg  00000000             4E59D98 3
_kseipre.107+3f      CALLrel  _kgeriv+0            
_ksesic2+24          CALLrel  _kseipre.107+0       
__VInfreq__kturdb+8  CALLrel  _ksesic2+0           1062 0 22 0 8
b                                                  
_kcoapl+1df          CALLreg  00000000             2BB0F94 2BB100A 11 6C37C014
_kcbapl+71           CALLrel  _kcoapl+0            2BB0F90 6C37C000 1 0 2000
_kcrfwr+734          CALLrel  _kcbapl+0            2BB0F90 6C3FC788 50D4FA0
_kcbchg1+7ec         CALLrel  _kcrfwr+0            
_ktuchg+630          CALLrel  _kcbchg1+0           0 4 50D5228 50D5240 0 0
_ktbchg2+75          CALLrel  _ktuchg+0            2 66F589A4 1 2C8CD14 2C8CD1C
                                                   2BB0F90 2C8C32C 2BB0ED0 0 0
_kddchg+18f          CALLrel  _ktbchg2+0           0 66F589A4 2C8CD14 2C8CD1C
                                                   2BB0F90 2C8C324 2BB0ED0 0 0
_kduovw.53+6e3       CALLrel  _kddchg+0            2C8C2E8 2C8CD14 2C8CD1C
                                                   2BB0F90 2BB0ED0 0 0
_kduurp.53+61a       CALLrel  _kduovw.53+0         2C8C2E8
_kdusru+aa5          CALLrel  _kduurp.53+0         2C8C2E8 66F589FC
_kauupd+12e          CALLrel  _kdusru+0            2C8C71C 66F589FC 2C8C2E8 0
_updrow+729          CALLrel  _kauupd+0            2C8C718 66F589FC 2C8C2E8 0
                                                   66F58448 E F 66F60EE0 12
                                                   50DBBA4 50DBBA8
_qerupFetch+107      CALLrel  _updrow+0            
_updaul+202          CALL???  00000000             66F58660 0 66F6BC3C 7FFF
_updThreePhaseExe+b  CALLrel  _updaul+0            66F6B9D0 50DBD34 0
6                                                  
_updexe+105          CALLrel  _updThreePhaseExe+0  66F6B9D0 0 2C8C2E8 50DBE10
                                                   66F6B9D0 1 50DBE10 0
_opiexe+f97          CALLrel  _updexe+0            66F6B9D0 50DBF4C
_opiodr+4cd          CALLreg  00000000             4 3 50DC898
_rpidrus.43+99       CALLrel  _opiodr+0            4 3 50DC898 A
_skgmstack+71        CALLreg  00000000             50DC488
_rpidru+6d           CALLrel  _skgmstack+0         50DC4A0 4E59C20 F618 778198
                                                   50DC488
_rpiswu2+17e         CALLreg  00000000             50DC7C0
_rpidrv+109          CALLrel  _rpiswu2+0           
_rpiexe+33           CALLrel  _rpidrv+0            A 4 50DC898 8
_ktuscu+2a8          CALLrel  _rpiexe+0            A
_kqrcmt+2c2          CALL???  00000000             66F6D654 3
..1.18_2.filter.95+  CALLrel  _kqrcmt+0            67B88CD4 1 0 4E59D98 4E59D98
159                                                FF 0 0 0
..1.23_5.filter.99+  CALLrel  _ktcrcm+0            67B88CD4 0 0 0 0 1 0 0
14d                                                
_ktuini+64           CALLrel  _ktuiup.99+0         50DD994
_adbdrv+2665         CALLrel  _ktuini+0            50DD994
..1.5_1.filter.29+2  CALLrel  _adbdrv+0            
9d                                                 
_opiosq0+9a4         CALLrel  _opiexe+0            4 0 50DDDDC
_kpooprx+c6          CALLrel  _opiosq0+0           3 E 50DDE74 24
_kpoal8+225          CALLrel  _kpooprx+0           50DE73C 50DE684 13 1 0 24
_opiodr+4cd          CALLreg  00000000             5E 14 50DE738
_ttcpip+a86          CALLreg  00000000             5E 14 50DE738 0
_opitsk+2f4          CALLrel  _ttcpip+0            
_opiino+5fc          CALLrel  _opitsk+0            0 0 4E5FEE8 2BDF044 F3 0
_opiodr+4cd          CALLreg  00000000             3C 4 50DFBD8
_opidrv+233          CALLrel  _opiodr+0            3C 4 50DFBD8 0
_sou2o+19            CALLrel  _opidrv+0            
_opimai+10a          CALLrel  _sou2o+0             
_OracleThreadStart@  CALLrel  _opimai+0            
4+35c                                              
7C824826             CALLreg  00000000             
 
--------------------- Binary Stack Dump ---------------------

比较明显时候由于在更新undo$的时候需要找前镜像信息

Block image after block recovery:
buffer tsn: 0 rdba: 0x0040018b (1/395)
scn: 0x0000.07d52871 seq: 0x01 flg: 0x04 tail: 0x28710201
frmt: 0x02 chkval: 0xc85e type: 0x02=KTU UNDO BLOCK
 
********************************************************************************
UNDO BLK:  
xid: 0x0000.05a.0000002d  seq: 0x33  cnt: 0x22  irb: 0x22  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f04     0x02 0x1e20     0x03 0x1d3c     0x04 0x1c58     0x05 0x1b74     
0x06 0x1a90     0x07 0x19ac     0x08 0x18c8     0x09 0x17e4     0x0a 0x1700     
0x0b 0x161c     0x0c 0x1538     0x0d 0x1454     0x0e 0x1370     0x0f 0x128c     
0x10 0x11a8     0x11 0x10c4     0x12 0x0fe0     0x13 0x0efc     0x14 0x0e18     
0x15 0x0d34     0x16 0x0c50     0x17 0x0b6c     0x18 0x0a88     0x19 0x09a4     
0x1a 0x08c0     0x1b 0x07dc     0x1c 0x06f8     0x1d 0x0614     0x1e 0x0530     
0x1f 0x044c     0x20 0x0368     0x21 0x0284     0x22 0x01a0     
 
*-----------------------------
* Rec #0x1  slt: 0x0b  objn: 15(0x0000000f)  objd: 15  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x00   
Undo type:  Regular undo    Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000
*-----------------------------
uba: 0x0040018a.0033.22 ctl max scn: 0x0000.07853941 prv tx scn: 0x0000.07853943
KDO undo record:
KTB Redo 
op: 0x04  ver: 0x01  
op: L  itl: xid:  0x0000.042.0000002d uba: 0x0040018a.0033.22
                      flg: C---    lkc:  0     scn: 0x0000.07d23460
KDO Op code: URP row dependencies Disabled
  xtype: XA  bdba: 0x0040006a  hdba: 0x00400069
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 0
ncol: 17 nnew: 12 size: 0
col  1: [ 9]  5f 53 59 53 53 4d 55 37 24
col  2: [ 2]  c1 02
col  3: [ 2]  c1 03
col  4: [ 3]  c2 02 06
col  5: [ 6]  c5 02 20 14 40 24
col  6: [ 1]  80
col  7: [ 4]  c3 0e 21 2d
col  8: [ 3]  c2 1b 34
col  9: [ 1]  80
col 10: [ 2]  c1 03
col 11: [ 2]  c1 02
col 16: [ 2]  c1 02

这部分信息异常,导致数据库update undo$的时候报ORA-00600: ?????????: [4194], [34], [8], [], [], [], [], []错误,通过修改对应的block信息,数据库正常open成功

SQL> alter database open;

数据库已更改。

但是关闭数据库又报ORA-600 4194错误

SQL> shutdown immediate;
ORA-00607: 当更改数据块时出现内部错误
ORA-00600: 内部错误代码，参数: [4194], [94], [61], [], [], [], [], []

alert日志信息

Wed Jul 21 12:58:42 2021
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 3
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Wed Jul 21 12:58:45 2021
ALTER DATABASE CLOSE NORMAL
Wed Jul 21 12:58:45 2021
Errors in file c:\oracle\admin\dcpdm\udump\dcpdm_ora_13628.trc:
ORA-00600: 内部错误代码，参数: [4194], [94], [61], [], [], [], [], []

Recovery of Online Redo Log: Thread 1 Group 3 Seq 496 Reading mem 0
  Mem# 0 errs 0: C:\ORACLE\ORADATA\DCPDM\REDO03.LOG
Recovery of Online Redo Log: Thread 1 Group 3 Seq 496 Reading mem 0
  Mem# 0 errs 0: C:\ORACLE\ORADATA\DCPDM\REDO03.LOG
ORA-607 signalled during: ALTER DATABASE CLOSE NORMAL...

通过重建undo,数据库启动关闭正常,也没有再报其他错误,建议逻辑方式重建库
参考以前的类似文章:
数据库报ORA-00607/ORA-00600[4194]错误
 使用bbed解决ORA-00607/ORA-00600[4194]故障
 使用bbed解决ORA-00607/ORA-00600[4194]故障

ora-600 kfdpMetaBlk_pickle 故障处理

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

标题：ora-600 kfdpMetaBlk_pickle 故障处理

客户反馈集群的crs无法正常启动观察发现是由于gmon进程crash asm实例导致,经过测试确认是在mount data磁盘组的时候会触发给问题

SQL> alter diskgroup data mount;
alter diskgroup data mount
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7517
Session ID: 918 Serial number: 5

对应的alert日志报ORA-600 [kfdpMetaBlk_pickle:01], [4294967295]错误

SQL> alter diskgroup data mount
NOTE: cache registered group DATA number=2 incarn=0x3078f05f
NOTE: cache began mount (first) of group DATA number=2 incarn=0x3078f05f
NOTE: Assigning number (2,1) to disk (/dev/rdisk/disk93)
NOTE: Assigning number (2,3) to disk (/dev/rdisk/disk96)
NOTE: Assigning number (2,2) to disk (/dev/rdisk/disk94)
NOTE: Assigning number (2,0) to disk (/dev/rdisk/disk92)
Sat Jul 17 05:21:01 2021
Errors in file /u01/app/crs_base/diag/asm/+asm/+ASM2/trace/+ASM2_gmon_7457.trc  (incident=255833):
ORA-00600: internal error code, arguments: [kfdpMetaBlk_pickle:01], [4294967295], [0], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/crs_base/diag/asm/+asm/+ASM2/incident/incdir_255833/+ASM2_gmon_7457_i255833.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 /u01/app/crs_base/diag/asm/+asm/+ASM2/trace/+ASM2_gmon_7457.trc:
ORA-00600: internal error code, arguments: [kfdpMetaBlk_pickle:01], [4294967295], [0], [], [], [], [], [], [], [], [], []
GMON (ospid: 7457): terminating the instance due to error 493
Sat Jul 17 05:21:03 2021
System state dump requested by (instance=2, osid=7457 (GMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/crs_base/diag/asm/+asm/+ASM2/trace/+ASM2_diag_7429.trc
Instance terminated by GMON, pid = 7457

对于ORA-600 [kfdpMetaBlk_pickle:01], [4294967295]错误,查询了mos没有任何有效信息

对应的trace文件发现如下信息

2021-07-17 03:51:16.277603*:800002A2:KGF:kgfdputl.c@1411:kgfdpMetaSet_getMaxClique():   inc=2 ver=4294967295
2021-07-17 03:51:16.277619 :800002A3:KFDP:kfdp.c@9314:kfdpMetaSet_filterOld(): filtered old meta on disk 2
2021-07-17 03:51:16.277620 :800002A4:KFDP:kfdp.c@9314:kfdpMetaSet_filterOld(): filtered old meta on disk 2
2021-07-17 03:51:16.277992 :800002A5:KFDP:kfdp.c@9417:kfdpMetaSet_readDta():kfdpMetaSet_readDta unpickle upto 6 metablks
2021-07-17 03:51:16.277993 :800002A6:KFDP:kfdp.c@9425:kfdpMetaSet_readDta():kfdpMetaSet_readDta unpickle metablk for disk 3
2021-07-17 03:51:16.278154 :800002A7:KFDP:kfdp.c@9425:kfdpMetaSet_readDta():kfdpMetaSet_readDta unpickle metablk for disk 1
2021-07-17 03:51:16.278268 :800002A8:KFDP:kfdp.c@5851:kfdp_read(): kfdp_read end ok=1
2021-07-17 03:51:16.278277 :800002A9:KFDP:kfdp.c@7073:kfdp_doQuery(): kfdp_doQuery   rewrite_kfdp=1
2021-07-17 03:51:16.278282 :800002AA:KFDP:kfdp.c@12511:kfdpLckValue_pickle(): kfdpLckValue_pickle size=0 
                            endian=0xff ndisks=0 lckvalid=0
2021-07-17 03:51:16.278293 :800002AB:db_trace:kfdp.c@12803:kfdpLck_convPriv(): [10499:19:396] 
                            kfdpLck_conv: grp=1, type=0, mode=5, line=7155
2021-07-17 03:51:16.278294 :800002AC:KFDP:kfdp.c@12663:kfdpLckValue_unpickle(): kfdpLckValue_unpickle
                            size=28 res=0 ok=0 ver=-1 dcnt=0 lckvalid=0 flags=0x2 inst=0 (I am 2) version=0
2021-07-17 03:51:16.278499*:800002AD:KGF:kgfdputl.c@485:kgfdpDta_getAllDsks(): kgfdpDta_getAllDsks using 
                            saved iterator 0x9ffffffffd571220 with 4 disks
2021-07-17 03:51:16.278688 :800002AE:KFDP:kfdp.c@5566:kfdp_write(): kfdp_write: pstDskCnt=3 grow=0 degenerate=0
2021-07-17 03:51:16.278688*:800002AF:KGF:kgfdputl.c@2619:kgfdpTraceSet(): writing pst to disks (n=3): 0 1 3

通过删除信息,基本上可以确认由于pst信息异常(pst中记录的只有0 1 3三个磁盘，认为2是老磁盘),但是实际磁盘为4个,导致gmon进程异常.通过底层解决该问题,数据库恢复成功

SQL> recover database using backup controlfile;
ORA-00279: change 30075814973 generated at 07/17/2021 01:12:08 needed for
thread 2
ORA-00289: suggestion : +FRA
ORA-00280: change 30075814973 for thread 2 is in sequence #120561


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/tmp/asm/group_16
ORA-00279: change 30075814973 generated at 07/17/2021 01:11:54 needed for
thread 1
ORA-00289: suggestion :
+FRA/xff/archivelog/2021_07_17/thread_1_seq_79949.1543.1078103529
ORA-00280: change 30075814973 for thread 1 is in sequence #79949


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/tmp/asm/group_13
ORA-00279: change 30075815013 generated at 07/17/2021 01:12:09 needed for
thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 30075815013 for thread 1 is in sequence #79950
ORA-00278: log file '/tmp/asm/group_13' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/tmp/asm/group_11
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

运气不错,对于该故障的恢复,实现数据0丢失.

磁盘空间不足迁移数据文件导致故障恢复

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

标题：磁盘空间不足迁移数据文件导致故障恢复

有客户由于磁盘空间不足，在线把oracle数据迁移到其他位置

Tue Jun 01 11:44:32 2021
Thread 1 advanced to log sequence 28754 (LGWR switch)
  Current log# 2 seq# 28754 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Tue Jun 01 11:59:54 2021
Non critical error ORA-48113 caught while writing to trace file
      "/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mmon_23341.trc"
Error message: 
Writing to the above trace file is disabled for now on...
Tue Jun 01 12:00:00 2021
Non critical error ORA-48181 caught while writing to trace file
       "/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_29692.trc"
Error message: Linux-x86_64 Error: 28: No space left on device
Additional information: 1
Writing to the above trace file is disabled for now on...
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_29692.trc:
ORA-12012: error on auto execute of job "XIFENFEI"."STATISTICS_1_JOBS"
ORA-06575: Package or function PKG_STAT_1_2018 is in an invalid state
Tue Jun 01 12:12:26 2021

迁移走数据文件之后,数据库报错，并且强制关闭数据库

ORA-01116: error in opening database file 30
ORA-01110: data file 30: '/u02/orcdate/AAAA.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m001_29106.trc:
ORA-01116: error in opening database file 31
ORA-01110: data file 31: '/u02/orcdate/CBD.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Mon Jun 07 10:25:03 2021
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_9817.trc:
ORA-01116: error in opening database file 24
ORA-01110: data file 24: '/u02/orcdate/ABC.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Mon Jun 07 10:25:10 2021
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Read of datafile '/u02/orcdate/XXXXXXX.dbf' (fno 21) header failed with ORA-01208
Rereading datafile 21 header failed with ORA-01208
Mon Jun 07 10:25:36 2021
Adjusting the default value of parameter parallel_max_servers
from 640 to 485 due to the value of parameter processes (500)
Starting ORACLE instance (normal)
Mon Jun 07 10:28:20 2021
Shutting down instance (abort)
License high water mark = 152
USER (ospid: 7987): terminating the instance
Termination issued to instance processes. Waiting for the processes to exit
Mon Jun 07 10:28:30 2021
Instance termination failed to kill one or more processes
Instance terminated by USER, pid = 7987
Mon Jun 07 10:28:31 2021
Instance shutdown complete

然后又把文件迁移回来,并且进行了一系列数据库恢复,最后我们接手是情况是有多个文件被offline,并且有一个文件报WRONG FILE NUMBER,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本检查,对其中的v$datafile,v$datafile_header,v$tablespace综合分析

确认是WXD_YPT表空间数据文件直接拷贝为WXD表空间数据文件,经过客户确认,WXD数据不重要,客户先忽略.
通过一系列处理，尝试open数据库,报ORA-600 2662错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [3786], [2612118101], [3786], [2612128448], [12583040]
ORA-00600: internal error code, arguments: [2662], [3786], [2612118100], [3786], [2612128448], [12583040]
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [3786], [2612118098], [3786], [2612128448], [12583040]
Process ID: 14888
Session ID: 198 Serial number: 3

修改数据库scn(参考blog相关link:ORA-600 2662)数据库顺利open,并且协助客户导出数据并导入新库,完成数据库恢复.
这次运气比较好,只是丢失了一点数据,没有引起重大事故.再此提醒：不太了解oracle的朋友,操作数据库需谨慎,不要在线直接移动数据文件,另外为了更好的恢复效果,更快的恢复,故障之后,最好尽可能的告知所有操作.

ORA-00704 ORA-00943故障恢复

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

标题：ORA-00704 ORA-00943故障恢复

有朋友找到我们,他们数据库启动报ORA-01092 ORA-00704 ORA-00702错

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_4868.trc:
ORA-00704: 引导程序进程失败
ORA-00702: 引导程序版本 '' 与版本 '8.0.0.0.0' 不一致
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_4868.trc:
ORA-00704: 引导程序进程失败
ORA-00702: 引导程序版本 '' 与版本 '8.0.0.0.0' 不一致
Error 704 happened during db open, shutting down database
USER (ospid: 4868): terminating the instance due to error 704
Thu May 06 09:20:28 2021
Instance terminated by USER, pid = 4868
ORA-1092 signalled during: ALTER DATABASE OPEN...

以前有过类似恢复经历:
2019恢复第一单—ORA-704-ORA-702恢复
 恶意删除bootstrap$导致数据库无法正常启动
 ORA-00702: bootstrap verison ” inconsistent with version ’8.0.0.0.0′
这个问题本身相对比较简单,客户根据网上介绍,自行尝试恢复,经过客户一系列的处理之后,数据库启动报ORA-00704 ORA-00943错

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_6600.trc:
ORA-00704: 引导程序进程失败
ORA-00943: 簇不存在
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_6600.trc:
ORA-00704: 引导程序进程失败
ORA-00943: 簇不存在
Error 704 happened during db open, shutting down database
USER (ospid: 6600): terminating the instance due to error 704
Thu May 06 22:17:18 2021
Instance terminated by USER, pid = 6600
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (6600) as a result of ORA-1092

因为根据最初的错误知道是bootstrap$异常,现在报该错误,应该是bootstrap$没有处理正确导致,我们通过对bootstrap$重新处理,数据库直接open成功

raid强制上线后数据库无法启动故障处理

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

标题：raid强制上线后数据库无法启动故障处理

由于raid掉盘过多,强制raid上线,然后启动数据库报以下错误

Mon Apr 19 23:19:28 2021
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 106750 KB redo, 9080 data blocks need recovery
Mon Apr 19 23:19:45 2021
Slave exiting with ORA-1115 exception
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_3277.trc:
ORA-01115: IO error reading block from file 9 (block # 339)
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/dev02.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I
Mon Apr 19 23:19:45 2021
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 106750 KB redo, 9080 data blocks need recovery
Aborting crash recovery due to error 1115
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3275.trc:
ORA-01115: IO error reading block from file 9 (block # 329)
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/dev02.dbf'
ORA-1115 signalled during: ALTER DATABASE OPEN...

错误提示比较明显IO error,结合客户强行上线raid的操作,比较明显是由于底层io问题导致该错误,直接对此文件dbv检查

[oracle@database orcl]$ dbv file=dev02.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Apr 19 23:59:03 2021

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/dev02.dbf

DBV-00600: Fatal Error - [28] [27061] [0] [0]

对于此类情况,通过工具进行处理

DUL> copy file from  /u01/app/oracle/oradata/orcl/dev02.dbf to /oradata/dev02.dbf
 
starting copy datafile '/u01/app/oracle/oradata/orcl/dev02.dbf' to '/oradata/dev02.dbf'
read data error from file '/u01/app/oracle/oradata/orcl/dev02.dbf'.error message:Input/output error
read block# error: 303
read data error from file '/u01/app/oracle/oradata/orcl/dev02.dbf'.error message:Input/output error
read block# error: 304
read data error from file '/u01/app/oracle/oradata/orcl/dev02.dbf'.error message:Input/output error
read block# error: 329
datafile copy completed with 2 block error.

dbv校验文件

[oracle@database oradata]$ dbv file=dev02.dbf 

DBVERIFY: Release 11.2.0.4.0 - Production on Tue Apr 20 00:28:31 2021

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

DBVERIFY - Verification starting : FILE = /oradata/dev02.dbf
Page 303 is marked corrupt
Corrupt block relative dba: 0x0240012f (file 9, block 303)
Completely zero block found during dbv: 

Page 304 is marked corrupt
Corrupt block relative dba: 0x02400130 (file 9, block 304)
Completely zero block found during dbv: 

Page 329 is marked corrupt
Corrupt block relative dba: 0x02400149 (file 9, block 329)
Completely zero block found during dbv: 



DBVERIFY - Verification complete

Total Pages Examined         : 3932160
Total Pages Processed (Data) : 3213723
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 714294
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 4139
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 3
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 85078875 (6.85078875)

通过对io error的文件进行处理,最终损坏三个block,最大限度抢救数据.使用被恢复出来的文件,尝试open库遭遇以下错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [6], [85035771], [6],
[85084136], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [6], [85035770], [6],
[85084136], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [6], [85035764], [6],
[85084136], [12583040], [], [], [], [], [], []
Process ID: 6733
Session ID: 570 Serial number: 3

ora-600 2662这个错误比较明显,处理文件头scn,继续open库

SQL> alter database open ;
alter database open 
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6840
Session ID: 570 Serial number: 3

查看alert日志信息

Tue Apr 20 01:22:27 2021
alter database open upgrade
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 1 KB redo, 3 data blocks need recovery
Started redo application at
 Thread 1: logseq 1, block 3
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 1, block 5, scn 25854859541
 3 data blocks read, 3 data blocks written, 1 redo k-bytes read
Tue Apr 20 01:22:28 2021
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Apr 20 01:22:28 2021
SMON: enabling cache recovery
[6840] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:5902014 end:5905574 diff:3560 (35 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc  (incident=63970):
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_63970/orcl_smon_6824_i63970.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORACLE Instance orcl (pid = 14) - Error 600 encountered while recovering transaction (24, 2) on object 89023.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc:
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Tue Apr 20 01:22:38 2021
ORACLE Instance orcl (pid = 14) - Error 600 encountered while recovering transaction (63, 3) on object 89023.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc:
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc  (incident=63974):
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_63974/orcl_smon_6824_i63974.trc
Tue Apr 20 01:22:55 2021
PMON (ospid: 6798): terminating the instance due to error 474

这个错误是比较常见的错误,参考:ORACLE Instance XFF (pid = 18) – Error 600 encountered while recovering transaction ，通过处理之后,数据库open成功

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

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size            1023413408 bytes
Database Buffers          570425344 bytes
Redo Buffers                7319552 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

后续安排逻辑导出,导入新库

oracle dul 12.2正式版发布

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

标题：oracle dul 12.2正式版发布

oracle官方dul 正式发布 12.2版本(在上次的测试中dul 12.2完美支持Oracle 19c恢复还是beta版本)

[root@iZbp1hx0enix3hix1kvyrxZ tmp]# ./dul      

Data UnLoader: 12.2.0.0.1 - Internal Only - on Sun Mar 21 13:55:39 2021
with 64-bit io functions and the decompression option

Copyright (c) 1994 2021 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only
DUL> show parameters;
_SLPE_DEBUG               = FALSE
ALLOW_CHECKSUM_MISMATCH   = FALSE
ALLOW_DBA_MISMATCH        = FALSE
ALLOW_OTHER_OBJNO         = FALSE
ALLOW_TRAILER_MISMATCH    = FALSE
ALLOW_ZERO_IN_DATE_COLUMNS = FALSE
ASM_DO_HARD_CHECKS        = TRUE
AUTO_UPDATE_CHECKSUM      = TRUE
AUTO_UPDATE_TRAILER       = TRUE
BUFFER                    = 104857600
CF_FILES                  = 1022
CF_TABLESPACES            = 64
COMPATIBLE                = 11
CONTROL_FILE              = control.txt
DB_BLOCK_SIZE             = 8192
DB_NAME                   = 
DB_ID                     = 0
DC_COLUMNS                = 2000000
DC_LOB_ENTRIES            = 327680
DC_EXTENTS                = 10000
DC_OBJECTS                = 1000000
DC_SEGMENTS               = 100000
DC_TABLES                 = 10000
DC_USERS                  = 400
DEFAULT_CHARACTER_SET     = 
DEFAULT_NATIONAL_CHARACTER_SET = 
EXPORT_MODE               = true
FEEDBACK                  = 10000
FILE                      = 
FILE_SIZE_IN_MB           = 0
LDR_ENCLOSE_CHAR          = |
LDR_OUTPUT_IN_UTF8        = FALSE
LDR_PHYS_REC_SIZE         = 0
LOGFILE                   = dul.log
MAX_OPEN_FILES            = 8
MAX_SCAN_ROWS             = 0
MAX_SAMPLE_ROWS           = 5
OSD_MAX_THREADS           = 1055
OSD_BIG_ENDIAN_FLAG       = false
OSD_DBA_FILE_BITS         = 10
OSD_FILE_LEADER_SIZE      = 0
OSD_C_STRUCT_ALIGNMENT    = 32
OSD_WORD_SIZE             = 32
PARSE_HEX_ESCAPES         = FALSE
RESET_LOGFILE             = FALSE
SCAN_DATABASE_SCANS_LOB_SEGMENTS = TRUE
SCAN_STEP_SIZE            = 512
TRACE_FLAGS               = 0
UNEXP_MAX_ERRORS          = 1000
UNEXP_VERBOSE             = FALSE
USE_LOB_FILES             = FALSE
USE_SCANNED_EXTENT_MAP    = FALSE
VERIFY_NUMBER_PRECISION   = TRUE
WARN_RECREATE_FILES       = TRUE
WRITABLE_DATAFILES        = FALSE
DUL> exit

Life is DUL without it
[root@iZbp1hx0enix3hix1kvyrxZ tmp]#

分类目录归档：Oracle备份恢复