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

  • huyi_tony

    2014-07-31 22:14:35
  • Oracle
  • 原创
非归档模式下，做一个冷备份。 

点击(此处)折叠或打开

  1. RMAN> shutdown immediate;

  3. database closed
  4. database dismounted
  5. Oracle instance shut down

  7. RMAN> startup mount;

  9. connected to target database (not started)
  10. Oracle instance started
  11. database mounted

  13. Total System Global Area 1068937216 bytes

  15. Fixed Size 2235208 bytes
  16. Variable Size 616563896 bytes
  17. Database Buffers 444596224 bytes
  18. Redo Buffers 5541888 bytes

  20. RMAN> backup database;

  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

  48. RMAN> open database;

  50. database opened
然后创建一个新表。

点击(此处)折叠或打开

  1. scott@PRODDB>create table test
  2.   2 tablespace newts
  3.   3 as select * from emp;

  5. Table created.

  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块中

  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

  27. 14 rows selected.

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


点击(此处)折叠或打开

  1. [oracle@Redhat55 ~]$ rm /u02/oradata/proddb/newts01.dbf

  3. RMAN> restore datafile 3;

  5. Starting restore at 31-JUL-14
  6. allocated channel: ORA_DISK_1
  7. channel ORA_DISK_1: SID=133 device type=DISK

  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

  18. RMAN> recover datafile 3;

  20. Starting recover at 31-JUL-14
  21. using channel ORA_DISK_1

  23. starting media recovery
  24. media recovery complete, elapsed time: 00:00:00

  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 /

  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;           -- 查看坏块的块号

  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 /

  14. PL/SQL procedure successfully completed.

  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 /

  25. PL/SQL procedure successfully completed.

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


点击(此处)折叠或打开

  1. sys@PRODDB>select * from scott.test;

  3. no rows selected

  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)












请使用浏览器的分享功能分享到微信等