Blogs

DataPump Import Job Failing on Multitenant Environment Due to ORA-01001: Invalid Cursor

Recently I was doing the database migration using expdp/impdp from on-prem non-CDB to ExaCC PDB. 

While doing import into PDB I face the below issue.

[oracle@hostname dump]$ impdp parfile=imp_full.par
Import: Release 19.0.0.0.0 - Production on Sat Jun 3 23:32:20 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 3405
ORA-06512: at "SYS.KUPM$MCP", line 2635
ORA-39097: Data Pump job encountered unexpected error -1001

After checking on MOS I found the document that suggest the cause and solution of this problem.

CAUSE: The dataPump utility may get corrupted and we need to recreate the DataPump utility to overcome internal corruption.

The solution as per MOS: 

DataPump Job Fails To Start Due To ORA-01001: invalid cursor (Doc ID 2354428.1) and

How To Reload Datapump Utility EXPDP/IMPDP (Doc ID 430221.1)

 

On Multitenant Environment, for Oracle versions 12.1, 12.2, 18c, 19c, 21c, and 23c:

On Multitenant Environment, for Oracle versions 12.1, 12.2, 18c, 19c, 21c, and 23c:

-- 1. To rebuild the DataPump packages with the following steps.
Under the ORACLE_HOME, execute:
cd rdbms/admin

-- run the dpload.sql in the CDB with all of the PDBs open, as described in "How to execute sql scripts in Multitenant environment (catcon.pl)" NOTE 1932340.1

Syntax:
$ORACLE_HOME/perl/bin/perl catcon.pl -d /u01/oracle -l "logging directory" -b "prefix for logfile of dpload for each PDB" dpload.sql

 
Where:
-l - directory to use for spool log files; for example, /tmp
-b - base name for log and spool file names; for example, dp_rebuild_log_base_name

For example, at OS prompt

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catcon.pl -d /u01/oracle -l /tmp -b dp_rebuild_log_base_name dpload.sql

We tried the above solution as per the MOS doc but that did not work and failed with error.

catcon::sureunlink: unlink(/exaccnfs/ZDM/PDB/DUMP/log/ppb3.log) succeeded after 1 attempt(s)

catcon::sureunlink: verify that the file really no longer exists

catcon::sureunlink: confirmed that /exaccnfs/ZDM/PDB/DUMP/log/ppb3.log no longer exists after 1 attempts

catcon::delete_idle_logs: Deleting log file /exaccnfs/ZDM/PDB/DUMP/log/ppb4.log because SQL*Plus process for which
  it was created did no work

So the solution worked for us when we run the dpload.sql script from sqlplus. 

NOTE: In the CDB Environment, all PDBS must be in the open state.

@$ORACLE_HOME/rdbms/admin/dpload.sql;

After this Import started without issue.

More from this Author