Solved

Copy Oracle database from 11g (11.2.0.1.0) to 12c (12.1.0.2.0)

Posted on 2016-09-21
15
108 Views
Last Modified: 2016-09-26
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 11.2.0.1.0. The new server is running version 12c Release 12.1.0.2.0. 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.
0
Comment
Question by:Russ Suter
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 41810218
0
 
LVL 20

Author Comment

by:Russ Suter
ID: 41810880
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.
0
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41811345
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 :

http://allthingsoracle.com/upgrading-a-database-using-recovery-manager-rman-duplicate-command-in-oracle-12c/

You still might want to read more about oracle internals to have a better understanding of each step.
0
 
LVL 20

Author Comment

by:Russ Suter
ID: 41811389
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 11.2.0.1.0 and restore to 12c.
0
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41811487
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.
0
 
LVL 20

Author Comment

by:Russ Suter
ID: 41811677
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:
20
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 2048576000 limit

Open in new window

0
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 41812048
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:
http://docs.oracle.com/database/121/UPGRD/preup.htm#UPGRD52880
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:
http://docs.oracle.com/database/121/UPGRD/upgrade.htm#sthref363

after all this, login to sqlplus and again:
startup

--> at this point your database is open and ready for use
1
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 41812651
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:
Advantages:
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.
Disadvantages:
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:
Advantages:
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.
Disadvantages:
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).
0
 
LVL 20

Author Comment

by:Russ Suter
ID: 41813106
@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:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Open in new window

I don't know how to get past this issue.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 41813116
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.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41813131
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
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41813145
In compmgmt.msc in local user groups on the new (and old) server
Add your user to the group oradba

This might also help
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 41813299
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.
0
 
LVL 20

Author Closing Comment

by:Russ Suter
ID: 41816299
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!
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41816867
Well... Some oracle consultants have a jeep for bumpy rides :)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now