I need to copy an Oracle 10g (10.2.0.4.0) database (150GB) to an Oracle 12c (12.1.0.2.0) database. Both are Standard Editions. What method should I use?
Target is on a different server – empty database. File system structure is different. Target is a Testserver which needs a consistent state but does not need to be up-to-date to production.
Best Answer
In your case you could not use transportable tablespace because the source database have to be Enterprise edition.
In general, I would prefer upgrade using rman. But in your case it is not so easy because direct upgrade from 10.2.0.4 to 12.1.0.2 is not supported. You would have to upgrade to intermediate release (e.g. 10.2.0.5) that can be directly upgraded to 12.1.0.2. See https://docs.oracle.com/database/121/UPGRD/preup.htm#UPGRD12359
So for you the best approach will be upgrade using old import (imp) or data pump import (impdp).
I have one recipe using old import. Upgrade using data pump import will be similar:
Assume the source database name is "sourcedb" and target "targetdb".
check out d:\dump\full_import_sourcedb.log for errors
Note:
import using data pump import (expdp, impdp) may be easier:
instead of consistent=y you have to use flashback_time="to_timestamp('04-04-2016 11:55:00', 'DD-MM-YYYY HH24:MI:SS')" .........
During import you don't have create tablespaces manually and you can use remap_datafile parameter.