Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Use RMAN to copy/clone a database

Oracle Tips by Burleson Consulting
January 1, 2015

Question: I want to use RMAN to copy a database to a different server, in a different disk directory. Can I use RMAN to restore a database to another server?


Answer:  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

Also see: RMAN cloning on a local host

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 at $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.


 

Answer on RMAN cloning by Sorabh HaritApplies to:  Oracle Server - Enterprise Edition - Version: 10.2.0.0.0

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; }


Here, Rampant TechPress author Kamran Agayev describes how to copy or clone a database using RMAN. 

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

Also see my notes on the 12c RMAN noopen clause.

 
 
 
Get the Complete
Oracle Backup & Recovery Details 

The landmark book "Oracle Backup & Recovery: Expert secrets for using RMAN and Data Pump " provides real world advice for resolving the most difficult Oracle performance and recovery issues. Buy it for 40% off directly from the publisher.
 


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.