In this article We will explain the steps for cloning a database using hot backup method without using RMAN utility. During hot backup, the source database will be up and running.

NOTE – SOURCE DATABASE SHOULD BE IN ARCHIVELOG MODE FOR HOT BACKUP

Note- Both the source and target db server should be on same platform and the target db version will be that of the source db.So make sure oracle binary is already installed on target db server.

Here we will clone a database SRCDB to a new server with name TRGDB

SOURCE DB – SRCDB
TARGET DB – TRGDB

1. Make sure source database in ARCHIVE LOG MODE;[SOURCE DB]

2. Create required directories in target db server[TARGET DB]

3. Take backup of controlfile to trace[SOURCE DB]

4. Put source database in hot backup mode:[SOURCE DB]

5. Copy the datafile and tempfiles and the backup controlfile sql to target DB server

6. Once copy is done, remove db from hot backup[SOURCE DB]

6. Now prepare the target db initfile[TARGET DB]

We can copy the pfile from source db and the change the required parameters like DB_NAME and control_file,audit_file_dest,diag location.

7. Start the database in nomount stage:[TARGET DB]

export ORACLE_SID=TRGDB

startup nomount pfile=initTRGDB.ora

8. Re-recreate the controlfile [ TARGET DB ]

This is an important steps in cloning process. In step 1 , We had taken backup of the controlfile as trace, We will use that sql file to re-create the controlfile.

SNIPPET FROM controlfile sql script:

Lets modify this sql by replacing the datafile location of source with that of target and the new db name as TRGDB.

After changing it will look as below:

SET DATABASE:

Here we have use SET DATABASE, Because we are cloning the database in target with a new db name.

CREATE CONTROLFILE SET DATABASE “SRCDB” RESETLOGS FORCE LOGGING ARCHIVELOG

If you wish to keep the database same as source, then you can use the command REUSE DATABASE command.

Run the create controlfile statement:

Now if you try to open the database in resetlog mode it will fail with below errors.

SQL>  alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/u01/oracle/oradata/TRGDB/system01.dbf’

To fix it , we need to RECOVER DATABASE by apply the archives generated during which db was in hot backup mode.

It needs the archives starting from 1_5642_907410560.arc to recover the database.
Copy the available archives from the sequence #5642 from source db to target db archive location.

Once copied run the recovery command again: and select AUTO

Once all are applied, try to open the database in resetlog:

While opening the database , if you are still getting error like datafile need more recovery, they you might have missed few archive logs to apply.