Cloning and Refreshing an Oracle Database

Dear Friends and Blog Readers,

I have been asked many times about the Cloning and Refresh process of Oracle Database by emails of blog readers and the users of the Oracle Technology Network (OTN) Forums.

Even though the information about Cloning and Refreshing a Database process available over web widely or has already been discussed. Here, in this post, I would like to explain and provide the information on the following Questions about Cloning and Refreshing of a Database with my simple terms.

Terms used in this post:

Source System - the system to be cloned - Production
Target System - the newly created (or cloned) system – Non Production
Production Database – PROD
Test Database – TEST
Development Database - DEV

What is a Database Clone?

* A database clone is an activity/procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test/Development teams.

* Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area.

* Cloning is a procedure for preparing and creating a test or development servers with the copy of Oracle production database for testing upgrades, migrating an existing system to new hardware.

* A cloning process includes a copy of Oracle Home (Directories and Binaries) backup and Database (Database related files) backup to prepare the instance on another server.

* Though, it is possible to clone a database on the same server, Oracle doesn’t suggest to clone a database on the same server, where the production database is running.

What is a Database Refresh?

* A Database Refresh is also referred to as a database clone. However, we don’t clone Oracle Home rather we clone the Database as refresh.

* Refreshing a database is something like applying the changes or updates of production database to the database where the database is already cloned. i.e. let’s say you have cloned a database a month back, and now you are asked for doing refresh of a database, then you will perform the backup of database and prepare the clone the instance again on test server. This is nothing but refreshing.

* Refreshing of a particular table, group of tables, schema, or tablespace will be done using traditional export/import, transportable Tablespaces, or data pump methods.

* When an Oracle patch is applied on Production System, or in doubt, you have to prepare and clone the database again with the copy of Oracle Home (Directories and Binaries) Backup and Database (Database related files) Backup to prepare the instance.

* The difference between Cloning and Refreshing is that cloning process includes Oracle Home and database Clone; where as Refreshing process only includes database clone.

* If seen, the words, Clone and Refresh are used interchangeably for the sake of convenient.

When and why we Clone a Database?

* Generally production (PROD) database is cloned for various reasons and needs i.e. for something to be tested or something to be developed later those to be moved to production.

* It’s normal and quite common thing is that whenever there is any change or update to be performed and do not know the impact or effect after applying it on production (PROD), it’s required to be applied and tested on *NON* production database first (TEST or DEV), after the confirmation of change success, given by the users, then the changes will be moved to production.

* A Cloned test instance (TEST) for testing team/environment is exclusively used for testing the changes or issues which will be come severe on Production. Oracle Support gives the solution as fix when there is an issue in the database, so this fix needs to perform or apply on test/development databases.

* A Cloned development instance (DEV) for development team/environment is used for developing the new changes and then deploying the same on Production.

* A Cloned patch instance is used for patching to know the impact and the time required to apply the same on Production.

How to clone an Oracle Database and different ways of cloning.

There are many possible methods available for cloning a database, but each of them has pros and cons, and significance. Following are the methods.

Using Cold (Offline) Backup:

This is an easy and simple method to perform a clone of a database. This method requires your production database (PROD) needs to be shutdown gracefully, and take the backup of the database related files i.e. Data files, Control files, Redo Log files, using Operating System commands i.e. cp or copy. This is not possible where your PROD database is running 24/7 and should be available continuously for users.

For syntax and the series of steps to perform the clone using cold backup, refer the following URLs from the reference.

References:
http://www.samoratech.com/TopicOfInterest/swCloneDB.htm
http://www.pgts.com.au/pgtsj/pgtsj0211b.html
http://www.jlcomp.demon.co.uk/faq/clone_db.html

Using Hot (Online) Backup:

In this method, backup of the database will be done online i.e. without shutting down the database.

For this, your Production Database is must be in Archive log mode. For syntax and the series of steps to perform the clone using hot backup, refer the following URLs from the reference.

Reference:
http://www.quest-pipelines.com/newsletter/cloning.htm
http://www.oralnx.com/index.php/2007/03/22/cloning-an-oracle-database/
http://www.shutdownabort.com/quickguides/clone_hot.php

Using RMAN Commands:

Cloning can also be performed using RMAN Backups and RMAN commands and it’s also an easy method to perform so. The RMAN DUPLICATE command is used to perform the clone. Until Oracle 9i, to clone the database, it is required to be the Source and Target systems should have the same OS i.e. it is not possible to clone across the platform. But as workaround, using export/import can be cloning the database across the platforms. But starting from Oracle 10g the RMAN capabilities have improved immensely. Cross platform cloning/duplicating a database can be done using RMAN CONVERT commands.

For syntax and the series of steps to perform the clone using RMAN Commands, refer the following URLs from the reference.

References:

Creating and Updating Duplicate Databases with RMAN

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#i1008564

Cross-Platform Transportable Database: RMAN CONVERT DATABASE

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/dbxptrn002.htm#CHDCFFDI

Creating a Duplicate Database on a Local or Remote Host

http://sabdarsyed.blogspot.com/2007/06/clone-of-database-on-same-host-ie.html

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb005.htm

Pre & Post Cloning Steps/Changes:

* Do *NOT* set the clone database name as good as production database Name.

* It’s *NOT* mandatory to have the initialization parameter values of cloned instance similar to Production Instance.

* It is *NOT* mandatory to have the cloned instance in Archive log mode. Because unnecessarily archive log files are generated, which consume the hard disk space? If at all, the cloned instance crashed and need to be recovered, it can easily be again cloned from the production.

* After the clone, change the system users passwords i.e. SYS & SYSTEM, and for any critical users passwords.

* Disable the jobs which are not required to be run in the cloned instance.

* Change any application users tables from the cloned database which are still referring the Production Database i.e. Server IP, Port Details, Printer Details etc,

Other Useful Links:

OTN Forums on Cloning:
http://forums.oracle.com/forums/search.jspa?threadID=&q=clone+a+database&objID=f61&dateRange=all&userID=&numResults=30&rankBy=10001

Ask Tom Forums:

DB cloning -- what is it and why
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:575623107841

Creating test environment from production box
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:526422273445

Metalink Notes:

Note:245262.1 - Subject: Create a Duplicate Database with NOCATALOG on Same Node
Note:458450.1 - Subject: Steps to Manually Clone a Database
Note:388431.1 - Subject: Creating a Duplicate Database on a New Host.

Conclusion: These are only my views and outlines about cloning and need *NOT* to be the same with others or Oracle. One individual can still follow their cloning procedure documents which are there in place with them. I strongly suggest one to do the changes first in the test/development before doing it on Production. And also, use Oracle Documentations and Metalink Support for any kind of help.

Hope that this post helps you in understanding the Cloning and Refreshing activity of a database.

**Please leave your comments/suggestions about this post**.

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/