[20190718]12c壞塊處理一例.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle壞塊處理Oracle
- rootvg壞塊處理
- ORACLE 壞塊處理Oracle
- 處理塊損壞
- [20150811]模擬壞塊處理.txt
- DBA實踐---壞塊處理
- 資料庫壞塊處理資料庫
- Oracle壞塊處理相關Oracle
- Oracle壞塊問題處理Oracle
- oracle corrupt block壞塊處理OracleBloC
- 第7章 處理塊損壞
- BAD Block 壞塊的處理BloC
- ORACLE資料庫壞塊的處理 (處理無物件壞快的方法)Oracle資料庫物件
- [20121016]壞塊處理以及資料恢復.txt資料恢復
- ORACLE資料庫壞塊的處理 (一次壞快處理過程)Oracle資料庫
- 【BLOCK】Oracle壞塊處理命令參考BloCOracle
- oracle壞塊模擬處理（筆記）Oracle筆記
- Oracle 壞塊處理三板斧Oracle
- Oracle壞塊修復處理實驗Oracle
- 一次使用BBED處理壞塊
- [20190718]12c rman新特性 表恢復.txt
- 壞塊的處理思維（用程式製作壞塊不如用系統）
- bad block表上壞塊的處理BloC
- ORA-01578(資料塊損壞)跳過壞塊處理辦法
- 一次壞塊的處理過程(一)
- 一次壞塊的處理過程(二)
- 資料庫壞塊Corrupt block的處理方法資料庫BloC
- 對oracle中出現的壞塊的處理方法Oracle
- 一次壞塊的處理過程 [轉]
- ORACLE壞塊(ORA-01578)處理方法Oracle
- ReiserFS檔案系統壞塊的處理(轉)
- rman備份後發現壞塊的處理
- 發生壞塊後的處理及確認
- 一次ORACLE資料庫undo壞塊處理Oracle資料庫
- ORACLE壞塊(ORA-01578)處理方法（zt）Oracle
- 教你如何處理Oracle資料庫中的壞塊Oracle資料庫
- RMAN備份時候檔案壞塊的處理
- 處理 Oracle 塊損壞 (文件 ID 1526911.1)Oracle