Clone of a database on the same Host i.e. Server (Linux RHEL -4)

Important Note: It is recommended NOT to clone the production database on the same server or host. But as a practice you can do it for any non-prod instances.

Below are the software details:

DB Name: PROD
DB Version: Oracle 10g Release 2 (10.2.0.1)
OS Version: Red Hat Enterprise Linux (RHEL) – 4

The steps which I have followed for cloning:

Perform the database backup using RMAN.

Login as ORACLE on Linux

$ su – oracle Password: xxxxxx (Issue the password here)

Check and set the ORACLE_HOME, ORACLE_SID, and other environment variables.

$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0 $ export ORACLE_SID=prod $ echo $ORACLE_HOME

Note: Oracle user has a profile (.bash_profile) in which all required environment variables are set, whenever the oracle user logs in to the server the environment variables are set automatically.

Below are the .bash_profile contents.

$ vi. .bash_profile

# User specific environment and startup programs
PATH=$HOME/bin:/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin
export PATH
ORACLE_HOME=/u01/app/oracle/product/10.2.0
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
ORACLE_SID=prod
export ORACLE_HOME
export TNS_ADMIN
export LD_LIBRARY_PATH
export ORACLE_SID

:q!

Check if the target database is in Archive log mode or not.

$ sqlplus / as sysdba

SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 59
Next log sequence to archive 61
Current log sequence 61
SQL>

Here in our case the database is already configured for Archive log mode, and the archive log destination is used by Flash Recovery Area.

If your DB is in no Archive log mode then change the mode to Archive log.

Set FRA (Flash Recovery Area) in your init/spfile.ora

DB_RECOVERY_FILE_DEST=/u03/FRA
DB_RECOVERY_FILE_DEST_SIZE=4G

Startup up the database in mount state

$ sqlplus / as sysdba

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVE LOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;

Connect to RMAN and target instance to perform RMAN backup

$ export ORACLE_SID=prod
$ rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

-: BACKUP DATABASE PLUS ARCHIVELOG:-

This command will take the backup of all database files and archived log files, and place them in flash recovery area (/u03/FRA/PROD/backupsets/__

/, all the backup files contain the extension with .bkp

To check and list the backup taken previously using RMAN’s LIST commands.

RMAN> LIST BACKUP;

Prepare the initialization parameter file (init.ora) for clone database.

Copy the target database init.ora file and rename it corresponding to you clone instance.

$ cd $ORACLE_HOME/dbs

Note: If you do not find the init.ora file for your target instance under the directory $ORACLE_HOME/dbs, may be you are using spfile or the init.ora file might have kept somewhere in another location;

If the spfile is being used, then create the pfile from spfile

SQL> CREATE PFILE FROM SPFILE;

$ cp –p initPROD.ora initTEST.ora

Edit the initialization parameter file (initTEST.ora) and update the following parameters accordingly to your clone instance.

USER_DUMP_DEST=
BACKGROUND_DUMP_DEST=
AUDIT_FILE_DEST=
CORE_DUMP_DEST=
USER_DUMP_DEST=
CONTROL_FILES=
DB_NAME=
INSTANCE_NAME=
SERVICE_NAMES=
DB_FILE_CONVERT_NAME=
LOG_FILE_CONVERT_NAME=

Note: In general, cloned instances are not kept in archive log mode, so you can remove or comment both parameters related to FRA, DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

Create the password file for clone (TEST) instance.

$ export ORACLE_SID=test

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwtest password=sys

Note: Make sure that the Production (Target) password and Test (Clone) password for orapwd file must be same.

Prepare the listener and tnanemes.ora entries for clone (TEST) instance.

$ cd $ORACLE_HOME/network/admin

Open listener.ora file and add the TEST instance entry as below.

(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
)

Open tnsnames.ora file and add the TEST instance entry as below.

test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = unicorn.kware) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =test)
)
)

Check the Prod and Test service can be pinged.

$ tnsping prod

$ tnsping test

Start the clone instance (TEST) in no mount state.

$ export ORACLE_SID=test
$ sqlplus / as sysdba

SQL> startup nomount;

SQL> select instance_name from v$instance;

INSTANCE_NAME
-------------------------
test

Connect to RMAN and target & auxiliary instances.

Open a new connection.

$ export ORACLE_SID=prod
$ rman target / auxiliary sys/sys@test

RMAN> RUN
2> {
3> ALLOCATE AUXILIARY CHANNEL AUX1 DEVICE TYPE DISK;
4> DUPLICATE TARGET DATABASE TO 'test';
5> }

Above command output will be a self explanatory, please go through the output generated.

RMAN> exit;

You are now done with clone process.

Checks to be performed after the clone process is finished

Execute the below steps in Test (Clone) instance, and cross check the result with the Production (Target) instance.

$ export ORACLE_SID=test

$ sqlplus / as sysdba

SQL> SELECT OPEN_MODE,NAME FROM V$DATABASE;

SQL> ARCHIVE LOG LIST;

SQL> SELECT NAME FROM V$TABLESPACE;

SQL> SELECT USERNAME FROM DBA_USERS;

SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';


If you find invalid objects then you can compile them using $ORACLE_HOME/rdbms/admin/utlrp.sql