****欧欧美毛片4,国产午夜精品视频,97视频在线观看免费视频,久久七国产精品

數據恢復咨詢熱線:400-666-3702??

歡迎訪問南京兆柏數據恢復公司，專業數據恢復15年

兆柏數據恢復公司

?常見問題

?當前位置： 主頁 > 常見問題

聯系我們

 400-666-3702

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
復制代碼
相關推薦