数据恢复咨询热线:400-666-3702  

欢迎访问南京兆柏数据恢复公司,专业数据恢复15年

兆柏数据恢复公司

 常见问题

 当前位置: 主页 > 常见问题

oracle数据库修复,oracle数据块修复

浏览量: 次 发布日期:2019-11-10 21:33:24

oracle数据库修复,oracle数据块修复
数据块坏块:

    对于发生数据块不一致的数据块,如果当前数据库有备份且处于归档模式,那么就可以利用rman工具数据块恢复功能 对数据块进行恢复,这种方法最简单有效,而且可以在数据文件在线时进行,不会发生数据丢失。对于被有备份的数据库 发生数据块损坏,可能会发生数据的丢失或数据不丢失,这要根据发生坏块的所在的对象决定的,如索引块发生损坏,数据 就不会丢失,重建索引就可以了,发生数据丢失的多发生在表或分区表数据块上。

 

1.不丢数据的恢复方法

复制代码
---使用rman工具的

blockrecover 


blockrecover datafile xx block xx;--修复单个坏块
blockrecover corruption list;--修复全部坏块

SQL> select * from livan.test;
select * from livan.test
                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 12)
ORA-01110: data file 6: '/u02/app/oradata/PSDB/livan_tbs01.dbf'
复制代码
复制代码
[oracle@std u02]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Feb 5 17:02:23 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: PSDB (DBID=1410134833)

RMAN> blockrecover datafile 6 block 12;

Starting blockrecover at 05-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /u02/PSDB_BACKUP/full_PSDB_870868610
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u02/PSDB_BACKUP/full_PSDB_870868610 tag=TAG20150205T115650
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished blockrecover at 05-FEB-15
复制代码
复制代码
[oracle@std u02]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 5 17:04:15 2015

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from livan.test;

        ID NAME
---------- ------------------------------
         1 beijing
         2 shanghai
         3 shandong
复制代码

如果坏块上的表最近都没有更新,还可以利用bbed的copy命令来从一个最近的备份中copy过来一个数据块恢复,具体不演示。

 

2.有可能存在数据丢失的恢复(在没有备份没有归档的情况下)

---用户表数据损坏

<1>常情况下数据条目数

SQL> select count(*) from test;

  COUNT(*)
----------
     50604

 

<2>制作一个坏块

复制代码
select rowid,
dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno,
dbms_rowid.rowid_row_number(rowid) rowno
from livan.test;   




     
BBED> set dba 6,76
        DBA             0x0180004c (25165900 6,76)

BBED> d /v dba 6,76 offset 0
 File: /u02/app/oradata/PSDB/livan_tbs01.dbf (6)
 Block: 76      Offsets:    0 to  127  Dba:0x0180004c
-------------------------------------------------------
 06a20000 4c008001 d3220800 00000104 l .?.L...?......
 b8510000 01000000 ddce0000 b4220800 l 窺......菸..?..
 00000000 03003201 41008001 ffff0000 l ......2.A.......
 00000000 00000000 00000000 00800000 l ................
 b4220800 00000000 00000000 00000000 l ?..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00019200 l ................

 <16 bytes per line>

BBED> modify /x 12345 dba 6,76 offset 0
 File: /u02/app/oradata/PSDB/livan_tbs01.dbf (6)
 Block: 76               Offsets:    0 to  127           Dba:0x0180004c
------------------------------------------------------------------------
 01234500 4c008001 d3220800 00000104 b8510000 01000000 ddce0000 b4220800 
 00000000 03003201 41008001 ffff0000 00000000 00000000 00000000 00800000 
 b4220800 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00019200 

 <32 bytes per line>

BBED> sum play
BBED-00202: invalid parameter (play)


BBED> sum apply 
Check value for File 6, Block 76:
current = 0xd0fa, required = 0xd0fa
     



SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 76)
ORA-01110: data file 6: '/u02/app/oradata/PSDB/livan_tbs01.dbf'
复制代码

发现我们第6个文件第76号数据块损坏,报ORA-0178错误,我们知掉只要数据库报ORA-01578错误,
说明该数据块已经被标识为:"software corrupt"

 

<3>确认坏块的类型

复制代码
SQL> select segment_name,partition_name,segment_type,owner,tablespace_name
  2  from sys.dba_extents
  3  where file_id=&AFN
  4  and &bad_block_id between block_id and block_id + blocks-1;
Enter value for afn: 6
old   3: where file_id=&AFN
new   3: where file_id=6
Enter value for bad_block_id: 76
old   4: and &bad_block_id between block_id and block_id + blocks-1
new   4: and 76 between block_id and block_id + blocks-1

SEGMENT_NAME    PARTITION_NAME       SEGMENT_TYPE       OWNER      TABLESPACE_NAME
--------------- -------------------- ------------------ ---------- ------------------------------
TEST                                 TABLE              LIVAN      LIVAN_TBS
复制代码

经查我们的数据损坏坏位于我们的用户表上,无备份数据会丢失。

 

<4>标记坏块为"software corrupt"

在第2步的时候全表扫描时已经报ORA-01578错误,说明该数据块已经被标识为:"software corrupt", 正常情况下可以跳过这步。 我们使用dbms_repair包演示标记坏块为"software corrupt"

使用dbms_repair包可参考:http://blog.itpub.net/8494287/viewspace-1357457/

 

--利用dbms_repair包必须先创建repair table两个表:

复制代码
SQL> begin
  2  dbms_repair.admin_tables(
  3  table_name=>'REPAIR_TABLE',
  4  table_type=>dbms_repair.repair_table,
  5  action=>dbms_repair.create_action,
  6  tablespace=>'LIVAN_TBS');   
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>  col object_name for a20
SQL> select owner,object_name,object_type
  2  from dba_objects
  3  where object_name like '%REPAIR_TABLE%';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
SYS                            REPAIR_TABLE         TABLE
SYS                            DBA_REPAIR_TABLE     VIEW
复制代码

 

--再创建orphan key table

复制代码
SQL> begin
  2  dbms_repair.admin_tables(
  3  table_type=>dbms_repair.orphan_table,
  4  action=>dbms_repair.create_action,
  5  tablespace=>'LIVAN_TBS');   
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select owner,object_name,object_type
  2  from dba_objects
  3  where object_name like '%ORPHAN_KEY_TABLE%';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
SYS                            ORPHAN_KEY_TABLE     TABLE
SYS                            DBA_ORPHAN_KEY_TABLE VIEW
复制代码

 

--检查对象,检查结果会放到我们之前创建的repair_table中

复制代码
SQL> set serveroutput on
SQL> declare
  2  rpr_count int;
  3  begin
  4  rpr_count:=0;
  5  dbms_repair.check_object(
  6  schema_name=>'LIVAN',
  7  object_name=>'TEST',
  8  repair_table_name=>'REPAIR_TABLE',
  9  corrupt_count=>rpr_count);  
 10  dbms_output.put_line('repair count:'||to_char(rpr_count));
 11  end;
 12  /
repair count:1

PL/SQL procedure successfully completed.
复制代码

检查出有1个坏块

 

--检查校验的坏块结果

复制代码
SQL> select object_name,block_id,corrupt_type,marked_corrupt,
  2  corrupt_description,repair_description
  3  from repair_table;

OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIP REPAIR_DESCRIPTION
---------- ---------- ------------ ---------- --------------- ------------------------------
TEST               76         6148 TRUE                       mark block software corrupt
复制代码

我们知道当marked_corrupt为TRUE时,标识这个数据块已经被标识过software corrupt

 

---标识坏块为software corrupt(重新演示一下)

复制代码
SQL> declare
  2  fix_count int;
  3  begin
  4  fix_count:=0;
  5  dbms_repair.fix_corrupt_blocks(
  6  schema_name=>'LIVAN',
  7  object_name=>'TEST',
  8  object_type=>dbms_repair.table_object,
  9  repair_table_name=>'REPAIR_TABLE',
 10  fix_count=>fix_count);
 11  dbms_output.put_line('fix count:'||to_char(fix_count));
 12  end;
 13  /
fix count:0

PL/SQL procedure successfully completed.
复制代码

 

--再次检查,因为已经被标志为software corrupt,所以在此标志也没什么变化

复制代码
SQL> select object_name,block_id,corrupt_type,marked_corrupt,
  2  corrupt_description,repair_description
  3  from repair_table;

OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIP REPAIR_DESCRIPTION
---------- ---------- ------------ ---------- --------------- ------------------------------
TEST               76         6148 TRUE                       mark block software corrupt
复制代码

未被标志为oftware corrupt ,marked_corrupt列会显示FALSE

 

<5>检查其他关联对象

检查有多少个索引项指向了坏块的记录

SQL> select index_name from dba_indexes
  2  where table_name in (select distinct object_name from repair_table);

INDEX_NAME
------------------------------
INDEX_TEST

查询发现有一个索引指向这个坏块

 

--检查都有多少索引数据

复制代码
SQL> set serveroutput on
SQL> declare
  2  key_count int;
  3  begin
  4  key_count:=0;
  5  dbms_repair.dump_orphan_keys(
  6  schema_name=>'LIVAN',
  7  object_name=>'INDEX_TEST',
  8  object_type=>dbms_repair.index_object,
  9  repair_table_name=>'REPAIR_TABLE',
 10  orphan_table_name=>'ORPHAN_KEY_TABLE',
 11  key_count=>key_count);
 12  dbms_output.put_line('orphan key count:'||to_char(key_count));
 13  end;
 14  /
orphan key count:146

PL/SQL procedure successfully completed.



SQL> select index_name,count(*) from orphan_key_table
  2  group by index_name;

INDEX_NAME                       COUNT(*)
------------------------------ ----------
INDEX_TEST                            146
复制代码

可以看到有146条数据指向坏块

 

<6>使用dbms_repair.skip_corrupt_blocks或10231事件方式跳过坏块

复制代码
SQL> select count(*) from livan.test;
select count(*) from livan.test
                           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 76)
ORA-01110: data file 6: '/u02/app/oradata/PSDB/livan_tbs01.dbf'


SQL> begin
  2  dbms_repair.skip_corrupt_blocks(
  3  schema_name=>'LIVAN',
  4  object_name=>'TEST',
  5  object_type=>dbms_repair.table_object,
  6  flags=>dbms_repair.skip_flag);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select count(*) from livan.test;

  COUNT(*)
----------
     50458
复制代码
相关推荐