Monday, July 15, 2013

Oracle 11g Active Database Duplication

Oracle 11g introduced new feature called 'Active Database Duplication' using which we can create a duplicate database of the target database without any backups.

Duplication is performed over the network.

Overview:

On the source Server:

- Create Pfile from source database
- Create an entry in tnsnames.ora for duplictae database on target host on port 1522

On the target Server:

- Add a line in the file /etc/oratab to reflect the database instance you are going to copy
- create folders
- Copy the initialization parameter file from the source database add edit it.
- Copy the password file
- Create a listener in database home on port 1522 and register duplicate database statically with it
- Startup the target database in nomount mode using modified parameter file
- Using RMAN connect to the source database(orcl) as target database and duplicate database (orclt) as auxiliary instance
- duplicate the target database

********************************
Source database: orcl
Duplicate database: orclt
***********************************

Implementation

– On source host
– CREATE PFILE FROM SOURCE DATABASE

SQL> CREATE PFILE=’/u01/app/oracle/oradata/orcl/initsource.ora’ FROM SPFILE;

– On source database, create a service for orclt on target host on port 1522

The rest of the steps occur on the target host.

– Add a line in the file /etc/oratab to reflect the database instance you are going to copy

orclt:/u01/app/oracle/product/11.2.0/db1:N

– Now set the Oracle SID as the duplicated database SID:

– create folders

mkdir -p /u01/app/oracle/oradata/orclt
mkdir -p /u01/app/oracle/flash_recovery_area/orclt
mkdir -p /u01/app/oracle/admin/orclt/adump
mkdir -p /u01/app/oracle/admin/orclt/dpdump

– Copy the initialization parameter file from the main database.

$cp /u01/app/oracle/oradata/orcl/initsource.ora /u01/app/oracle/oradata/orclt/inittarget.ora

– Edit the initialization parameter file

$vi /u01/app/oracle/oradata/orclt/inittarget.ora

– Change db_name = orclt

– Edit it to reflect the new locations that might be appropriate such as control file locations,audit dump destinations, datafile locations, etc.

– add these lines –

db_file_name_convert = (“/u01/app/oracle/oradata/orcl”,“/u01/app/oracle/oradata/orclt”)
log_file_name_convert = (“/u01/app/oracle/oradata/orcl”,"/u01/app/oracle/oradata/orclt”)

In case source and destination databases ae ASM, following lines can be added :

db_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”)
log_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”, “+FRA/orcl”,”+FRA/orclt”)

– Copy the password file as well

$cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt

– Startup the target database in nomount mode using modified parameter file

$ . oraenv

ORACLE_SID = [orclt]

$sqlplus sys/oracle as sysdba

SQL> startup nomount pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora’;

create spfile from pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora’;

– create a listener on port 1522 in database home on target host and statically register service orclt with it.
– connect to the auxiliary instance

$. oraenv

orclt

$rman target sys/oracle@orcl auxiliary sys/oracle@orclt

– duplicate the database orcl to orclt from active database
– the command performs the following steps:

* Creates an SPFILE
* Shuts down the instance and restarts it with the new spfile
* Restores the controlfile from the backup
* Mounts the database
* Performs restore of the datafiles. In this stage it creates the files in the converted names.
* Recovers the datafiles up to the time specified and opens the database

RMAN> duplicate target database to orclt from active database;

– check that duplicate database is up

$sqlplus / as sysdba

sql> conn hr/hr

select * from tab;

SQL> select dbid from v$database;

For any further queries email me samiora@gmail.com.