基于RMAN實(shí)現(xiàn)壞塊介質(zhì)恢復(fù)(blockrecover)
http://blog.csdn.net/leshami/article/details/10500997
對(duì)于物理?yè)p壞的數(shù)據(jù)塊，我們可以通過(guò)RMAN塊介質(zhì)恢復(fù)(BLOCK MEDIA RECOVERY)功能來(lái)完成受損塊的恢復(fù)，而不需要恢復(fù)整個(gè)數(shù)據(jù)庫(kù)或所有文件來(lái)修復(fù)這些少量受損的數(shù)據(jù)塊。恢復(fù)整個(gè)數(shù)據(jù)庫(kù)或數(shù)據(jù)文件那不是大炮用來(lái)打蚊子，有點(diǎn)不值得！但前提條件是你得有一個(gè)可用的RMAN備份存在，因此，無(wú)論何時(shí)備份就是一切。本文演示了產(chǎn)生壞塊即使用RMAN實(shí)現(xiàn)壞塊恢復(fù)的全過(guò)程。
1、創(chuàng)建用于演示的data file
SQL> create tablespace tbs datafile '/u01/app/oracle/oradata/DBdb/tbs.dbf' size 10m autoextend on;
Tablespace created.
--基于新的數(shù)據(jù)文件創(chuàng)建對(duì)象tb
SQL> create table tb tablespace tbs as select * from dba_objects;
Table created.
SQL> col file_name format a60
SQL> select file_id,file_name from dba_data_files where tablespace_name='TBS';
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
7 /u01/app/oracle/oradata/DBdb/tbs.dbf
SQL> COL SEGMENT_NAME FOR A15
SQL> select segment_name , header_file , header_block,blocks from dba_segments where segment_name = 'TB' and owner='SYS';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
--------------- ----------- ------------ ----------
TB 7 130 1280
--首先使用rman備份對(duì)應(yīng)的數(shù)據(jù)文件
[oracle@wang ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 27 22:39:49 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBDB (DBID=3282897732)
RMAN> backup datafile 7;
Starting backup at 27-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf
channel ORA_DISK_1: starting piece 1 at 27-NOV-17
channel ORA_DISK_1: finished piece 1 at 27-NOV-17
piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-NOV-17
RMAN>
2、單塊數(shù)據(jù)塊損壞的恢復(fù)處理
--下面使用了linux自帶的dd命令來(lái)?yè)p壞單塊數(shù)據(jù)塊
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=130 <<eof > Corrupted block!
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 0.000124852 s, 136 kB/s
[oracle@wang ~]$
--清空buffer cache
SQL> alter system flush buffer_cache;
System altered.
--查詢表tb，收到ORA-01578
SQL> select count(*) from tb;
select count(*) from tb
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 130)
ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
--查詢視圖v$database_block_corruption，提示有壞塊，注意該視圖可能不會(huì)返回任何數(shù)據(jù)，如無(wú)返回，先執(zhí)行backup validate
V$DATABASE_BLOCK_CORRUPTION：
V$DATABASE_BLOCK_CORRUPTION displays information about database blocks that were corrupted after the last backup.
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
7 130 1 0 CORRUPT
--下面使用blockrecover來(lái)恢復(fù)壞塊
RMAN> blockrecover datafile 7 block 130;
Starting recover at 27-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-NOV-17
RMAN>
--再次查詢表tb正常
SQL> show user;
USER is "SYS"
SQL>
SQL> select count(*) from tb;
COUNT(*)
----------
87046
3、多塊數(shù)據(jù)塊損壞的恢復(fù)處理
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=133 <<eof
> New corrupted block!
> EOF
0+1 records in
0+1 records out
21 bytes (21 B) copied, 4.6398e-05 s, 453 kB/s
[oracle@wang ~]$
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=143 <<eof
> New01 corrupted block!
> EOF
0+1 records in
0+1 records out
23 bytes (23 B) copied, 6.3948e-05 s, 360 kB/s
[oracle@wang ~]$
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=153 <<eof
> New02 corrupted block!
> EOF
0+1 records in
0+1 records out
23 bytes (23 B) copied, 6.5705e-05 s, 350 kB/s
[oracle@wang ~]$
--刷新共享池
SQL> alter system flush buffer_cache;
System altered.
--下面提示塊133被損壞，注意我們損壞了多塊數(shù)據(jù)塊，但查詢時(shí)，從塊號(hào)最小的開(kāi)始提示，如133被修復(fù)后還有壞塊則繼續(xù)提示133之后的壞塊
SQL> select count(*) from tb;
select count(*) from tb
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 133)
ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
--查詢視圖v$database_block_corruption無(wú)任何記錄
SQL> select * from v$database_block_corruption;
no rows selected
--下面使用backup validate來(lái)校驗(yàn)數(shù)據(jù)文件
RMAN> backup validate datafile 7;
Starting backup at 27-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 FAILED 0 138 1536 3821836
File Name: /u01/app/oracle/oradata/DBdb/tbs.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1240
Index 0 0
Other 3 158 --有3個(gè)Blocks Failing
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_17497.trc for details
Finished backup at 27-NOV-17
RMAN>
--再次查詢v$database_block_corruption，表明有3個(gè)損壞的塊
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
7 133 1 0 CORRUPT
7 143 1 0 CORRUPT
7 153 1 0 CORRUPT
--下面直接使用blockrecover corruption list來(lái)恢復(fù)，如下所有剛剛被校驗(yàn)的壞塊都會(huì)被恢復(fù) ：
blockrecover corruption list;
或者如下命令
run{
allocate channel ch2 device type disk;
blockrecover datafile 7 block 133;
blockrecover datafile 7 block 143;
blockrecover datafile 7 block 153;
release channel ch2;}
執(zhí)行如下：
RMAN> run{
2> allocate channel ch2 device type disk;
3> blockrecover datafile 7 block 133;
4> blockrecover datafile 7 block 143;
5> blockrecover datafile 7 block 153;
6> release channel ch2;}
released channel: ORA_DISK_1
allocated channel: ch2
channel ch2: SID=50 device type=DISK
Starting recover at 27-NOV-17
channel ch2: restoring block(s)
channel ch2: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp
channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014
channel ch2: restored block(s) from backup piece 1
channel ch2: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-NOV-17
Starting recover at 27-NOV-17
channel ch2: restoring block(s)
channel ch2: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp
channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014
channel ch2: restored block(s) from backup piece 1
channel ch2: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-NOV-17
Starting recover at 27-NOV-17
channel ch2: restoring block(s)
channel ch2: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp
channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014
channel ch2: restored block(s) from backup piece 1
channel ch2: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-NOV-17
released channel: ch2
--驗(yàn)證，檢查：
SQL> select * from v$database_block_corruption;
no rows selected.
SQL>
SQL> select count(*) from tb;
COUNT(*)
----------
87046
4、壞塊的對(duì)象定位與影響
SQL> col object_name for a25
SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,owner,object_name,object_id from tb where dbms_rowid.rowid_block_number(rowid)=163 and rownum<=2;
OBJECT_ID FILE_ID BLOCK_ID OWNER OBJECT_NAME OBJECT_ID
---------- ---------- ---------- ------------------------------ ------------------------- ----------
89910 7 163PUBLIC GV$BACKUP_SET 2364
89910 7 163SYS GV_$BACKUP_PIECE 2365
--使用上面的方法，損塊塊163，173:
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=163 <<eof
> New03 corrupted block!
> EOF
0+1 records in
0+1 records out
23 bytes (23 B) copied, 3.9521e-05 s, 582 kB/s
[oracle@wang ~]$
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=173 <<eof
> New04 corrupted block!
> EOF
0+1 records in
0+1 records out
23 bytes (23 B) copied, 6.0101e-05 s, 383 kB/s
[oracle@wang ~]$
a、對(duì)于壞塊對(duì)象無(wú)法進(jìn)行聚合匯總等操作:
SQL> select count(*) from tb;
select count(*) from tb
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 163)
ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
b、對(duì)于壞塊上的記錄無(wú)法被查詢
--我們使用基于之前查詢到的OBJECT_ID來(lái)查詢
SQL> select owner,object_name,object_id from tb where object_id in(2364,2365);
select owner,object_name,object_id from tb where object_id in(2364,2365)
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 163)
ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
--如下面的查詢，位于損壞塊上(163的數(shù)據(jù)無(wú)法被查詢到，但對(duì)于未損壞的依舊可以查詢。下面的查詢時(shí)塊161上的對(duì)象
SQL> select owner,object_name,object_id from tb where dbms_rowid.rowid_block_number(rowid)=161 and rownum<3;
OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------- ----------
SYS GV_$LATCHNAME 2203
PUBLIC GV$LATCHNAME 2204
c、定位受損塊所對(duì)應(yīng)的對(duì)象
select tablespace_name,segment_type,owner,segment_name,partition_name FROMdba_extentsWHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks -1;
--查詢?nèi)缦拢?/strong>
SQL> SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks -1;
Enter value for file_id: 7
Enter value for block_id: 163
old 1: SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks -1
new 1: SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = 7 AND 163 BETWEEN block_id AND block_id + blocks -1
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------ ------------------------------ --------------- ------------------------------
TBS TABLE SYS TB
d、對(duì)于損壞的數(shù)據(jù)文件，缺省情況下，不能對(duì)其進(jìn)行備份，如下
RMAN> backup datafile 7 tag='corruption';
Starting backup at 27-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf
channel ORA_DISK_1: starting piece 1 at 27-NOV-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/27/2017 23:23:49
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/DBdb/tbs.dbf
RMAN>
--查詢對(duì)應(yīng)數(shù)據(jù)文件壞塊數(shù)：
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
7 163 1 0 CORRUPT
7 173 1 0 CORRUPT
--需要設(shè)定允許損壞塊的數(shù)量之后才能進(jìn)行備份
run{
set maxcorrupt for datafile 7 to 2;
backup datafile 7 tag='corruption';
}
執(zhí)行如下：
RMAN> run{
2> set maxcorrupt for datafile 7 to 2;
3> backup datafile 7 tag='corruption';
4> }
executing command: SET MAX CORRUPT
Starting backup at 27-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf
channel ORA_DISK_1: starting piece 1 at 27-NOV-17
channel ORA_DISK_1: finished piece 1 at 27-NOV-17
piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_CORRUPTION_f1rcshjk_.bkp tag=CORRUPTION comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-NOV-17
RMAN>
--查看備份信息如下，應(yīng)在修復(fù)壞塊后重新備份以避免由于保留策略導(dǎo)致先前可用的備份被aged out
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 27-NOV-17 1 1 NO TAG20171127T224014
2 B F A DISK 27-NOV-17 1 1 NO CORRUPTION
RMAN>
5、后記
a、對(duì)于受損的數(shù)據(jù)塊，僅僅壞塊上的數(shù)據(jù)無(wú)法被查詢或讀取，其余正常塊的數(shù)據(jù)依舊可以使用。
b、對(duì)于受損的表對(duì)象進(jìn)行聚合等相關(guān)運(yùn)算時(shí)收到錯(cuò)誤提示，因?yàn)閴膲K上的數(shù)據(jù)無(wú)法被統(tǒng)計(jì)。如果你聚合的是索引列，索引未損壞的情形則可正常返回。
c、可以基于RMAN可用的備份文件實(shí)現(xiàn)塊介質(zhì)恢復(fù)，其數(shù)據(jù)文件無(wú)需offline，開(kāi)銷(xiāo)最小，影響最小。
d、對(duì)于多個(gè)數(shù)據(jù)塊的損壞，先執(zhí)行backup validate校驗(yàn)數(shù)據(jù)庫(kù)或相應(yīng)的數(shù)據(jù)文件以便標(biāo)記受損的壞塊后，記錄到視圖v$database_block_corruption，然后后續(xù)恢復(fù)。
e、對(duì)于使用backup validate 校驗(yàn)后的情形，壞塊恢復(fù)時(shí)可以直接使用blockrecover corruption list一次性恢復(fù)所有的壞塊。
f、缺省情況下，存在壞塊的數(shù)據(jù)文件無(wú)法成功備份，也會(huì)導(dǎo)致自動(dòng)備份腳本失敗。
對(duì)于物理?yè)p壞的數(shù)據(jù)塊，我們可以通過(guò)RMAN塊介質(zhì)恢復(fù)(BLOCK MEDIA RECOVERY)功能來(lái)完成受損塊的恢復(fù)，而不需要恢復(fù)整個(gè)數(shù)據(jù)庫(kù)或所有文件來(lái)修復(fù)這些少量受損的數(shù)據(jù)塊。恢復(fù)整個(gè)數(shù)據(jù)庫(kù)或數(shù)據(jù)文件那不是大炮用來(lái)打蚊子，有點(diǎn)不值得！但前提條件是你得有一個(gè)可用的RMAN備份存在，因此，無(wú)論何時(shí)備份就是一切。本文演示了產(chǎn)生壞塊即使用RMAN實(shí)現(xiàn)壞塊恢復(fù)的全過(guò)程。
1、創(chuàng)建用于演示的data file
SQL> create tablespace tbs datafile '/u01/app/oracle/oradata/DBdb/tbs.dbf' size 10m autoextend on;
Tablespace created.
--基于新的數(shù)據(jù)文件創(chuàng)建對(duì)象tb
SQL> create table tb tablespace tbs as select * from dba_objects;
Table created.
SQL> col file_name format a60
SQL> select file_id,file_name from dba_data_files where tablespace_name='TBS';
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
7 /u01/app/oracle/oradata/DBdb/tbs.dbf
SQL> COL SEGMENT_NAME FOR A15
SQL> select segment_name , header_file , header_block,blocks from dba_segments where segment_name = 'TB' and owner='SYS';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
--------------- ----------- ------------ ----------
TB 7 130 1280
--首先使用rman備份對(duì)應(yīng)的數(shù)據(jù)文件
[oracle@wang ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 27 22:39:49 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBDB (DBID=3282897732)
RMAN> backup datafile 7;
Starting backup at 27-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf
channel ORA_DISK_1: starting piece 1 at 27-NOV-17
channel ORA_DISK_1: finished piece 1 at 27-NOV-17
piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-NOV-17
RMAN>
2、單塊數(shù)據(jù)塊損壞的恢復(fù)處理
--下面使用了linux自帶的dd命令來(lái)?yè)p壞單塊數(shù)據(jù)塊
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=130 <<eof > Corrupted block!
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 0.000124852 s, 136 kB/s
[oracle@wang ~]$
--清空buffer cache
SQL> alter system flush buffer_cache;
System altered.
--查詢表tb，收到ORA-01578
SQL> select count(*) from tb;
select count(*) from tb
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 130)
ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
--查詢視圖v$database_block_corruption，提示有壞塊，注意該視圖可能不會(huì)返回任何數(shù)據(jù)，如無(wú)返回，先執(zhí)行backup validate
V$DATABASE_BLOCK_CORRUPTION：
V$DATABASE_BLOCK_CORRUPTION displays information about database blocks that were corrupted after the last backup.
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
7 130 1 0 CORRUPT
--下面使用blockrecover來(lái)恢復(fù)壞塊
RMAN> blockrecover datafile 7 block 130;
Starting recover at 27-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-NOV-17
RMAN>
--再次查詢表tb正常
SQL> show user;
USER is "SYS"
SQL>
SQL> select count(*) from tb;
COUNT(*)
----------
87046
3、多塊數(shù)據(jù)塊損壞的恢復(fù)處理
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=133 <<eof
> New corrupted block!
> EOF
0+1 records in
0+1 records out
21 bytes (21 B) copied, 4.6398e-05 s, 453 kB/s
[oracle@wang ~]$
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=143 <<eof
> New01 corrupted block!
> EOF
0+1 records in
0+1 records out
23 bytes (23 B) copied, 6.3948e-05 s, 360 kB/s
[oracle@wang ~]$
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=153 <<eof
> New02 corrupted block!
> EOF
0+1 records in
0+1 records out
23 bytes (23 B) copied, 6.5705e-05 s, 350 kB/s
[oracle@wang ~]$
--刷新共享池
SQL> alter system flush buffer_cache;
System altered.
--下面提示塊133被損壞，注意我們損壞了多塊數(shù)據(jù)塊，但查詢時(shí)，從塊號(hào)最小的開(kāi)始提示，如133被修復(fù)后還有壞塊則繼續(xù)提示133之后的壞塊
SQL> select count(*) from tb;
select count(*) from tb
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 133)
ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
--查詢視圖v$database_block_corruption無(wú)任何記錄
SQL> select * from v$database_block_corruption;
no rows selected
--下面使用backup validate來(lái)校驗(yàn)數(shù)據(jù)文件
RMAN> backup validate datafile 7;
Starting backup at 27-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 FAILED 0 138 1536 3821836
File Name: /u01/app/oracle/oradata/DBdb/tbs.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1240
Index 0 0
Other 3 158 --有3個(gè)Blocks Failing
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_17497.trc for details
Finished backup at 27-NOV-17
RMAN>
--再次查詢v$database_block_corruption，表明有3個(gè)損壞的塊
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
7 133 1 0 CORRUPT
7 143 1 0 CORRUPT
7 153 1 0 CORRUPT
--下面直接使用blockrecover corruption list來(lái)恢復(fù)，如下所有剛剛被校驗(yàn)的壞塊都會(huì)被恢復(fù) ：
blockrecover corruption list;
或者如下命令
run{
allocate channel ch2 device type disk;
blockrecover datafile 7 block 133;
blockrecover datafile 7 block 143;
blockrecover datafile 7 block 153;
release channel ch2;}
執(zhí)行如下：
RMAN> run{
2> allocate channel ch2 device type disk;
3> blockrecover datafile 7 block 133;
4> blockrecover datafile 7 block 143;
5> blockrecover datafile 7 block 153;
6> release channel ch2;}
released channel: ORA_DISK_1
allocated channel: ch2
channel ch2: SID=50 device type=DISK
Starting recover at 27-NOV-17
channel ch2: restoring block(s)
channel ch2: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp
channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014
channel ch2: restored block(s) from backup piece 1
channel ch2: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-NOV-17
Starting recover at 27-NOV-17
channel ch2: restoring block(s)
channel ch2: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp
channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014
channel ch2: restored block(s) from backup piece 1
channel ch2: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-NOV-17
Starting recover at 27-NOV-17
channel ch2: restoring block(s)
channel ch2: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp
channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014
channel ch2: restored block(s) from backup piece 1
channel ch2: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-NOV-17
released channel: ch2
--驗(yàn)證，檢查：
SQL> select * from v$database_block_corruption;
no rows selected.
SQL>
SQL> select count(*) from tb;
COUNT(*)
----------
87046
4、壞塊的對(duì)象定位與影響
SQL> col object_name for a25
SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,owner,object_name,object_id from tb where dbms_rowid.rowid_block_number(rowid)=163 and rownum<=2;
OBJECT_ID FILE_ID BLOCK_ID OWNER OBJECT_NAME OBJECT_ID
---------- ---------- ---------- ------------------------------ ------------------------- ----------
89910 7 163PUBLIC GV$BACKUP_SET 2364
89910 7 163SYS GV_$BACKUP_PIECE 2365
--使用上面的方法，損塊塊163，173:
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=163 <<eof
> New03 corrupted block!
> EOF
0+1 records in
0+1 records out
23 bytes (23 B) copied, 3.9521e-05 s, 582 kB/s
[oracle@wang ~]$
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=173 <<eof
> New04 corrupted block!
> EOF
0+1 records in
0+1 records out
23 bytes (23 B) copied, 6.0101e-05 s, 383 kB/s
[oracle@wang ~]$
a、對(duì)于壞塊對(duì)象無(wú)法進(jìn)行聚合匯總等操作:
SQL> select count(*) from tb;
select count(*) from tb
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 163)
ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
b、對(duì)于壞塊上的記錄無(wú)法被查詢
--我們使用基于之前查詢到的OBJECT_ID來(lái)查詢
SQL> select owner,object_name,object_id from tb where object_id in(2364,2365);
select owner,object_name,object_id from tb where object_id in(2364,2365)
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 163)
ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
--如下面的查詢，位于損壞塊上(163的數(shù)據(jù)無(wú)法被查詢到，但對(duì)于未損壞的依舊可以查詢。下面的查詢時(shí)塊161上的對(duì)象
SQL> select owner,object_name,object_id from tb where dbms_rowid.rowid_block_number(rowid)=161 and rownum<3;
OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------- ----------
SYS GV_$LATCHNAME 2203
PUBLIC GV$LATCHNAME 2204
c、定位受損塊所對(duì)應(yīng)的對(duì)象
select tablespace_name,segment_type,owner,segment_name,partition_name FROMdba_extentsWHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks -1;
--查詢?nèi)缦拢?/strong>
SQL> SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks -1;
Enter value for file_id: 7
Enter value for block_id: 163
old 1: SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks -1
new 1: SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = 7 AND 163 BETWEEN block_id AND block_id + blocks -1
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------ ------------------------------ --------------- ------------------------------
TBS TABLE SYS TB
d、對(duì)于損壞的數(shù)據(jù)文件，缺省情況下，不能對(duì)其進(jìn)行備份，如下
RMAN> backup datafile 7 tag='corruption';
Starting backup at 27-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf
channel ORA_DISK_1: starting piece 1 at 27-NOV-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/27/2017 23:23:49
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/DBdb/tbs.dbf
RMAN>
--查詢對(duì)應(yīng)數(shù)據(jù)文件壞塊數(shù)：
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
7 163 1 0 CORRUPT
7 173 1 0 CORRUPT
--需要設(shè)定允許損壞塊的數(shù)量之后才能進(jìn)行備份
run{
set maxcorrupt for datafile 7 to 2;
backup datafile 7 tag='corruption';
}
執(zhí)行如下：
RMAN> run{
2> set maxcorrupt for datafile 7 to 2;
3> backup datafile 7 tag='corruption';
4> }
executing command: SET MAX CORRUPT
Starting backup at 27-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf
channel ORA_DISK_1: starting piece 1 at 27-NOV-17
channel ORA_DISK_1: finished piece 1 at 27-NOV-17
piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_CORRUPTION_f1rcshjk_.bkp tag=CORRUPTION comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-NOV-17
RMAN>
--查看備份信息如下，應(yīng)在修復(fù)壞塊后重新備份以避免由于保留策略導(dǎo)致先前可用的備份被aged out
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 27-NOV-17 1 1 NO TAG20171127T224014
2 B F A DISK 27-NOV-17 1 1 NO CORRUPTION
RMAN>
5、后記
a、對(duì)于受損的數(shù)據(jù)塊，僅僅壞塊上的數(shù)據(jù)無(wú)法被查詢或讀取，其余正常塊的數(shù)據(jù)依舊可以使用。
b、對(duì)于受損的表對(duì)象進(jìn)行聚合等相關(guān)運(yùn)算時(shí)收到錯(cuò)誤提示，因?yàn)閴膲K上的數(shù)據(jù)無(wú)法被統(tǒng)計(jì)。如果你聚合的是索引列，索引未損壞的情形則可正常返回。
c、可以基于RMAN可用的備份文件實(shí)現(xiàn)塊介質(zhì)恢復(fù)，其數(shù)據(jù)文件無(wú)需offline，開(kāi)銷(xiāo)最小，影響最小。
d、對(duì)于多個(gè)數(shù)據(jù)塊的損壞，先執(zhí)行backup validate校驗(yàn)數(shù)據(jù)庫(kù)或相應(yīng)的數(shù)據(jù)文件以便標(biāo)記受損的壞塊后，記錄到視圖v$database_block_corruption，然后后續(xù)恢復(fù)。
e、對(duì)于使用backup validate 校驗(yàn)后的情形，壞塊恢復(fù)時(shí)可以直接使用blockrecover corruption list一次性恢復(fù)所有的壞塊。
f、缺省情況下，存在壞塊的數(shù)據(jù)文件無(wú)法成功備份，也會(huì)導(dǎo)致自動(dòng)備份腳本失敗。
本文題目：基于RMAN實(shí)現(xiàn)壞塊介質(zhì)恢復(fù)(blockrecover)
本文URL：http://www.chinadenli.net/article26/jogdcg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站建設(shè)、搜索引擎優(yōu)化、定制開(kāi)發(fā)、軟件開(kāi)發(fā)、建站公司、移動(dòng)網(wǎng)站建設(shè)
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 網(wǎng)站優(yōu)化公司怎么判斷關(guān)鍵詞優(yōu)化難度 2016-09-03
- seo核心關(guān)鍵詞優(yōu)化需要注意哪幾點(diǎn)？ 2015-04-17
- seo關(guān)鍵詞優(yōu)化之百度經(jīng)驗(yàn)操作方法 2014-04-29
- 整站的優(yōu)化對(duì)于單一的關(guān)鍵詞優(yōu)化有什么優(yōu)勢(shì) 2016-03-15
- 網(wǎng)站關(guān)鍵詞優(yōu)化，企業(yè)網(wǎng)站在設(shè)計(jì)過(guò)程中有怎樣的優(yōu)化技巧 2023-04-14
- 讀懂關(guān)鍵詞優(yōu)化技巧讓企業(yè)優(yōu)先占領(lǐng)商機(jī) 2023-03-28
- seo關(guān)鍵詞優(yōu)化:關(guān)鍵詞合理布局寫(xiě)文章開(kāi)頭和結(jié)尾的建議 2023-04-17
- 怎么做好seo關(guān)鍵詞優(yōu)化工作？ 2015-09-20
- 成都關(guān)鍵詞優(yōu)化技巧有哪些呢？ 2016-10-27
- 成都關(guān)鍵詞優(yōu)化公司哪家好?成都SEO優(yōu)化公司 2014-05-30
- 關(guān)鍵詞優(yōu)化報(bào)價(jià)怎么做？ 2016-11-03
- 沈陽(yáng)網(wǎng)站關(guān)鍵詞優(yōu)化常見(jiàn)的誤區(qū)有哪些 2020-12-09