Cloning an Oracle Database

You have a Production database running in one server. The company management wants to develop some new modules and they have hired some programmers to do that. Now these programmers require access to the Production database and they want to make changes to it. You as a DBA can’t give direct access to Production database so you want to create a copy of this database on another server and wants to give developers access to it.

Let us see an example of cloning a database

We have a database running the production server with the following files

PARAMETER FILE located in /u01/oracle/ica/initica.ora

CONTROL FILES=/u01/oracle/ica/control.ora
BACKGROUND_DUMP_DEST=/u01/oracle/ica/bdump
USER_DUMP_DEST=/u01/oracle/ica/udump
CORE_DUMP_DEST=/u01/oracle/ica/cdump
LOG_ARCHIVE_DEST_1=”location=/u01/oracle/ica/arc1”

DATAFILES =

/u01/oracle/ica/sys.dbf
/u01/oracle/ica/usr.dbf
/u01/oracle/ica/rbs.dbf
/u01/oracle/ica/tmp.dbf
/u01/oracle/ica/sysaux.dbf

LOGFILE=

/u01/oracle/ica/log1.ora
/u01/oracle/ica/log2.ora

Now you want to copy this database to SERVER 2 and in SERVER 2 you don’t have /u01 filesystem. In SERVER 2 you have /d01 filesystem.

To Clone this Database on SERVER 2 do the following.

Steps :-

1. In SERVER 2 install the same version of o/s and same version Oracle as in SERVER 1.

2. In SERVER 1 generate CREATE CONTROLFILE statement by typing the following command

SQL>alter database backup controlfile to trace;

Now, go to the USER_DUMP_DEST directory and open the latest trace file. This file will contain steps and as well as CREATE CONTROLFILE statement. Copy the CREATE CONTROLFILE statement and paste in a file. Let the filename be cr.sql

The CREATE CONTROLFILE Statement will look like this.

CREATE CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('/u01/oracle/ica/log1.ora'
GROUP 2 ('/u01/oracle/ica/log2.ora'
DATAFILE '/u01/oracle/ica/sys.dbf' SIZE 300M,
'/u01/oracle/ica/rbs.dbf' SIZE 50M,
'/u01/oracle/ica/usr.dbf' SIZE 50M,
'/u01/oracle/ica/tmp.dbf' SIZE 50M,
‘/u01/oracle/ica/sysaux.dbf’ size 100M;
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;

3. In SERVER 2 create the following directories

$ cd /d01/oracle
$ mkdir ica
$ mkdir arc1
$ cd ica
$ mkdir bdump udump cdump

Shutdown the database on SERVER 1 and transfer all datafiles, logfiles and control file to SERVER 2 in /d01/oracle/ica directory.

Copy parameter file to SERVER 2 in /d01/oracle/dbs directory and copy all archive log files to SERVER 2 in /d01/oracle/ica/arc1 directory. Copy the cr.sql script file to /d01/oracle/ica directory.

4. Open the parameter file SERVER 2 and change the following parameters

CONTROL FILES=//d01/oracle/ica/control.ora
BACKGROUND_DUMP_DEST=//d01/oracle/ica/bdump
USER_DUMP_DEST=//d01/oracle/ica/udump
CORE_DUMP_DEST=//d01/oracle/ica/cdump
LOG_ARCHIVE_DEST_1=”location=//d01/oracle/ica/arc1”

5. Now, open the cr.sql file in text editor and change the locations like this

CREATE CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('/d01/oracle/ica/log1.ora'
GROUP 2 ('/d01/oracle/ica/log2.ora'
DATAFILE '/d01/oracle/ica/sys.dbf' SIZE 300M,
'/d01/oracle/ica/rbs.dbf' SIZE 50M,
'/d01/oracle/ica/usr.dbf' SIZE 50M,
'/d01/oracle/ica/tmp.dbf' SIZE 50M,
‘/d01/oracle/ica/sysaux.dbf’ size 100M;
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;

In SERVER 2 export ORACLE_SID environment variable and start the instance

$export ORACLE_SID=ica
$sqlplus
Enter User:/ as sysdba
SQL> startup nomount;

6. Run cr.sql script to create the controlfile

SQL> @/d01/oracle/ica/cr.sql

7. Open the database

SQL> alter database open;

«« Previous

Index

DB Exam Study