Oracle中如何使用 DBMS

182次閱讀

共計(jì) 5861 個(gè)字符，預(yù)計(jì)需要花費(fèi) 15 分鐘才能閱讀完成。

這篇文章主要為大家展示了“Oracle 中如何使用 DBMS_REPAIR 修復(fù)壞塊”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓丸趣 TV 小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“Oracle 中如何使用 DBMS_REPAIR 修復(fù)壞塊”這篇文章吧。

對(duì)于 Oracle 數(shù)據(jù)塊物理?yè)p壞的情形，在我們有備份的情況下可以直接使用備份來(lái)恢復(fù)。對(duì)于通過(guò)備份恢復(fù)，Oracel 為我們提供了很多種方式，冷備，基于用戶管理方式，RMAN 方式等等。對(duì)于這幾種方式我們需要實(shí)現(xiàn)基于數(shù)據(jù)庫(kù)以及文件級(jí)別的恢復(fù)。RMAN 同時(shí)也提供了基于塊介質(zhì)方式的恢復(fù)。也就是說(shuō)我們根本不需要還原數(shù)據(jù)文件，而是直接從備份文件基于塊來(lái)提取以實(shí)現(xiàn)聯(lián)機(jī)恢復(fù)?？蓞⒖蓟?RMAN 實(shí)現(xiàn)壞塊介質(zhì)恢復(fù) (blockrecover) 。這是比較理想的情形。如果沒(méi)有任何備份怎么辦？我們可以使用 Oracle 自帶的 DBMS_REPAIR 包來(lái)實(shí)現(xiàn)修復(fù)。注意，不要被文章題目有所誤導(dǎo)。這里的修復(fù)是有損修復(fù)也就是說(shuō)將受損的數(shù)據(jù)塊標(biāo)記為壞塊，不對(duì)其進(jìn)行訪問(wèn)而已。就好比我們磁盤(pán)有壞道，找個(gè)磁盤(pán)修復(fù)工具將壞道標(biāo)出來(lái)不使用，同理。那受損的數(shù)據(jù)豈不是無(wú)力回天啦，嗚嗚 …… 要記得隨時(shí)備份阿。

1、DBMS_REPAIR 包所含的過(guò)程
Procedure_Name Description
—————– ————————————
ADMIN_TABLES Provides administrative functions (create, drop, purge) for repair or orphan key tables.
Note: These tables are always created in the SYS schema.
CHECK_OBJECT Detects and reports corruptions in a table or index
DUMP_ORPHAN_KEYS Reports on index entries that point to rows in corrupt data blocks
FIX_CORRUPT_BLOCKS Marks blocks as software corrupt that have been previously identified as corrupt by the CHECK_OBJECT procedure
REBUILD_FREELISTS Rebuilds the free lists of the object
SEGMENT_FIX_STATUS Provides the capability to fix the corrupted state of a bitmap entry when segment space management is AUTO
SKIP_CORRUPT_BLOCKS When used, ignores blocks marked corrupt during table and index scans.
If not used, you get error ORA-01578 when encountering blocks marked corrupt.

2、DBMS_REPAIR 的一些局限性
Tables with LOB data types, nested tables, and varrays are supported, but the out-of-line columns are ignored.
Clusters are supported in the SKIP_CORRUPT_BLOCKS and REBUILD_FREELISTS procedures, but not in the CHECK_OBJECT procedure.
Index-organized tables and LOB indexes are not supported.
The DUMP_ORPHAN_KEYS procedure does not operate on bitmap indexes or function-based indexes.
The DUMP_ORPHAN_KEYS procedure processes keys that are no more than 3,950 bytes long.

3. 創(chuàng)建演示環(huán)境
– 創(chuàng)建表空間
SQL show user;
USER is SCOTT
SQL create tablespace tbs_tmp datafile /u01/app/oracle/oradata/DBdb/tbs_tmp.dbf size 10m autoextend on;

Tablespace created.

– 創(chuàng)建表對(duì)象 tb_obj 及其索引
SQL create table tb_obj tablespace tbs_tmp as select * from dba_objects;

Table created.

SQL create index i_tb_obj on tb_obj(object_id);

Index created.

– 表段上的相關(guān)信息
SQL select segment_name , header_file , header_block,blocks from dba_segments where segment_name = TB_OBJ

SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
——————————————————————————— ———– ———— ———-
TB_OBJ 6 130 1280

– 使用 linux 自帶的 dd 命令來(lái)?yè)p壞數(shù)據(jù)塊
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs_tmp.dbf bs=8192 conv=notrunc seek=131 eof
Corrupt me!
EOF
0+1 records in
0+1 records out
13 bytes (13 B) copied, 0.000117639 s, 111 kB/s
[oracle@wang ~]$

– 下面的查詢收到了錯(cuò)誤提示
SQL select count(*) from tb_obj;
select count(*) from tb_obj
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: /u01/app/oracle/oradata/DBdb/tbs_tmp.dbf

SQL

4. 使用 DBMS_REPAIR 修復(fù)壞塊
Step a 創(chuàng)建相應(yīng)的表對(duì)象 :

– 使用 DBMS_REPAIR.ADMIN_TABLES 過(guò)程創(chuàng)建一個(gè)表對(duì)象，用于記錄需要被修復(fù)的表
SQL BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME = REPAIR_TABLE ,
TABLE_TYPE = dbms_repair.repair_table,
ACTION = dbms_repair.create_action,
TABLESPACE = USERS
END;
/

PL/SQL procedure successfully completed.

SQL

– 使用 DBMS_REPAIR.ADMIN_TABLES 過(guò)程創(chuàng)建一個(gè)表對(duì)象，用于記錄在表塊損壞后那些孤立索引，也就是指向壞塊的那些索引
SQL BEGIN
DBMS_REPAIR.ADMIN_TABLES
(
TABLE_NAME = ORPHAN_KEY_TABLE ,
TABLE_TYPE = DBMS_REPAIR.ORPHAN_TABLE,
ACTION = DBMS_REPAIR.CREATE_ACTION,
TABLESPACE = USERS
);
END;
/