oracle數(shù)據(jù)庫(kù)壞塊修復(fù)
瀏覽量: 次 發(fā)布日期:2018-01-25 15:33:46
oracle數(shù)據(jù)庫(kù)修復(fù)壞塊
1.檢測(cè)Oracle 數(shù)據(jù)庫(kù)修復(fù)壞快的方法有以下2種:
a)DBV(DB File Verify)工具
外部命令,物理介質(zhì)數(shù)據(jù)結(jié)構(gòu)完整性檢查;
只能用于數(shù)據(jù)文件(offline或online),不支持控制文件和重做日志文件的塊檢查;
也可以驗(yàn)證備份文件(rman的copy命令備份或操作系統(tǒng)CP命令備份);
b)RMAN(Recovery Manger)工具
邏輯數(shù)據(jù)結(jié)構(gòu)完整性檢查;
在線使用Recovery Manager掃描壞塊和備份時(shí),需要數(shù)據(jù)庫(kù)運(yùn)行在歸檔模式(archive log),否則只能在數(shù)據(jù)庫(kù)未打開(kāi)(mount)的情況下進(jìn)行;
oracle 數(shù)據(jù)庫(kù)修復(fù)熱線:400-666-3702
數(shù)據(jù)庫(kù)修復(fù)
行RMAN冷備的過(guò)程中報(bào)如下錯(cuò),某個(gè)表空間備份失敗。
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch00 channel at 04/25/2015 22:02:30
ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/dbrac/datafile/tbs_11.11.435678937
經(jīng)過(guò)分析發(fā)現(xiàn)壞塊導(dǎo)致,且壞塊不屬于任何對(duì)象(空塊),以下是本次壞塊修復(fù)步驟:
1、查找壞塊
1)使用RMAN查找壞塊
驗(yàn)證整個(gè)數(shù)據(jù)庫(kù):
Rman> backup validate check logical database ;
注:當(dāng)數(shù)據(jù)庫(kù)版本低于11g且非歸檔模式,以上命令必須在數(shù)據(jù)庫(kù)處于mounted狀態(tài)執(zhí)行
驗(yàn)證單個(gè)datafile
Rman> backup validate check logical datafile 11 ;
而后執(zhí)行以下SQL查看壞塊:
SQL>Select * from v$database_block_corruption ;
例如:
validate.sh
#!/bin/bash
source /home/Oracle/.bash_profile
$ORACLE_HOME/bin/rman log=/home/oracle/users/validate.log <<EOF
connect target /
Backup validate check logical datafile 11 ;
exit;
EOF
2)使用DBV查找壞塊:
dbv userid=system/system file='+DATA/dbrac/datafile/tbs_11.11.435678937' blocksize=32768
2、確認(rèn)壞塊是否不屬于任何對(duì)象
select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupted block number> between block_id
and block_id + blocks -1;
例如:
alter session force parallel query parallel 10;
select segment_name, segment_type, owner
from dba_extents
where file_id = 11
and 184959440 between block_id
and block_id + blocks -1;
3、確認(rèn)塊在 dba_free_space存在
Select * from dba_free_space where file_id= <Absolute file number>
and <corrupted block number> between block_id and block_id + blocks -1;
例如:
Select * from dba_free_space where file_id= 11 and 184959440 between block_id and block_id + blocks -1;
4、創(chuàng)建表
create table s (
n number,
c varchar2(4000)
) nologging tablespace <tablespace name having the corrupt block> pctfree 99;
例如:
create table users.s (
n number,
c varchar2(4000)
) nologging tablespace TBS_11 pctfree 99;
select segment_name,tablespace_name from dba_segments
where segment_name='S' ;
Select table_name,tablespace_name from dba_tables where table_name='S' ;
5、創(chuàng)建觸發(fā)器
CREATE OR REPLACE TRIGGER corrupt_trigger
AFTER INSERT ON users.s
REFERENCING OLD AS p_old NEW AS new_p
FOR EACH ROW
DECLARE
corrupt EXCEPTION;
BEGIN
IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
RAISE corrupt;
END IF;
EXCEPTION
WHEN corrupt THEN
RAISE_APPLICATION_ERROR( -20000, 'Corrupt block has been formatted');
END;
/
6、分配空間創(chuàng)建在有壞塊的datafile上的表
注:
i)因?yàn)锳SSM會(huì)自動(dòng)確定下一個(gè)區(qū)段的大小,所以在ASSM的表空間上,需要?jiǎng)?chuàng)建多個(gè)表及
不斷的分配空間給這些表,直到壞塊被分配至其中一個(gè)對(duì)象。
ii)設(shè)置datafile的AUTOEXTEND為OFF
1)查找壞塊的extent size
Select BYTES from dba_free_space where file_id=<file no> and <corrupt block no> between
block_id and block_id + blocks -1;
例如:
alter database datafile '+DATA/dbrac/datafile/tbs_11.11.435678937' autoextend off;
SQL> Select BYTES from dba_free_space where file_id=11 and 184959440 between
2 block_id and block_id + blocks -1;
BYTES
----------
29360128
2)不斷allocate直到壞塊是S表的一部分
如果步驟1輸出結(jié)果是64K,執(zhí)行以下SQL:
alter table users.s
allocate extent (DATAFILE '+DATA/dbrac/datafile/tbs_11.11.435678937' SIZE 64K);
如果大于64K使用以下
BEGIN
for i in 1..1000000 loop
EXECUTE IMMEDIATE 'alter table users.s allocate extent (DATAFILE '||'''+DATA/dbrac/datafile/tbs_11.11.435678937'''||'SIZE 64K) ';
end loop;
end ;
/
使用如下SQL查詢(xún)壞塊是否已屬于某個(gè)對(duì)象:
select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupt block number> between block_id
and block_id + blocks -1 ;
例如:
select segment_name, segment_type, owner
from dba_extents
where file_id = 11
and 184959440 between block_id
and block_id + blocks -1 ;
3)插入數(shù)據(jù)初始化壞塊
Begin
FOR i IN 1..1000000000 loop
for j IN 1..100000 loop
Insert into users.s VALUES(i,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
end loop;
commit;
END LOOP;
END;
7、驗(yàn)證是否存在壞塊
Rman> Backup validate check logical datafile <fileno> ;
或者validate datafile <fileno> block <blockno reported corrupt>, <blockno reported corrupt> ;
Select * from v$database_block_corruption ;
例如:
validate datafile 11 block 184959440, 184961480,184961481 ;
Select * from v$database_block_corruption ;
8、當(dāng)?shù)?步確認(rèn)壞塊已消除,即可刪除測(cè)試表
DROP TABLE users.s purge;
9、切換多次日志及checkpoint
Alter system switch logfile ;
Alter system checkpoint ;
10、刪除觸發(fā)器
DROP trigger CORRUPT_TRIGGER ;
DROP trigger corrupt_trigger1 ;
DROP trigger corrupt_trigger2 ;
. oracle11g修復(fù),Oracle 11g TNS-12545錯(cuò)誤排查與修復(fù)指南
. oracle數(shù)據(jù)庫(kù)數(shù)據(jù)恢復(fù),Oracle數(shù)據(jù)庫(kù)數(shù)據(jù)恢復(fù)策略與實(shí)戰(zhàn)指南
. oracle數(shù)據(jù)文件損壞 怎么恢復(fù),全面解析與實(shí)操步驟
. oracle數(shù)據(jù)庫(kù)收費(fèi)標(biāo)準(zhǔn),全面了解授權(quán)模式與費(fèi)用構(gòu)成
. oracle快照恢復(fù)數(shù)據(jù),高效的數(shù)據(jù)保護(hù)與恢復(fù)策略
. oracle數(shù)據(jù)庫(kù)導(dǎo)入dmp,Oracle數(shù)據(jù)庫(kù)導(dǎo)入dmp文件詳解
. oracle數(shù)據(jù)庫(kù)基本知識(shí),Oracle數(shù)據(jù)庫(kù)簡(jiǎn)介
. oracle誤刪除數(shù)據(jù)恢復(fù),Oracle數(shù)據(jù)庫(kù)誤刪除數(shù)據(jù)恢復(fù)指南
. oracle許可只能找oracle買(mǎi)嗎,是否只能從Oracle官方購(gòu)買(mǎi)?
. oracle收費(fèi)標(biāo)準(zhǔn),Oracle收費(fèi)標(biāo)準(zhǔn)的全面解析
. 如何使用 Oracle 環(huán)境變量,什么是 Oracle 環(huán)境變量
. oracle刪除的數(shù)據(jù)怎么恢復(fù),Oracle數(shù)據(jù)庫(kù)中刪除數(shù)據(jù)后的恢復(fù)方法詳解
. oracle誤刪數(shù)據(jù)怎么恢復(fù),Oracle誤刪數(shù)據(jù)恢復(fù)指南
. oracle數(shù)據(jù)庫(kù)免費(fèi)版區(qū)別,XE版與其他版本的差異
. oracle常見(jiàn)故障,Oracle數(shù)據(jù)庫(kù)常見(jiàn)故障解析及應(yīng)對(duì)策略
. oracle刪除主鍵約束,Oracle數(shù)據(jù)庫(kù)中刪除主鍵約束的詳細(xì)指南
. oracle連接數(shù)據(jù)庫(kù),Oracle數(shù)據(jù)庫(kù)連接詳解
. oracle客戶(hù)端環(huán)境配置,系統(tǒng)要求