Copy Oracle database from 11g ( to 12c (

Russ Suter
Russ Suter used Ask the Experts™
I have an existing Oracle database on a server that I need to copy to a new server. The old server is running version 11g Release The new server is running version 12c Release I need to keep the current database online but create a duplicate of that database on the new server. Can anyone tell me how this is accomplished?

Please be kind. I'm very familiar with Microsoft SQL Server but know practically nothing about Oracle.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russ SuterSenior Software Developer


I came here looking for help because Oracle docs are hard to follow. I've read pretty much everything you posted links to already and still don't fully understand all the steps. You have references to expdp and imppdp and rman and clonedb. I don't know what any of these tools are.
Abhimanyu SuriDatabase Engineer
Top Expert 2016

Hi Russ,

RMAN Recovery and Backup Manager is an Oracle utility, widely used to backup, restore, recover oracle databases.

Please follow steps mentioned in this article, they are pretty straight forward :

You still might want to read more about oracle internals to have a better understanding of each step.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Russ SuterSenior Software Developer


Yes, that works for backing up and restoring within the same version but does not seem to cover my case where I need to backup from 11g release and restore to 12c.
Abhimanyu SuriDatabase Engineer
Top Expert 2016

It will, take backup of database in 11g, preferably cold.

Use DUPLICATE database using backup on 12c with ‘NO OPEN’ mentioned in link.
Once done, run catupgrd.

So all you need is 12c binaries set up , pfile with right location for datafiles,control file and logfiles and Convert parameters need to be set.

I will try to provide you my own RMAN script/steps for 10g to 11g using backup (in my opinion that too should work fine), but I don't have access to it at the moment.
Russ SuterSenior Software Developer


I tried running the backup command using rman and got the following error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/22/2016 15:48:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 2048576000 limit

Open in new window

Oracle dba
Top Expert 2009
i know the feeling ... i have litle experiece in mssql
since you an mssql person, i assume you are running oracle on windows server ?

i would advice contacting a local oracle dba for consulting
you need to upgrade the database after copying it
it takes about 1/2 day for an experienced person to copy and upgrade an oracle database

but if you want to do it yourself, this is the smallest task list i could think of
i assume you have the oracle database software installed and patched on the new server ?
(the database patching is optional for this to work)

i would first start to try and copy a database.
you won't be able to open it, but you need the copying as a base

for all items in [] don't type the square brackes, but replace the text as needed

what has to be copied:
datafiles, tempfiles, controlfiles, init file (or sp file)

login on the source database server and start a cmd
set oracle_home=[directory of oracle db software]
set oracle_sid=[name of db]
sqlplus / as sysdba
-- this will list all files to copy
select name from v$datafile;
select name from v$tempfile;
-- start the backup operation
alter database begin backup;

now open an explorer and find the files
copy them to the same location on the new server
be very aware:
at this point you can actually delete files ... they is no longer a lock on the files, so don't mess up !

after copy:

alter database end backup;
alter database create standby controlfile as 'c:\temp\sby.ctl';
show parameter control_files

--> copy c:\temp\sby.ctl to the locations listed in the control_files to the new server and rename the copied files accordingly

in [oracle_home]\database you will find an init[DBNAME].ora
or an spfile[DBNAME].Ora

if it's an spfile: in sqlplus:
create pfile from spfile;

copy that init[dbname].ora to the new server
in [oracle_12_home]\database

start a cmd window on the new server
set oracle_home=[your dir for oracle 12 software]
set oracle_sid=[dbname]
oradim -new -sid [dbname]
>> you might want to create a new password file
orapwd file=%oracle_home%\database\pwd[dbname].ora password=yourpassword entries=5
sqlplus / as sysdba
startup mount
-- if it fails to start, give the warning (or try the archivelog step further down and retry)
-- at this point you'll be able to read fixed tables in the new database
select name from v$datafile;
select name from v$tempfile;

once you get here ... you have the database copied
you might have to repeat this process if something failes

the upgrade
on the source: you need to run the preupgrd tool on the source database:
you'll have to fix problems > those are noted further down in the upgrade guide
in sqlplus on the source:
alter system switch logfile;
--> this creates an extra archivelog with all the last actions

then you'll have to copy the archivelogs
(i hope your database is in archivelog mode)
on the source:select name from v$archived_log where first_time > sysdate-1;
copy the archivelogs from the source to the same destination on the new server

in sqlplus on the new server:
recover database using backup controlfile until cancel;

> this should prompt you for actions to apply log files
check the directory to see if an archivelog is available
hit enter if it is available
if no more available, type cancel and enter
--> at this point, your new database is ready for upgrade

alter database activate standby database;
alter database open upgrade;

now follow the upgrade guide again from step 11:

after all this, login to sqlplus and again:

--> at this point your database is open and ready for use
Mark GeerlingsDatabase Administrator

There are basically two options to move the contents of an Oracle11 database to an Oracle12 database:
1. Use backup, restore and upgrade  (RMAN, etc.)
2. Use export and import (DataPump export and DataPump import)

Both options are documented and supported by Oracle, but they have different advantages and disadvantages.  I'll try to summarize those here.

First for the backup, restore and upgrade approach:
1. This is usually the best (or only practical) option for large databases (multiple terabytes in size).
2.  This is usually faster than the export and import approach.
1. This can only be used when the O/S is the same (or similar) on the two servers.
2. This will leave any wasted space in tables or indexes (this varies in different Oracle databases depending on the application) so you may keep some performance problems.

Second, for the export and import approach:
1. This will remove and clean up any/all wasted space in tablespaces, tables and indexes.  So, the resulting database may perform significantly faster.
2. This can accommodate a move to a different server O/S.
1. The time required for the import may be substantial.  This may take from 2-20 times longer than the export depending on the number of indexes, foreign keys, PL\SQL objects, views, etc. in the system.
2. This is only practical on Oracle databases of small to moderate size, maybe up to a terabyte or two depending a bit on your server and storage hardware.

Both options will require that you install new Oracle software on the new system.  But, depending on which of these two options you choose, you may need both software version installed on the new system (if you use the backup, restore and upgrade option).
Russ SuterSenior Software Developer


@Geert Gruwez

I got as far as trying to log in after creating the new orapwd file but the login is not working. I'm getting:
ORA-12514: TNS:listener does not currently know of service requested in connect

Open in new window

I don't know how to get past this issue.
Mark GeerlingsDatabase Administrator

The orapwd file and this error are unrelated.  This error indicates a problem in your connection configuration files.  On the server these are usually: listener.ora and tnsnames.ora, that are usually in the $ORACLE_HOME/network/admin directory.  If you received this error on a client machine, that means that your local tnsnames.ora file doesn't match what the server is set up to accept.  

These files (not: orapwd) are just plain text files that you can view, and edit if necessary with any text editor.  Be careful though if you edit them manually, to not introduce invisible control characters (like Microsoft Word will do) and be aware that the formatting of these files is fussy.

The orapwd file has a totally different purpose and is not used for "normal" database activities.
Geert GOracle dba
Top Expert 2009

You are trying to login with @Dbname

In cmd:
Set oracle_sid=orcl
Sqlplus / as Sysdba

Orcl is THE name of Your database

That should log you in
Geert GOracle dba
Top Expert 2009

In compmgmt.msc in local user groups on the new (and old) server
Add your user to the group oradba

This might also help
Mark GeerlingsDatabase Administrator

The orapwd file is not required.  That is optional.  You can log onto a new Oracle database without creating an orapwd file.  When you installed the database software or when you created the new database (if you did those tasks separately) you were asked to provide passwords for the SYS and SYSTEM users.  Those are the administrator accounts for the database.  You should try to log on with one of those accounts and the password you set up for it.
Or, on many Oracle servers, at a command prompt, as the owner of the Oracle software, you can type:
sqlplus / as sysdba [Enter]
and that will connect you to the database.
Russ SuterSenior Software Developer


I finally got through it and learned quite a lot in the process. Ultimately, after getting around a couple of bumps this step-by-step instruction guide pretty much did the trick. Thanks!
Geert GOracle dba
Top Expert 2009

Well... Some oracle consultants have a jeep for bumpy rides :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial