Duplicate a 11gR2 Database with Rman — the one where we duplicate from a backup.

This time I’m going to provide the steps involved to create a duplicate of a database, with RACOne and ASM in place.. There are more examples out there, but THIS version also repect the brave DBA’s who have an RAC installation on ASM in place.

Of course, in order to do this, make sure the backup is done WITH the SPFile and Control files included, otherwise this exercise will be very short ;-).

When using this method, the database which you like to duplicate from (the “TARGET” in Oracle terms) is not being impacted with any load, since we are going to use the backup. In my case, although I have the FRA on ASM,  I need to have the backup available on DISK. Also I have a retention of 1 day. The method I use here, can still apply on databases with a higher retention, but be aware: the duplicate of the FRA (later on in this post, will copy the WHOLE FRA). but I digress..more on this later, with a small work-around provided.

What we are trying to achieve:

– A duplicate of a running RAC database, on another RAC server with another name, with the files in ASM.

Steps we are going to do:

– Make the source available to the new server.
– Prepare the environment/new database.
– Duplicate database.

In my case the backup is in the FRA in ASM. To get this out, you can either get the latest backup set and copy this out with ASMCMD to a disk outside of ASM. It is a bit of work, but doable. I can get away with the following: a complete duplicate of the FRA on a shared disk which the other server can read. This is working since I have a retention of 1 day, and enough space on the shared disk to hold the backup. As long as there is enough space to hold the FRA, this wil work:

Set the ORACLE_SID to the target database (i.e.: the database you want to clone) and start rman:

oracle$ EXPORT ORACLE_SID="orgDB"
oracle$ rlwrap rman TARGET /

RMAN> backup recovery area to destination '/mnt/sharedDisk/orgDB';
piece handle=/mnt/sharedDisk/orgDB/backupset/2013_03_18/o1_mf_annnn_TAG20130318T040003_8nfymxf4_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-MAR-13
channel ORA_DISK_1: backup piece complete, elapsed time: 00:01:05
channel ORA_DISK_1: input backup set: count=1868, stamp=810360105, piece=1
channel ORA_DISK_1: starting piece 1 at 18-MAR-13
channel ORA_DISK_1: backup piece +FRAORGDBDG/orgdb/backupset/2013_03_18/nnndf0_tag20130318t040145_0.261.810360107
<snip>
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
Finished backup at 18-MAR-13

Starting Control File and SPFILE Autobackup at 18-MAR-13
piece handle=+FRAORGDBDG/orgdb/backupset/autobackup/2013_03_18/s_810391182.258.810391183 comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAR-13
RMAN>

Now we have the backup, we leave the source (aka TARGET) database alone and in peace (“piece(s)” huh-huh-huh! 😉 ).

Log into the new server and set the ORACLE_SID to the new database name. I use EXPDB02 as the new SID name.

oracle$ EXPORT ORACLE_SID="EXPDB02"

Assuming this is a new database (otherwise: clean up first 😉 ) we need to create a fresh pfile to be able to start the database in mount mode.

I prefer to create this file in the $ORACLE_HOME/dbs dir.

Create a file in the above dir with this as content:

$ cat /oracle/base/dbhome01/dbs/initEXPDB02.ora
DB_NAME=EXPDB02
$

We will first restore the spfile, then create a pfile with the spfile as source, alter it a bit and recreate a spfile to accommodate the new settings.

Startup the new database in nomount mode with the new spfile:

$ rlwrap sqlplus / as sysdba

$ startup nomount;

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
SQL>

Exit the sql prompt, and start a session with RMAN:

$ rlwrap rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Mar 18 12:17:53 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: EXPDB02 (not mounted)
RMAN>

Now: restore the spfile..

RMAN>restore spfile to pfile '/oracle/base/dbhome01/dbs/initEXPDB02.ora' from '/mnt/sharedDisk/orgDB/backupset/2013_03_18/s_810391182.258.810391183';

Starting restore at 18-MAR-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /mnt/sharedDisk/orgDB/backupset/2013_03_18/s_810391182.258.810391183
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 18-MAR-13

RMAN> exit

The pfile is created in the requested location, and we need to make the following edits:

1: Change the entry:

CLUSTER=TRUE
to
CLUSTER=FALSE

2: Remove the entry

*.remote_listener='oldRACServer:1521'

Otherwise this database will attach itself to this listener.

3. Search and replace the name of the database to the new name..
orgDB_1 becomes EXPDB02 (no trailing number, this indicates a node of a RAC).

4. Change the AUDIT_FILE_DEST entry to the new location, create dir if not exist on OS:

*.audit_file_dest='/oracle/base/expdb02/admin/expdb02/adump'

5. Change ASM file locations:

OLD:

*.control_files='+DATA/orgDB/controlfile/current.261.789129455','+DATA/orgDB/controlfile/current.260.789129455'

NEW:

*.control_files='+DATAEXP/expdb02/controlfile/current.261.789129455','+FRAEXP/expdb02/controlfile/current.260.789129455'

OLD:

*.db_create_file_dest='+DATA'

NEW:

*.db_create_file_dest='+DATAEXP'

6. Change the CORE_DUMP_DEST entry to the new location, create dir if not exist on OS:

Before:

*.core_dump_dest='/oracle/base/orgDB/diag/rdbms/orgDB/orgDB_1/cdump

After:

*.core_dump_dest='/oracle/base/expdb02/diag/rdbms/expdb02/expdb02_1/cdump'

7. Change the db_name:

*.db_name='orgDB to *.db_name='EXPDB02'

8. Change the db_recovery_file_dest:

*.db_recovery_file_dest='+FRA' to *.db_recovery_file_dest='+FRAEXP'

9. Change diagnostic_dest:

*.diagnostic_dest='/oracle/base/orgDB' to *.diagnostic_dest='/oracle/base/expdb02'

Make sure the directory actually exists.

10: Drop/replace the line

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orgDBXDB)

11. Change the local_listener to the CURRENT host:

*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=190.X.XXX.XXX)(PORT=1521))))'

12. REMOVE *.service_names=’racone’

Save the new pfile, and shutdown the running instance of the new database, and use this pfile to start it again in NOMOUNT mode:

$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 18 14:27:52 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Real Application Testing options

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Real Application Clusters and Real Application Testing options

$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 18 14:28:37 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/oracle/base/dbhome01/dbs/initEXPDB02.ora';
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2245480 bytes
Variable Size            5536484504 bytes
Database Buffers         3003121664 bytes
Redo Buffers                9723904 bytes

Create a spfile from the pfile:

SQL>  create spfile from pfile;

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>

Now we are going to start the database with the new SPFILE:

SQL> startup nomount
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2245480 bytes
Variable Size            5536484504 bytes
Database Buffers         3003121664 bytes
Redo Buffers                9723904 bytes
SQL> exit

The instance preparation is done.
Start RMAN:

$ rlwrap rman auxiliary /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Mar 18 15:22:41 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to auxiliary database: EXPDB02 (not mounted)

RMAN> DUPLICATE DATABASE TO EXPDB02
2> backup location '/mnt/backSrv/orgDB/backupset/2013_03_18'
3> NOFILENAMECHECK;

Starting Duplicate Db at 18-MAR-13

contents of Memory Script:
{
sql clone "alter system set  control_files =
''+DATAEXP/expdb02/controlfile/current.257.810401569'', ''+FRAEXPDG/expdb02/controlfile/current.256.810401569'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set  db_name =
''orgDB' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set  db_unique_name =
''EXPDB02'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from  '/mnt/backSrv/orgDB/backupset/2013_03_18/s_810391182.258.810391183';
alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATAEXP/expdb02/controlfile/current.257.810401569'', ''+FRAEXPDG/expdb02/controlfile/current.256.810401569'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''orgDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''EXPDB02'' comment= ''Modified by RMAN duplicate'' scope=spfile
<SNIP>
datafile 31 switched to datafile copy
input datafile copy RECID=30 STAMP=810404882 file name=+DATAEXP/expdb02/datafile/md_indx.278.810402287

contents of Memory Script:
{  
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 18-MAR-13

RMAN> exit

Recovery Manager complete.
[oracle@bedr-odb0002 dbs]$ ps -ef|grep -i pmon
oracle   21289     1  0 Mar15 ?        00:00:40 asm_pmon_+ASM1
oracle   29448     1  0 16:27 ?        00:00:02 ora_pmon_EXPDB02
oracle   29483 19551  0 20:35 pts/4    00:00:00 grep -i pmon

And that’s it. At least, as long as you don’t have any errors that is..

The last action is to convert this database back to RAC/RACOne (which is done in this example).

Shutdown the instance, and open the pfile for editing.

Change/add the following lines:

*.remote_listener='bedr-odb0002:1521'
instance_number=1
*.cluster_database=TRUE

Save the file, and start the database with this pfile.

SQL> startup pfile='$ORACLE_HOME/dbs/initEXPDB02.ora';
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2245480 bytes
Variable Size            5536484504 bytes
Database Buffers         3003121664 bytes
Redo Buffers                9723904 bytes
Database mounted.
Database opened.

SQL> select instance_name from gv$instance;

INSTANCE_NAME
----------------
EXPDB02

SQL> select instance_number from gv$instance;
INSTANCE_NUMBER
---------------
1

SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Now we startup for the last time, but with SPFILE:

SQL> startup
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2245480 bytes
Variable Size            5536484504 bytes
Database Buffers         3003121664 bytes
Redo Buffers                9723904 bytes
Database mounted.
Database opened.
SQL>

And the job…..is done.

Advertisements

About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases, Technical Stuff and tagged , , , . Bookmark the permalink.

2 Responses to Duplicate a 11gR2 Database with Rman — the one where we duplicate from a backup.

  1. fam says:

    Wonderful Post Gems,

    I didn’t get create spfile from pfile you have mentioned in last step shouldn’t we put spfile to shared location/ASM location

    Thanks

    • Hi!

      Yes, you are right, it is better to have the spfile created in a shared location (even mandatory for a RAC). I was working with an standalone installation during this blog, and for a standalone every location will suffice, if it’s reachable for the oracle engine..hence this “mistake”..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s