oracle数据坏块恢复(blockrecover)
环境:
OS:Centos 6.9
DB:11.2.0.4
########################################单块数据块损坏的恢复处理##########################################
1.全备数据库
run
{
allocate channel ch1 device type disk;
backup full database format '/u01/rmanbak/db_fullbackup_%d_%s_%p_%T';
backup current controlfile format '/u01/rmanbak/ctl_%d_%s_%p_%T';
backup archivelog all format '/u01/rmanbak/arch_%d_%s_%p_%T';
backup spfile format '/u01/rmanbak/spfile_%d_%s_%p_%T';
release channel ch1;
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
}
需要进行数据库的全备份,坏块的恢复需要有一个完整的备份,或是只针某个数据文件进行备份.
2.下面使用了linux自带的dd命令来损坏单块数据块
确定模拟的数据文件
SQL> column file_name format a64;
SQL> select file_name from dba_data_files where tablespace_name='TPS_HXL';
FILE_NAME
----------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf
该表空间下的表
SQL> select owner,table_name,tablespace_name from dba_tables where owner='HXL';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
HXL TB_TEST01 TPS_HXL
HXL TB_TEST02 TPS_HXL
模拟坏块,执行如下命令
[oracle@ora11g rmanbak]$dd of=/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf bs=8192 conv=notrunc seek=136 <<EOF
Corrupted block!
EOF
这个时候如下视图没有记录
SQL> select * from v$database_block_corruption;
no rows selected
3.校验数据文件(视图v$database_block_corruption有体现)
RMAN> backup validate datafile 5;
Starting backup at 13-SEP-23
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=00005 name=/u01/app/oracle/oradata/ora11g/tps_hxl01.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
---- ------ -------------- ------------ --------------- ----------
5 FAILED 0 18133 177920 2972329
File Name: /u01/app/oracle/oradata/ora11g/tps_hxl01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 143365
Index 0 15260
Other 1 1162
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_9479.trc for details
Finished backup at 13-SEP-23
这个时候视图有体现出来了
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 136 1 0 CORRUPT
查看该块对应的信息
SQL> SELECT owner,segment_name,segment_type,file_id,block_id,blocks
2 FROM dba_extents
3 WHERE file_id =5
4 and 136 between block_id AND block_id + blocks -1;
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE FILE_ID BLOCK_ID BLOCKS
------------------ ---------- ---------- ----------
HXL
TB_TEST02
TABLE 5 136 8
或是使用dbv(视图v$database_block_corruption不体现)
[oracle@ora11g ~]$ dbv file=/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Sep 13 07:13:47 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ora11g/tps_hxl01.dbf
Page 136 is marked corrupt
Corrupt block relative dba: 0x01400088 (file 5, block 136)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x65747075
last change scn: 0x636f.6c622064 seq: 0x6b flg: 0x21
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x2b7e2002
check value in block header: 0xf70a
block checksum disabled
DBVERIFY - Verification complete
Total Pages Examined : 177920
Total Pages Processed (Data) : 143365
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 15260
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1161
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 18133
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2972329 (0.2972329)
4.单个块修复
RMAN> recover datafile 5 block 136;
Starting recover at 13-SEP-23
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/rmanbak/db_fullbackup_ORA11G_1_1_20230913
channel ORA_DISK_1: piece handle=/u01/rmanbak/db_fullbackup_ORA11G_1_1_20230913 tag=TAG20230913T012021
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 13-SEP-23
也可以使用如下命令
blockrecover datafile 5 block 130;这个命令是10G的,也是可以的(11G代替了blockrecover).
以上命令可以放在脚本里执行,数据文件很大的情况下，放后台执行
vi blockrecover.sh
#!/bin/bash
rman target / log=/home/oracle/rman_output.log<<EOF
run
{
allocate channel ch1 device type disk;
recover datafile 5 block 136;
release channel ch1;
exit;
}
EOF
nohup ./blockrecover.sh > blockrecover.out 2>&1 &
再次查询如下视图:
SQL> select * from v$database_block_corruption;
no rows selected
发现已经没有了坏块
########################################修复多个坏块##########################
1.模拟多个坏块
[oracle@linux1 ~]$ dd of=/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf bs=8192 conv=notrunc seek=133 <<EOF
New corrupted block!
EOF
[oracle@linux1 ~]$ dd of=/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf bs=8192 conv=notrunc seek=143 <<EOF
New corrupted block!
EOF
[oracle@linux1 ~]$ dd of=/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf bs=8192 conv=notrunc seek=153 <<EOF
New corrupted block!
EOF
2.校验数据文件
rman target /
backup validate datafile 5;
执行这句后视图会有记录
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 133 1 0 CORRUPT
5 143 1 0 CORRUPT
5 153 1 0 CORRUPT
恢复
RMAN>recover datafile 5 block 133,143,153;
或是逐个块恢复
RMAN>recover datafile 5 block 133;
RMAN>recover datafile 5 block 143;
RMAN>recover datafile 5 block 153;
或是一次性恢复所有坏块
RMAN>recover corruption list;
