[20190718]12c坏块处理一例.txt

来源：这里教程网时间：2026-03-03 13:59:35 作者：

[20190718]12c坏块处理一例.txt --//今天做一些测试时遇到system表空间出现一个坏块。自己分析处理看看。 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 RMAN> backup database format '/tmp/backup/full20190718_%U'; Starting backup at 2019-07-18 21:17:32 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=16 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF input datafile file number=00005 name=D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF input datafile file number=00007 name=D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF channel ORA_DISK_1: starting piece 1 at 2019-07-18 21:17:34 RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/18/2019 21:17:41 ORA-19566: exceeded limit of 0 corrupt blocks for file D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ continuing other job steps, job failed will not be re-run channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00011 name=D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF input datafile file number=00010 name=D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF input datafile file number=00008 name=D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF input datafile file number=00009 name=D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF input datafile file number=00036 name=D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF channel ORA_DISK_1: starting piece 1 at 2019-07-18 21:17:42 channel ORA_DISK_1: finished piece 1 at 2019-07-18 21:18:17 piece handle=D:\TMP\BACKUP\FULL20190718_1OU707FM_1_1 tag=TAG20190718T211733 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF input datafile file number=00006 name=D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF input datafile file number=00004 name=D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF channel ORA_DISK_1: starting piece 1 at 2019-07-18 21:18:17 channel ORA_DISK_1: finished piece 1 at 2019-07-18 21:18:52 piece handle=D:\TMP\BACKUP\FULL20190718_1PU707GP_1_1 tag=TAG20190718T211733 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/18/2019 21:17:41 ORA-19566: exceeded limit of 0 corrupt blocks for file D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF --//cdb数据库数据文件D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF备份出现问题。 2.dbv分析看看： d:\>dbv file=D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF DBVERIFY: Release 12.2.0.1.0 - Production on Thu Jul 18 21:19:55 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF Page 186 is marked corrupt Corrupt block relative dba: 0x004000ba (file 1, block 186) Bad check value found during dbv: Data in bad block: type: 0 format: 2 rdba: 0x000000ba last change scn: 0x0000.0000.00000000 seq: 0x1 flg: 0x05 spare3: 0x0 consistency value in tail: 0x00000001 check value in block header: 0xa7ba computed block checksum: 0x5c2d --//视乎检查和不一致。 DBVERIFY - Verification complete Total Pages Examined : 115200 Total Pages Processed (Data) : 78206 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 14791 Total Pages Failing (Index): 0 Total Pages Processed (Other): 4576 Total Pages Processed (Seg) : 1 Total Pages Failing (Seg) : 0 Total Pages Empty : 17626 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 9263501 (0.9263501) SYS@test> @ which_obj 1 186 OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ----- ------------ -------------- ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------ SYS I_TS# INDEX SYSTEM 0 1 184 65536 8 1 --//TS#的索引i_ts$损坏，什么会这样? BBED> set dba 1,187 DBA 0x004000bb (4194491 1,187) --//注：我使用windows下的bbed，数据块存在+1的偏移。 BBED> dump /v count 12 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1) Block: 187 Offsets: 0 to 11 Dba:0x004000bb ----------------------------------------------------------------------------------------------------------- 00a20000 ba000000 00000000 l .?.?...... <32 bytes per line> --//还是1个空块，前面是00a2.注如果有数据的块前面应该是06a2. BBED> dump /v dba 1,186 count 12 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1) Block: 186 Offsets: 0 to 11 Dba:0x004000ba ----------------------------------------------------------------------------------------------------------- 06a20000 b9004000 ae6f0000 l .?.?@.畂.. <32 bytes per line> ---- BBED> set dba 1,187 DBA 0x004000bb (4194491 1,187) BBED> sum apply Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y Check value for File 1, Block 187: current = 0xfb97, required = 0xfb97 BBED> verify DBVERIFY - Verification starting FILE = D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF BLOCK = 186 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 1 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 3.再次验证: RMAN> validate datafile 1; Starting validate at 2019-07-18 21:38:22 using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF channel ORA_DISK_1: validation complete, elapsed time: 00:00:15 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 1 OK 0 17627 115200 9270549 File Name: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 78206 Index 0 14791 Other 0 4576 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation including current control file for validation including current SPFILE in backup set channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 List of Control File and SPFILE =============================== File Type Status Blocks Failing Blocks Examined ------------ ------ -------------- --------------- SPFILE OK 0 2 Control File OK 0 1148 Finished validate at 2019-07-18 21:38:39 --//OK,应该修复完成。 2.我估计我可能某个时间执行错误，修改了dba=1,186的数据块。 BBED> info File# Name Size(blks) ----- ---- ---------- 1 D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF 0 2 D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF 0 3 D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF 0 4 D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF 0 5 D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF 0 6 D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF 0 7 D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF 0 8 D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF 0 9 D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF 0 10 D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF 0 11 D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF 0 28 D:\APP\ORACLE\ORADATA\TEST\TEST02P\SYSTEM01.DBF 0 29 D:\APP\ORACLE\ORADATA\TEST\TEST02P\SYSAUX01.DBF 0 30 D:\APP\ORACLE\ORADATA\TEST\TEST02P\UNDOTBS01.DBF 0 31 D:\APP\ORACLE\ORADATA\TEST\TEST02P\USERS01.DBF 0 --//猜测最大的可能就是本来想修改dba=11,186块不小心修改了dba=1,186. SCOTT@test01p> @ which_obj 11 186 OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ----- ------------ -------------- ------------ ------------------------- ---------- ---------- ---------- ---------- ------------ SCOTT T1 TABLE USERS 1 11 184 65536 8 11 --//虽然是测试环境,做这些修改操作一定要小心。突然想起bbed的修改有log.bbd记录看看里面的内容看看： --//看到如下记录： BBED: Release 2.0.0.0.0 - Limited Production on Mon Dec 10 20:27:51 2018 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** .... --//时间太久了，回忆不起来当时的一些细节了。 set dba 11,186 set dba 1,187 ~~~~~~~~~~~~~~~~ map /v p offset 36 dump /v offset 36 count 4 dump /v offset 264 m /x 12 offset 36 #modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 36. m /x 12 offset 264 #modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 264. m /x 11 offset 48 #modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 48. m /x 58 offset 52 #modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 52. m /x 80 offset 56 #modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 56. m /x 11 offset 92 #modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 92. m /x 58 offset 96 #modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 96. m /x 80 offset 100 #modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 100. m /x 5802 offset 60 #modify /x 0000 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 60. m /x c002 offset 62 #modify /x 0000 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 62. m /x 5802 offset 104 #modify /x 0000 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 104. m /x c200 offset 106 #modify /x 0000 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 106. m /x 2f5e offset 272 #modify /x 0000 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 272. set dba 11,187 --//可以发现在下划线处我输入错误，导致后续的操作错误的数据块。 BBED> set dba 1,187 DBA 0x004000bb (4194491 1,187) --//选择offset＝36，272，106，验证看看： BBED> dump offset 36 count 2 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1) Block: 187 Offsets: 36 to 37 Dba:0x004000bb --------------------------------------------------------------------- 1200 <64 bytes per line> BBED> dump offset 272 count 2 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1) Block: 187 Offsets: 272 to 273 Dba:0x004000bb --------------------------------------------------------------------- 2f5e <64 bytes per line> BBED> dump offset 106 count 2 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1) Block: 187 Offsets: 106 to 107 Dba:0x004000bb --------------------------------------------------------------------- c200 <64 bytes per line> --//可以发现当时确实操作错误!!看来做这些修复工作一定要小心小心再小心!! --//我看了笔记最大可能自己在家里做truncate恢复时操作。 --//上班后测试链接如下，可以发现偏移基本可以对上。 [20181212]truncate的另类恢复5.txt->http://blog.itpub.net/267265/viewspace-2284984/