Migrate a remote oracle database (11g) into a docker oracle XE database (11g)

dockeroracleoracle-11g-r2

I am brand new to Oracle and I am trying to migrate an existing VM that has deployment scripts run on it every release to use a docker container instead. Then I can run the release scripts and tag a new container each release (to be used for our integration testing).

Unfortunately it seems like no matter what I try (exp, expdp, generating ddl from datagrip, generating ddl from data studio, expdp in toad) it is impossible to fully copy a remote database into the XE database without a ton of errors in the packages or key constraint errors.

What am I missing?

Here's what I've done so far:

expdp user/pass@XE directory=dp_dir FULL=YES dumpfile=8_17.dmp logfile=8_17.log network_link=XE_BUILD content=ALL TRANSPORT_FULL_CHECK=YES

This creates a dump file that seems like it should be valid.

Then I do this;
impdp importer/importer@XE directory=dp_dir full=Y dumpfile=8_17.dmp logfile=imp_full.log

This also completes with a relatively small number of errors. However I constantly get key constraint errors and broken packages that I don't experience when using the remote database.

I've tried rebuilding all package and things like that, still no luck.

Best Answer

A child table may be exported at some point of time, let's say 12:00. Then as expdp goes on, the parent table may be exported minutes later, let's say at 12:05. Your dump file contains the content of those tables from different points of times.

Yes, if those tables were changing during that 5 minutes, their relation may become inconsistent and you will get key constraint errors. The child table may have references on parent entries that do not exist.

That is why you should use the flashback_scn or flashback_time or consistent=y parameters for expdp. With them, everything will be exported as they existed at 1 specific SCN or point of time. Not using these parameters is a very common mistake.

Then for the packages. No, not even a FULL=Y expdp exports everything. Most of SYS objects are not exported.

Let's say your packages use the SYS.DBMS_LOCK package. By default, users are not able to use that package, because they need to have the EXECUTE privilege granted on it. As the package was not exported, it will not be imported. Since DBMS_LOCK will not be imported, privileges granted on it will not be imported as well. So for this specific package, you need to grant the required privilege manually.

That was just 1 example. Then there is SYS.DBMS_CRYPTO and the others.

The "broken packages" part is just too broad to answer here. You are the one with access to the packages and the code, you are the one who can check the errors.

Related Question