Duplicating Database using RMAN:-

This test was done in 11.2.0.4 version

Target Database is UAT database :- UAT

Auxiliary Database is Prod database (duplicate database) :- Prod

1. Checking password file for TARGET and Auxiliary Database

Sql>select * from V$PWFILE_USERS;

Sql>grant sysdba to sys;

Sql>alter user sys identified by password;

Create Password File On Remote Server For Aux DB (if its not already present).

You must also have a Password file on the original server for the Target DB.

IMPORTANT: ** Set the password to the same as target database. **

If no password file on Target, create and bounce the database

On UNIX:

$cd $ORACLE_HOME/dbs

$orapwd file=orapwSID password=xxxxxxxx force=y (if you already have one,

use same name)

(Note: Replace xxxxxxxxx with your actual password for the SYS user.)

2. Checking connections between TARGET and Auxiliary Databases.

Configure TNSNAMES.ora  entry for TARGET DB entry in Auxiliary DB and Auxiliary DB entry in TARGET DB

Check the connections by tnsping on both DB side .

3. Performing TARGET Full Database backup with controlfile on Disk Level by RMAN

Note:- The storage should be common or you need to do NFS this backup mountpoint to the remote server (auxiliary database location) during the rman duplicate command

Connect TARGET DB

Connect RMAN and RUN the below RMAN script to take FULL TARGET Database backup.

RUN {

  ALLOCATE CHANNEL t1 TYPE DISK FORMAT ‘/dbrestore/UAT_rman/UATDB%d_DB_%u_%s_%p’;

  BACKUP DATABASE PLUS ARCHIVELOG;

  RELEASE CHANNEL t1;

  }

4. Set the following 2 parameters in Auxiliary DB init.ora (PFILE)

Create PFILE for Aux DB.

Copy the initTARGET.ora file to the Aux server and modify.

Make sure all paths are set correctly for new database.

update these:

Set CONTROL_FILES parameter based on new database locations.

Set DB_NAME parameter based on new database.

These are new:

Set DB_FILE_NAME_CONVERT based on new database.

Ex: db_file_name_convert=’OLD_FILE_NAME’,’NEW_FILE_NAME’

Set LOG_FILE_NAME_CONVERT based on new database.

Ex: log_file_name_convert=’OLD_FILE_NAME’,’NEW_FILE_NAME’

5. Start Auxiliary Database (prod) In A NOMOUNT State From Aux Server Through SQL*Plus.

Sqlplus / as sysdba

STARTUP FORCE NOMOUNT

6. Duplicating TARGET Database :-

RMAN> connect target

connected to target database: UAT (DBID=293507132)

RMAN> connect auxiliary sys/W0rldw1de@prod

connected to auxiliary database: PROD (DBID=534731388)

RMAN>

  run {

       allocate auxiliary channel t1 type disk;

       allocate auxiliary channel t2 type disk;

       allocate auxiliary channel t3 type disk;

      duplicate target database to prod;  

    }

Note:- If the target (UAT) database backup was taken as offline backup it will automatically duplicate, if the target DB is in online, you need to set the archivelog number in the beginning during this duplicate command so that the auxiliary (prod) DB will be restored upto that point of time.

7. Checking VALID/INVALID objects in Auxiliary DB (After DUPLICATED from TARGET).

Check the object count (VALID & INVALID) in TARGET DB by

Select count(*) from dba_objects where status=’VALID’;

Select count(*) from dba_objects where status=’INVALID’;

(Confirm all the objects are duplicated properly)

Check the object count (VALID & INVALID) in Auxiliary DB by

Select count(*) from dba_objects where status=’VALID’;

Select count(*) from dba_objects where status=’INVALID’;