Steps to clone a database using RMAN

 1: Create a password file on the destination server

2: Establish connectivity between the target and destination server (tnsnames.ora, 
     sqlnet.ora)

3: Create the directories for the database files

4: Take the RMAN backup from the target server and copy it to the destination 
      server.

5: Startup the destination database in nomount mode

6: Run the RMAN duplicate database command

Cloning the database on the remote host requires two servers. Both servers 
should have Oracle Software installed on them. Make sure the Operating System
 is the same on both the servers. On the first server, we should have one database 
which will be duplicated to the second host. The first server will be named as 
"production server" and the database on it will be called "production database
" (SID of the production database is db1). However, the second server will
 have a name as "auxiliary server" and the database which will be created
 on it will be called "auxiliary database" (SID of the auxiliary database is aux).
 The server name of the  production database is PROD_SERVER and the server 
name of the auxiliary database is DUP_SERVER

Before cloning the production database, we should configure some initial actions as follows: 

Step 1 - Create a password file for the auxiliary instance

It's possible to create a password file on the production server as well as on the auxiliary server. Because the passwords of target and auxiliary databases password files should not be similar as it's a security threat. However, it should be mentioned that when creating standby database from RMAN, the password file has to be same. But here, as we don't create standby database, the new password file should be created.   Create a password file using ORAPWD utility as follows:

$ orapwd file=$ORACLE_HOME/dbs/orapwaux password=test entries=3;

Step 2 - Configure auxiliary instance listener net configuration

To clone the production database to auxiliary server, the connection should be configured between two servers i.e. must be able to access the production database from auxiliary server and vice versa.

Thus, you need to make changes to tnsnames.ora and listener.ora files. If you haven't, these files are located in the $ORACLE_HOME/network/admin directory, create them using netca utility. First of all, change LISTENER.ORA file on auxiliary server and add the following lines:

LISTENER.ORA #auxiliary instance 

    (SID_DESC =

      (SID_NAME = aux)

      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)

    )

The reason why we made a change to listener.ora file is because we make auxiliary server to listen for the connections which comes from the production database. While duplicating the database, we'll connect to the auxiliary instance from the production database and as an auxiliary instance should understand and accept the connections which come from production database, we configured the listener.ora file. Without stopping and starting the listener, we apply new changes made to listener configuration by running the following command:

 $ lsnrctl reload

Now, edit the TNSNAMES.ORA file at the production database and add the entry about auxiliary database to it as follows:

AUX =

  (DESCRIPTION =

    (ADDRESS_LIST =

       (ADDRESS=(PROTOCOL=TCP)(HOST= DUP_SERVER)(PORT=1521))

    )

    (CONNECT_DATA=

      (ORACLE_SID=aux)

    )

   )

After changing the file, make sure you can connect to auxiliary instance by using tnsping utility

$ tnsping aux

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST= DUP_SERVER)(PORT=1521))) (CONNECT_DATA= (ORACLE_SID=aux)))

OK (40 msec)

It means that we have access to the auxiliary server from production server and we successfully configured connection between two servers

Step 3 ? Create a parameter file for an Auxiliary database

We'll use this parameter file to start auxiliary instance in NOMOUNT mode.  You can create pfile from spfile of production database and copy it to the auxiliary server. You can also create a parameter file which consists only of the following few parameters:

• DB_NAME: As we create duplicate database at the remote host, we can keep database name the same as the production database name. However, this parameter will be different when we duplicate database on the local host, because it?s impossible to have two databases with the same name in one host.
• CONTROL_FILES: This parameter defines the name of Control Files which will be restored to auxiliary instance
• DB_BLOCK_SIZE: This parameter must be the same as in the target instance
• COMPATIBLE: If production database uses specific compatible parameter, add this parameter to auxiliary parameter file with the same value
• SGA_TARGET: This parameter specifies the total size of all SGA components. To automatically size these components, make it the same as in the production database.  However it is not mandatory that this be the same size as in the production database.  The cloning may be done to create a Test / Development environment on a smaller server with lesser RAM.

You need also to create the following directories on the auxiliary server and add them to the parameter file:

• adump
•  bdump
•  udump
•  cdump
•  Flash Recovery Area

• $ cd $ORACLE_HOME
• $ mkdir admin
• $ cd admin/
• $ mkdir aux
• $ cd aux/
• $ mkdir adump bdump cdump udump
• $ cd ..
• $ mkdir flash_recovery_area

After changes are made to parameter file, it will look like as follows:

DB_NAME=aux

CONTROL_FILE=(/u02/oradata/aux/control01.ctl,/u02/oradata/aux/control02.ctl,
                                 /u02/oradata/aux/control03.ctl')

DB_BLOCK_SIZE=8192

COMPATIBLE='10.2.0.1.0'

SGA_TARGET=285212672

audit_file_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/adump'

background_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/bdump'

core_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/cdump'

user_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/udump'

db_recovery_file_dest='/u01/oracle/product/10.2.0/db_1/flash_recovery_area'

db_recovery_file_dest_size=2147483648

Now, to make Oracle automatically uses this parameter file each time, create spfile as follows:

$ export ORACLE_SID=aux

$ export ORACLE_HOME=/u01/oracle/product/10.2.0/db_1/

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 29 00:48:57 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> CREATE SPFILE FROM  PFILE='/u01/oracle/product/10.2.0/db_1/dbs/pfile.ora';

File created

Step 4 - Startup auxiliary instance in NOMOUNT mode

SQL> STARTUP NOMOUNT

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              92276304 bytes

Database Buffers          188743680 bytes

Redo Buffers                2973696 bytes

Step 5 - Backup the production database and copy all backup files to auxiliary server

As the auxiliary instance is ready, you need to backup the production database and copy backup files to auxiliary server. As the backup will be recovered on the auxiliary server, the production database should be in ARCHIVELOG mode

Connect to target instance and take the compressed backup of the database using nocatalog. The backup of the database can be taken by connecting to  the RMAN catalog database as well

$ export ORACLE_SID=db1

$ rman target /

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Step 6 - Create datafile locations on an auxiliary server

If you want to create the datafiles in the same directory as in the production database, create the folder of datafiles on the auxiliary server

# mkdir u02

# cd u02/

# mkdir oradata

# cd oradata/

# mkdir db1

# cd db1/

# chown -R oracle:oinstall /u02/

Step 7 - Copy the backup of the production database to the same directory residing on the auxiliary database server

As we configured the database to use Flash Recovery Area, all RMAN backups will be stored under the flash recovery area folder. So the same folder should be created at the auxiliary server and all backup files should be copied to that folder. When you issue the DUPLICATE DATABASE command, RMAN searches the auxiliary server for the backup files which should reside at the same folder where they were at the production server.

Identify the location of RMAN backups on the production server and create the same folders on the auxiliary server. Then copy all backup files from the production server to the auxiliary server

Step 8 - Duplicate the Database

After all above steps are done correctly, we're ready to create the clone database. For this, we connect to both production and auxiliary database from RMAN and issue the command "DUPLICATE TARGET DATABASE TO aux". 

$ rman target sys/my_pass auxiliary sys/test@aux

connected to target database: DB1 (DBID=1298725119)

connected to auxiliary database: AUX (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO aux NOFILENAMECHECK;

Starting Duplicate Db at 06-DEC-09

=================

database opened

Finished Duplicate Db at 06-DEC-09

After performing all steps, we have successfully duplicated the production database to the remote host (auxiliary server). RMAN performed the following steps automatically to duplicate the database :

• Allocates automatic auxiliary channel
• Creates a controlfile for the clone database
• Performs an incomplete recovery of the clone database using incremental backups and archived redo log files up to the last backed up archived redo log file.
• Shutdowns the database and opens it using RESETLOGS option.
• Generates a new unique DBID for the clone database  

But remember, all file locations were the same as in the production database. However, in case we need to change clone database?s directory structure, we can do it in different ways.

---

The goal is restoring a database using RMAN on a different node with different backup directory structures and different database directory structures .

• You have a database backed up on NODE 1.
• You need to restore the database on NODE 2.
• The directory structure is different on NODE 2.
• You need to put the backups in a new directory structure in NODE 2, unlike as they were in NODE 1.
• You need to restore the database files into a new directory structure in NODE 2, unlike as they were in NODE 1.

Solution

Below is the procedure with an example of using RMAN to copy a database to another directory:

1. Connect to the target database using rman and backup the database ---> NODE 1$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 13 00:29:33
   2007Copyright ? 1982, 2005, Oracle. All rights reserved.connected to target database: ORA10G (DBID=3932056136)RMAN>
   backup database plus archivelog;Starting backup at 13-FEB-07current log archivedusing channel ORA_DISK_1channel
   ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1
   sequence=143 recid=109 stamp=614392105channel ORA_DISK_1: starting piece 1 at 13-FEB-07channel ORA_DISK_1: finished piece
   1 at 13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp
   tag=TAG20070213T002825 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 13-FEB-07Starting
   backup at 13-FEB-07using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying
   datafile(s) in backupsetinput datafile fno=00003 name=/u01/oracle/product/oradata/ora10g/data/sysaux01.dbfinput datafile
   fno=00001 name=/u01/oracle/product/oradata/ora10g/data/system01.dbfinput datafile fno=00002 name=/u01/oracle/product/oradata/ora10g/data/undotbs01.dbfinput
   datafile fno=00004 name=/u01/oracle/product/oradata/ora10g/data/users01.dbfinput datafile fno=00005 name=/home/oracle/1.dbfinput
   datafile fno=00006 name=/u01/oracle/product/oradata/ora10g/data/sysaux02.dbfinput datafile fno=00007 name=/u01/oracle/product/oradata/ora10g/data/undotbs02.dbfchannel
   ORA_DISK_1: starting piece 1 at 13-FEB-07channel ORA_DISK_1: finished piece 1 at 13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp
   tag=TAG20070213T002827 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:55Finished backup at 13-FEB-07Starting
   backup at 13-FEB-07current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel
   ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=144 recid=110 stamp=614392165channel
   ORA_DISK_1: starting piece 1 at 13-FEB-07channel ORA_DISK_1: finished piece 1 at 13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp
   tag=TAG20070213T002925 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 13-FEB-07Starting
   Control File and SPFILE Autobackup at 13-FEB-07piece handle=/u01/oracle/product/ora10g/dbs/c-3932056136-20070213-02 comment=NONEFinished Control File and SPFILE Autobackup at 13-FEB-07RMAN> exit
2. Move the following files to the NODE 2 :+ The database backup pieces
   + Controlfile backup piece
   + The parameter file i.e init.ora file
3. Edit the PFILE on NODE 2 to change the environment specific parameters like .
   user_dump_dest = 
   background_dump_dest = 
   control_files =
4. Once the PFILE is suitably modified invoke Rman on the NODE 2 after setting the Oracle environment variables and start the database in nomount mode:

   
   

   [oracle@test-br test]$ export ORACLE_HOME=/u01/oracle/product/ora10g 
   [oracle@test-br test]$ export ORACLE_SID=ora10g 
   [oracle@test-br test]$ export PATH=$ORACLE_HOME/bin:$PATH 
   [oracle@test-br test]$ rman target / 
   Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 13 00:36:55 2007 
   Copyright ? 1982, 2005, Oracle. All rights reserved. 
   connected to target database (not started) 
   RMAN> startup nomount 
   Oracle instance started 
   Total System Global Area 205520896 bytes 
   Fixed Size 1218508 bytes 
   Variable Size 75499572 bytes 
   Database Buffers 121634816 bytes 
   Redo Buffers 7168000 bytes
5. Restore the controlfile from the backup piece.

   
   

   RMAN> restore controlfile from '/u01/oracle/product/ora10g/dbs/c-3932056136-20070213-02'; 
   Starting restore at 13-FEB-07 
   using target database control file instead of recovery catalog 
   allocated channel: ORA_DISK_1 
   channel ORA_DISK_1: sid=155 devtype=DISK 
   channel ORA_DISK_1: restoring control file 
   channel ORA_DISK_1: restore complete, 
   elapsed time: 00:00:02 
   output filename=/u01/oracle/product/oradata/ora10g/cntrl/control01.ctl 
   Finished restore at 13-FEB-07
6. Mount the database.

   
   

   RMAN> alter database mount ;
7. Now catalog the backup pieces that were shipped from NODE 1.

   
   

   RMAN> catalog backuppiece '/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp';Starting
   implicit crosscheck backup at 13-FEB-07allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked
   3 objectsFinished implicit crosscheck backup at 13-FEB-07Starting implicit crosscheck copy at 13-FEB-07using channel
   ORA_DISK_1Finished implicit crosscheck copy at 13-FEB-07searching for all files in the recovery areacataloging files...no
   files catalogedcataloged backuppiecebackup piece handle=/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp
   recid=41 stamp=614393265RMAN> catalog backuppiece '/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp';cataloged
   backuppiecebackup piece handle=/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp recid=42 stamp=614393292RMAN>
   catalog backuppiece '/home/oracle/test/backup/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp';cataloged backuppiecebackup
   piece handle=/home/oracle/test/backup/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp recid=43 stamp=614393310
8. Get to know the last sequence available in the archivelog backup using the following command.

   
   

   RMAN > list backup of archivelog all;
9. Rename the Redologfiles,so that they can be created in new locations when opened the database is opened in resetlogs.

   
   

   SQL> alter database rename file '/u01/oracle/product/oradata/ora10g/log/redo01.log' to '/home/oracle/test/log/redo01.log';..................
10. Now restore the datafiles to new locations and recover.
    RMAN> run { set until sequence <seq_no> set newname for datafile 1 to '/home/oracle/test/data/sys01.dbf';
    set newname for datafile 2 to '/home/oracle/test/data/undotbs01.dbf'; set newname for datafile 3 to '/home/oracle/test/data/sysaux01.dbf';
    set newname for datafile 4 to '/home/oracle/test/data/users01.dbf'; set newname for datafile 5 to '/home/oracle/test/data/1.dbf';
    set newname for datafile 6 to '/home/oracle/test/data/sysaux02.dbf'; set newname for datafile 7 to '/home/oracle/test/data/undotbs02.dbf';
    restore database; switch datafile all; recover database; alter database open resetlogs; }

---