Steps for Cloning Oracle database with hot backup

Cloning Oracle database with hot backup

1.First get the details of datafiles and archivelog location that present in the original database using the below command.

SQL> Select name from v$datafile;
SQL> archive log list;

2.Get the latest SCN by using the below command.

SQL> select max(first_change#) chng
2 from v$archived_log
3 /

CHNG
———-
424485

3.Bring the database in begin backup mode.

SQL> Alter database begin backup;

Database altered.

Note :- Make sure once you start begin backup mode there shouldn’t be No RMAN backup run on the database till you end the backup mode. Because till 10G if begin backup mode and RMAN backup is start at the same time there is a change of undo segment corruption and its a BUG reported by oracle support and I too faced the same problem, to resolve this we need downtime so be cautious.

4.Check the status whether the datafiles are in backup mode.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 ACTIVE 424935 22-FEB-12
2 ACTIVE 424935 22-FEB-12

5.Now copy the datafiles physically from the original location to the destination.
Example :
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u04/app/oracle/product/10.2.0/oradata/test/system01.dbf
/u04/app/oracle/product/10.2.0/oradata/test/undotbs01.dbf
SQL>exit
$ cd /u04/app/oracle/product/10.2.0/oradata/test/
$ ls -lrt
-rw-r—– 1 oracle staff 209723392 Feb 22 12:29 undotbs01.dbf
-rw-r—– 1 oracle staff 429924352 Feb 22 12:29 system01.dbf
$ cp *.dbf /u03/oradata/clonedb/data

Note :- If you want to clone the database to the different server refer the end of the command for SCP (server level copy command)

6.After copying all the datafiles to destination location stop the backup mode in the original database.

SQL> alter database end backup;

Database altered.

7. Check the status of the datafiles.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 424935 22-FEB-12
2 NOT ACTIVE 424935 22-FEB-12

8.Archive the current logfile.

SQL> alter system archive log current;

System altered.

9.Get the details of archivelogs that will be needed for recovery while bringing up the clone database.

SQL> select name
2 from v$archived_log
3 where first_change# >= &change_no (424485)
4 order by name
5 /

Enter value for change_no: 424485
old 3: where first_change# >= &change_no
new 3: where first_change# >= 424485 (Enter the no which we got already ref #2)

NAME
——————————————————————————-
/u04/app/oracle/product/10.2.0/oradata/test/arch/1_26_775911007.dbf
/u04/app/oracle/product/10.2.0/oradata/test/arch/1_27_775911007.dbf

Copy the above listed archivelog files to the clone database archivelog location. (These logs are need for point in time recovery since we are cloning the database using hot backup)

10.Create Pfile from the spfile.

SQL> show parameter spfile

NAME     TYPE       VALUE
———————————— ———– ——————————
spfile      string        /u04/app/oracle/product/10.2.0/dbs/spfileprim.ora
SQL> create pfile from spfile;

File created.

11. In original database generate CREATE CONTROLFILE statement by typing the following command.

SQL>alter database backup controlfile to trace;
This will create a trace file containing the “CREATE CONTROLFILE” command to recreate the controlfile in text form.

12.Create the necessary directory on the clone database (destination database) server on your desired location.

Example :- mkdir udump adump cdump bdump arch

udump – user dump destination
bdump – background dump destination
adump – audit dump destination
cdump – core dump destination
arch – Archive log destination

13. Now, go to the USER_DUMP_DEST directory on the original Database server and open the latest trace file.The trace file will have the form “ora_NNNN.trc with NNNN being a number. This file will contain steps and as well as CREATE CONTROLFILE statement. Copy the CREATE CONTROLFILE statement and paste it in a notepad.

14. Edit the file

FROM: CREATE CONTROLFILE REUSE DATABASE “olddbname” RESETLOGS …
TO: CREATE CONTROLFILE SET DATABASE “newdbname” RESETLOGS …

Change the word ‘REUSE’ to ‘set’ and the ‘olddbname’ to ‘newdbname’. Also change the datafiles location parameter to clone database location.

15. Now copy the pfile from the original database server to the clone database server and placed it under the $ORACLE_HOME/dbs location. Now open the parameter file in clone database and change the following parameters with the respective current location.

CONTROL FILES
BACKGROUND_DUMP_DEST
USER_DUMP_DEST
CORE_DUMP_DEST
LOG_ARCHIVE_DEST_1

16. In clone database SERVER export ORACLE_SID environment variable and start the instance

$export ORACLE_SID=clone database name
$sqlplus
Enter User:/ as sysdba
SQL> startup nomount pfile=’init.clonedb.ora’;

17.Run create controlfile script to create the controlfile

SQL>@createcontrolfile.sql

Control file created.

18.Check the status of the Database.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ———-
CLONE MOUNTED

19. Also check whether the clone database is pointing to its datafiles and dump files (just for verification)

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u03/oradata/clone/data/system01.dbf
/u03/oradata/clone/data/undotbs01.dbf
/u03/oradata/clone/data/sysaux01.dbf
/u03/oradata/clone/data/users01.dbf

SQL> show parameter dump

NAME TYPE VALUE
————————- ———– ——————————
background_core_dump   string          partial
background_dump_dest    string         /u03/oradata/clone/bdump
core_dump_dest                   string         /u03/oradata/clone/cdump
max_dump_file_size           string         UNLIMITED
shadow_core_dump           string          partial
user_dump_dest                  string          /u03/oradata/clone/udump

20. Now media recovery is needed because we are cloning from the HOT backup. Follow the below setps.

SQL> recover database using BACKUP CONTROLFILE until cancel;

ORA-00279: change 424935 generated at 02/22/2012 12:29:27 needed for thread 1
ORA-00289: suggestion : /u03/oradata/clone/arch/1_27_775911007.dbf
ORA-00280: change 424935 for thread 1 is in sequence #27

21. Now we need to apply the necessary archive log files. (Refer the point no 9 & apply all the archivelog files )

Specify log: {=suggested | filename | AUTO | CANCEL}
/u03/oradata/clone/arch/1_27_775911007.dbf (Give the archive location and file name)
ORA-00279: change 425120 generated at 02/22/2012 12:37:58 needed for thread 1
ORA-00289: suggestion : /u03/oradata/clone/arch/1_28_775911007.dbf
ORA-00280: change 425120 for thread 1 is in sequence #28
ORA-00278: log file ‘/u03/oradata/clone/arch/1_27_775911007.dbf’ no longer
needed for this recovery

22.Once applied all the neceesary archive log files give cancel .

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

23.Now open the database with open reset logs.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ———-
CLONE READ WRITE

24. Get the latest SCN no in the clone database

SQL> select max(first_change#) chng
2 from v$archived_log
3 /

CHNG
———-
424488 (It will matches with the No which we got it on the Point no #2 )

============================================================================

Command for Server copy (SCP)

If you want to clone the database on the different server, for copying the file from source to destination we need to use SCP comand.

Syntax:

$ scp your_username@remotehost.edu:foobar.txt /some/local/directory

Where, username is your Clone DB server login user id
remotehost is CLONE DB host name
foobar.txt is your filename
/some/local/directory is your destination location
Example :
Consider you are in the source server location (ie., Original DB location IP address 10.250.27.234)

$ pwd

/u04/app/oracle/product/10.2.0/oradata/test/

$ ls -lrt

rw-r—– 1 oracle staff 209723392 Feb 22 12:29 undotbs01.dbf
rw-r—– 1 oracle staff 429924352 Feb 22 12:29 system01.dbf

$ scp oracle@10.251.55.123:undotbs01.dbf /u03/oradata/clone/data/

Where, oracle is your Clone DB server login user id
10.251.55.123 is CLONE DB host name
undotbs01.dbf is your filename
/u03/oradata/clone/data is your destination