1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to create a test oracle 11g database using RMAN?

Discussion in 'Server Administration and Options' started by amilasan, Sep 15, 2013.

  1. amilasan

    amilasan Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I want to take a copy of production Oracle 11g database and create a test database. Appreciate if anyone can explain how to proceed.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Depending on the release of Oracle you're using RMAN and expdp/impdp are options. With RMAN you would use (on 11.2):

    duplicate target database to '<new sid>';

    once you have connected to your target database and the auxiliary; a sample shell script is shown below:

    export ORACLE_SID=samptest
    export RMANDATE='01-JAN-2013 00:00'
    export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
    DATESTAMP=`date '+%y%m%d%H%M'`
    MSGLOG=dup_ofap_${DATESTAMP}.log
    echo "=============================================================================" >> $MSGLOG
    echo "Recovering to $RMANDATE" >> $MSGLOG
    echo "=============================================================================" >> $MSGLOG
    $ORACLE_HOME/bin/rman msglog $MSGLOG append << EOF
    connect target sys/pass@sample
    connect auxiliary /
    run {
    set until time "to_date('${RMANDATE}','DD-MON-YYYY HH24:MI')" ;
    sql 'alter session set optimizer_mode=rule';
    allocate auxiliary channel c1 type disk ;
    allocate auxiliary channel c2 type disk ;
    allocate auxiliary channel c3 type disk ;
    allocate auxiliary channel c4 type disk ;
    allocate auxiliary channel c5 type disk ;
    allocate auxiliary channel c6 type disk ;
    duplicate target database to 'samptest'
    logfile
    GROUP 1 ( '+DATA_MYDG1','+DATA_MYDG2' ) SIZE 512M,
    GROUP 2 ( '+DATA_MYDG1','+DATA_MYDG2' ) SIZE 512M,
    GROUP 3 ( '+DATA_MYDG1','+DATA_MYDG2' ) SIZE 512M,
    GROUP 4 ( '+DATA_MYDG1','+DATA_MYDG2' ) SIZE 512M ;
    }
    EOF

    OF course you don't need to recover to a specific date in the past, you can simply duplicate the database:

    export ORACLE_SID=samptest
    export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
    DATESTAMP=`date '+%y%m%d%H%M'`
    MSGLOG=dup_ofap_${DATESTAMP}.log
    $ORACLE_HOME/bin/rman msglog $MSGLOG append << EOF
    connect target sys/pass@sample
    connect auxiliary /
    run {
    sql 'alter session set optimizer_mode=rule';
    allocate auxiliary channel c1 type disk ;
    allocate auxiliary channel c2 type disk ;
    allocate auxiliary channel c3 type disk ;
    allocate auxiliary channel c4 type disk ;
    allocate auxiliary channel c5 type disk ;
    allocate auxiliary channel c6 type disk ;
    duplicate target database to 'samptest'
    logfile
    GROUP 1 ( '+DATA_MYDG1','+DATA_MYDG2' ) SIZE 512M,
    GROUP 2 ( '+DATA_MYDG1','+DATA_MYDG2' ) SIZE 512M,
    GROUP 3 ( '+DATA_MYDG1','+DATA_MYDG2' ) SIZE 512M,
    GROUP 4 ( '+DATA_MYDG1','+DATA_MYDG2' ) SIZE 512M ;
    }
    EOF

    You DO need to have an init.ora file for database samptest and have it started nomount; this is so you can connect RMAN to the target. You can copy the init.ora for sample (in this example), rename it to initsamptest.ora then change the entry for db_name to samptest, db_unique_name to samptest and any dump locations to point to the samptest locations. You will also need to create the dump destinations for the samptest database, then you can start the instance and run the script. Also, the RMAN backup pieces from the souorce database need to be available on the server where you are creating the test database if that's different from the production server (and it should be). Once you have the various pieces in place the clone should run without error.

    If you don't want to use RMAN you can use empdp/impdp but you'll need to create a blank database for samptest before you can proceed. Once you have a shell destination database created you can import the schemas and objects from the souorce database into the new database.

    Verify that all packages/procedures/views/etc compile without error and you should be good with your new test database.

    You can also search google for instructions on how to clone an Oracle database; there are many blogs and websites that provide other instructions for this for various releases of Oracle.
     
  3. amilasan

    amilasan Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks David for the support & information provided by you. I'll try your approach and let you know if I get any errors. Thanks again.