当前位置:Oracle研究中心 > 运维DBA >
时间:2016-07-25 22:31 来源:Oracle研究中心 作者:惜分飞 点击: 次
C:\Users\oracleplus>sqlplus chf/oracleplus SQL*Plus: Release 11.2.0.1.0 Production on 星期五 12月 23 10:49:52 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> create tablespace t_xff datafile 'E:\ORACLE\ORADATA\XFF\t_xff01.dbf' 2 size 10m autoextend on next 10m maxsize 1g; 表空间已创建。 SQL> create table t_oracleplus tablespace t_xff 2 as 3 select * from dba_objects; 表已创建。 SQL> select count(*) from t_oracleplus; COUNT(*) ---------- 73286
使用ULtraEdit破坏数据(关闭数据库执行)
SQL> select count(*) from t_oracleplus; select count(*) from t_oracleplus * 第 1 行出现错误: ORA-01578: ORACLE 数据块损坏 (文件号 13, 块号 373) ORA-01110: 数据文件 13: 'E:\ORACLE\ORADATA\XFF\T_XFF01.DBF'
The "LOW_RID" is the lowest rowid INSIDE the corrupt block: SELECT dbms_rowid.rowid_create(1,, , ,0) LOW_RID from DUAL; The "HI_RID" is the first rowid AFTER the corrupt block: SELECT dbms_rowid.rowid_create(1, , , +1,0) HI_RID from DUAL; SQL> col tablespace_name for a30 SQL> col segment_type for a5 SQL> col owner for a10 SQL> col segment_name for a20 SQL> SELECT tablespace_name, segment_type, owner, segment_name 2 FROM dba_extents 3 WHERE file_id =13 4 AND 373 between block_id AND block_id + blocks - 1 ; TABLESPACE_NAME SEGME OWNER SEGMENT_NAME ------------------------------ ----- ---------- -------------------- T_XFF TABLE CHF T_oracleplus SQL> SELECT data_object_id 2 FROM dba_objects 3 WHERE object_name = 'T_oracleplus' and owner='CHF'; DATA_OBJECT_ID -------------- 77759 --坏块的最小rowid SQL> select dbms_rowid.rowid_create(1, 77759,13,373,0) from dual; DBMS_ROWID.ROWID_C ------------------ AAAS+/AANAAAAF1AAA 坏块的最大rowid(block+1得到) SQL> select dbms_rowid.rowid_create(1, 77759,13,374,0) from dual; DBMS_ROWID.ROWID_C ------------------ AAAS+/AANAAAAF2AAA
SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_oracleplus A 2 WHERE ROWID>='AAAS+/AANAAAAF2AAA'; COUNT(*) ---------- 55858 SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_oracleplus A 2 WHERE ROWID<'AAAS+/AANAAAAF1AAA'; COUNT(*) ---------- 17358 SQL> SELECT 77759-55858-17358 from dual; 77759-55858-17358 ----------------- 4543 SQL> CREATE TABLE T_oracleplus_BAK TABLESPACE T_XFF 2 AS 3 SELECT /*+ ROWID(A) */ * FROM T_oracleplus A 4 WHERE ROWID>='AAAS+/AANAAAAF2AAA'; 表已创建。 SQL> INSERT INTO T_oracleplus_BAK 2 SELECT /*+ ROWID(A) */ * FROM T_oracleplus A 3 WHERE ROWID<'AAAS+/AANAAAAF1AAA'; 已创建17358行。 SQL> COMMIT; 提交完成。 SQL> SELECT COUNT(*) FROM T_oracleplus_BAK; COUNT(*) ---------- 73216
SQL> CONN / AS SYSDBA 已连接。 SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_oracleplus'); PL/SQL 过程已成功完成。 SQL> select skip_corrupt from dba_tables where table_name='T_oracleplus'; SKIP_COR -------- ENABLED SQL> select count(*) from chf.t_oracleplus; COUNT(*) ---------- 73216
通过跳过坏块和rowid功能对比可以看出,两者丢失的数据是相同的,如果有index,同样利用rowid结合index,可能会找回部分数据。当然dbms_repair也提供了类此的功能。两种方法的使用看个人的爱好与习惯。
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle坏块 通过ROWID找回Oracle数据文件坏块中的数据
本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/891.html
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。