Cloning an Oracle 10g Database on a New Server

In the previous blog, I explained how to clone an Oracle 10g Database on the same server using RMAN. As explained in the previous blog, cloning an Oracle database on the same server contends for the resources with the primary database, and it may not work for many of you. Your only option is to clone the database on a different server.

Whether you are planning to clone an Oracle database on the same server or different server, the RMAN procedure is pretty much the same with the exception of few additional steps that you need to perform. All steps are described below.

The requirement for this task is similar – clone an existing development database C10gDEV as C11gDEV, but on a new server. Let’s use the terms primary and secondary servers. C10gDEV DB resides on the primary server, but C11gDEV DB will reside on the secondary server. The plan is to clone C10gDEV database and then upgrade C11gDEV database to 11g R2. Whatever your requirement may be, the steps described here are still applicable. 

Oracle database version is 10.2.0.4 and operating system is Windows 2008 64-bit.

Step 1 - Assign Oracle SID

Assign the following identifiers to the new database. All identifiers should be different than the database being cloned.

Database Name - C11gDEV
Instance Name - C11gDEV
Service Name - C11gDEV
TNS Connect String – C11gDEV

Step 2 - Allocate Disk Resources

The first step is to allocate sufficient disk space for the new database. For simplicity, assume that all data files and log files are going to the same folder E:\Oradata\C11gDEV. It’s development environment!

Step 3 - Create folder structure

Create the following folders to hold administrative data. All folder names defined here are as per local organization naming standards. Follow your organizational naming standards.

Audit Files - E:\Oradmin\C11gDEV\audit
Background Dump Destination - E:\Oradmin\C11gDEV\bdump
Core Dump Destination - E:\Oradmin\C11gDEV\cdump
User Dump Destination - E:\Oradmin\C11gDEV\udump

Archive Log Destination– E:\Orarch\C10gDEV (if ARCHIVELOG needed)

Please note that the name of the archive log destination is the same on both databases. Change the name later if needed. Otherwise you need to copy the archive logs to new location on the primary server and then run “CATALOG FROM” command to catalog archive logs.

Step 4 - Init.ora Updates

Copy existing C10gDEV’s init.ora file as initC11gDEV.ora, and make the following changes. The change list suggested below should be sufficient for most DBAs, but it’s not a complete list. You may have additional parameters that would require updates.

db_name=C11gDEV
instance_name= C11gDEV
service_names= C11gDEV

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

background_dump_dest
core_dump_dest
user_dump_dest
audit_file_dest

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

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

C10GDEV’s datafiles reside on E:\OraData\C10gDEV, but Oracle DBAs have decided to store C11gDEV’s database in E:\OraData\C11gDEV.

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

db_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV')

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

For example,

db_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV',
                      'd:\oradata\C10gDEV','e:\oradata\C11gDEV' )

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

log_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV')

Adjust the following memory parameters as per memory availability.

sga_target
sga_max_size
db_cache_size
pga_aggregate_target
java_pool_size
large_pool_size
shared_pool_size
streams_pool_size

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

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 C11gDEV -syspwd verysecretdbpassword -startmode auto -pfile initc11gdev.ora

This command creates a Windows service for C11gDEV 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 C11gDEV database as SYSDBA.

orapwd file=orapwC11gDEV password=verysecretpassword

Please note the password file name.

Step 6 - C11gDEV Instance Startup

Start C11gDEV Oracle database instance with nomount option by:

C:\> set ORACLE_SID=C11gDEV
C:> sqlplus /nolog
SQL> connect / as sysdba
SQL> Startup nomount
SQL> exit

Remember to logout from this SQL*Plus session. Otherwise, your cloning process would hang as new database will be shutdown in normal mode during the cloning process.

Step 7 - Listener Configuration

Add C11gDEV database to listener.ora file. Also create a new TNSNAMES.ORA alias. Use tnsping or SQL*plus to test connectivity by

Sqlplus sys@c10gdev as sysdba

Sqlplus sys@c11gdev as sysdba

Step 8 - Copy Backup to the Secondary Server

Restore on secondary server C10gDEV database’s RMAN backup and required set of archived redo logs to the same location as on the primary server.  RMAN expects the backup sets to be available in the location on the secondary server as primary database server.

If you don’t have the same folder structure available on the secondary server, then you need to perform few additional steps. For example, C10gDEV’s RMAN backup resides on D:\OraBack\C10GDEV, but you need to restore backup to E:\OraBack\C11gDEV on the secondary server. Copy C10gDEV’s RMAN backup to the new location on the primary server, and run CATALOG FROM command to catalog a copy of the backup.

Step 9 - Update RMAN Catalog

C:\> set ORACLE_SID=C10gDEV

C:\> rman nocatalog

RMAN> connect target /

RMAN> crosscheck backup

RMAN> crosscheck archivelog all

RMAN> delete expired backup

RMAN> catalog start with '<RMAN Backup Location>' ;

Step 10 - RMAN Cloning

Start RMAN session as shown below. This example is for RMAN with NOCATALOG.

C:\> set ORACLE_SID=C11gDEV

C:\> rman nocatalog

RMAN> connect target sys@C10gDEV

RMAN> Connect auxiliary /

RMAN> duplicate target database to C11gDEV ;

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

RMAN> duplicate target database to C11gDEV 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! C11gDEV Database is up and running.

DUPLICATE command has performed the following steps:

• Restored and recovered C10GDEV 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 C11gDEV database per control_files init.ora parameter
• DB_NAME renamed to C11gDEV
• Mounted C11gDEV database and performed complete media recovery. Applied necessary archive logs to accomplish this.
• GLOBAL_NAME updated
• TEMP files created 
• Opened database with RESETLOGS option