0

Context: 2 DBs in ORACLE Cloud (free) account, version 21c and version 19c 1 ORACLE 23c instance in Linux VM instance running on a desktop computer.

  • Doing expdp and impdp of a specific schema from home computer instantclient_19_8 installation, between the 2 cloud instances (from 21 to 19 version) works without problem (using SCHEMA mode thus),
  • then downloading the 2 dmp files with cURL, into the folder shared with the Linux VM
  • and then move them to the DATA_PUMP_DIR of the ORACLE 23c instance
  • and doing another impdp locally in the VM, fails with ORA-39246.

I generated the trace file (trace=1ff0300) and there is no data corruption reported, just this error as if the dmp set was incomplete while there are only 2 dmp files, and they are both referenced in the 2 trace files.

The expdb of the Cloud is done with ADMIN account, and the impdp on VM 23c is done using SYSTEM. The impdp on VM using a remapping of the DATA tablespace to USERS one.

I also tested with a newly created DATA tablespace w/o remapping but also without success.

Does somebody have any idea - steps to accomplish - to get a better diagnostic of the root cause?

I also loaded the schema from SQL files and then run expdp/impdp locally and of course this works, problem only when using the DMPs downloaded from the Cloud storage.

(all these are used for testing purposes only, so nothing dramatic)

Improve this question

2 Answers 2

Reset to default
1

Apparently it's the execution of the script

@?/rdbms/admin/catdevrol.sql

that was apparently missing in the current 23c Developer VM and that solves the problem. Found as side effect of trying to test the Javascript module definition for which I also got trouble but https://www.thatjeffsmith.com/archive/2023/04/oracle-23c-free-developer-release-mle-js-modules-snippets/ gives this solution and it seems to also solve the impdp problem. So eventually the problem was indeed related to permissions, but ones that should be there by default, not ones that one could forget to grant.

Improve this answer
-1

ORA-39246: cannot locate master table within provided dump files It seems this is a bug?

Have you tried importing the dump in Cloud DB under different schema? Also on VM, please ensure permission, ownership, pdb-cdb/environment is set properly

Improve this answer
5
  • On Cloud, I import in a different DB so by def. it's a different schema but with the same name, and that's what I am also trying to do in the VM. On the VM, impdp of the Cloud's DMP fails also under the schema owner name, while the test expdp/impdp under that name succeeds. (For the failing test, I see the Master Table create statement: CREATE TABLE "SYSTEM"."SYS_IMPORT_FULL_01" in the .trc file...)
    – p3consulting
    Commented May 4, 2023 at 13:55
  • can you share the par files?
    – Aakash
    Commented May 4, 2023 at 14:17
  • Don't use a par file: use cmd line, for the impd the args are: REMAP_TABLESPACE:DATA:USERS (but I have tried without after creating a DATA tablespace) version=19 exclude=cluster,indextype,db_link logfile=schema.log dumpfile=schema01.dmp schema02.dmp (I have also tried with the original schema%U.dmp) content=DATA_ONLY directory=DATA_PUMP_DIR trace=1ff0300
    – p3consulting
    Commented May 4, 2023 at 14:39
  • TRC files: ufile.io/f/ohbl7
    – p3consulting
    Commented May 4, 2023 at 14:42
  • I redo a test with "content=DATA_ONLY" and "table_exists_action=TRUNCATE" and this time it worked. ... have to continue to investigate...
    – p3consulting
    Commented May 6, 2023 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.