非歸檔模式 無法用備份修復新建的表

huyi_tony發表於2014-07-31
模式
非歸檔模式下，做一個冷備份。 

點選(此處)摺疊或開啟

  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)












來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12238525/viewspace-1243391/，如需轉載，請註明出處，否則將追究法律責任。

相關文章