非归档模式下,做一个冷备份。
然后创建一个新表。
删除该表所在的数据文件,然后用备份来修复
修复完后,该表依旧无法访问
alert日志里的错误信息
处理方法,用 dbms_repair 来跳过该坏块
检查有坏块的对象
跳过该坏块
现在,test表可以使用了,不过 131块上的数据没有了。
点击(此处)折叠或打开
- RMAN> shutdown immediate;
-
- database closed
- database dismounted
- Oracle instance shut down
-
- RMAN> startup mount;
-
- connected to target database (not started)
- Oracle instance started
- database mounted
-
- Total System Global Area 1068937216 bytes
-
- Fixed Size 2235208 bytes
- Variable Size 616563896 bytes
- Database Buffers 444596224 bytes
- Redo Buffers 5541888 bytes
-
- RMAN> backup database;
-
- Starting backup at 31-JUL-14
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=133 device type=DISK
- channel ORA_DISK_1: starting full datafile backup set
- channel ORA_DISK_1: specifying datafile(s) in backup set
- input datafile file number=00004 name=/u02/oradata/proddb/users01.dbf
- input datafile file number=00006 name=/u02/oradata/proddb/myundo01.dbf
- input datafile file number=00001 name=/u02/oradata/proddb/system01.dbf
- input datafile file number=00002 name=/u02/oradata/proddb/sysaux01.dbf
- input datafile file number=00008 name=/u02/oradata/proddb/ts_small01.dbf
- input datafile file number=00003 name=/u02/oradata/proddb/newts01.dbf
- input datafile file number=00009 name=/u02/oradata/proddb/ts_small02.dbf
- channel ORA_DISK_1: starting piece 1 at 31-JUL-14
- channel ORA_DISK_1: finished piece 1 at 31-JUL-14
- piece handle=/u02/backup/prod/PRODDB/backupset/2014_07_31/o1_mf_nnndf_TAG20140731T054048_9xn3s13c_.bkp tag=TAG20140731T054048 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
- channel ORA_DISK_1: starting full datafile backup set
- channel ORA_DISK_1: specifying datafile(s) in backup set
- including current control file in backup set
- including current SPFILE in backup set
- channel ORA_DISK_1: starting piece 1 at 31-JUL-14
- channel ORA_DISK_1: finished piece 1 at 31-JUL-14
- piece handle=/u02/backup/prod/PRODDB/backupset/2014_07_31/o1_mf_ncsnf_TAG20140731T054048_9xn3skmq_.bkp tag=TAG20140731T054048 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
- Finished backup at 31-JUL-14
-
- RMAN> open database;
-
- database opened
点击(此处)折叠或打开
- scott@PRODDB>create table test
- 2 tablespace newts
- 3 as select * from emp;
-
- Table created.
-
- 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#
- 2 from test; -- 该表的数据都在 文件3 的第 131块中
-
- EMPNO ROWID FILE# BLOCK# ROW#
- ---------- ------------------ ---------- ---------- ----------
- 7369 AAADaKAADAAAACDAAA 3 131 0
- 7499 AAADaKAADAAAACDAAB 3 131 1
- 7521 AAADaKAADAAAACDAAC 3 131 2
- 7566 AAADaKAADAAAACDAAD 3 131 3
- 7654 AAADaKAADAAAACDAAE 3 131 4
- 7698 AAADaKAADAAAACDAAF 3 131 5
- 7782 AAADaKAADAAAACDAAG 3 131 6
- 7788 AAADaKAADAAAACDAAH 3 131 7
- 7839 AAADaKAADAAAACDAAI 3 131 8
- 7844 AAADaKAADAAAACDAAJ 3 131 9
- 7876 AAADaKAADAAAACDAAK 3 131 10
- 7900 AAADaKAADAAAACDAAL 3 131 11
- 7902 AAADaKAADAAAACDAAM 3 131 12
- 7934 AAADaKAADAAAACDAAN 3 131 13
-
- 14 rows selected.
删除该表所在的数据文件,然后用备份来修复
点击(此处)折叠或打开
- [oracle@Redhat55 ~]$ rm /u02/oradata/proddb/newts01.dbf
-
- RMAN> restore datafile 3;
-
- Starting restore at 31-JUL-14
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=133 device type=DISK
-
- channel ORA_DISK_1: starting datafile backup set restore
- channel ORA_DISK_1: specifying datafile(s) to restore from backup set
- channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/proddb/newts01.dbf
- channel ORA_DISK_1: reading from backup piece /u02/backup/prod/PRODDB/backupset/2014_07_31/o1_mf_nnndf_TAG20140731T054048_9xn3s13c_.bkp
- channel ORA_DISK_1: piece handle=/u02/backup/prod/PRODDB/backupset/2014_07_31/o1_mf_nnndf_TAG20140731T054048_9xn3s13c_.bkp tag=TAG20140731T054048
- channel ORA_DISK_1: restored backup piece 1
- channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
- Finished restore at 31-JUL-14
-
- RMAN> recover datafile 3;
-
- Starting recover at 31-JUL-14
- using channel ORA_DISK_1
-
- starting media recovery
- media recovery complete, elapsed time: 00:00:00
-
- Finished recover at 31-JUL-14
修复完后,该表依旧无法访问
点击(此处)折叠或打开
- scott@PRODDB>select * from test; -- 提示 该表的 数据块 ( 文件3 第131块) 损坏
- select * from test
- *
- ERROR at line 1:
- ORA-01578: ORACLE data block corrupted (file # 3, block # 131)
- ORA-01110: data file 3: \'/u02/oradata/proddb/newts01.dbf\'
- ORA-26040: Data block was loaded using the NOLOGGING option
alert日志里的错误信息
点击(此处)折叠或打开
- Thu Jul 31 05:55:02 2014
- Errors in file /u01/app/oracle/diag/rdbms/proddb/Prod/trace/Prod_ora_7161.trc (incident=44604):
- ORA-01578: ORACLE data block corrupted (file # 3, block # 131)
- ORA-01110: data file 3: \
处理方法,用 dbms_repair 来跳过该坏块
点击(此处)折叠或打开
- sys@PRODDB>BEGIN -- 创建修复的表 放在USERS表空间下
- 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 => \'USERS\');
- 7 END;
- 8 /
-
- PL/SQL procedure successfully completed.
检查有坏块的对象
点击(此处)折叠或打开
- sys@PRODDB>DECLARE num_corrupt INT;
- 2 BEGIN
- 3 num_corrupt := 0;
- 4 DBMS_REPAIR.CHECK_OBJECT (
- 5 SCHEMA_NAME => \'SCOTT\',
- 6 OBJECT_NAME => \'TEST\',
- 7 REPAIR_TABLE_NAME => \'REPAIR_TABLE\',
- 8 corrupt_count => num_corrupt);
- 9 DBMS_OUTPUT.PUT_LINE(\'number corrupt: \' || TO_CHAR (num_corrupt));
- 10 END;
- 11 /
- sys@PRODDB>COL CORRUPT_DESCRIPTION FOR A20
- sys@PRODDB>select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION from REPAIR_TABLE; -- 查看坏块的块号
-
- BLOCK_ID CORRUPT_TYPE CORRUPT_DESCRIPTION
- ---------- ------------ --------------------
- 131 6148
跳过该坏块
点击(此处)折叠或打开
- sys@PRODDB>DECLARE num_fix INT;
- 2 BEGIN
- 3 num_fix := 0;
- 4 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
- 5 SCHEMA_NAME => \'SCOTT\',
- 6 OBJECT_NAME=> \'TEST\',
- 7 OBJECT_TYPE => dbms_repair.table_object,
- 8 REPAIR_TABLE_NAME => \'REPAIR_TABLE\',
- 9 FIX_COUNT=> num_fix);
- 10 DBMS_OUTPUT.PUT_LINE(\'num fix: \' || to_char(num_fix));
- 11 END;
- 12 /
-
- PL/SQL procedure successfully completed.
-
- sys@PRODDB>BEGIN
- 2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
- 3 SCHEMA_NAME => \'SCOTT\',
- 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.
现在,test表可以使用了,不过 131块上的数据没有了。
点击(此处)折叠或打开
- sys@PRODDB>select * from scott.test;
-
- no rows selected
-
- sys@PRODDB>set linesize 100
- sys@PRODDB>desc scott.test
- Name Null? Type
- ----------------------------------------------------- -------- ------------------------------------
- EMPNO NUMBER(4)
- ENAME VARCHAR2(10)
- JOB VARCHAR2(9)
- MGR NUMBER(4)
- HIREDATE DATE
- SAL NUMBER(7,2)
- COMM NUMBER(7,2)
- DEPTNO NUMBER(2)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12238525/viewspace-1243391/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12238525/viewspace-1243391/