4

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.

  • 1
    If you can afford downtime , datapump impdp/expdp seems the easiest way to me. – a1ex07 Jun 1 '16 at 20:04
  • Do you want to copy data from 10g to an existing 12c database or you want to upgrade 10g to 12c? Is 12c on the same server? – teo Jun 2 '16 at 4:22
  • I added it to the question. Is transportable import/export a good option? – r0tt Jun 2 '16 at 7:24
  • Before I post my answer. Specify exact 10g version (e. g. 10.2.0.4, 10.2.0.5). Is it production database - do you want to migrade with minimal downtime or it does not matter? Which edition of database do you have - Enterprise, Standard? Transportable tablespaces are available only in Enterprise edition. – teo Jun 3 '16 at 4:56
  • I added it to the question. I think I have to do a full export data pump with the consistent option. This way I don't need downtime at production side. Correct? – r0tt Jun 3 '16 at 6:57
3

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

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

  1. 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%';

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

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

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

  • Did you try my recipe? How it works? – teo Jun 10 '16 at 3:47
0

I used Oracle datapump expdp/impdp. On the source database using expdp:

SQL*PLUS:
CREATE USER username IDENTIFIED BY pw;
GRANT dba TO username;
CREATE DIRECTORY db_copy AS ‘E:\DB_copy’;
GRANT read, write ON DIRECTORY db_copy TO username;

CMD:
expdp username/pw DIRECTORY= db_copy DUMPFILE=filename_%u.dmp 
filesize=5gb FULL=Yes METRICS=Y EXCLUDE=STATISTICS 
flashback_time=systimestamp LOGFILE=dbname_exp.log

After transfering the datapump, on the target database using impdp:

(Create a backup before starting the import.)

SQL*PLUS:
CREATE USER username IDENTIFIED BY pw;
GRANT dba to username;
CREATE DIRECTORY db_copy AS ‘E:\DB_copy’;
GRANT read, write ON DIRECTORY db_copy TO username;

First I went for remaping the datafile – has follows within the impdp parameter file:

REMAP_DATAFILE=D:\ORACLE\ORADATA\DBNAME\DATAFILE_01.DBF:
E:\ORACLE\ORADATA\DBNMAE\DATAFILE_01.DBF

It works – but this didn’t meet the requirements for reorganising the file system structure completely. Another way is to do an import using impdp with sqlfile option. e.g.:

CMD:
impdp username/pw parfile=impdp.par


impdp.par File:

DIRECTORY=db_copy 
FULL=yes 
DUMPFILE= filename_1.dmp, filename_2.dmp…
**SQLFILE=script.sql**
LOGFILE=dbname_imp.log

From the script.sql it is possible to copy the ddl commands e.g.

...CREATE TABLESPACE tbs_02     DATAFILE 
F:\ORACLE\ORADATA\DBNMAE\DATAFILE_01.DBF, 
G:\ORACLE\ORADATA\DBNMAE\DATAFILE_02.DBF,
H:\ORACLE\ORADATA\DBNMAE\DATAFILE_03.DBF 
SIZE 500K REUSE    AUTOEXTEND ON NEXT 500K MAXSIZE 100M;

I created a script with all tablespaces and remaped the datafiles as wished. After executing the script, all tablespaces are created. Now it is possible to do the impdp again but without the sqlfile option. It will generate errors because the tablespaces already exist but the data is still imported!

CMD:
impdp username/pw parfile=impdp.par

impdp.par file:

DIRECTORY=db_copy 
FULL=yes 
DUMPFILE= filename_1.dmp, filename_2.dmp…
LOGFILE=dbname_imp.log

It creates many archivelogs. I needed to run a backup to free space. After completion I checked the logfile for errors – everything worked.

I run @?/rdbms/sadmin/utlrp.sql but it seem to be not necessary. There was no compiling done. These steps worked for me to do a copy/migration from 10g to 12c.

  • Ok. Ideally, the final result of your question should be one asnwer marked as accepted. So if some answer helps you, please mark it as accepted. – teo Jun 13 '16 at 7:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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