非归档模式 无法用备份修复新建的表

非归档模式下，做一个冷备份。 

点击(此处)折叠或打开

1. RMAN> shutdown immediate;
   
2. 
   
3. database closed
   
4. database dismounted
   
5. Oracle instance shut down
   
6. 
   
7. RMAN> startup mount;
   
8. 
   
9. connected to target database (not started)
   
10. Oracle instance started
    
11. database mounted
    
12. 
    
13. Total System Global Area 1068937216 bytes
    
14. 
    
15. Fixed Size 2235208 bytes
    
16. Variable Size 616563896 bytes
    
17. Database Buffers 444596224 bytes
    
18. Redo Buffers 5541888 bytes
    
19. 
    
20. RMAN> backup database;
    
21. 
    
22. Starting backup at 31-JUL-14
    
23. allocated channel: ORA_DISK_1
    
24. channel ORA_DISK_1: SID=133 device type=DISK
    
25. channel ORA_DISK_1: starting full datafile backup set
    
26. channel ORA_DISK_1: specifying datafile(s) in backup set
    
27. input datafile file number=00004 name=/u02/oradata/proddb/users01.dbf
    
28. input datafile file number=00006 name=/u02/oradata/proddb/myundo01.dbf
    
29. input datafile file number=00001 name=/u02/oradata/proddb/system01.dbf
    
30. input datafile file number=00002 name=/u02/oradata/proddb/sysaux01.dbf
    
31. input datafile file number=00008 name=/u02/oradata/proddb/ts_small01.dbf
    
32. input datafile file number=00003 name=/u02/oradata/proddb/newts01.dbf
    
33. input datafile file number=00009 name=/u02/oradata/proddb/ts_small02.dbf
    
34. channel ORA_DISK_1: starting piece 1 at 31-JUL-14
    
35. channel ORA_DISK_1: finished piece 1 at 31-JUL-14
    
36. piece handle=/u02/backup/prod/PRODDB/backupset/2014_07_31/o1_mf_nnndf_TAG20140731T054048_9xn3s13c_.bkp tag=TAG20140731T054048 comment=NONE
    
37. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
    
38. channel ORA_DISK_1: starting full datafile backup set
    
39. channel ORA_DISK_1: specifying datafile(s) in backup set
    
40. including current control file in backup set
    
41. including current SPFILE in backup set
    
42. channel ORA_DISK_1: starting piece 1 at 31-JUL-14
    
43. channel ORA_DISK_1: finished piece 1 at 31-JUL-14
    
44. piece handle=/u02/backup/prod/PRODDB/backupset/2014_07_31/o1_mf_ncsnf_TAG20140731T054048_9xn3skmq_.bkp tag=TAG20140731T054048 comment=NONE
    
45. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    
46. Finished backup at 31-JUL-14
    
47. 
    
48. RMAN> open database;
    
49. 
    
50. database opened

然后创建一个新表。

点击(此处)折叠或打开

1. scott@PRODDB>create table test
   
2.   2 tablespace newts
   
3.   3 as select * from emp;
   
4. 
   
5. Table created.
   
6. 
   
7. scott@PRODDB>select empno, rowid, dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block#, dbms_rowid.rowid_row_number(rowid) row#
   
8.   2 from test;                                                              -- 该表的数据都在 文件3 的第 131块中
   
9. 
   
10.      EMPNO ROWID FILE# BLOCK# ROW#
    
11. ---------- ------------------ ---------- ---------- ----------
    
12.       7369 AAADaKAADAAAACDAAA 3 131 0
    
13.       7499 AAADaKAADAAAACDAAB 3 131 1
    
14.       7521 AAADaKAADAAAACDAAC 3 131 2
    
15.       7566 AAADaKAADAAAACDAAD 3 131 3
    
16.       7654 AAADaKAADAAAACDAAE 3 131 4
    
17.       7698 AAADaKAADAAAACDAAF 3 131 5
    
18.       7782 AAADaKAADAAAACDAAG 3 131 6
    
19.       7788 AAADaKAADAAAACDAAH 3 131 7
    
20.       7839 AAADaKAADAAAACDAAI 3 131 8
    
21.       7844 AAADaKAADAAAACDAAJ 3 131 9
    
22.       7876 AAADaKAADAAAACDAAK 3 131 10
    
23.       7900 AAADaKAADAAAACDAAL 3 131 11
    
24.       7902 AAADaKAADAAAACDAAM 3 131 12
    
25.       7934 AAADaKAADAAAACDAAN 3 131 13
    
26. 
    
27. 14 rows selected.

删除该表所在的数据文件，然后用备份来修复

点击(此处)折叠或打开

1. [oracle@Redhat55 ~]$ rm /u02/oradata/proddb/newts01.dbf
   
2. 
   
3. RMAN> restore datafile 3;
   
4. 
   
5. Starting restore at 31-JUL-14
   
6. allocated channel: ORA_DISK_1
   
7. channel ORA_DISK_1: SID=133 device type=DISK
   
8. 
   
9. channel ORA_DISK_1: starting datafile backup set restore
   
10. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    
11. channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/proddb/newts01.dbf
    
12. channel ORA_DISK_1: reading from backup piece /u02/backup/prod/PRODDB/backupset/2014_07_31/o1_mf_nnndf_TAG20140731T054048_9xn3s13c_.bkp
    
13. channel ORA_DISK_1: piece handle=/u02/backup/prod/PRODDB/backupset/2014_07_31/o1_mf_nnndf_TAG20140731T054048_9xn3s13c_.bkp tag=TAG20140731T054048
    
14. channel ORA_DISK_1: restored backup piece 1
    
15. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    
16. Finished restore at 31-JUL-14
    
17. 
    
18. RMAN> recover datafile 3;
    
19. 
    
20. Starting recover at 31-JUL-14
    
21. using channel ORA_DISK_1
    
22. 
    
23. starting media recovery
    
24. media recovery complete, elapsed time: 00:00:00
    
25. 
    
26. Finished recover at 31-JUL-14

修复完后，该表依旧无法访问

点击(此处)折叠或打开

1. scott@PRODDB>select * from test;                                      -- 提示 该表的 数据块 （ 文件3 第131块） 损坏
   
2. select * from test
   
3.               *
   
4. ERROR at line 1:
   
5. ORA-01578: ORACLE data block corrupted (file # 3, block # 131)
   
6. ORA-01110: data file 3: \'/u02/oradata/proddb/newts01.dbf\'
   
7. ORA-26040: Data block was loaded using the NOLOGGING option

alert日志里的错误信息

点击(此处)折叠或打开

1. Thu Jul 31 05:55:02 2014
   
2. Errors in file /u01/app/oracle/diag/rdbms/proddb/Prod/trace/Prod_ora_7161.trc (incident=44604):
   
3. ORA-01578: ORACLE data block corrupted (file # 3, block # 131)
   
4. ORA-01110: data file 3: \

处理方法，用 dbms_repair 来跳过该坏块

点击(此处)折叠或打开

1. sys@PRODDB>BEGIN                                            --  创建修复的表  放在USERS表空间下
   
2.   2 DBMS_REPAIR.ADMIN_TABLES (
   
3.   3 TABLE_NAME => \'REPAIR_TABLE\',
   
4.   4 TABLE_TYPE => dbms_repair.repair_table,
   
5.   5 ACTION => dbms_repair.create_action,
   
6.   6 TABLESPACE => \'USERS\');
   
7.   7 END;
   
8.   8 /
   
9. 
   
10. PL/SQL procedure successfully completed.

检查有坏块的对象

点击(此处)折叠或打开

1. sys@PRODDB>DECLARE num_corrupt INT;
   
2.   2 BEGIN
   
3.   3 num_corrupt := 0;
   
4.   4 DBMS_REPAIR.CHECK_OBJECT (
   
5.   5 SCHEMA_NAME => \'SCOTT\',
   
6.   6 OBJECT_NAME => \'TEST\',
   
7.   7 REPAIR_TABLE_NAME => \'REPAIR_TABLE\',
   
8.   8 corrupt_count => num_corrupt);
   
9.   9 DBMS_OUTPUT.PUT_LINE(\'number corrupt: \' || TO_CHAR (num_corrupt));
   
10.  10 END;
    
11.  11 /
12. sys@PRODDB>COL CORRUPT_DESCRIPTION FOR A20
13. sys@PRODDB>select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION from REPAIR_TABLE;           -- 查看坏块的块号
14. 
    
15. BLOCK_ID CORRUPT_TYPE CORRUPT_DESCRIPTION
16. ---------- ------------ --------------------
17.        131         6148
    

跳过该坏块

点击(此处)折叠或打开

1. sys@PRODDB>DECLARE num_fix INT;
   
2.   2 BEGIN
   
3.   3 num_fix := 0;
   
4.   4 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
   
5.   5 SCHEMA_NAME => \'SCOTT\',
   
6.   6 OBJECT_NAME=> \'TEST\',
   
7.   7 OBJECT_TYPE => dbms_repair.table_object,
   
8.   8 REPAIR_TABLE_NAME => \'REPAIR_TABLE\',
   
9.   9 FIX_COUNT=> num_fix);
   
10.  10 DBMS_OUTPUT.PUT_LINE(\'num fix: \' || to_char(num_fix));
    
11.  11 END;
    
12.  12 /
    
13. 
    
14. PL/SQL procedure successfully completed.
    
15. 
    
16. sys@PRODDB>BEGIN
    
17.   2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
    
18.   3 SCHEMA_NAME => \'SCOTT\',
    
19.   4 OBJECT_NAME => \'TEST\',
    
20.   5 OBJECT_TYPE => dbms_repair.table_object,
    
21.   6 FLAGS => dbms_repair.SKIP_FLAG);
    
22.   7 END;
    
23.   8 /
    
24. 
    
25. PL/SQL procedure successfully completed.

现在，test表可以使用了，不过 131块上的数据没有了。

点击(此处)折叠或打开

1. sys@PRODDB>select * from scott.test;
   
2. 
   
3. no rows selected
   
4. 
   
5. sys@PRODDB>set linesize 100
   
6. sys@PRODDB>desc scott.test
   
7.  Name Null? Type
   
8.  ----------------------------------------------------- -------- ------------------------------------
   
9.  EMPNO NUMBER(4)
   
10.  ENAME VARCHAR2(10)
    
11.  JOB VARCHAR2(9)
    
12.  MGR NUMBER(4)
    
13.  HIREDATE DATE
    
14.  SAL NUMBER(7,2)
    
15.  COMM NUMBER(7,2)
    
16.  DEPTNO NUMBER(2)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12238525/viewspace-1243391/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12238525/viewspace-1243391/