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:
-- 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.