[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/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2651196/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2651196/