exp导出数据报EXP-00056/ORA-01403错误

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:exp导出数据报EXP-00056/ORA-01403错误

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

exp导出数据报EXP-00056/ORA-01403错误

[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/chf.dmp log=/tmp/chf.log owner=chf

Export: Release 9.2.0.4.0 - Production on Sun Apr 29 03:11:31 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user CHF 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user CHF 
About to export CHF's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 1403 encountered
ORA-01403: no data found
EXP-00000: Export terminated unsuccessfully

查看组件信息

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> col comp_id for a15
SQL> col status for a7
SQL> col version for a10
SQL> col comp_name for a30
SQL> set pagesize 1000
SQL> SELECT substr(comp_id,1,15) comp_id, status, substr(version,1,10) version,
  2     substr(comp_name,1,30) comp_name FROM dba_registry ORDER BY 1;

COMP_ID         STATUS  VERSION    COMP_NAME
--------------- ------- ---------- ------------------------------
AMD             VALID   9.2.0.4.0  OLAP Catalog
APS             LOADED  9.2.0.4.0  OLAP Analytic Workspace
CATALOG         VALID   9.2.0.4.0  Oracle9i Catalog Views
CATJAVA         VALID   9.2.0.4.0  Oracle9i Java Packages
CATPROC         VALID   9.2.0.4.0  Oracle9i Packages and Types
CONTEXT         VALID   9.2.0.4.0  Oracle Text
JAVAVM          VALID   9.2.0.4.0  JServer JAVA Virtual Machine
ODM             LOADED  9.2.0.1.0  Oracle Data Mining
ORDIM           VALID   9.2.0.4.0  Oracle interMedia
OWM             VALID   9.2.0.1.0  Oracle Workspace Manager
SDO             LOADED  9.2.0.4.0  Spatial
WK              VALID   9.2.0.4.0  Oracle Ultra Search
XDB             VALID   9.2.0.4.0  Oracle XML Database
XML             VALID   9.2.0.6.0  Oracle XDK for Java
XOQ             LOADED  9.2.0.4.0  Oracle OLAP API

15 rows selected.

SQL> SELECT status, object_id, object_type, owner||'.'||object_name 
  2     "OWNER.OBJECT" FROM dba_objects WHERE owner='XDB' AND status != 'VALID' 
  3     ORDER BY 4,2;

no rows selected

做1403跟踪

SQL> ALTER SYSTEM SET EVENTS '1403 trace name errorstack level 3';

[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/chf.dmp log=/tmp/chf.log owner=chf

SQL> ALTER SYSTEM SET EVENTS '1403 trace name errorstack off';

trace文件关键内容

*** SESSION ID:(11.17) 2012-04-29 03:17:13.555
*** 2012-04-29 03:17:13.555
ksedmp: internal or fatal error
ORA-01403: no data found
Current SQL statement for this session:
SELECT xdb_uid FROM SYS.EXU9XDBUID

问题原因
因为控制文件重建或者使用历史控制文件恢复,忘记添加临时文件

SQL> select name from v$tempfile;

no rows selected

解决方法
添加临时文件

SQL> alter tablespace TEMP add tempfile
  2 '/u01/oracle/oradata/xifenfei/temp01.dbf' size 10M reuse;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
------------------------------------------------
/u01/oracle/oradata/xifenfei/temp01.dbf

验证exp导出

[oracle@xifenfei udump]$ exp chf/xifenfei file=/tmp/chf.dmp log=/tmp/chf.log owner=chf

Export: Release 9.2.0.4.0 - Production on Sun Apr 29 05:20:21 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user CHF 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user CHF 
About to export CHF's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export CHF's tables via Conventional Path ...
. . exporting table ACC_OWE_TASK_LIST_HIS_07711202       4111 rows exported
. . exporting table                   CHF_XIFENFEI        868 rows exported
. . exporting table                     PLAN_TABLE          0 rows exported
. . exporting table                        T_XFF01          0 rows exported
. . exporting table                     T_XIFENFEI          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully no warnings.
此条目发表在 逻辑备份/恢复 分类目录。将固定链接加入收藏夹。

exp导出数据报EXP-00056/ORA-01403错误》有 3 条评论

  1. 惜分飞 说:

    对是当发现ora-1403的时候做trace

  2. arong 说:

    EXP-00056: ORACLE error 1403 encounteredORA-01403: no data foundEXP-00000: Export terminated unsuccessfully

    下面是跟踪上面的1403的错误吧?
    SQL> ALTER SYSTEM SET EVENTS ’1403 trace name errorstack level 3′;System altered.

  3. 惜分飞 说:

    Oracle 9.2.0.4.0: Schema Export Fails with ORA-1403 (No Data Found) on Exporting Cluster Definitions [ID 281780.1]

    The information in this article applies to: 
    - Oracle Server - Enterprise Edition - Version: 9.2.0.4 to 9.2.0.4
    - Oracle Server - Personal Edition   - Version: 9.2.0.4 to 9.2.0.4
    - Oracle Server - Standard Edition   - Version: 9.2.0.4 to 9.2.0.4
    This problem can occur on any platform.
    
    
    ERRORS
    ------
    
    EXP-56 ORACLE error encountered
    ORA-1403 no data found
    EXP-0: Export terminated unsuccessfully
    
    
    SYMPTOMS
    --------
    
    A schema level export with the 9.2.0.4 export utility from a 9.2.0.4 or higher
    release database in which XDB has been installed, fails when exporting 
    the cluster definitions with:
    
    ...
    . exporting cluster definitions
    EXP-00056: ORACLE error 1403 encountered
    ORA-01403: no data found
    EXP-00000: Export terminated unsuccessfully
    
    
    You can confirm that XDB has been installed in the database:
    
    SQL> SELECT substr(comp_id,1,15) comp_id, status, substr(version,1,10) version,
       substr(comp_name,1,30) comp_name FROM dba_registry ORDER BY 1;
    
    COMP_ID         STATUS      VERSION    COMP_NAME
    --------------- ----------- ---------- ------------------------------
    ...
    XDB             INVALID     9.2.0.4.0  Oracle XML Database
    XML             VALID       9.2.0.6.0  Oracle XDK for Java
    XOQ             LOADED      9.2.0.4.0  Oracle OLAP API
    
    
    You create a trace file of the ORA-1403 error:
    
    SQL> SHOW PARAMETER user_dump
    SQL> ALTER SYSTEM SET EVENTS '1403 trace name errorstack level 3';
    System altered.
    
    -- Re-run the export
    
    SQL> ALTER SYSTEM SET EVENTS '1403 trace name errorstack off';
    System altered.
    
    
    The trace file that was written to your USER_DUMP_DEST directory, shows:
    
    ksedmp: internal or fatal error
    ORA-01403: no data found
    Current SQL statement for this session:
    SELECT xdb_uid FROM SYS.EXU9XDBUID
    
    
    You can confirm that you have no invalid XDB objects in the database:
    
    SQL> SET lines 200
    SQL> SELECT status, object_id, object_type, owner||'.'||object_name 
       "OWNER.OBJECT" FROM dba_objects WHERE owner='XDB' AND status != 'VALID' 
       ORDER BY 4,2;
    
    no rows selected
    
    Note: If you do have invalid XDB objects, and the same ORA-1403 error occurs
          when performing a full database export, see the solution mentioned in:
          Note:255724.1 "Oracle 9i: Full Export Fails with ORA-1403 (No Data 
                           Found) on Exporting Cluster Defintions"
    
    
    CHANGES
    -------
    
    You recently restored the database from a backup or you recreated the 
    controlfile, or you performed Operating System actions on your database 
    tempfiles.
    
    
    CAUSE
    -----
    
    The Temporary tablespace does not have any tempfiles.
    
    Note that the errors are different when exporting with a 9.2.0.3 or earlier
    export utility:
    
    . exporting cluster definitions
    EXP-00056: ORACLE error 1157 encountered
    ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
    ORA-01110: data file 201: 'M:\ORACLE\ORADATA\M9201WA\TEMP01.DBF'
    ORA-06512: at "SYS.DBMS_LOB", line 424
    ORA-06512: at "SYS.DBMS_METADATA", line 1140
    ORA-06512: at line 1
    EXP-00000: Export terminated unsuccessfully
    
    The errors are also different when exporting with a 9.2.0.5 or later export
    utility:
    
    . exporting cluster definitions
    EXP-00056: ORACLE error 1157 encountered
    ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
    ORA-01110: data file 201: 'M:\ORACLE\ORADATA\M9205WA\TEMP01.DBF'
    EXP-00000: Export terminated unsuccessfully
    
    
    FIX
    ---
    
    1. If the controlfile does not have any reference to the tempfile(s), 
       add the tempfile(s):
    
       SQL> SET lines 200
       SQL> SELECT status, enabled, name FROM v$tempfile;
       no rows selected
    
       SQL> ALTER TABLESPACE temp ADD TEMPFILE 
         'M:\ORACLE\ORADATA\M9204WA\TEMP01.DBF' REUSE;
    
    or: 
    
       If the controlfile has a reference to the tempfile(s), but the files are
       missing on disk, re-create the temporary tablespace, e.g.:
    
       SQL> SET lines 200
       SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE
         'M:\ORACLE\ORADATA\M9204WA\TEMP201.DBF' SIZE 100m AUTOEXTEND ON 
         NEXT 100M MAXSIZE 2000M;
       SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
       SQL> DROP TABLESPACE temp;
       SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE
         'M:\ORACLE\ORADATA\M9204WA\TEMP01.DBF' SIZE 100m AUTOEXTEND ON 
         NEXT 100M MAXSIZE 2000M;
       SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
       SQL> SHUTDOWN IMMEDIATE
       SQL> STARTUP
       SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
    
    2. Now re-run the export.