ANBOB™

a few days ago, I to migrate oracle database from 10g(10.2.0.4) HA on hpux ia-31 to 11gR2(11.2.0.4) RAC on RHEL 6.6, when to impdp encountered two problems , here I write it to share , I hope it’s useful to you for can be save your time.

step:
expdp schemas to dumpfile
ftp dumpfile to new db server
impdp dumpfile using parallel cause

Problems 1:

$ impdp system directory=datapump dumpfile=UIM%U.DUMP logfile=uim.log parallel=8

Import: Release 11.2.0.4.0 - Production on Fri Mar 20 19:34:58 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=datapump dumpfile=UIM%U.DUMP logfile=uim.log parallel=8 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CTCARD" already exists
ORA-31684: Object type USER:"TELERPS" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-31684: Object type DB_LINK:"CTCARD"."REMOTECARD.US.ORACLE.COM" already exists
ORA-31684: Object type DB_LINK:"TELERPS"."CARD_MANAGER.US.ORACLE.COM" already exists
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CTCARD"."RES_IMSI_NDC"                     1003. MB 9925548 rows
. . imported "CTCARD"."RES_IMSIHISTORY"                  1.193 GB 14096860 rows
. . imported "CTCARD"."RES_IMSI"                         855.1 MB 9925792 rows
ORA-31693: Table data object "CTCARD"."RES_IMSI1104IMPORTANT" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/home/oracle/datapump/UIM04.DUMP" for read
ORA-19505: failed to identify file "/home/oracle/datapump/UIM04.DUMP"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Cause:
The issue is due to I am using parallel clause, when impdp using ‘parallel’ clause,
the impdp connections are distributed to both the nodes (note expdp similar problem) ,
and the dump file directory not being accessible from all nodes in the RAC, so Due to this we receive this error.
If you want to make sure it can be checked the session details from gv$session view.

Solution:

1. Create a directory object on clustered file system
2. impdp without using parallel clause
3. impdp with cluster=n option

# Added cluster=N parameter and impdp again
Problems 2:

$ impdp system directory=datapump dumpfile=UIM%U.DUMP logfile=uim.log parallel=8 cluster=n
Import: Release 11.2.0.4.0 - Production on Fri Mar 20 19:46:45 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 
...
. . imported "TELERPS"."WRITE_LOG":"RPSREPORT_PART_1408"  1.245 MB    1952 rows
. . imported "TELERPS"."WRITE_LOG":"RPSREPORT_PART_1311"  1.363 MB    2211 rows
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.MOVE_DATA [TABLE_DATA:"TELERPS"."WRITE_LOG":"RPSREPORT_PART_1409"] 
SELECT flags, NVL(target_xml_clob,xml_clob) FROM "SYSTEM"."SYS_IMPORT_FULL_01" WHERE  process_order = :1
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9721

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x5077dfb08     21979  package body SYS.KUPW$WORKER
0x5077dfb08      9742  package body SYS.KUPW$WORKER
0x5077dfb08     16536  package body SYS.KUPW$WORKER
0x5077dfb08      4549  package body SYS.KUPW$WORKER
0x5077dfb08     10464  package body SYS.KUPW$WORKER
0x5077dfb08      1824  package body SYS.KUPW$WORKER
0x50afa40a8         2  anonymous block

... had truncated
. . imported "TELERPS"."WRITE_LOG":"RPSREPORT_PART_1502"  935.4 KB    1386 rows
. . imported "TELERPS"."WRITE_LOG":"RPSREPORT_PART_1212"  236.4 KB     353 rows
...
... had truncated

Cause
The problem is caused by:
Bug 18082965 – ORA-22924 AND ORA-1555 IN 11.2.0.4 ON IMPDP OF PARTITION WITH PARALLEL=3 OR MORE

Workers from an Import Data Pump job with PARALLEL > 2 into an 11.2.0.4 instance fail with ORA-22924 and ORA-1555 when importing a partition from a 10gR2 dumpfile.
The partition itself is not the cause: starting the import job all over again, the ORA-22924 and ORA-1555 can occur on a different partition.

Solution

1, apply patch 18082965
2, impdp without using parallel clause

— that is all —

References:
IMPDP – ORA-01555 ORA-22924 (snapshot Too Old) In 11.2.0.4 With PARALLEL=3 Or More And 10g Dumpfile (文档 ID 1628103.1)