Database Cloning

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

1.2 Scope
The document covers Database related information

1.3 Audience
database administrator prepares this document for so and so




2 CLONING PROCEDURE

PROD system, go into SQL*plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”.

This will create a create controlfile text in the udump directory

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database Altered

SQL>show parameter user_dump_test

.TRC file will be under the directory that is displayed by the above command

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MUAT" NORESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:\ORADATA\MUAT\REDO01.LOG’ SIZE 100M,
GROUP 2 'D:\ORADATA\MUAT\REDO02.LOG’ SIZE 100M,
GROUP 3 'D:\ORADATA\MUAT\REDO03.LOG’ SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORADATA\MUAT\SYSTEM01.DBF',
'D:\ORADATA\MUAT\SYSTEM02.DBF',
'D:\ORADATA\MUAT\DRSYS01.DBF',
'D:\ORADATA\MUAT\INDX01.DBF',
'D:\ORADATA\MUAT\TOOLS01.DBF',
'D:\ORADATA\MUAT\USERS01.DBF',
'D:\ORADATA\MUAT\USER_DATA.DBF',
'D:\ORADATA\MUAT\USER_INDEX.DBF',
'D:\ORADATA\MUAT\ROLLBACK_DATA.ORA',
'D:\ORADATA\MUAT\USER_INDEX2.DBF',
'D:\ORADATA\MUAT\USER_DATA2.DBF',
'D:\ORADATA\MUAT\RMAN01.DBF',
'D:\ORADATA\MUAT\USER_DATA3.DBF',
'D:\ORADATA\MUAT\USER_INDEX3.DBF',
'D:\ORADATA\MUAT\USER_DATA4.DBF',
'D:\ORADATA\MUAT\USER_INDEX4.DBF',
'D:\ORADATA\MUAT\USER_INDEX5.DBF',
'D:\ORADATA\MUAT\USER_DATA5.DBF',
'D:\ORADATA\MUAT\USERDATA\USER_DATA.DBF',
'D:\ORADATA\MUAT\USER_INDEX6.DBF'
CHARACTER SET UTF8
;
# 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;
# No tempfile entries found to add.


This will be the script used to recreate the control file in the Target Machine .



Shutdown the OLD Database.

SQL>SHUTDOWN IMMEDIATE


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.
Files to be copied
Datafiles
Controlfiles (For the safer side)
Redolog File
Init.ora File
PWD file




Copy and Edit the Control file – Using the output syntax from 2.2, modify the controlfile creation script by changing the following:Old: CREATE CONTROLFILE REUSE DATABASE "MUAT" NORESETLOGS
New: CREATE CONTROLFILE REUSE DATABASE "MUATDEV" NORESETLOGS

Note: The above step is needed only if we are going to change the Instance Name.



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;


Copy the files to any directory and edit the control file where the file has been moved physically. This is needed while starting the DB or else you will get ora- error stating missing file.
Save as Create_Control.sql.Old:
DATAFILE'D:\oradata\muat\system01.dbf','D:\oradata\muat\mydatabase.dbf'


New:
DATAFILE'G: \oradata\muat\system01.dbf','G: \oradata\muat\mydatabase.dbf’


Start the database in nomount state and recreate the control file created from the earlier script in 2.2.This is will create the control file and to opened.
SQL>Startup mount
SQL>@create_controlfile.sql

SQL>Alter database open

Put the Database in archivelog mode


This will help us to recover the Database if a file has been corrupted. Since the Database runs in ARCHIVELOG MODE we can recover in any kind of situation.




Hope this helps a bit