标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,655)
- DB2 (22)
- MySQL (71)
- Oracle (1,519)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (553)
- Oracle安装升级 (90)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (76)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
- 如何修改集群的公网信息(包括 VIP) (Doc ID 1674442.1)
- 如何在 oracle 集群环境下修改私网信息 (Doc ID 2103317.1)
- ORA-600 [kcvfdb_pdb_set_clean_scn: cleanckpt] 相关bug
- ORA-600 krhpfh_03-1210故障处理
- 19c库启动报ORA-600 kcbzib_kcrsds_1
- DBMS_SESSION.set_context提示ORA-01031问题解决
- redo写丢失导致ORA-600 kcrf_resilver_log_1故障
- 硬件故障导致ORA-01242 ORA-01122等错误
- 200T 数据库非归档无备份恢复
- 利用flashback快速恢复failover 的备库
- [comingback2022@cock.li].eking和[tsai.shen@mailfence.com].faust扩展名勒索病毒数据库可以完美恢复
- opatch auto 出现unable to get oracle owner for 错误
- Oracle 23ai 表和视图的列最多支持到4096个
- 断电引起redo和数据文件不一致故障恢复
- ORA-03113: 通信通道的文件结尾
标签归档:系统回滚段坏块
记录一次系统回滚段坏块恢复
在上一篇中深入分析一次ORA-00314错误的数据库继续恢复,出现file 1 block 403和404等坏块,使得后面的恢复进一步写入了复杂境地.更加麻烦的是,这个库里面有一张核心表有40个字段,包括long,nvarchar2等一些字段,但是使用aul,dul,odu挖取都出现异常(表的行数正确,但是有些列的数据不能正常被挖出来),原因是该表中有特殊字段数据,被逼无赖只能继续拉库
数据库启动报错
SQL> startup mount pfile='c:\pfile.txt' ORACLE 例程已经启动。 Total System Global Area 452984832 bytes Fixed Size 1291120 bytes Variable Size 201329808 bytes Database Buffers 243269632 bytes Redo Buffers 7094272 bytes 数据库装载完毕。 SQL> recover database; 完成介质恢复。 SQL> alter database open; alter database open upgrade * 第 1 行出现错误: ORA-01092: ORACLE 实例终止。强制断开连接
查看alert日志
SMON: enabling cache recovery Fri May 16 22:49:53 2014 Hex dump of (file 1, block 404) in trace file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2788.trc Corrupt block relative dba: 0x00400194 (file 1, block 404) Fractured block found during buffer read Data in bad block: type: 0 format: 0 rdba: 0x00000000 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000000 check value in block header: 0x0 block checksum disabled Reread of rdba: 0x00400194 (file 1, block 404) found same corrupted data Fri May 16 22:49:55 2014 Errors in file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2788.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01578: ORACLE data block corrupted (file # 1, block # 404) ORA-01110: data file 1: 'C:\ORADATA\INTERLIB\SYSTEM01.DBF' Error 604 happened during db open, shutting down database USER: terminating instance due to error 604
跟踪数据库启动过程
这里可以清晰的看到是因为block 404有坏块,数据库递归sql访问到该坏块报错,从而数据库无法继续open,对数据库启动过程做10046跟踪
PARSING IN CURSOR #2 len=148 dep=1 uid=0 oct=6 lid=0 tim=80533759 hv=3540833987 ad='2a6ce1ec' update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1 END OF STMT PARSE #2:c=62500,e=211766,p=14,cr=85,cu=0,mis=1,r=0,dep=1,og=4,tim=80533755 BINDS #2: kkscoacd Bind#0 oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00 oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0 kxsbbbfp=2a6ceac2 bln=32 avl=09 flg=09 value="_SYSSMU1$" Bind#1 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9b7c bln=24 avl=02 flg=05 value=2 Bind#2 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9b58 bln=24 avl=02 flg=05 value=9 Bind#3 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9b34 bln=24 avl=02 flg=05 value=2 Bind#4 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9b10 bln=24 avl=02 flg=05 value=1 Bind#5 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9aec bln=24 avl=04 flg=05 value=13332 Bind#6 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9ac8 bln=24 avl=04 flg=05 value=24672 Bind#7 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9aa4 bln=24 avl=05 flg=05 value=47727002 Bind#8 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9a80 bln=24 avl=01 flg=05 value=0 Bind#9 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9a5c bln=24 avl=01 flg=05 value=0 Bind#10 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9a38 bln=24 avl=02 flg=05 value=1 Bind#11 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9a14 bln=24 avl=02 flg=05 value=1 Bind#12 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9ba0 bln=22 avl=02 flg=05 value=1 WAIT #2: nam='db file sequential read' ela= 10794 file#=1 block#=404 blocks=1 obj#=-1 tim=80546515 Hex dump of (file 1, block 404) Dump of memory from 0x1FB9C000 to 0x1FB9E000 1FB9C000 00000000 00000000 00000000 00000000 [................] Repeat 286 times 1FB9D1F0 00000000 00000000 00000000 00000001 [................] 1FB9D200 0000A200 0001D831 00000000 05010000 [....1...........] 1FB9D210 00007F30 00000000 00000000 00000000 [0...............] 1FB9D220 00000000 00000000 00000000 00000000 [................] Repeat 221 times Corrupt block relative dba: 0x00400194 (file 1, block 404) Fractured block found during buffer read Data in bad block: type: 0 format: 0 rdba: 0x00000000 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000000 check value in block header: 0x0 block checksum disabled Reread of rdba: 0x00400194 (file 1, block 404) found same corrupted data EXEC #2:c=0,e=45015,p=1,cr=1,cu=3,mis=1,r=0,dep=1,og=4,tim=80578919 ERROR #2:err=1578 tim=811723 ORA-00604: error occurred at recursive SQL level 1 ORA-01578: ORACLE data block corrupted (file # 1, block # 404) ORA-01110: data file 1: 'C:\ORADATA\INTERLIB\SYSTEM01.DBF' EXEC #1:c=328125,e=3468857,p=52,cr=619,cu=10,mis=0,r=0,dep=0,og=1,tim=82331842 ERROR #1:err=1092 tim=811898
dbv检测坏块
C:\oracle\product\10.2.0\db_1\BIN>dbv FILE = C:\ORADATA\INTERLIB\SYSTEM01.DBF DBVERIFY: Release 10.2.0.3.0 - Production on 星期一 5月 19 15:22:41 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. DBVERIFY - 开始验证: FILE = C:\ORADATA\INTERLIB\SYSTEM01.DBF 页 403 标记为损坏 Corrupt block relative dba: 0x00400193 (file 1, block 403) Bad header found during dbv: Data in bad block: type: 70 format: 1 rdba: 0x00030030 last change scn: 0x0000.3a6c2e79 seq: 0x0 flg: 0x00 spare1: 0x4c spare2: 0x45 spare3: 0x2 consistency value in tail: 0x00070000 check value in block header: 0x1 block checksum disabled 页 404 流入 - 很可能是介质损坏 Corrupt block relative dba: 0x00400194 (file 1, block 404) Fractured block found during dbv: Data in bad block: type: 0 format: 0 rdba: 0x00000000 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000000 check value in block header: 0x0 block checksum disabled 页 498 流入 - 很可能是介质损坏 Corrupt block relative dba: 0x004001f2 (file 1, block 498) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x004001f2 last change scn: 0x0000.02d7eb9d seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x37290601 check value in block header: 0x6c5e computed block checksum: 0xc2b5 页 61078 流入 - 很可能是介质损坏 Corrupt block relative dba: 0x0040ee96 (file 1, block 61078) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0040ee96 last change scn: 0x0000.02d5cf11 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xa6e30601 check value in block header: 0x51d4 computed block checksum: 0xf572 页 61147 流入 - 很可能是介质损坏 Corrupt block relative dba: 0x0040eedb (file 1, block 61147) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0040eedb last change scn: 0x0000.02d7f7e6 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x6ed80601 check value in block header: 0x4ae8 computed block checksum: 0x893f 页 61502 流入 - 很可能是介质损坏 Corrupt block relative dba: 0x0040f03e (file 1, block 61502) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0040f03e last change scn: 0x0000.02d810dd seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xaf4c0601 check value in block header: 0xd99b computed block checksum: 0xbf91 页 61989 流入 - 很可能是介质损坏 Corrupt block relative dba: 0x0040f225 (file 1, block 61989) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0040f225 last change scn: 0x0000.02d80f65 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xdff70601 check value in block header: 0x4e2a computed block checksum: 0xd092 DBVERIFY - 验证完成 检查的页总数: 62720 处理的页总数 (数据): 37740 失败的页总数 (数据): 0 处理的页总数 (索引): 7021 失败的页总数 (索引): 0 处理的页总数 (其它): 1784 处理的总页数 (段) : 0 失败的总页数 (段) : 0 空的页总数: 16169 标记为损坏的总页数: 7 流入的页总数: 5 最高块 SCN : 316431787 (0.316431787) C:\oracle\product\10.2.0\db_1\BIN>
根据我们的经验,对数据库启动影响很大的,主要是403,404,498三个坏块,在10201的库中查询得出结论(403,404是rollback,498是seq$).那现在我们可以理解在数据库启动过程中需要undo$表中相关列信息,但是由于rollback对应的block有坏块,使得数据库无法操作update操作,从而无法正常启动.
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME 2 FROM DBA_EXTENTS A 3 WHERE FILE_ID = &FILE_ID 4 AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1; 输入 file_id 的值: 1 原值 3: WHERE FILE_ID = &FILE_ID 新值 3: WHERE FILE_ID = 1 输入 block_id 的值: 404 原值 4: AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1 新值 4: AND 404 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1 OWNER ------------------------------ SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE TABLESPACE_NAME PARTITION_NAME ------------------ ------------------------------ ------------------------------ SYS SYSTEM ROLLBACK SYSTEM
常用方法
设置event跳过坏块,使用隐含参数屏蔽回滚段
_corrupted_rollback_segments= _SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$, _SYSSMU11$, _SYSSMU12$, _SYSSMU13$, _SYSSMU14$, _SYSSMU15$, _SYSSMU16$, _SYSSMU17$, _SYSSMU18$, _SYSSMU19$, _SYSSMU20$ undo_management = MANUAL event = 10231 trace name context forever, level 10, 10232 trace name context forever, level 10, 10233 trace name context forever, level 10 SQL> startup mount pfile='c:\pfile.txt' ORACLE 例程已经启动。 Total System Global Area 452984832 bytes Fixed Size 1291120 bytes Variable Size 201329808 bytes Database Buffers 243269632 bytes Redo Buffers 7094272 bytes 数据库装载完毕。 SQL> recover database; 完成介质恢复。 SQL> alter database open; alter database open upgrade * 第 1 行出现错误: ORA-01092: ORACLE 实例终止。强制断开连接
这里可以看出来,数据库在update undo$的时候因为rollback异常无法通过屏蔽回滚段和跳过坏块的方法来解决。因为是system undo系统块,想法就是从别的相同版本库中拷贝一个相同位置块过来试试看。
拷贝数据块
C:\oracle\product\10.2.0\db_1\BIN>bbed listfile=c:\bbed.txt Password: BBED: Release 2.0.0.0.0 - Limited Production on Mon May 19 10:47:14 2014 (c) Copyright 2000 Oracle Corporation. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set blocksize 8192 BLOCKSIZE 8192 BBED> set mode edit MODE Edit BBED> copy file 2 block 405 to file 1 block 405 File: C:\ORADATA\INTERLIB\SYSTEM01.DBF (1) Block: 405 Offsets: 0 to 511 Dba:0x00400195 ------------------------------------------------------------------------ 02a20000 94014000 d4c50800 00000104 60890000 00000500 36000000 66004545 0000e81f 401fc81e 201ea01d 481da01c f81b501b e81a801a d8197019 08196018 f8179017 e8168016 1816c815 68152815 d8145814 f8137813 18139812 3812f811 a8116811 18119810 3810b80f 580fd80e 780e380e e80da80d 580d180d c80c480c f00b480b e00a380a d0092809 c0087008 1008d007 80074007 f006b006 60062006 d0052805 a8046004 2004d003 d802b401 6001e000 00000000 0a001800 0c001400 1300ff1d 78100000 78100000 02000000 00000000 0a165f45 00002e00 0201e71e 94014000 4c004300 03020100 2b05c000 5e15c000 0c001800 0c002000 29000100 82230000 82230000 02000000 00000000 0a163347 00003200 02014500 94014000 58004600 03022100 f30ec000 f50ec000 00000000 00000000 03000000 0d000e00 0e000e00 06c52008 4b5a2705 c51e3337 5f06c520 084b5a27 06c51f4c 28393906 c520084b 5a2706c5 273e0355 0d000000 ff165f43 0a001800 0c001400 0d004000 82230000 82230000 02000000 00000000 0a163346 00003200 02014500 94014000 58004500 03020100 f30ec000 f80ec000 00000000 00000000 06c52008 4b5a2705 c4201c60 5f000000 0c001800 0c002000 2a000100 82230000 82230000 02000000 00000000 0a163345 00003200 02014500 94014000 58004400 03022100 f30ec000 <32 bytes per line> BBED> sum apply Check value for File 1, Block 405: current = 0x8960, required = 0x8960
重新启动数据库
SQL> startup mount pfile='c:\pfile.txt' ORACLE 例程已经启动。 Total System Global Area 452984832 bytes Fixed Size 1291120 bytes Variable Size 201329808 bytes Database Buffers 243269632 bytes Redo Buffers 7094272 bytes 数据库装载完毕。 SQL> recover database; 完成介质恢复。 SQL> alter database open; alter database open upgrade * 第 1 行出现错误: ORA-01092: ORACLE 实例终止。强制断开连接
分析alert日志
Mon May 19 10:54:05 2014 Recovery of Online Redo Log: Thread 1 Group 3 Seq 13 Reading mem 0 Mem# 0: C:\ORADATA\INTERLIB\REDO03.LOG Block recovery stopped at EOT rba 13.5.16 Block recovery completed at rba 13.5.16, scn 0.316366922 Doing block recovery for file 1 block 9 Block recovery from logseq 13, block 3 to scn 316366921 Mon May 19 10:54:05 2014 Recovery of Online Redo Log: Thread 1 Group 3 Seq 13 Reading mem 0 Mem# 0: C:\ORADATA\INTERLIB\REDO03.LOG Block recovery completed at rba 13.5.16, scn 0.316366922 Mon May 19 10:54:07 2014 Errors in file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_1208.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-00607: 当更改数据块时出现内部错误 ORA-00600: 内部错误代码, 参数: [4193], [102], [58], [], [], [], [], [] Error 604 happened during db open, shutting down database USER: terminating instance due to error 604
这里是因为我们拷贝了一个其他库的undo段过来,然后数据库启动的时候首先使用到该undo块和rollback segment header 不匹配,所以通过通过修改undo header 来修复,使用bbed修改段头信息,因为在以前的文章中描述过,在此不再重复,具体参考:使用bbed解决ORA-00607/ORA-00600[4194]故障启动数据库
SQL> startup mount pfile='c:\pfile.txt' ORACLE 例程已经启动。 Total System Global Area 452984832 bytes Fixed Size 1291120 bytes Variable Size 201329808 bytes Database Buffers 243269632 bytes Redo Buffers 7094272 bytes 数据库装载完毕。 SQL> recover database; 完成介质恢复。 SQL> alter database open upgrade; 数据库已更改。
但是因为seq$有坏块,数据库启动后,如果使用非系统认证登录数据库会报如下错误
C:\oracle\product\10.2.0\db_1\BIN>sqlplus interlib/oracle SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 5月 19 16:29:29 2014 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. ERROR: ORA-00600: 内部错误代码, 参数: [6807], [AUDSES$], [144], [], [], [], [], [] 请输入用户名: C:\oracle\product\10.2.0\db_1\BIN>sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 5月 19 16:30:08 2014 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select object_type from dba_objects where object_name='AUDSES$'; OBJECT_TYPE ------------------- SEQUENCE
因为seq$有坏块导致该问题,因为该数据库需要重建,使用exp导出来数据,然后重建完成相关工作