This discussion is archived
7 Replies Latest reply: Jan 18, 2013 6:09 PM by TSharma-Oracle RSS

IMPDP: The job SYS_IMPORT_FULL_01 has halted due to a fatal error

908635 Newbie
Currently Being Moderated
Hi,

I'm having problems importing a database whose export was successful.
The tool I'm using is expdp and impdp.
The source schema is in a tablespace different from the target schema, but in the same instance of the Oracle database.
I granted the role of EXP_FULL_DATABASE for the source user and granted the role IMP_FULL_DATABASE to the target user.

Specifically, the following happens: I can export the schema and can successfully import the first four tables to the target schema. So the impdp provides the following message:

Processing object type SCHEMA_EXPORT / TABLE / TABLE_DATA
. . imported "BDE_INEA_DES". "GPL_DECLIV_FRAGIL" 328.4 MB 668 706 lines

. . imported "BDE_INEA_DES". "GLN_CURVA_NIVEL" 336.3 MB 124 324 lines

. . imported "BDE_INEA_DES". "GPL_APP_10" 2920 lines 238.7 MB

. . imported "BDE_INEA_DES". "GLN_CURVA_NIVEL_10" 200.8 MB 15 344 lines

The job "BDE_INEA_DES". "SYS_IMPORT_SCHEMA_01" was halted due to a fatal error at 11:52:41

I've tried exporting using SYSTEM user and the user's schema source owner. I tried the same procedures with the import, but without success.

Information of my OS:
Windows Server 2008 R2 x64

Information of Oracle database:
SQL> SELECT * FROM V $ VERSION;

BANNER
-------------------------------------------------- ------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL / SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
  • 1. Re: IMPDP: The job SYS_IMPORT_FULL_01 has halted due to a fatal error
    908635 Newbie
    Currently Being Moderated
    I found that the problem was related to certain tables that are imported to the interrupted job causing a fatal error. Just do not understand why Oracle does not indicate any problem in export nor import.

    I did the import again excluding the problematic tables:

    impdp source_user/target_user@instance directory=DATA_PUMP_DIR DUMPFILE=20130116_source_schema_%U.DMP LOGFILE=20130117_target_schema_IMP.log EXCLUDE=TABLE:\"IN\(\'GPL_SUSCEPT_INCENDIO_EA\',\'GPL_AREA_POT_RESTAURACAO_EA\',\'GPL_PRIORIT_CONSERV_EA\',\'GPL_PERMEABILIDADE_ECO_EA\'\)\" REMAP_SCHEMA=source_schema:target_schema

    Now my problem is to identify what's wrong with these tables, since they are apparently "normal".
  • 2. Re: IMPDP: The job SYS_IMPORT_FULL_01 has halted due to a fatal error
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post the complete expdp command used. Are there any errors in the database alert log file ?

    HTH
    Srini
  • 3. Re: IMPDP: The job SYS_IMPORT_FULL_01 has halted due to a fatal error
    908635 Newbie
    Currently Being Moderated
    My expdp command was:

    expdp source_user/source_user_password@instance directory=DATA_PUMP_DIR DUMPFILE=20130116_source_schema_%U.DMP LOGFILE=20130116_source_schema_EXP.log schemas=source_schema

    Where can i find the database alert log file?
  • 4. Re: IMPDP: The job SYS_IMPORT_FULL_01 has halted due to a fatal error
    Dean Gagne Expert
    Currently Being Moderated
    I would run your import command with tracing turned on. This way, the trace file should contain more information about what was happening.

    "There is an MOS note on tracing, but I would turn on the worker trace bit (minimally)".

    Some times there is a fatal error that tears down the process and since the process would be reporting the error back to the user, if this process goes away, the error goes with it. With tracing, the trace file should contain information on what was happening at the time. May not answer the question, but usually does help.

    These types of errors do happen occasionally and they are very difficult to debug. Is this repeatable?

    Dean
  • 5. Re: IMPDP: The job SYS_IMPORT_FULL_01 has halted due to a fatal error
    908635 Newbie
    Currently Being Moderated
    Yes. I had the same problem in a IBM AIX 5.3 server using the same source_schema.
    The only solution was also excluding the problematic tables. But apparently they are all right.

    They are all spatial tables and are ok.

    I will try to trace the problems using impdp + tracing on and post the outputs here.

    Thanks.
  • 6. Re: IMPDP: The job SYS_IMPORT_FULL_01 has halted due to a fatal error
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    For database alert log, pl see these links

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/startup.htm#CNCPT89063
    http://docs.oracle.com/cd/E11882_01/server.112/e25494/diag005.htm#ADMIN11267

    HTH
    Srini
  • 7. Re: IMPDP: The job SYS_IMPORT_FULL_01 has halted due to a fatal error
    TSharma-Oracle Guru
    Currently Being Moderated
    Follow these steps. You will get exactly what you are trying to find.

    1) spool invalidobj_and_registry.txt

    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;
    SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
    FROM dba_objects
    WHERE status != 'VALID'
    ORDER BY 4,2;
    spool.off

    2) $ sqlplus "/as sysdba"
    sql>EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
    sql>EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

    3) Run your import with these two extra parameters along with your parameters

    CONTENT=METADATA_ONLY METRICS=Y TRACE=480300

    4) As soon as impdp job started:

    -- In SQL*Plus, obtain Data Pump processes info:

    CONNECT / as sysdba

    select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
    from v$session s, v$process p, dba_datapump_sessions d
    where p.addr=s.paddr and s.saddr=d.saddr;

    -- Get the sid and serial# for DM00 and DW01 and execute:

    exec DBMS_SYSTEM.SET_EV([SID],[SERIAL#],10046,12 ,'');

    for ***both*** of them.

    After hang is noticed please leave the import running for one more hour and kill it.

    Please check:
    - alert log
    - impdp log file
    - trace files generated during import time in bdump directory

    ++ before restarting the impdp check if there are orphan Data Pump jobs left in database. Use Note 336014.1 - "How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points