oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Cloning an Oracle Database

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;

 

 

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved