Trying to copy a schema using impdp

Question

I'm trying to copy a SCHEMA in its entirity using oracle's IMPDP tool. I've setup a database link back to itself and the link works fine. Then I'm running this:

impdp username/password@instancename schemas=TARGET_SCHEMA 
      network_link=LINK_BACK_TO_TARGET 
      directory=HOME_DIR logfile=IMPDP_COPY_BACKUP.log 
      remap_schema=TARGET_SCHEMA:SOURCE_SCHEMA 
      TRANSFORM=STORAGE:n:table;

Everything works fine in my two tests systems, but in Oracle 11gR2 production, it gets to the moving of tables and produces this:

Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in 
    KUPW$WORKER.UPDATE_TD_BASE_PO_INFO [UPDATE 
    "ADMIN"."SYS_IMPORT_SCHEMA_04" mtu SET 
    mtu.base_process_order = NVL((SELECT mts1.process_order 
    FROM "ADMIN"."SYS_IMPORT_SCHEMA_04" mts1 WHERE 
    mts1.process_order > 0 AND mts1.duplicate = 0 
    AND mts1.object_schema = mtu.base_object_schema 
    AND mts1.object_name = mtu.base_object_name AND 
    mts1.object_type = mtu.base_object_type AND 
    mts1.processing_state != :1 ), 
    (-1000 - (SELECT MIN(mts2.process_order) FROM 
    "ADMIN"."SYS_IMPORT_SCHEMA_04" mts2 WHERE 
    mts2.process_order > 0 AND mts2.duplicate = 0 
    AND mts2.object_schema = mtu.base_object_schema 
    AND mts2.object_name = mtu.base_object_name 
    AND mts2.object_type = mtu.object_type))) 
    WHERE mtu.process_order > 0 AND mtu.duplicate = 0 
    AND mtu.object_type = 'TABLE_DATA' 
    AND mtu.processing_status = :2]
ORA-01427: single-row subquery returns more than one row

I've tried it a few times, but I'm clueless whats going on.

I'm also open to other ways to clone a schema.

Answer 1

If you just want to get the job done and not worry about why it works on test but not on production then the old export/import methods will work. You don't mention an operating system so I assume Windows because that's what I work on. Something like:

REM from a DOS .bat script located on the server 

SET oracle_sid=YourDatabaseName 

REM  export the schema
C:\app\YourOraclePath\bin\exp SchemaOwner/SchemaPass@SchemaName \
   file=(C:\Dump\SchemaName01,C:\Dump\SchemaName02,C:\dump\SchemaName03) \
   filesize=1GB log=C:\dump\Logs\SchemaName.log

REM drop and recreate the new user if they have existing objects
REM otherwise comment out this line

sqlplus /nolog @C:\Dump\DropRecreateUsers.sql

imp NewSchemaOwner/NewSchemaOwnerPassword \
  LOG=C:\dump\logs\importNewSchemaOwner.log file=SchemaOwner01.dmp \
  fromuser=SchemaOwner touser=NewSchemaOwner

This does not address your error using the data pump which is likely to be connected to the difference between test and production. Nor does it address the wisdom of importing data sets into production from production. If this operation fails what are the consequences?