Copy Oracle database 10g to 12c

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.

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".

• make full export on source database:

set oracle_home=c:\oracle\product\10.2.0\db_1


set oracle_sid=sourcedb


c:\oracle\product\10.2.0\db_1\bin\exp system/pass buffer=1200000 file=d:\dump\full_sourcedb.dmp log=full_sourcedb.log full=y
consistent=y


copy full_sourcedb.dmp to the target machine

• create new target database "targetdb" using dbca (custom database - select or remove all components what you need oracle spatial etc.)

• choose the right character set. You can find out character set on your sourcedb by this query:

select parameter, value from nls_database_parameters where parameter
like '%CHARACTERSET%';

• on the target machine run following command. It will produce the log with sql stamenets used by import, where you can find the create statements for creating tablespaces.

You have to edit them (with new datafiles locations) and run manually before import because old import cannot remap datafiles:


create tablespace "TEST" blocksize 8192 datafile
'd:\oradata\targetdb\test01.ora' size 2000m extent management
local autoallocate online permanent nologging segment space
management auto;


etc.

• after all tablespaces are created, you can run import on the target machine:

imp system/pass full=y log=d:\dump\full_import_sourcedb.log
file=d:\dump\full_sourcedb.dmp

check out d:\dump\full_import_sourcedb.log for errors

• after sucessful import on the target machine:

--recompile objects


@%ORACLE_HOME%/rdbms/admin/utlrp.sql


--check out all database components


select owner, object_type, object_name from dba_objects where status != 'VALID' order by 1,2,3;


--gather statistics
shutdown immediate startup


begin

dbms_stats.gather_database_stats(
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate_percent => 100,
cascade => TRUE );


end;


/

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.

StackExchange Database Administrators Q#140164, answer score: 3