DUPLICATE from ACTIVE Database Using RMAN, a Step-by-Step Guide

Oct 24, 2012 / By Kamran Bakhshandeh

Tags: , , ,

Duplicate using RMAN

Recently, I had a request from one of our clients to duplicate a single instance database into a two node RAC database. Both source and destination databases were 11g Rel 2 on Linux.

I could take the old approach and use RMAN backups or use 11g’s new feature DUPLICATE FROM ACTIVE database, which doesn’t need any backup from target database. I preferred to use the second approach.

Duplicate from ACTIVE database

Duplicating from an active database doesn’t require any RMAN backup to be taken from the source database. Actually, it reads all database structure from the source database that needs to be mounted or opened during the duplication. Although this method seems easy as we don’t need to backup source database or make it accessible for auxiliary instance anymore, it has its own disadvantages as well.

Two big disadvantages of the ACTIVE database duplication method are:

  1. Negative performance impact on the source database. This impact applies to the whole duplication time.
  2. High network traffic on the connection between the source and target databases.

If either of these negative impacts are not acceptable in your environment , you will need to switch to the old backup-based approach.
By the way, I think that if you have the latest backup of your source database available and it can be accessed by auxiliary instance, the best method for duplication is still the backup-based approach.

Duplicate from active database is an easy task ONLY IF you follow all the steps accurately. Otherwise, it can be a complicated task, and you can waste your time troubleshooting.

The following part is simplified step-by-step instructions for DUPLICATE from ACTIVE database:

Step-by-step instructions

In this guide, assume we are migrating a single instance database HRDEV to a two node RAC database HRPRD with the following specifications:

Source database:

Database name: HRDEV
Single instance
Version: 11.2.0.3
Hostname: dev-db-01
Filesystem: ASM
Target database:
Database name: HRPRD
RAC  2 nodes
Version: 11.2.0.3
ORACLE_HOME: /apps/oracle/product/11.2.0/db_1
GI_HOME: /apps/grid/11.2.0/
Hostname: prd-db-01/prd-db-02
Filesystem: ASM
Diskgroup: +HR
For target database HRPRD, we assume that an empty database has already been created with two instances, spfile and controlfiles already exist, and the database is already a member of clusterware. As a matter of fact, we will use only instances of this database as auxiliary instance, and all datafiles can be deleted manually before duplication, as this database is going to be a refreshed from HRDEV database by our DUPLICATE command.
The diskgroup that will be used for this database is “+HR”.

1- Prepare auxiliary instance HRPRD1 on prd-db-01:

Stop all instances of your cluster database except one.
In this example, we will use only the HRPRD1 instance, which runs on prd-db-01. We need to stop the other instance, HRPRD2:

  srvctl stop instance -d HRPRD -i HRPRD2

Set the following parameters on HRPRD1 instance:

   . oraenv HRPRD1
   sqlplus / as sysdba

   alter system set db_name=HRPRD scope=spfile;
   alter system set cluster_database=false scope=spfile;
   alter system set db_create_file_dest='+HR';
   alter system set db_create_online_log_dest_1='+HR';

   shutdown immediate
   startup nomount

2- Enable status registration for HRPRD1 to run LISTENER:

Add the following entries into listener.ora file in GI_HOME.

Edit /apps/grid/11.2.0/network/admin/listener.ora and add the following lines:
   SID_LIST_LISTENER =
     (SID_LIST =
       )
       (SID_DESC =
         (SID_NAME = HRPRD1)
         (ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
         (GLOBAL_DBNAME = HRPRD)
       )
     )

Make sure that ORACLE_HOME in this entry points to correct home, which is the home from which HRPRD database runs.

3- Add following TNS entries to BOTH auxiliary and target tnsnames.ora file:

   HRDEV =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = dev-db-01)(PORT = 1521))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = HRDEV)
       )
     )

   HRPRD1 =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = prd-db-01)(PORT = 1521))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = HRPRD)
       )
     )

4- Ceate a password file for auxiliary instance HRPRD1 on prd-db-01:

Connections to both instances will be through listener and TNS, so we need to use passwords for both auxiliary and target connections.

For HRPRD, since it is a new and empty database, we may need to create a password file for it as follows:

   . oraenv
   HRPRD1
   cd $ORACLE_HOME/dbs
   orapwd password=sys file=orapwHRPRD1
I have assumed that the SYS password on the source database HRDEV is “sys”.

5- Test connectivity to auxiliary and target instances from BOTH hosts using TNS:

Make sure your connectivity to the source database and to your auxiliary instance works fine; otherwise, duplicate from active database won’t work.

   sqlplus sys/sys@HRPRD1 as sysdba
   sqlplus sys/sys@HRDEV as sysdba
Try above commands on both target and auxiliary hosts prd-db-01 and dev-db-01. Do not continue unless your connectivity is fine.

6- On the auxiliary host, start RMAN and run the DUPLICATE command:

From host prd-db-01, which runs auxiliary instance hrprd1, start RMAN. Make sure the auxiliary connection is established through listener and not through OS authentication.

   . oraenv
   HRPRD1
   rman target sys/sys@HRDEV auxiliary sys/sys@hrprd1
   RMAN>run{
            DUPLICATE TARGET DATABASE TO HRPRD
            FROM ACTIVE DATABASE;
           }

7- When step 6 finishes successfully, start HRPRD database using srvctl. You need to enable the second thread as well:

Change the HRPRD database to be a cluster database again and start both instances:
   . oraenv HRPRD1
   sqlplus / as sysdba

   alter database add logfile thread 2 group 4 size 200m;
   -- add few more groups
   create tablespace UNDOTBS2 datafile '+HR' size 1g;
   alter system set undo_tablespace='UNDOTBS2' sid='HRPRD2';
   alter database enable public thread 2;
   alter system set cluster_database=true scope=spfile;
   shutdown immediate

   srvctl start db -d HRPRD

That’s all. I hope it will work for you!

35 Responses to “DUPLICATE from ACTIVE Database Using RMAN, a Step-by-Step Guide”

  • Heiko Herms says:

    Very useful description – thanks!

    Beside the “Two bold disadvantages of ACTIVE database” you’ve mentioned I see an other points: the whole directory structure has to be created before.

    Regards, Heiko

  • Gilbert Standen says:

    This is an awesome post Kamran. In my case, I found that a few extra steps were needed however because I got this error after RMAN dup completed (only HRPRD1 will start (in my experience). Here is the error message:

    [oracle@cgi-ma-uat-db1v ~]$ srvctl start database -d RATIONAL
    PRCR-1079 : Failed to start resource ora.rational.db
    CRS-5017: The resource action “ora.rational.db start” encountered the following error:
    ORA-01618: redo thread 2 is not enabled – cannot mount
    . For details refer to “(:CLSN00107:)” in “/u01/app/11.2.0/grid/log/cgi-ma-uat-db2v/agent/crsd/oraagent_oracle/oraagent_oracle.log”.

    CRS-2674: Start of ‘ora.rational.db’ on ‘cgi-ma-uat-db2v’ failed
    CRS-2632: There are no more servers to try to place resource ‘ora.rational.db’ on that would satisfy its placement policy

    Following is needed to fix and convert single-instance cloned DB to RAC 2-node (more steps would be needed for 3-node etc) but similar to below.

    Login to the HRPRD1 up and running instance.

    alter database add logfile thread 2 group 4;
    alter database add logfile thread 2 group 5;
    alter database add logfile thread 2 group 6;

    Then also:

    create undo tablespace UNDOTBS2 datafile size 99614720;

    (I used size identical to UNDOTBS1).

    Now 2nd instance startup fine:

    srvctl start instance -d HRPRD -i HRPRD2

    Thanks again for this awesome post Kamran!!!

    • Kamran Bakhshandeh says:

      Thanks for your useful comment Gilber
      You are absolutely right. In my test case HRPRD was originally a two node RAC set , that’s why all these steps has been missed.
      Anyway thanks again for this awesome comment and I am glad my post has helped you.

    • Kamran Bakhshandeh says:

      I updated my post considering your recommendations related to extra steps for enabling second thread.

      Thanks again for your valuable feedback

  • Gilbert Standen says:

    Forgot one command in my post above. After the add of the logfiles on thread 2, you have to run this command also:

    alter database enable public thread 2;

    Thanks

  • Hector Albarran says:

    Hi

    The script catclust.sql is not needed? I mean, if the source is single instance, all GV$ views are missing, am i right?

    Regards

    • Kamran Bakhshandeh says:

      Hi Hector

      Thanks for your comment , I have done this duplications many times and I’ve never executed catclus.sql
      If you check a single instance database you can see al GV$ exist

  • Panuganti. Bharath says:

    As spelled above about the bold disadvantages for duplicating database from target (Implies HRDEV in current case) , can you please detail on the tools that can be used to “gauge” the high network traffic connection prob and the impending performance impact on the source before ahead.

  • Panuganti. Bharath says:

    Hi Kamran Bakhshandeh,

    Another worthy point that I’m persuaded to emphasize is regards the “Directory Structure” and the “Host” wherein which you are been planning to DUPLICATE the database.

    CASE -1
    =======
    Duplicating a Database on a “Remote Host” with the ‘Same Directory Structure’ as source (HRDEV), then NO changes is required to be made for PFILE.

    DUPLICATE DATABASE TO HRPRD
    FROM ACTIVE DATABASE
    PFILE (if starting the auxiliary instance with a client-side parameter file)
    NOFILENAMECHECK; (if duplicated datafiles location= Source data file’s location and database’s are in DIFFERENT Host’s)

    CASE-2
    ======
    Duplicating a Database on a Remote Host with a Different Directory Structure:

    Here you MUST change the PFILE as the duplicate database filenames structure is DIFFERENT from the Source filenames.

    DUPLICATE DATABASE TO HRPRD
    FROM ACTIVE DATABASE
    DB_FILE_NAME_CONVERT=(/oracle/oradata/HRDEV/,/dup/oracle/oradata/HRPRD/)
    LOGFILE (names and sizes for the duplicate database redo logs)
    ‘/dup/oracle/oradata/HRPRD/redo01.log’ SIZE 200K,
    ‘/dup/oracle/oradata/HRPRD/redo02.log’ SIZE 200K;

    Surmise : If we DON’T specify the LOGFILE option, then RMAN after having done with Incomplete Recovery, opens up the database with RESETLOGS option to create the online logs.

    CASE 3
    ======
    Creating a Duplicate Database on the Local Host wherein the SOURCE (HRDEV) is resident.

    Same procedure need to be followed as spelled in CASE 2.

    NOTE : Do not use the NOFILENAMECHECK option in here as doing so will lead to overwriting the SOURCE Datafiles.

    SO the above enunciation by Kamran Bakhshandeh will falls in CASE 2.

    • Kamran Bakhshandeh says:

      Hi Panuganti

      Thanks for your comments.
      My case was exactly CASE 1 , directory structure is same but actual location is different and I didn’t used NOFILENAMECHECK option.

      For CASE 2 and CASE 3 yes we need to use DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters either at instance level or in the command.
      For CASE 3 we must NOT use NOFILENAMECHECK

      Thanks again for your valuable updates.

  • Franno says:

    Hi,

    there is a typo for the listener config in step 2 – extra bracket:

    This ->

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = HRPRD1)
    (ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
    (GLOBAL_DBNAME = HRPRD)
    )
    )

    Should be ->

    SID_LIST_LISTENER =
    (SID_LIST =
    )
    (SID_DESC =
    (SID_NAME = HRPRD1)
    (ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
    (GLOBAL_DBNAME = HRPRD)
    )
    )

    • Franno says:

      Sorry – pasted in the wrong order! [The extra bracket is under the "SID_LIST" value]

      This ->

      SID_LIST_LISTENER =
      (SID_LIST =
      )
      (SID_DESC =
      (SID_NAME = HRPRD1)
      (ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
      (GLOBAL_DBNAME = HRPRD)
      )
      )

      Should be ->

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = HRPRD1)
      (ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
      (GLOBAL_DBNAME = HRPRD)
      )
      )

  • Adrian says:

    this command is wrong for creating an undo ts:
    create tablespace UNDOTBS2 datafile ‘+HR’ size 1g;
    ===> correct one would be:
    create undo tablespace UNDOTBS2 datafile ‘+HR’ size 1g;

    cheers
    Adrian

  • Karthik says:

    Hi, thanks for the beautiful explanation here. My doubt, is it possible to have same database name but having diff DBID and DB_UNIQUE_NAME while duplicating DB using RMAN. If yes what should I use with RMAN duplicate command dbname or db_unique_name

    • Kamran Bakhshandeh says:

      Hi Karthik
      Thanks for you comment.
      Yes it is possible to duplicate database with same database name while DBID and DB_UNIQUE_NAME are different.
      You will need to use database name in your duplicate command ,but when launching RMAN you have to make sure that your target and auxiliary instance connection are correct. RMAN duplicates your target database into auxiliary one.
      This valid only when source and duplicate databases reside on different machines , note that you cannot use the same database name for the source database and duplicate database when the duplicate database resides in the same Oracle home as the source database. However, if the duplicate database resides in a different Oracle home from the source database, then its database name just has to differ from other database names in its Oracle home.

  • Hormoz says:

    Hi Kamran

    Great post, very useful and professional. Just wondering if all recommended changes are in the same post or different one?!

    ba tashakoor faravan

    Hormoz

    • Kamran Bakhshandeh says:

      Thanks for your comment Hormoz , glad that it helped :)
      Yes , all recommendations should be already in actual post unless I missed one.

      Eradatmand !

  • Mohammad Shamsul Haque says:

    Hi Kamran

    Helpful post, What should be the process if I want to duplicate from RAC database to single instance database without ASM. I think in the auxiliary parameter file need to set
    cluster_database=false;
    db_create_file_dest=non_asm_file_location;
    alter system set db_create_online_log_dest_1=non_asm_file_location;
    DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters

    Do I need any command regarding logfile thread and undo tablespace after issuing

    . oraenv
    HRPRD1
    rman target sys/sys@HRDEV auxiliary sys/sys@hrprd1
    RMAN>run{
    DUPLICATE TARGET DATABASE TO HRPRD
    FROM ACTIVE DATABASE;
    }

    or

    DUPLICATE DATABASE TO HRPRD
    FROM ACTIVE DATABASE
    PFILE (if starting the auxiliary instance with a client-side parameter file)
    NOFILENAMECHECK; (if duplicated datafiles location= Source data file’s location and database’s are in DIFFERENT Host’s)

    or is there any article DUPLICATE from ACTIVE Database Using RMAN from rac to single instance database:

    • Kamran Bakhshandeh says:

      Hi Mohammad

      Thanks for your comment , glad it has been helpful for you.
      The parameter settings you mentioned in your post should suffice and you can use same duplicate command which is in the post.
      Also you need to ignore the step for enabling other thread and adding new UNDO tablespaces.

      Hope it will help you.

      Thanks

      Kamran

  • farid says:

    Hello all,
    first thank you for this article.
    I need your help …. I installed oracle 11g on my pc “desktop”, with a database that used by an application that contain 2 parts ” the 2 parts use the same tables of the database” . now I want to separate the 2 parts of the application and I have to duplicate my database on the same oracle installation, in way that I can use the 2 parts of the application in the same time separately and the changes makes by the part 1 on database 1 don’t affect the database 2.
    is this possible ? if yes how ? …. please help me …
    THANK YOU….

    • Kamran Bakhshandeh says:

      Hi Farid

      This is possible and it is an easy task , it is not necessary to use DUPLICATE FROM ACTIVE database method which is the subject of this post.

      You have to follow up instructions for “Duplicate database in same host”.
      If you google this clause you will find lots of useful instructions to implement your requirement.

      Good luck

      Kamran

      • farid says:

        Hi Kamaran,
        thx for the reply.
        on more thing, I configure my database ‘db1′ to automatically start with the os “Linux REDHAT”, if I create a new database ‘db2′ on the same host, can I configure this new one db2 to start at the same time of db1 automatically with the os ? if yes how?….
        Thank you & Regards…

  • Lokanath says:

    Could you please explain how duplicate works internally?Does it copy the data-files using any operating system copy utility like rsync or scp in unix.

    • Kamran Bakhshandeh says:

      Hi Lokonath

      Thanks for your comment.
      Internal mechanism of DUPLICATE from ACTIVE database is based on RMAN “backup as copy” command.
      When you initiate this commands you can see following line in log file:

      (This is few lines of the whole log for one datafile only)
      Starting backup at 08-MAY-13
      using channel ORA_DISK_1
      channel ORA_DISK_1: starting datafile copy
      input datafile file number=00009 name=+HR/hrdev/o1_mf_concept_3x15f8fw_.dbf
      output file name=+HR/hrprd/o1_mf_concept_3x15f8fw_.dbf tag=TAG20130508T185836
      channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:15

      Hope you have found your answer.

      Kamran

  • Mathew says:

    Interesting and useful information shared by you. If you want to remove duplicate data, then it would be great idea to use a data deduplication software. It helps to find and remove duplicate data easy and fast way.

  • Littlefoot says:

    Hi..,

    This is an interesting feature, though it has its own disadvantages.

    I have tried to do the same in 11.2.0.3 databases, but during duplication, the RMAN did not consider all the datafiles – i have created 4 datafiles before i started the duplication, and during the set up i did not consider these 4 datafiles, any particular reason for this not to consider these files ???

    Because of this the recovery stopped. Can you please let me know the reason for this ?

    • Kamran Bakhshandeh says:

      Hi Littlefoot

      Thanks for your comment.
      Any datafile that has been added to source database before starting duplicate must be included in duplication , not sure what has gone wrong in your scenario , could you please provide more details , maybe I will be able to help.

      Thanks

      Kamran

  • MEUNIER says:

    Hi there,

    I guess all of this is not possible to be done between one single instance on SunSolaris to RAC instances on Linux because of endian. Which RMAN does not support. Right ?

    Pierre-Jean.

    • Kamran Bakhshandeh says:

      Hi Meunier

      That’s right , you won’t be able to duplicate database from one endian to another using active database

  • Alexander says:

    Hi Kamran,
    in my lab I was doing the opposite, from a rac db as target and clone to a single db.
    I have executed the steps accordingly, however at the end of the process I got an error that sounds different from what it should be.
    Please take a look at this:

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

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 03/06/2014 18:55:47
    RMAN-03015: error occurred in stored script Memory Script
    RMAN-06136: ORACLE error from auxiliary database: ORA-01152: file 2 was not restored from a sufficiently old backup
    ORA-01110: data file 2: ‘/oracle/oradata/usa/sysaux01.dbf’

    Do you have any suggestion what should I do in order to open the cloned database??
    Thank you and congrats for the great article.

    • Alexander says:

      I tried to solve this issue by putting the target db in Mount mode, so that it wouldn’t be required to recover the database at the end. The cloned db wasnt with FRA enabled. Thank you.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>