This content has been marked as final. Show 7 replies
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".
Pl post the complete expdp command used. Are there any errors in the database alert log file ?
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?
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?
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.
For database alert log, pl see these links
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
ORDER BY 1;
SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
WHERE status != 'VALID'
ORDER BY 4,2;
2) $ sqlplus "/as sysdba"
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.
- 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 ?