[20171114]恢复数据文件块头2.txt
--//曾经写过一篇[20161111]数据库文件头的修复.txt,但是利用大小相似的数据文件头覆盖来恢复,那是属于特种恢复.
--//参考链接:http://blog.itpub.net/267265/viewspace-2128309/
--//不在正常操作范围,完全是不得已而为之.基本写那篇在一年之前,这次做一个带引号"常规恢复"看看.
--//后记:纯属无聊,千万不要把这当作常规的恢复.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.建立测试环境:
CREATE TABLESPACE TEA DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t1 tablespace tea as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
--//建立rman备份:
backup database filesperset=1 format '/home/oracle/backup/20171114_%U';
--//注:这是我个人主张的备份方式设置filesperset=1,可惜我们团队一直不建议使用.这样备份文件显得太多了.实际上这样做与我后面的恢复有关.
create table t2 tablespace tea as select rownum id ,lpad('B',32,'B') name from dual connect by level<=1e5;
--//建立大小5M的表。注:说明一点,我是先做数据库备份,再建立t2表.
--//破坏数据文件头,注:数据文件头在数据文件的第2块.第1块OS头.
update t2 set name=lpad('C',32,'C') where id <=5;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
rollback ;
update t2 set name=lpad('D',32,'D') where id between 5 and 9;
commit ;
alter database datafile 6 offline ;
$ dd if=/dev/zero of=/mnt/ramdisk/book/tea01.dbf count=1 bs=8192 conv=notrunc seek=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.8338e-05 seconds, 214 MB/s
--//再次提醒不要忘记conv=notrunc参数,不然数据文件会被截断.我自己在以前测试中遇到这种情况!!
2.检查破坏的数据文件:
RMAN> backup validate datafile 6;
Starting backup at 2017-11-14 15:15:07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=144 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=158 device type=DISK
RMAN-06169: could not read file header for datafile 6 error reason 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/14/2017 15:15:08
RMAN-06056: could not access datafile 6
$ dbv file=/mnt/ramdisk/book/tea01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Nov 14 15:15:46 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/tea01.dbf
Page 1 is marked corrupt
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Completely zero block found during dbv:
Page 2 is marked corrupt
Corrupt block relative dba: 0x00000002 (file 0, block 2)
Bad header found during dbv:
Data in bad block:
type: 29 format: 2 rdba: 0x01800002
last change scn: 0x0003.176e7555 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x75551d02
check value in block header: 0x68de
computed block checksum: 0x0
Page 3 is marked corrupt
Corrupt block relative dba: 0x00000003 (file 0, block 3)
Bad header found during dbv:
Data in bad block:
type: 30 format: 2 rdba: 0x01800003
last change scn: 0x0003.176e7555 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x75551e01
check value in block header: 0x59b6
computed block checksum: 0x0
.....
--//一旦文件头损坏,下面的检查感觉全部是坏块的.实际上检查都是不对的.
SCOTT@book> select * from v$database_block_corruption;
no rows selected
SCOTT@book> alter database datafile 6 online ;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
ORA-01210: data file header is media corrupt
3.修复:
RMAN> recover datafile 6 block 1;
Starting recover at 2017-11-14 15:19:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=158 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=171 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=184 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-11-14 15:19:52
--//实际上什么都没做.换一种执行方式:
RMAN> blockrecover datafile 6 block 1;
Starting recover at 2017-11-14 15:20:22
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-11-14 15:20:22
--//你使用dbv检查还是报一样的错误.也就是这样方式无法修复文件头.
--//如果执行以下步骤恢复,是一种常见的做法.但是如果如果文件很大restore需要很长时间,而recover需要时间与应用归档日志量有关.
restore datafile 6;
recover datafile 6;
--//我采用另外的方法恢复,通过特殊方式取出文件头,然后在执行recover看看是否可行.
4.恢复测试:
RMAN> list backup by file;
List of Datafile Backups
========================
File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag
---- ------- - -- - ---------- ------------------- ------- ------- ---------- ---
1 204 B F A 13278016597 2017-11-14 15:09:34 1 1 NO TAG20171114T150933
2 203 B F A 13278016596 2017-11-14 15:09:34 1 1 NO TAG20171114T150933
3 201 B F A 13278016595 2017-11-14 15:09:34 1 1 NO TAG20171114T150933
4 205 B F A 13278016602 2017-11-14 15:09:38 1 1 NO TAG20171114T150933
5 202 B F A 13278016599 2017-11-14 15:09:35 1 1 NO TAG20171114T150933
6 206 B F A 13278016603 2017-11-14 15:09:38 1 1 NO TAG20171114T150933
List of Control File Backups
============================
CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Compressed Tag
---------- ------------------- ------- - ------- ------- ---------- ---
13278016610 2017-11-14 15:09:39 207 A 1 1 NO TAG20171114T150939
List of SPFILE Backups
======================
Modification Time BS Key S #Pieces #Copies Compressed Tag
------------------- ------- - ------- ------- ---------- ---
2017-11-06 15:26:50 207 A 1 1 NO TAG20171114T150939
--//确定key=206
RMAN> list backupset 206;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
206 Full 5.69M DISK 00:00:00 2017-11-14 15:09:38
BP Key: 207 Status: AVAILABLE Compressed: NO Tag: TAG20171114T150933
Piece Name: /home/oracle/backup/20171114_fcsji7ti_1_1
List of Datafiles in backup set 206
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
6 Full 13278016603 2017-11-14 15:09:38 /mnt/ramdisk/book/tea01.dbf
--//确定备份片文件是/home/oracle/backup/20171114_fcsji7ti_1_1.
--//我的数据库名=BOOK.
$ strings -t d /home/oracle/backup/20171114_fcsji7ti_1_1 | grep BOOK
8223 OBOOK
5955615 OBOOK
--//8223/8192=1.0037841796875
--//5955615/8192=727.0037841796875
--//可以确定包含BOOK在备份文件集的第1块,第727块.
BBED> set filename '/home/oracle/backup/20171114_fcsji7ti_1_1'
FILENAME /home/oracle/backup/20171114_fcsji7ti_1_1
BBED> set block 1
BLOCK# 1
BBED> map /v
File: /home/oracle/backup/20171114_fcsji7ti_1_1 (100)
Block: 1 Dba:0x19000001
------------------------------------------------------------
BBED-00400: invalid blocktype (19)
BBED> map
File: /home/oracle/backup/20171114_fcsji7ti_1_1 (100)
Block: 727 Dba:0x190002d7
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
ub4 tailchk @8188
--//可以确定文件头备份在727块中.从这里还可以看出文件头实际上最后写入备份文件集中的.
$ ls -l /home/oracle/backup/20171114_fcsji7ti_1_1
-rw-r----- 1 oracle oinstall 5971968 2017-11-14 15:09:38 /home/oracle/backup/20171114_fcsji7ti_1_1
--// 备份文件占 5971968/8192=729块.基本上倒数第2块就是文件头.
BBED> set filename '/home/oracle/backup/20171114_fcsji7ti_1_1'
FILENAME /home/oracle/backup/20171114_fcsji7ti_1_1
BBED> set block 729
BBED-00309: out of range block number (729)
BBED> set block 728
BLOCK# 728
5.直接使用备份集导入数据文件看看.
$ dd if=/home/oracle/backup/20171114_fcsji7ti_1_1 skip=727 of=/mnt/ramdisk/book/tea01.dbf seek=1 count=1 bs=8192 conv=notrunc
--//再次提醒输入输出千万不要搞错了.注意参数选择
--// seek=BLOCKS skip BLOCKS obs-sized blocks at start of output
--// skip=BLOCKS skip BLOCKS ibs-sized blocks at start of input
$ dd if=/home/oracle/backup/20171114_fcsji7ti_1_1 skip=727 of=/mnt/ramdisk/book/tea01.dbf seek=1 count=1 bs=8192 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.4931e-05 seconds, 182 MB/s
$ dbv file=/mnt/ramdisk/book/tea01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Nov 14 15:37:34 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/tea01.dbf
Page 1 is marked corrupt
Corrupt block relative dba: 0x01800001 (file 6, block 1)
Bad header found during dbv:
Data in bad block:
type: 11 format: 2 rdba: 0x018000d7
~~~~~~~~~~~~~~~~~
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x1
~~~~~~~~~~~~
consistency value in tail: 0x00000b01
check value in block header: 0x63c4
computed block checksum: 0x0
--//rdba错误.很容易修正.关于这方面的内容可以参考我以前写的:
--// http://blog.itpub.net/267265/viewspace-2128672/ => [20161118]rman备份的疑问2.txt
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> assign kcvfh.kcvfhbfh.rdba_kcbh=0x01800001
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 rdba_kcbh @4 0x01800001
BBED> sum apply;
Check value for File 6, Block 1:
current = 0x6312, required = 0x6312
BBED> p dba 6,1 kcvfh.kcvfhbfh.spare3_kcbh
ub2 spare3_kcbh @18 0x0001
BBED> p dba 5,1 kcvfh.kcvfhbfh.spare3_kcbh
ub2 spare3_kcbh @18 0x0000
BBED> assign dba 6,1 kcvfh.kcvfhbfh.spare3_kcbh=0x0000
ub2 spare3_kcbh @18 0x0000
BBED> sum apply dba 6,1;
Check value for File 6, Block 1:
current = 0x6313, required = 0x6313
--//记住修改2处:kcvfh.kcvfhbfh.rdba_kcbh kcvfh.kcvfhbfh.spare3_kcbh.
RMAN> recover datafile 6 ;
Starting recover at 2017-11-14 15:56:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=144 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=158 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-11-14 15:56:58
SCOTT@book> alter database datafile 6 online ;
Database altered.
SCOTT@book> column name format a40
SCOTT@book> select * from t2 where id between 1 and 9;
ID NAME
---------- ----------------------------------------
1 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
2 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
3 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
4 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
5 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
6 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
7 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
8 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
9 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
9 rows selected.
--//OK现在恢复正常了.
