How to clone a database using user managed hot backup in oracle database 11g

How to clone a database using user managed hot backup in oracle database 11g

Objective :-We are cloning a database running on target server  and restoring it into clone server.


Database Version :- Oracle Enterprise Edition  11.2.0.1 on RHEL 6.4

Target Server: 192.168.0.101
Database sid:- prim

Clone Server: 192.168.0.104
database sid: newprim


****At target Database

Take online backup of database

Before taking the online backup we need to put the database into archivelog mode.


$ export ORACLE_SID=prim

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>alter database begin backup;
Database altered.

SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> /

1 row created.

SQL> /
SQL> commit;
SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                1067890 01-MAY-18
         2 ACTIVE                1067890 01-MAY-18
         3 ACTIVE                1067890 01-MAY-18
         4 ACTIVE                1067890 01-MAY-18
         5 ACTIVE                1067890 01-MAY-18

SQL> exit


Now switch into the datafile directory and copy all the .dbf and .log files into the backup directory

[oracle@server1]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ cp -rpf *.log /home/oracle/bkup/
[oracle@server1 prim]$ cp -rpf *.dbf /home/oracle/bkup/


SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE            1067890 01-MAY-18
         2 NOT ACTIVE            1067890 01-MAY-18
         3 NOT ACTIVE            1067890 01-MAY-18
         4 NOT ACTIVE            1067890 01-MAY-18
         5 NOT ACTIVE            1067890 01-MAY-18

Now take backup of controlfile
SQL> alter database backup controlfile to trace as '/home/oracle/control.sql';

Database altered.

Now edit the content of control file as per below.

[oracle@server1 ~]$vi /home/oracle/control.sql
Remove word REUSE and make it SET
remove word NORESETLOGS and make it RESETLOGS
remove or keep the word ARCHIVELOG depending upon our requirement
change database name from "prim" to "newprim"
change folder name everywhere from 'prim' to 'newprim'
:wq

The content should look like this


CREATE CONTROLFILE SET DATABASE "NEWPRIM" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/newprim/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/newprim/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/newprim/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/newprim/system01.dbf',
  '/u01/app/oracle/oradata/newprim/sysaux01.dbf',
  '/u01/app/oracle/oradata/newprim/undotbs01.dbf',
  '/u01/app/oracle/oradata/newprim/users01.dbf',
  '/u01/app/oracle/oradata/newprim/example01.dbf'
CHARACTER SET WE8MSWIN1252
;



Now transfer all the datafiles, controlfile, redolog files into clone server using scp.

[oracle@server1 ~]$ scp control.sql oracle@192.168.0.104:/u01/app/oracle/oradata/newprim/
The authenticity of host '192.168.0.104 (192.168.0.104)' can't be established.
RSA key fingerprint is 0b:59:e4:8b:b1:e6:12:3a:38:4f:ba:74:ef:8a:ad:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.104' (RSA) to the list of known hosts.
oracle@192.168.0.104's password:
control.sql                                 100%  698     0.7KB/s   00:00   
[oracle@server1 ~]$ cd bkup/
[oracle@server1 bkup]$ ll
total 1534024
-rw-r-----. 1 oracle oinstall 104865792 May  1 20:23 example01.dbf
-rw-r-----. 1 oracle oinstall  52429312 May  1 20:33 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 May  1 20:23 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 May  1 20:23 redo03.log
-rw-r-----. 1 oracle oinstall 524296192 May  1 20:29 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 May  1 20:33 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 May  1 19:58 temp01.dbf
-rw-r-----. 1 oracle oinstall  57679872 May  1 20:31 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 May  1 20:33 users01.dbf
[oracle@server1 bkup]$ scp * oracle@192.168.0.104:/u01/app/oracle/oradata/newprim/
oracle@192.168.0.104's password:
example01.dbf                                100%  100MB  50.0MB/s   00:02   
redo01.log                                   100%   50MB  50.0MB/s   00:01   
redo02.log                                   100%   50MB  25.0MB/s   00:02   
redo03.log                                   100%   50MB  25.0MB/s   00:02   
sysaux01.dbf                                 100%  500MB  19.2MB/s   00:26   
system01.dbf                                 100%  670MB  12.6MB/s   00:53   
temp01.dbf                                   100%   20MB  20.0MB/s   00:01   
undotbs01.dbf                                100%   55MB  55.0MB/s   00:00   
users01.dbf                                  100% 5128KB   5.0MB/s   00:00   
[oracle@server1 bkup]$ cd
[oracle@server1 ~]$ ll



****At clone server

Create necessary directory structure for clone database.
mkdir -p /u01/app/oracle/oradata/newprim/
mkdir -p /u01/app/oracle/admin/newprim/adump


export ORACLE_SID=newprim
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initnewprim.ora';
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             268437496 bytes
Database Buffers          138412032 bytes
Redo Buffers                4308992 bytes

Now recreate control file.
SQL> @/u01/app/oracle/oradata/newprim/control.sql;

Control file created.


Now recover the database.

sql> recover database using backup controlfile until cancel;
here one by one apply all archivelog files
apply all 3 redo log files with full path until we get the msg that recovery is done.

Here its better to notedown the current redo log file and the pending archivelogs from target db at the time of backup. We need to apply only those
archives and only one current redo log file.


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1067890 generated at 05/01/2018 20:23:49 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.
arc
ORA-00280: change 1067890 for thread 1 is in sequence #7


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/newprim/redo01.log
Log applied.
Media recovery complete.


Open the database with resetlogs

SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
newprim

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Now the process of cloning a database is complete into a new server using user managed backup.
Thanks