Monday, September 13, 2010

Cloning an Oracle Database on the Same Server

Oracle DBAs routinely clone Oracle databases for variety of reasons. This blog explains how to clone an Oracle database on the same server. The requirement for this task is quite simple – clone an existing database. A development Oracle10g instance C10gDEV already exists. We just need to clone this database on the same server as C11gDEV, and then upgrade to 11g later. In this blog, we will talk about the cloning. Upgrade discussion would be part of future blogs.

With RMAN, Oracle DBAs can easily clone the database on the same server with few steps. The time needed to clone the database on the same server is proportional to the time it would take to restore/recover the database plus time to perform the few steps listed below.

In addition to creating or cloning a database for upgrade testing, you may also find few other creative and innovative applications. I have effectively used cloning for the following purposes:

• To quickly create a second database environment on the same server for testing/debugging/tuning.
• To validate and verify RMAN Backup/Recovery procedures by cloning a database and then verifying the data.
• To test Oracle upgrades by cloning the database and then upgrading it. You can also use this approach to test the patches.

Cloning the Database the Same Server technique is quite fast. No need to copy the backups to secondary server. RMAN backups are on disk and available. There are only few steps to perform the cloning.

Caution

• Think twice if you are planning to clone the production database on the same server. Some DBAs supporting mission critical applicaitons may not be able to clone the database on thhe same server. If you are 24 X 7 X 365, then be extra careful.

• Please ensure that you have sufficient CPU, disk and memory capacity on the server to mount a cloned instance and database on the server.

• You may want to perform cloning during maintenance window or off-peak hours to minimize performance impact on the primary database.

• And ensure that your backups are good and recoverable, just in-case!

1.2 Clone the DB!

The requirement for this task is quite simple – clone an existing development database C10gDEV on the same server as C11gDEV. Oracle DBA’s plan is to clone C10gDEV database on the same server and then upgrade C11gDEV database to 11g R2. Existing database version is 10.2.0.4 and operating system is Windows 2008 64-bit.

1.2.1 Assign Oracle SID

Assign the following identifiers to the new database. All identifiers must be different than the identifiers used for the database being cloned.

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

1.2.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!

1.2.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\C11gDEV (if ARCHIVELOG needed)

1.2.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.

1.2.5 Service and/or Password File Creation

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

set ORACLE_SID=C11gDEV

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

Note the difference in password file names on Windows and Unix. Please be careful with the password file name.

1.2.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 Oracle shuts down the new database in normal mode during the cloning process.

1.2.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.

1.2.8 Update RMAN Catalog

Most likely, you won't need this statp. This step is needed if (1) your objective is to verify database recovery process by restoring the RAMN backups from tape to disk to an alternate location and then clonig the database or (2) RMAN backup location has changed and control file doesn’t have the correct information.

If your objective is to test RMAN backup/recovery procedures, then you want to make sure that RMAN picks up the backups that were restored from the tape. Rename the existing C10gDEV database's RMAN backup location temporarily on disk to a new name, and then perform the following steps. If you don't perform this step, then RMAN would pick up the latest backup on disks, if available.

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 'E:\OraBack\C10gDev\RMANHot' ;

1.2.9 RMAN Cloning

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

Note that target database is your source database - the database that you want to clone. Auxiliary specifies the new database.

C:\> set ORACLE_SID=C10gDEV

C:\> rman nocatalog
RMAN> connect target /
RMAN> Connect auxiliary sys@C11gDEV
RMAN> duplicate target database to C11gDEV ;

Wow! C11gDEV Database is up and running.

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 as specified in init.ora file. Otherwise, you would get “RMAN-05520: database name mismatch”
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

1.2.10 Log File Excerpts

RMAN> duplicate target database to C11gDEV ;
Starting Duplicate Db at 15-DEC-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
   set newname for datafile  1 to ......
   set newname for datafile  2 to ......
   set newname for datafile  3 to ......
   ...........
   ...........
   ...........
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME
executing command: SET NEWNAME
..........
..........
..........

Starting restore at 15-DEC-10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to .........
restoring datafile 00002 to .........
restoring datafile 00003 to .........

channel ORA_AUX_DISK_1: reading from backup piece ..........
Finished restore at 15-DEC-09
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "C11gDEV" RESETLOGS NOARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
..........
..........
..........
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
.............
.............
.............

contents of Memory Script:
{
   recover
   clone database
   noredo
   ,
    delete archivelog
   ;
}
executing Memory Script

Starting recover at 15-DEC-09
using channel ORA_AUX_DISK_1
Finished recover at 15-DEC-09

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     629145600 bytes
Fixed Size                     1298288 bytes
Variable Size                171966608 bytes
Database Buffers             452984832 bytes
Redo Buffers                   2895872 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "C11gDEV" RESETLOGS NOARCHIVELOG
...............
...............


renamed temporary file 1 ......
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 15-DEC-09

RMAN>

2 comments:

  1. This is an excellent post. Clear, concise and easy to follow.

    Thanks you

    ReplyDelete