ORACLE DBA: clone database in oracle

cloning or refreshing or renaming oracle database
A database clone is a complete and separate copy of a database system that includes the business data, the DBMS software and any other application tiers that make up the environment. Cloning is a different kind of operation to replication and backups in that the cloned environment is both fully functional and separate in its own right. Additionally the cloned environment may be modified at its inception due to configuration changes or data subsetting.
The cloning refers to the replication of the server in order to have a backup, to upgrade the environment.

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue:
SQL>alter database backup controlfile to trace;

This will put the create database syntax in the trace file directory(UDUMP FOLDER, last created trace file in user trace files in udump folder). The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
copy and paste the below lines from your user trace file and save it as dbclone_controlfile_creation.sql in any location in your system

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u01/oradata/oldlsq/log1a.dbf',
'/u01/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u01/oradata/oldlsq/log2a.dbf',
'/u01/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

note:you have to change the yellow marked words thats why i marked those words in yellow.

STEP 2: Shutdown the old database

SQL>shut immediate;

STEP 3: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

open the dbclone_controlfile_creation.sql in a notepad or text editor and change the below yellow lines to green lines

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 4: Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

STEP 5: Re-names of the data files location to new clone location in a dbclone_controlfile_creation.sql.
change the yellow marked lines to green marked lines in dbclone_controlfile_creation.sql
Old:

DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'

STEP 6: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq

otherwise create newfolder for clone database and copy and paste all datafiles and redolog files from primary database folder to the clone database destination folder.

/u01/oradata/oldlsq folder to /u01/oradata/newlsq folder.

STEP 7: Create the bdump, udump and cdump directories

cd /u01/admin/
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

STEP 8: Copy-over the old init.ora file

open the primary database pfile in a notepad or text editor and change the udump,bdump,pfile,controlfile destination and save it in new clone pfile location newlsq folder and save as newlsq.ora
eg: primary database location /u01/admin/oldlsq/pfile/oldlsq.ora
open that oldlsq.ora file in a text editor or notepad and change the required destinations
cdump,udump,bdump,controlfile destinations and save as newlsq.ora in the below destination
/u01/admin/newlsq/pfile/newlsq.ora

STEP 9: Start the new database

SQL>startup nomount pfile=' /u01/admin/newlsq/pfile/newlsq.ora ' ;

SQL> @dbclone_controlfile_creation.sql

SQL>alter database open resetlogs;

SQL>create spfile from pfile;

STEP 10: Place the new database in archivelog mode
for enabling archivelog mode refer the following link:
http://rajeshkumar-oracledba.blogspot.com/2008_10_01_archive.html

thanks for reading this documentation and visiting this blog