Tuesday 8 October 2013

Step by step clone of an oracle database using RMAN

 Both the target and source database are running in 11g release 2version 


Source DB

Step 1:

[oracle@exilant ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 12:36:33 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     826
Next log sequence to archive   828
Current log sequence           828
SQL> exit

Step 2:

[oracle@exilant dbs]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 18 12:38:10 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: APPLE (DBID=3254491270)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name APPLE are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Step 3:

RMAN> backup database plus archivelog;


Starting backup at 18-SEP-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=222 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=615 RECID=180 STAMP=821463136
input archived log thre

channel ORA_DISK_1: starting piece 1 at 18-SEP-13
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_annnn_TAG20130918T123905_93lndlbc_.bkp tag=TAG20130918T123905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp tag=TAG20130918T123905 comment=NONE

Starting Control File and SPFILE Autobackup at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/autobackup/2013_09_18/o1_mf_s_826461889_93lnqbsp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-13

Step 4:

RMAN> backup current controlfile;

Starting backup at 18-SEP-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 18-SEP-13
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_ncnnf_TAG20130918T124731_93lnwfpv_.bkp tag=TAG20130918T124731 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 18-SEP-13

Starting Control File and SPFILE Autobackup at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/autobackup/2013_09_18/o1_mf_s_826462057_93lnwlj8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-13

RMAN> exit

Step 5:

oracle@exilant dbs]$ cd /opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/
[oracle@exilant 2013_09_18]$ ls
o1_mf_annnn_TAG20130918T123905_93lndlbc_.bkp
o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp
o1_mf_annnn_TAG20130918T123905_93lnjctl_.bkp
o1_mf_annnn_TAG20130918T123905_93lnlqgs_.bkp
o1_mf_annnn_TAG20130918T124447_93lnq7v1_.bkp
o1_mf_ncnnf_TAG20130918T124731_93lnwfpv_.bkp
o1_mf_nnndf_TAG20130918T124339_93lno5bt_.bkp

Note: Before scp make the directory in the target  mkdir -p opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/

[oracle@exilant 2013_09_18]$ scp * oracle@10.0.13.10:/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/
oracle@10.0.13.10's password:
o1_mf_annnn_TAG20130918T123905_93lndlbc 100% 1140MB  20.0MB/s   00:57   
o1_mf_annnn_TAG20130918T123905_93lngn0k 100% 1129MB  22.1MB/s   00:51   
o1_mf_annnn_TAG20130918T123905_93lnjctl 100% 1137MB  19.9MB/s   00:57   
o1_mf_annnn_TAG20130918T123905_93lnlqgs 100% 1064MB  19.4MB/s   00:55   
o1_mf_annnn_TAG20130918T124447_93lnq7v1 100%   17KB  17.0KB/s   00:00   
o1_mf_ncnnf_TAG20130918T124731_93lnwfpv 100% 9984KB   9.8MB/s   00:01   
o1_mf_nnndf_TAG20130918T124339_93lno5bt 100% 1131MB  18.5MB/s   01:01  

Step 6:

[oracle@exilant 2013_09_18]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 12:54:42 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile='/tmp/initclonedb.ora' from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@exilant 2013_09_18]$ scp /tmp/initclonedb.ora oracle@10.0.13.10:/opt/app/oracle/product/11.2.0/dbhome_1/dbs/ {target oraclehome/dbs}
oracle@10.0.13.10's password:
initclonedb.ora                         100%  978     1.0KB/s   00:00   

[oracle@exilant 2013_09_18]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 13:11:34 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/apple/system01.dbf
/opt/app/oracle/oradata/apple/sysaux01.dbf
/opt/app/oracle/oradata/apple/undotbs01.dbf
/opt/app/oracle/oradata/apple/users01.dbf


In Target

Step 1:

SQL> archive log list;
Database log mode           Archive Mode

Step 2
:

$ mkdir -p /opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/

from the reference of the rman backup
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp tag=TAG20130918T123905 comment=NONE


[oracle@oraclecps dbs]$ cd /opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/

ls
o1_mf_annnn_TAG20130918T123905_93lndlbc_.bkp
o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp
o1_mf_annnn_TAG20130918T123905_93lnjctl_.bkp
o1_mf_annnn_TAG20130918T123905_93lnlqgs_.bkp
o1_mf_annnn_TAG20130918T124447_93lnq7v1_.bkp
o1_mf_ncnnf_TAG20130918T124731_93lnwfpv_.bkp

[oracle@oraclecps 2013_09_18]$ cd /opt/app/oracle/product/11.2.0/dbhome_1/dbs/
ls

[oracle@oraclecps dbs]$ ls
hc_DBUA0.dat    initclonedb.ora  peshm_DBUA0_0      spfileclonedb.ora
hc_clonedb.dat    initorcl.ora     peshm_clonedb_0  spfileorcl.ora
hc_orcl.dat    lkORCL         peshm_orcl_0
init.ora    orapworcl     snapcf_orcl.f

Step3:

$  vi initclonedb.ora  {previous init file now make changes}
\[oracle@oraclecps dbs]$ cat initclonedb.ora
apple.__db_cache_size=2080374784
apple.__java_pool_size=134217728
apple.__large_pool_size=67108864
apple.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
apple.__pga_aggregate_target=2684354560
apple.__sga_target=4026531840
apple.__shared_io_pool_size=0
apple.__shared_pool_size=1677721600
apple.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/apple/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/app/oracle/oradata/apple/control01.ctl','/opt/app/oracle/flash_recovery_area/apple/control02.ctl'
*.db_block_size=8192
*.db_domain='ads.exilant.in'
*.db_name='apple'
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=42949672960
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=appleXDB)'
*.memory_target=6655311872
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.star_transformation_enabled='TRUE'
*.undo_tablespace='UNDOTBS1'

Now the word apple has to change to cloned to achieve this type..in vi editor

esc-:1,$ s#apple#clonedb#g

And after that add two covert lines

*.db_file_name_convert='apple','clonedb'
*.log_file_name_convert='apple','clonedb'

Now my init files look like

[oracle@oraclecps dbs]$ cat initclonedb.ora
clonedb.__db_cache_size=2080374784
clonedb.__java_pool_size=134217728
clonedb.__large_pool_size=67108864
clonedb.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
clonedb.__pga_aggregate_target=2684354560
clonedb.__sga_target=4026531840
clonedb.__shared_io_pool_size=0
clonedb.__shared_pool_size=1677721600
clonedb.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/clonedb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/app/oracle/oradata/clonedb/control01.ctl','/opt/app/oracle/flash_recovery_area/clonedb/control02.ctl'
*.db_block_size=8192
*.db_domain='ads.exilant.in'
*.db_file_name_convert='apple','clonedb'
*.db_name='clonedb'
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=42949672960
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
*.log_file_name_convert='apple','clonedb'
*.memory_target=6655311872
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.star_transformation_enabled='TRUE'
*.undo_tablespace='UNDOTBS1'

Step 4:

Now make these directories reference from the init file highlited adump and two control files

[oracle@oraclecps dbs]$ mkdir -p /opt/app/oracle/admin/clonedb/adump
[oracle@oraclecps dbs]$ mkdir -p /opt/app/oracle/oradata/clonedb/
[oracle@oraclecps dbs]$ mkdir -p /opt/app/oracle/flash_recovery_area/clonedb/

Step 5:

[oracle@oraclecps dbs]$ vi /etc/oratab

orcl:/opt/app/oracle/product/11.2.0/dbhome_1:N
clonedb:/opt/app/oracle/product/11.2.0/dbhome_1:N  add this line only

Step 6:

[oracle@oraclecps dbs]$ . oraenv
ORACLE_SID = [orcl] ? clonedb
The Oracle base for ORACLE_HOME=/opt/app/oracle/product/11.2.0/dbhome_1 is /opt/app/oracle
[oracle@oraclecps dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 15:26:09 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;

SQL> show parameter service;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
service_names                 string     clonedb.ads.exilant.in
SQL>


[oracle@oraclecps dbhome_1]$ cd network/admin/

Make sure you copy the tnsnames entry to the target server from the source
[oracle@oraclecps admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


APPLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.11.23) (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apple.ads.exilant.in)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.13.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = clonedb.ads.exilant.in)
    )
  )

[oracle@oraclecps admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 18-SEP-2013 16:06:07

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/opt/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.13.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = clonedb.ads.exilant.in)))
OK (0 msec)

[oracle@oraclecps admin]$ tnsping apple

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 18-SEP-2013 16:06:11

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/opt/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.11.23) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apple.ads.exilant.in)))
OK (210 msec)

[oracle@oraclecps admin]$

sql>sqlplus sys/oracle@apple---tns entry

sql>connected

 it should connect

Step 7:


[oracle@oraclecps admin]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 18 16:08:06 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect auxiliary /

connected to auxiliary database: CLONEDB (not mounted)

RMAN> connect target sys/oracle@apple

connected to target database: APPLE (DBID=3254491270)

RMAN> run
2> {
3> allocate auxiliary channel c1 device type disk;
4> allocate channel c2 device type disk;
5> duplicate target database to 'clonedb';
6> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=10 device type=DISK

allocated channel: c2
channel c2: SID=6 device type=DISK

Starting Duplicate Db at 18-SEP-13

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''APPLE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter sys

$ sqlplus / as sysdba

sql>select name  , open_mode from v$database;

cloned read write

sql> select name  , open_mode,instance_name from v$database,v$instance;

dbname-clonedb
open_mode -read write
instance name-clonedb



Hope this will help to clone the oracle db


No comments:

Post a Comment