[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/

[20190718]12c壞塊處理一例.txt

相關文章