19c pluggable db import/export appears broken by data_pump_dir

[chrisns]

As you can see from the below the presence of the data_pump_dir made in the default install appears to stop any export/import of pluggable databases even when i make and specify a different directory from the default.

docker run --name oracle19 container-registry.oracle.com/database/enterprise:19.3.0.0

wait for DATABASE IS READY TO USE!

docker exec -ti oracle19 bash
./setPassword.sh oracle
bash-4.2$ expdp system/oracle@orclpdb1

Export: Release 19.0.0.0.0 - Production on Fri Aug 26 10:17:54 2022
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-12801: error signaled in parallel query server P002
ORA-06512: at "SYS.KUPF$FILE", line 4809
ORA-06512: at "SYS.KUPF$FILE", line 4783
ORA-65100: missing or invalid path prefix - /opt/oracle/admin/ORCLCDB/dpdump/E6FC41838DEA1227E053020011AC3834
ORA-06512: at "SYS.KUPF$FILE", line 4771
ORA-06512: at "SYS.KUPM$MCP", line 4422

ORA-39097: Data Pump job encountered unexpected error -12801

bash-4.2$ sqlplus / as sysdba
SQL> ALTER SESSION SET CONTAINER=orclpdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/oracle/admin/ORCLCDB/dpdump/';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;
SQL> exit;

bash-4.2$ expdp system/oracle@orclpdb1 directory=dump_dir

Export: Release 19.0.0.0.0 - Production on Fri Aug 26 11:04:38 2022
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-12801: error signaled in parallel query server P002
ORA-06512: at "SYS.KUPF$FILE", line 4809
ORA-06512: at "SYS.KUPF$FILE", line 4783
ORA-65100: missing or invalid path prefix - /opt/oracle/admin/ORCLCDB/dpdump/E6FC41838DEA1227E053020011AC3834
ORA-06512: at "SYS.KUPF$FILE", line 4771
ORA-06512: at "SYS.KUPM$MCP", line 4422

ORA-39097: Data Pump job encountered unexpected error -12801

bash-4.2$ sqlplus / as sysdba
SQL> drop directory data_pump_dir;
SQL> exit;

bash-4.2$ expdp system/oracle@orclpdb1 directory=dump_dir

Export: Release 19.0.0.0.0 - Production on Fri Aug 26 11:08:01 2022
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@orclpdb1 directory=dump_dir
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /opt/oracle/admin/ORCLCDB/dpdump/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 26 11:09:03 2022 elapsed 0 00:00:59

# now it works

🚨🚨🚨Caveat: I don't have a clue about anything oracle 🚨🚨🚨

so its more likely that I'm doing this entirely wrong, but I don't imagine I'll be the first to come across this

[abhisbyk]

@chrisns I am unable to reproduce this.

bash-4.2$ expdp system/oracle@ORCLPDB1

Export: Release 19.0.0.0.0 - Production on Mon Sep 5 11:14:47 2022
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@ORCLPDB1
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /opt/oracle/admin/ORCL1/dpdump/E7ED25A593770AC4E0536402000AD013/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Sep 5 11:15:19 2022 elapsed 0 00:00:30

It seems your data_pump_dir is corrupted somehow. Can you please retry it ? If the problem is reproducible, please share more about your environment etc.

[LvargaDS]

My issue with:

ORA-39097: Data Pump job encountered unexpected error -12801

problem was in btrfs with overlay2 storage driver. It seems as unsupported configuration. I have also tried to remove all from /var/lib/docker directory and apply btrfs storage driver, but problem persisted.

Workaround for me: Creating new ext4 partition for /var/lib/docker mountpoint with default storage driver (removed line "storage-driver": "btrfs", from /etc/docker/daemon.json file; beware, just changing key to something else, does not work) solved issue.

NOTE: I have download all my images and lost all my containers three times through process, so be warned.

[dowhiletrue]

I have the exact same issue ORA-65100: missing or invalid path prefix, using btrfs driver in podman. The issue does not occur on oracle 21c.