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/

Clone of a database on the same Host i.e. Server (Linux RHEL -4)

Dear friends,

As we all know, Cloning of a Database can be done using different backup methods i.e. using cold backup/hot backup (ALTER TABLESPACE <tablespace_name> BEGING BACKUP), and RMAN Backup with DUPLICATE TARGET DATABASE.

Recently I have performed the cloning for one of our University databases using the RMAN backup and DUPLICATE TARGET DATABASE commands, on the same host i.e. same server.

Important Note: It is recommended NOT to clone the production database on the same server or host. But as a practice you can do it for any non-prod instances.

Below are the software details:

DB Name: PROD
DB Version: Oracle 10g Release 2 (10.2.0.1)
OS Version: Red Hat Enterprise Linux (RHEL) – 4

The steps which I have followed for cloning:

Perform the database backup using RMAN.

Login as ORACLE on Linux

$ su – oracle Password: xxxxxx (Issue the password here)

Check and set the ORACLE_HOME, ORACLE_SID, and other environment variables.

$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0 $ export ORACLE_SID=prod $ echo $ORACLE_HOME

Note: Oracle user has a profile (.bash_profile) in which all required environment variables are set, whenever the oracle user logs in to the server the environment variables are set automatically.

Below are the .bash_profile contents.

$ vi. .bash_profile

# User specific environment and startup programs
PATH=$HOME/bin:/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin
export PATH
ORACLE_HOME=/u01/app/oracle/product/10.2.0
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
ORACLE_SID=prod
export ORACLE_HOME
export TNS_ADMIN
export LD_LIBRARY_PATH
export ORACLE_SID

:q!

Check if the target database is in Archive log mode or not.

$ sqlplus / as sysdba

SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 59
Next log sequence to archive 61
Current log sequence 61
SQL>

Here in our case the database is already configured for Archive log mode, and the archive log destination is used by Flash Recovery Area.

If your DB is in no Archive log mode then change the mode to Archive log.

Set FRA (Flash Recovery Area) in your init/spfile.ora

DB_RECOVERY_FILE_DEST=/u03/FRA
DB_RECOVERY_FILE_DEST_SIZE=4G

Startup up the database in mount state

$ sqlplus / as sysdba

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVE LOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;

Connect to RMAN and target instance to perform RMAN backup

$ export ORACLE_SID=prod
$ rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

-: BACKUP DATABASE PLUS ARCHIVELOG:-

This command will take the backup of all database files and archived log files, and place them in flash recovery area (/u03/FRA/PROD/backupsets/__

/, all the backup files contain the extension with .bkp

To check and list the backup taken previously using RMAN’s LIST commands.

RMAN> LIST BACKUP;

Prepare the initialization parameter file (init.ora) for clone database.

Copy the target database init.ora file and rename it corresponding to you clone instance.

$ cd $ORACLE_HOME/dbs

Note: If you do not find the init.ora file for your target instance under the directory $ORACLE_HOME/dbs, may be you are using spfile or the init.ora file might have kept somewhere in another location;

If the spfile is being used, then create the pfile from spfile

SQL> CREATE PFILE FROM SPFILE;

$ cp –p initPROD.ora initTEST.ora

Edit the initialization parameter file (initTEST.ora) and update the following parameters accordingly to your clone instance.

USER_DUMP_DEST=
BACKGROUND_DUMP_DEST=
AUDIT_FILE_DEST=
CORE_DUMP_DEST=
USER_DUMP_DEST=
CONTROL_FILES=
DB_NAME=
INSTANCE_NAME=
SERVICE_NAMES=
DB_FILE_CONVERT_NAME=
LOG_FILE_CONVERT_NAME=

Note: In general, cloned instances are not kept in archive log mode, so you can remove or comment both parameters related to FRA, DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

Create the password file for clone (TEST) instance.

$ export ORACLE_SID=test

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwtest password=sys

Note: Make sure that the Production (Target) password and Test (Clone) password for orapwd file must be same.

Prepare the listener and tnanemes.ora entries for clone (TEST) instance.

$ cd $ORACLE_HOME/network/admin

Open listener.ora file and add the TEST instance entry as below.

(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
)

Open tnsnames.ora file and add the TEST instance entry as below.

test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = unicorn.kware) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =test)
)
)

Check the Prod and Test service can be pinged.

$ tnsping prod

$ tnsping test

Start the clone instance (TEST) in no mount state.

$ export ORACLE_SID=test
$ sqlplus / as sysdba

SQL> startup nomount;

SQL> select instance_name from v$instance;

INSTANCE_NAME
-------------------------
test

Connect to RMAN and target & auxiliary instances.

Open a new connection.

$ export ORACLE_SID=prod
$ rman target / auxiliary sys/sys@test

RMAN> RUN
2> {
3> ALLOCATE AUXILIARY CHANNEL AUX1 DEVICE TYPE DISK;
4> DUPLICATE TARGET DATABASE TO 'test';
5> }

Above command output will be a self explanatory, please go through the output generated.

RMAN> exit;

You are now done with clone process.

Checks to be performed after the clone process is finished

Execute the below steps in Test (Clone) instance, and cross check the result with the Production (Target) instance.

$ export ORACLE_SID=test

$ sqlplus / as sysdba

SQL> SELECT OPEN_MODE,NAME FROM V$DATABASE;

SQL> ARCHIVE LOG LIST;

SQL> SELECT NAME FROM V$TABLESPACE;

SQL> SELECT USERNAME FROM DBA_USERS;

SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';


If you find invalid objects then you can compile them using $ORACLE_HOME/rdbms/admin/utlrp.sql

Reference:

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

Conclusion:

I have followed the above procedure/steps as per our requirement in the University. I would request you to follow the complete method and other options from referenced link above.

With Best Regards,

Sabdar Syed,
Oracle DBA/ Apps DBA,
Oracle Certified Professional (OCP) 8i, 9i & 10g

My Blog: http://sabdarsyed.blogspot.com

Creating a Recovery Catalog – RMAN in Oracle 10g

As we all know, RMAN maintains metadata about the target database and its backup and
recovery operations in the RMAN repository. The RMAN repository data is always in the control file of the target database. The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter controls how long backup records are kept in the control file before those records are re-used to hold information about more recent backups. By default this parameter set to 7 days.

Another copy of the RMAN repository data can also be saved in the recovery catalog.

Using a recovery catalog preserves RMAN repository information if the control file is lost, making it much easier to restore and recover following the loss of the control file. (A backup control file may not contain complete information about recent available backups.) The recovery catalog can also store a much more extensive history of your backups than the control file, due to limits on the number of control file records.

In addition to RMAN repository records, the recovery catalog can also hold RMAN stored scripts, sequences of RMAN commands for common backup tasks. Centralized storage of scripts in recovery catalog can be more convenient than working with command files.

Create a new database for RMAN – Recovery catalog database

Note: You can create a small database with minimal sizes of tablespaces and others, and you can name the database as CATDB for naming convention and to avoid the confusion between your production and rman databases.

Create a new tablespace in the new database (CATDB)

$ sqlplus /nolog

CONNECT SYS/passwd@catdb AS SYSDBA;

CREATE TABLESPACE rman
DATAFILE '/u02/app/oradata/rman/rman01.dbf' size 100m;

Create the Recovery Catalog Owner in the new database (CATDB)

CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;

Grant the necessary privileges to the schema owner

SQL> GRANT connect, resource, recovery_catalog_owner TO rman;

Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog

Creating the Recovery Catalog

Connect to the database which will contain the catalog as the catalog owner. For example:

$ rman catalog rman/passwd@catdb

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Apr 1 14:22:13 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN>

Run the CREATE CATALOG command to create the catalog

RMAN> CREATE CATALOG;

recovery catalog created

Registering a Database in the Recovery Catalog

Connect to the target database and recovery catalog database.

$ ORACLE_SID=prod; export ORACLE_SID

$ rman target / catalog rman/passwd@catdb

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Apr 1 14:25:30 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=3677528376)
connected to recovery catalog database

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Make sure that the registration was successful by running REPORT SCHEMA:

RMAN> REPORT SCHEMA;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 500 SYSTEM YES /u02/app/oradata/prod/system01.dbf
2 200 UNDOTBS1 YES /u02/app/oradata/prod/undotbs01.dbf
3 325 SYSAUX NO /u02/app/oradata/prod/sysaux01.dbf
4 100 EXAMPLE NO /u02/app/oradata/prod/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 200 TEMP 200 /u02/app/oradata/prod/temp01.dbf

Reference: Please go through the Oracle Documents for RMAN concepts.
http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/toc.htm

Regards,
Sabdar Ssyed.