how to fix ora-08103？
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: how to fix ora-08103？
|
1 2 |
关于ora-08103错误，其实在熊哥的博客以及dbsnake的博客都曾经写过一篇。我这里写这篇的目的不是为了 模拟ora-8103错误，而是为了测试在该种情况下的expdp和exp。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
SQL> create table t1 2 as select * 3 from dba_objects 4 where rownum analyze table t1 compute statistics; Table analyzed. SQL> select owner, segment_name, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS 2 from dba_extents 3 where segment_name='T1'; OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS -------------------- --------------- ---------- ---------- ---------- ---------- ROGER T1 1 5 433 8 ROGER T1 3 5 441 8 ROGER T1 0 6 521 8 ROGER T1 2 6 529 8 SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk#, 2 dbms_rowid.rowid_relative_fno(rowid) file# 3 from t1 4 order by 2,1; BLK# FILE# ---------- ---------- 433 5 434 5 435 5 436 5 437 5 438 5 439 5 440 5 441 5 442 5 443 5 444 5 445 5 524 6 525 6 526 6 527 6 528 6 530 6 531 6 532 6 533 6 534 6 535 6 536 6 25 rows selected. SQL> conn /as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> ! |
|
1 2 3 4 5 6 7 |
[ora10g@killdb ~]$ dd if=/dev/zero of=/home/ora10g/oradata/roger/roger02.dbf bs=8192 seek=536 count=1 conv=notrunc 1+0 records in 1+0 records out [ora10g@killdb ~]$ exit exit |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> startup ORA-00000: normal, successful completion SQL> conn /as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 96470248 bytes Database Buffers 67108864 bytes Redo Buffers 2920448 bytes Database mounted. Database opened. SQL> select count(*) from roger.t1; select count(*) from roger.t1 * ERROR at line 1: ORA-08103: object no longer exists |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
[ora10g@killdb ~]$ exp system/oracle file=t1.dmp tables=roger.t1 Export: Release 10.2.0.5.0 - Production on Mon Dec 12 05:41:25 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion) About to export specified tables via Conventional Path ... Current user changed to ROGER . . exporting table T1 EXP-00056: ORACLE error 8103 encountered ORA-08103: object no longer exists Export terminated successfully with warnings. [ora10g@killdb ~]$ expdp roger/roger directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t1 content=data_only Export: Release 10.2.0.5.0 - Production on Monday, 12 December, 2011 5:45:13 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "ROGER"."SYS_EXPORT_TABLE_01": roger/******** directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t1 content=data_only Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB ORA-31693: Table data object "ROGER"."T1" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-08103: object no longer exists Master table "ROGER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ROGER.SYS_EXPORT_TABLE_01 is: /home/ora10g/product/10.2/rdbms/log/t1.dmp Job "ROGER"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 05:45:16 |
|
1 |
使用bbed 将该block 修改为坏块，然后重启实例。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 |
SQL> shutdown abort; ORACLE instance shut down. SQL> startup ORA-00000: normal, successful completion SQL> conn /as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 104858856 bytes Database Buffers 58720256 bytes Redo Buffers 2920448 bytes Database mounted. Database opened. SQL> select count(*) from roger.t1; select count(*) from roger.t1 * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 536) ORA-01110: data file 6: '/home/ora10g/oradata/roger/roger02.dbf' SQL> drop table REPAIR_TABLE; Table dropped. SQL> BEGIN 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 => 'ROGER' 7 ); 8 END; 9 / PL/SQL procedure successfully completed. SQL> SET serveroutput ON SQL> DECLARE 2 num_corrupt INT; 3 4 BEGIN 5 num_corrupt := 0; 6 DBMS_REPAIR.CHECK_OBJECT ( 7 SCHEMA_NAME => 'ROGER', 8 OBJECT_NAME => 'T1', 9 REPAIR_TABLE_NAME => 'REPAIR_TABLE', 10 corrupt_count => num_corrupt 11 ); 12 DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt)); 13 END; 14 / number corrupt: 1 PL/SQL procedure successfully completed. SQL> SELECT object_id, RELATIVE_FILE_ID, block_id, CORRUPT_TYPE, object_name 2 from REPAIR_TABLE; OBJECT_ID RELATIVE_FILE_ID BLOCK_ID CORRUPT_TYPE OBJECT_NAME ---------- ---------------- ---------- ------------ ------------------------------ 52470 6 536 6148 T1 SQL> DECLARE 2 fix_count int; 3 4 BEGIN 5 fix_count := 0; 6 DBMS_REPAIR.fix_corrupt_blocks ( 7 schema_name => 'ROGER', 8 object_name => 'T1', 9 object_type => DBMS_REPAIR.table_object, 10 repair_table_name => 'REPAIR_TABLE', 11 fix_count => fix_count 12 ); 13 DBMS_OUTPUT.put_line('fix count: ' || TO_CHAR(fix_count)); 14 END; 15 / fix count: 0 PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_REPAIR.skip_corrupt_blocks ( 3 schema_name => 'ROGER', 4 object_name => 'T1', 5 object_type => DBMS_REPAIR.table_object, 6 flags => DBMS_REPAIR.skip_flag 7 ); 8 END; 9 / PL/SQL procedure successfully completed. SQL> select count(*) from roger.t1; COUNT(*) ---------- 1917 |
|
1 2 3 4 5 |
当然，最后你可以使用cats 或基于rowid方式将数据抽取出来，然后将表rename，我这里模拟的 ora-08103可能跟实际遇到的情况有些差别，群中的网友说使用expdp是可以进行导出的，我这里 模式发现是不行的，有点怪，欢迎大家一起探讨这个问题！ 补充：对于未格式化的坏块，oracle是如何判断的，我还不太清楚，正在研究。 |
Leave a Reply
You must be logged in to post a comment.