模擬壞塊故障並搶救資料（一.普通表）

1. select owner, segment_name, segment_type,
   
2. extent_id, file_id, block_id, blocks
   
3. from dba_extents
   
4. where owner='CC' and segment_name='CUSTACCOUNTS'

1. SQL> select * from CUSTACCOUNTS;

      CAID CAVALIDFROM         CAVALIDTO
---------- ------------------- -------------------
CANAME
----------------------------------------------------------------
CAPIN
----------------------------------------------------------------
CALASTLOGIN         CAFAILEDLOGINS CALASTFAILEDLOGIN
------------------- -------------- -------------------
     10763 1970-01-01 00:00:00 4312-12-31 23:59:59
0000000000010763
1234
1970-01-01 00:00:00              0 1970-01-01 00:00:00
--查詢進行了一段時間後，報錯
ERROR:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1000)
ORA-01110: data file 5: '/oradata/APR/datafile/o1_mf_ccdata_bn8l5x7l_.dbf'

1. RMAN> blockrecover datafile 5 block 1000;

1. select * from v$database_block_corruption

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ------------------
         5       1000          1                  0 CORRUPT

1. [oracle@apr datafile]$ dbv file=o1_mf_ccdata_bn8l5x7l_.dbf
   
2. 
   
3. DBVERIFY: Release 11.2.0.4.0 - Production on Sat May 2 16:54:32 2015
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. DBVERIFY - Verification starting : FILE = /oradata/APR/datafile/o1_mf_ccdata_bn8l5x7l_.dbf
   
8. Page 10000 is marked corrupt
   
9. Corrupt block relative dba: 0x01402710 (file 5, block 10000)
   
10. Bad header found during dbv:
    
11. Data in bad block:
    
12.  type: 255 format: 2 rdba: 0x01402710
    
13.  last change scn: 0x0000.0011261f seq: 0x1 flg: 0x06
    
14.  spare1: 0x0 spare2: 0x0 spare3: 0x0
    
15.  consistency value in tail: 0x261f0601
    
16.  check value in block header: 0x7764
    
17.  computed block checksum: 0xf9
    
18. 
    
19. 
    
20. 
    
21. DBVERIFY - Verification complete
    
22. 
    
23. Total Pages Examined : 222848
    
24. Total Pages Processed (Data) : 103647
    
25. Total Pages Failing (Data) : 0
    
26. Total Pages Processed (Index): 54090
    
27. Total Pages Failing (Index): 0
    
28. Total Pages Processed (Other): 2756
    
29. Total Pages Processed (Seg) : 0
    
30. Total Pages Failing (Seg) : 0
    
31. Total Pages Empty : 62354
    
32. Total Pages Marked Corrupt : 1
    
33. Total Pages Influx : 0
    
34. Total Pages Encrypted : 0
    
35. Highest block SCN : 1135578 (0.1135578)

1. select owner,segment_name,segment_type,block_id,blocks from dba_extents
   where file_id = 5
   and block_id  10000;
   

1. SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OWNER='CC' AND OBJECT_NAME='CUSTACCOUNTS';

 OBJECT_ID
----------
     87524

1. SQL> SELECT dbms_rowid.rowid_create(1, 87524, 5, 1000, 0) LOW_RID from DUAL;
   
2. LOW_RID
   ------------------------------------
   AAAVXkAAFAAAAPoAAA
   
3. SQL> SELECT dbms_rowid.rowid_create(1, 87524, 5, 1001, 0) HI_RID from DUAL;
4. HI_RID
   ------------------------------------
   AAAVXkAAFAAAAPpAAA
   

1. --將壞塊所在的錶轉存到另一“安全”的表空間
   
2. CREATE TABLE ccbak.CUSTACCOUNTS_BAK
   
3. TABLESPACE ccbak
   
4. AS SELECT /*+ ROWID(A) */ * FROM CC.CUSTACCOUNTS A
   
5. WHERE ROWID < 'AAAVXkAAFAAAAPoAAA';
6. --繼續
7. INSERT INTO ccbak.CUSTACCOUNTS_BAK 
8. SELECT /*+ ROWID(A) */ * FROM CC.CUSTACCOUNTS A
   
9. WHERE ROWID >= 'AAAVXkAAFAAAAPpAAA';
10. commit;
    

1. drop table CC.CUSTACCOUNTS;
   
2. purge recyclebin;

1. SQL> create directory ccdir as '/home/oracle/ccdir';
   
2. [oracle@apr ~]$ expdp system/oracle@apr schemas=cc dumpfile=cc.dmp DIRECTORY=ccdir

1. SQL> drop tablespace ccdata including contents AND datafiles;

1. SQL> create tablespace ccdata datafile size 1G autoextend on;
   
2. 
   
3. SQL> alter user cc default tablespace ccdata;

1. impdp system/oracle@apr DIRECTORY=ccdir DUMPFILE=cc.dmp SCHEMAS=cc