sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-08103时expdp或exp的使用方法和解决办法

时间:2016-11-28 21:32   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库出现报错ORA-08103,在出现该报错时expdp或exp的使用方法和解决办法。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: how to fix ora-08103?

模拟ora-8103错误测试在该种情况下的expdp和exp

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   Oracleoracleplus.net 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> !
[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
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

[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

使用bbed 将该block 修改为坏块,然后重启实例。

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
当然,最后你可以使用cats 或基于rowid方式将数据抽取出来,然后将表rename,我这里模拟的ora-08103可能跟实际遇到的情况有些差别,群中的网友说使用expdp是可以进行导出的,我这里模式发现是不行的,有点怪,欢迎大家一起探讨这个问题!

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-08103时expdp或exp的使用方法和解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1336.html

Oracle研究中心

关键词:

Oracle报错ORA-08103解决办法

ORA-08103时expdp或exp的使用方法