Oracle Database Cloning

Oracle Database Cloning

* 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.

Here we can clone database using two methods they are

1.      Cloning using cold backup

2.      Cloning using hot backup

Cloning using cold backup

Clone an Oracle Database using Cold Physical Backup

Database Name: PROD

Source Database side: (PROD database)
Cold Backup Steps:
1. Get the file path information using below query

Select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

2. Parameter file backup
If PROD database running on spfile
Create pfile=’/u01/backup/inittroy.ora’ from spfile;

If database running in pfile using os command to copy the pfile and placed in backup path.
3. Taken the control file backup
sql>Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;
4.Shutdown immediate

5.Copy all the data files/log files using os command & placed in backup path.

6.Startup the database.
Clone Database side: (Clone database)

Database Name: TEST
Clone Database Steps:
1. Create the appropriate folder in corresponding path & placed the backup files in corresponding folder. (bdump,udump,create,pfile,cdump,oradata)

2. Change the init.ora parameter like control file path, dbname, instance name etc...

3. Create the password file using orapwd utility.

(Database in windows we need to create the service id using oradim utility)

4. Startup the Database in NOMOUNT stage.

5. Create the control file for cloning database.

Using backup controlfile trace to generate the create controlfile scripts.
Change the Database name & files path, also change 'REUSE' needs to be changed to 'SET'.


CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/U01/oradata/clone/redo01.log' SIZE 200M,
GROUP 2 '/U01/oradata/clone/redo02.log' SIZE 200M,
GROUP 3 '/U01/oradata/clone/redo03.log' SIZE 200M
DATAFILE
'/U01/oradata/clone/system01.dbf',
'/U01/oradata/clone/undotbs01.dbf',
'/U01/oradata/clone/users01.dbf',
CHARACTER SET WE8ISO8859P1;

Note: placed the script in sql prompt. Now controlfile created

6. Now open the database.

sql>Alter database open resetlogs;

Note: Check the logfile, datafiles & instance status

Cloning using Hot backup

Database Name: PROD

Database must be in Archive log mode.

Source Database side: (PROD database)

Hot Backup Steps:
1.Get the file path information using below query.
Select tablespace_name, file_name from dba_data_files order by 1;

2. Parameter file backup

If troy database running on spfile

Create pfile=’/u01/backup/inittroy.ora’ from spfile;

If database running in pfile using os command to copy the pfile and placed in backup path.

3.Put the database in begin backup mode Using os command to copy the datafiles, logfiles, archives  belongs to begin backup mode database& placed in backup path. (Refer below example)
4.Once copied the datafile,logfiles, archives release the database from begin backup mode to end backup
5.Taken the controlfile backup
sql >Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

6.Backup all your archive log files between the previous backup and the new backup as well.

Example:
steps:
2.Alter database begin backup;
3.copy entire datafiles, logfiles, archives.
4. Alter database end backup;
Clone Database side: (TEST database)

Database Name: TEST

Clone Database Steps:
1. Create the appropriate folder in corresponding path & placed the backup files in corresponding folder.(bdump,udump,create,pfile,cdump,oradata)

2.Change the init.ora parameter like control file path, dbname, instance name etc...

3. Create the password file using orapwd utility.

(Database in windows we need to create the service id using oradim utility)

4. Startup the Database in NOMOUNT stage.


5. Create the control file for cloning database.

Using backup controlfile trace to generate the create controlfile scripts.
Change the Database name & files path, also change 'REUSE' needs to be changed to 'SET'.

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/U01/oradata/clone/redo01.log' SIZE 200M,
GROUP 2 '/U01/oradata/clone/redo02.log' SIZE 200M,
GROUP 3 '/U01/oradata/clone/redo03.log' SIZE 200M
DATAFILE
'/U01/oradata/clone/system01.dbf',
'/U01/oradata/clone/undotbs01.dbf',
'/U01/oradata/clone/users01.dbf',
CHARACTER SET WE8ISO8859P1;
Note: placed the script in sql prompt. Now controlfile created.
6. Recover the database using controlfile.

Recover database using backup controlfile until cancel;

7.Now open the database.

Alter database open resetlogs;

Note: Check the logfile, datafiles status.