Copy schema with data pump when using editions

We have just been through a relatively tricky situation, trying to copy our test schemas from our Oracle 12c database to the new Oracle 19c test database. We were trying to use data pump to copy the schemas. The copy was ordered from operation, and the schemas was exported and imported as ordered. Before importing  both editions and the schemas was created manually. The schemas was also granted privileges to use the newly created editions. So check … test database was ready to be used? But no. Things failed in test.

It turns out that when you do an export and import, the default edition is used. To override this you can use the parameter SOURCE_EDITION when you export, and the parameter TARGET_EDITION when you import. The trouble is that, then you only have one certain edition. Our testing team needed to test on different edtions.

So … then we tried to export the edition we needed, for instance the editions RELEASE_2020_4 and RELEASE_2020_6, and then import RELEASE_2020_4 and RELEASE_2020_6 separately.

expdp system@orcl  DUMPFILE=ljdata_rel_2020_4.dmp DIRECTORY=data_pump_dir SCHEMAS=ljdata source_edition=release_2020_4
expdp system@orcl  DUMPFILE=ljdata_rel_2020_6.dmp DIRECTORY=data_pump_dir SCHEMAS=ljdata source_edition=release_2020_6

impdp system@orcl DUMPFILE=ljdata_rel_2020_4.dmp DIRECTORY=data_pump_dir schemas=ljdata REMAP_SCHEMA=ljdata:tstdata target_edition=release_2020_4 transform=oid:n
impdp system@orcl DUMPFILE=ljdata_rel_2020_6.dmp DIRECTORY=data_pump_dir schemas=ljdata REMAP_SCHEMA=ljdata:tstdata target_edition=release_2020_6 transform=oid:n

But again this failed. When the second import was ran, the TYPES are not created with CREATE OR REPLACE, and we get the error:

ORA-31684: Object type TYPE:"TSTDATA"."TEST_OT" already exists

So … what if I try to turn the imports around:

impdp system@orcl DUMPFILE=ljdata_rel_2020_6.dmp DIRECTORY=data_pump_dir schemas=ljdata REMAP_SCHEMA=ljdata:tstdata target_edition=release_2020_6 transform=oid:n
impdp system@orcl DUMPFILE=ljdata_rel_2020_4.dmp DIRECTORY=data_pump_dir schemas=ljdata REMAP_SCHEMA=ljdata:tstdata target_edition=release_2020_4 transform=oid:n

AND it finally worked!

 

Post a Comment

Your email is never published nor shared. Required fields are marked *