First : What is Oracle Database Clone :
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.
Second : Benefit Of Clone :
1-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.
2-quickly migrate a system from one server to another .
3-fastest way to copy a Oracle database .
How We do it :
Step-1 : On the old system, go into SQL*Plus, sign on as SYSDBA and issue:
SQL>alter database backup controlfile to trace;
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;
Step-2 : Shutdown Old Database
SQL>Shutdown 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:
CREATE CONTROLFILE REUSE DATABASE “OLDLSQ” NORESETLOGS
CREATE CONTROLFILE SET DATABASE “NEWLSQ” RESETLOGS
Step-4 :Remove the “recover database” and “alter database open” syntax
Step-5:Re-names of the data files location to new clone location in a 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.
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.
Thank you
osama mustafa