Monday, November 14, 2011

Online (Live) or Offline Cloning in Oracle11g

Oracle has added two more options in Oracle11g that further simplify the database cloning process. In Oracle11g, a database can be cloned directly from an active database or from backups specified via backup location clause. Database Cloning is accomplished via RMAN DUPLICATE command.
Cloning from an Active Database

This is a new feature in Oracle11g. The cloning process is LIVE. The cloning process is Online. Oracle, through RMAN, simultaneously opens up connections to both the source database and the clone database to create a clone database. The cloned database, or the duplicate database, is materialized directly from the source database. 

With Active Database Cloning, there is no need to take backups and then copy it to the clone database server. There is no need to copy archive logs to the clone database server. There is no need to catalog RMAN hot backups on the clone database server. RMAN Duplicate command is qualified by "FROM ACTIVE DATABASE" to create a clone database.

Clone from any backup location on the standby database server

With this technique, you simply copy database RMAN backups to the clone database server and specify its location as part of DUPLICATE command to create a clone db. Backups can be RMAN on-line or off-line, but should include control file and archive logs, if DB is in ARCHIVELOG mode and backups were taken online. Backup location clause is an Oracle11g enhancement to RMAN DUPLICATE command.

Let’s Clone the DB! 

Database version: 
Operating system: Windows 2008 64-bit

Step 1 – Specify Clone DB Parameters 

Assign the following identifiers to the new database. These identifiers should not be the same as the source database. For the purpose of this illustration, let’s call the clone database, well, CLONE.

Database Name: CLONE
Database Unique Name: CLONE
Instance Name: CLONE
TNS Connect String: CLONE

Step 2 - Allocate Disk Resources on the CLONE DB Server

The next step is to allocate disk space to the CLONE database. For sake of simplicity, assume that all data files, redo log files and controls files will be stored in E:\Oradata\CLONE. It’s development environment!

Step 3 - Create folder structure on the CLONE DB Server

Follow your organizational naming standards and requirements to create the folder structure on the clone database server. We will create the folder structure as follows for this illustration:

Audit Files - E:\Oradmin\CLONE\audit
Diagnostic Dump Destination - E:\Oracle\diag
Archive Log Destination - E:\Orarch\CLONE
Data Files - E:\Oradata\CLONE

Step 4 – Create initCLONE.ora file

Copy ORIGINAL database’s init.ora file as initCLONE.ora, and make the following changes. The changes suggested below should be sufficient for most DBAs. It’s not a complete list. There may be additional parameters that would require updates.


Update the following parameters to point to log/trace file locations as described above.


Update the following parameter to the location where you want to store CLONE’s control files. In this example, we will use E drive. Make sure that this folder is accessible.

control_files=(‘e:\oradata\CLONE\control1.ctl’,‘e:\oradata\CLONE\control2.ctl’ )

ORIGINAL’s data files reside on E:\OraData\ORIGINAL, but CLONE database’s data files will be stored in E:\OraData\CLONE.

Update the following parameter to instruct Oracle to translate datafile names during the cloning by specifying db_file_name_convert init.ora parameter.


Add additional translation strings to this parameter if you have multiple disk drives hosting the database.

For example,

                      'd:\oradata\ORIGINAL','e:\oradata\CLONE' )

Specify log_file_name_convert parameter to instruct Oracle to translate log file names.


Adjust the following memory parameters as per memory availability.


Make sure that COMPATIBLE parameter is the same for both the databases. Otherwise, you will get “ORA-01130: database file version incompatible” error.

RMAN-03002: failure of Duplicate Db command
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: 
ORA-00201: control file version incompatible with ORACLE version

Also make sure that remote_login_passwordfile is set to EXCLUSIVE.

If there are any password file issues, then you may get the following errors. You need to fix these errors before proceeding.

RMAN-03002: failure of Duplicate Db command at 09/24/2011 10:57:05
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/24/2011 10:57:05
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01031: insufficient privileges
ORA-17629: Cannot connect to the remote database server

Step 5 - Service and/or Password File Creation

On Windows platform, create a service for the new database using oradim as shown below:

oradim -new -sid CLONE -syspwd verysecretdbpassword -startmode auto -pfile initCLONE.ora

This command creates a Windows service for CLONE database. In addition to Windows service, this command also creates a password file. Note that pfile resides in %ORACLE_HOME%\database.

On *nix platforms, you don’t need to create a service, but create a password file by running orapwd. Password file is needed to connect to CLONE database as SYSDBA.

orapwd file=orapwCLONE password=verysecretpassword

Please note that password file name on Windows and Unix platforms are different.

In my test environment, I also had to set _compression_compatibility='11.2' as I was getting the following errors:

RMAN-03002: failure of Duplicate Db command at 09/24/2011 11:26:10
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-00600: internal error code, arguments: [kck_rls_check must use (11,0,0,0,0) or lower],
kdt.c], [9419], [], [], [], [], [], [], [], [], []

Step 6 - CLONE spfile creation and Instance Startup

Start CLONE Oracle database instance with nomount option by:


C:> sqlplus /nolog

SQL> connect / as sysdba

SQL> create spfile from pfile

SQL> Startup nomount

SQL> exit

Step 7 – TNSNAMES and Listener Configuration on both servers

Create a listener on the CLONE database server as follows:

    (SID_DESC =
      (ORACLE_HOME = E:\oracle\ora11gR2)
      (SID_NAME = CLONE)

Also add a TNSNAMES.ORA alias to tnsnames.ora file on the clone DB server:

        (ADDRESS = (PROTOCOL = TCP)(HOST = originaldbserver)(PORT = 1521))

Also add a TNSNAMES.ORA alias to tnsnames.ora file on the original DB server:

        (ADDRESS = (PROTOCOL = TCP)(HOST = clonedbserver)(PORT = 1521))

Use tnsping and  then SQL*plus to test connectivity on both the servers by

Sqlplus sys@ORIGINAL as sysdba

Sqlplus sys@CLONE as sysdba

Step 8 – RMAN Cloning

A CLONE database can be created either from the active database or backups, as shown below:

Step 8a - RMAN Cloning from Active Database

Start RMAN session on the clone database server as shown below. This example is for RMAN with NOCATALOG.

C:\> rman nocatalog
RMAN> connect target sys@ORIGINAL
RMAN> Connect auxiliary sys@CLONE
RMAN> duplicate target database to CLONE from active database;

Please note that you cannot use the UNTIL clause when performing active database cloning. RMAN chooses a time based on when the online datafiles have been completely copied, so that the datafiles can be recovered to a consistent point in time.

Step 8b - RMAN Cloning from Backup Location

Copy Oracle Backups, including control file, data files and archive logs to E:\OraBack\Clone folder on the CLONE DB Server

C:\> rman nocatalog
RMAN> connect target sys@ORIGINAL
RMAN> Connect auxiliary sys@CLONE
RMAN> duplicate target database to CLONE backup location 'e:\oraback\clone' ;

If you want to clone the database till a specific point-in-time, then use the following command.

RMAN> duplicate target database to CLONE 
      from backup location 'e:\oraback\clone' 
      until time '08/03/2010:23:00:00'

Make sure that you specify the same database name above as specified in init.ora file. Otherwise, you would get “RMAN-05520: database name mismatch”

Wow! CLONE Database is up and running.

DUPLICATE command has performed the following steps:
  • Restored and recovered ORIGINAL database
  • Datafile and logfile names are converted per db_file_name_convert and log_file_name_convert init.ora parameters
  • New Control files created for CLONE database per control_files init.ora parameter
  • DB_NAME renamed to CLONE
  • Mounted CLONE database and performed complete media recovery. Applied necessary archive logs to accomplish this.
  • GLOBAL_NAME updated
  • TEMP files created 
  • Opened database with RESETLOGS option

No comments:

Post a Comment