In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Monday, May 23, 2011

RMAN cloning in archive log mode

i have cloned the database SUNU in RAC2 machine to RAC1 Machine in the name DBDEMO.


****************************************************************
RAC2 MACHINE
****************************************************************
ORACLE_SID = [test] ? sunu
[oracle@rac2 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 15 19:27:41 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

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

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? sunu
[oracle@rac2 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 15 19:34:50 2011

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

connected to target database: SUNU (DBID=555109830)

RMAN> backup full database format '/home/u01/app/backup/%d_%U.bkp' plus archivelog format '/home/u01/app/backup/%d_%U.bkp';


[oracle@rac2 backup]$ pwd
/home/u01/app/backup
[oracle@rac2 backup]$ ls -ltr
total 516356
-rw-r----- 1 oracle oinstall 2124800 May 15 19:37 SUNU_01mcdp74_1_1.bkp
-rw-r----- 1 oracle oinstall 518946816 May 15 19:37 SUNU_02mcdp76_1_1.bkp
-rw-r----- 1 oracle oinstall 7143424 May 15 19:37 SUNU_03mcdp8j_1_1.bkp
-rw-r----- 1 oracle oinstall 2560 May 15 19:37 SUNU_04mcdp8l_1_1.bkp
[oracle@rac2 backup]$



step 2:-
copy the initsunu.ora file to rac1 machine and also create a password file in rac1 machine for dbdemo database
add an entry in oratab file in rac1 machine
also move the backup files to rac1 machine to exact folder as it is in rac2(source) machine

note: make sure the folder is available in RAC1 machine
here we are taking backup in /home/u01/app/backup so now i am going to create a folder in RAC1 machine and also moving the backup from RAC2 to RAC1.
[oracle@rac2 backup]$ scp * oracle@192.168.1.1:/home/u01/app/backup/


or you can create a symbolic link for the backup location

example, in machine RAC1(the machine/host/server i dont have exact folder or exact mount point as it is in RAC2 machine(target)
do like this,

[oracle@rac2 dbs]$ cd /home/u01/app/backup/
[oracle@rac2 backup]$ pwd
/home/u01/app/backup
[oracle@rac2 backup]$ scp * oracle@192.168.1.1:/home/u01/app/test_bk/

and then create a symbolic link:
[oracle@rac1 app]$ ln -s /home/u01/app/test_bk /home/u01/app/backup

[oracle@rac1 app]$ ls
backup oracle test_bk
[oracle@rac1 app]$ cd backup/
[oracle@rac1 backup]$ ls -ltr
total 671932
-rw-r----- 1 oracle oinstall 7029760 May 22 12:35 SUNU_09mcoc44_1_1.bkp
-rw-r----- 1 oracle oinstall 97460224 May 22 12:36 SUNU_0amcoc48_1_1.bkp
-rw-r----- 1 oracle oinstall 6144 May 22 12:36 SUNU_0cmcoc7a_1_1.bkp
-rw-r----- 1 oracle oinstall 1097728 May 22 12:36 SUNU_0bmcoc78_1_1.bkp
-rw-r----- 1 oracle oinstall 39838720 May 22 12:36 SUNU_0dmcvels_1_1.bkp
-rw-r----- 1 oracle oinstall 534740992 May 22 12:38 SUNU_0emcvem5_1_1.bkp
-rw-r----- 1 oracle oinstall 27648 May 22 12:38 SUNU_0gmcvepg_1_1.bkp
-rw-r----- 1 oracle oinstall 7143424 May 22 12:38 SUNU_0fmcvepe_1_1.bkp
[oracle@rac1 backup]$

now we are going to copy the init files from RAC2 to RAC1 machine using scp , as shown below.
you can mention hostname or ipaddress in scp command

[oracle@rac2 dbs]$ scp initsunu.ora oracle@192.168.1.1:$ORACLE_HOME/dbs/
initsunu.ora 100% 1070 1.0KB/s 00:00
***********************************************************************
IN RAC1 MACHINE
***********************************************************************

i copied the initsunu.ora file to the RAC1 machine and now i am going to replace
the database name sunu by dbdemo
also,
adding the parameter log_file_name_convert and db_file_name_convert parameters to clone database with different directory structure.

dbdemo.__db_cache_size=541065216
dbdemo.__java_pool_size=4194304
dbdemo.__large_pool_size=4194304
dbdemo.__shared_pool_size=192937984
dbdemo.__streams_pool_size=0
*.audit_file_dest='/home/u01/app/oracle/admin/dbdemo/adump'
*.background_dump_dest='/home/u01/app/oracle/admin/dbdemo/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/u01/app/oracle/oradata/dbdemo/control01.ctl','/home/u01/app/oracle/oradata/dbdemo/control02.ctl','/home/u01/app/oracle/oradata/dbdemo/control03.ctl'
*.core_dump_dest='/home/u01/app/oracle/admin/dbdemo/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dbdemo'
*.db_recovery_file_dest='/home/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbdemoXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=248512512
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/u01/app/oracle/admin/dbdemo/udump'
db_file_name_convert=(/home/u01/app/oracle/oradata/sunu/,/home/u01/app/oracle/oradata/dbdemo/)
log_file_name_convert=(/home/u01/app/oracle/oradata/sunu/,/home/u01/app/oracle/oradata/dbdemo/)

*.sga_target=745537536


Now i am going to add an entry in the /etc/oratab file for dbdemo database

dbdemo:/home/u01/app/oracle/product/10.2.0/db_1:N


going to create required folders for the dbdemo database files

[oracle@rac1 dbdemo]$ pwd
/home/u01/app/oracle/admin/dbdemo

[oracle@rac1 dbdemo]$ ls
adump bdump cdump dpdump hdump pfile udump
[oracle@rac1 dbdemo]$

[oracle@rac1 oradata]$ pwd
/home/u01/app/oracle/oradata
[oracle@rac1 oradata]$ ls
dbdemo
[oracle@rac1 oradata]$


creating a pwd file for dbdemo database

[oracle@rac1 oradata]$ orapwd file=$ORACLE_HOME/dbs/orapwdbdemo password=oracle
bash: orapwd: command not found
[oracle@rac1 oradata]$ . oraenv
ORACLE_SID = [orcl] ? dbdemo
[oracle@rac1 oradata]$ orapwd file=$ORACLE_HOME/dbs/orapwdbdemo password=oracle
[oracle@rac1 oradata]$

now i am going to start dbdemo database in nomount mode
and then do cloning

before that we should have add the tns entry of sunu database in RAC1 machine


[oracle@rac1 backup]$ cd /home/u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac1 admin]$ ls -ltr

add/check this entries available in both machines else add this entry in tnsnames.ora file of both machines.

SUNU =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sunu)
)
)

DBDEMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbdemo)
)
)
[oracle@rac1 admin]$ tnsping DBDEMO

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:31:21

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

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbdemo)))
OK (0 msec)
[oracle@rac1 admin]$ tnsping SUNU

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:31:26

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

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sunu)))
OK (10 msec)
[oracle@rac1 admin]$

[oracle@rac2 admin]$ tnsping DBDEMO

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:29:09

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

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbdemo)))
OK (10 msec)
[oracle@rac2 admin]$ tnsping SUNU

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:29:15

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

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sunu)))
OK (10 msec)
[oracle@rac2 admin]$

we are checking connectivity b/n the two machines using tnsping command.

now all the pre cloning steps finished.

now we can start the cloning in RAC1 machine.


startup nomount the DATABASE dbdemo in RAC1 machine


making/creating flash recovery area in RAC1 machine

[oracle@rac1 oracle]$ mkdir -p /home/u01/app/oracle/flash_recovery_area
[oracle@rac1 oracle]$ ls
admin flash_recovery_area oradata oraInventory product
[oracle@rac1 oracle]$ cd flash_recovery_area/
[oracle@rac1 flash_recovery_area]$ ls
[oracle@rac1 flash_recovery_area]$ mkdir dbdemo
[oracle@rac1 flash_recovery_area]$ ls
dbdemo


[oracle@rac1 dbs]$ . oraenv
ORACLE_SID = [dbdemo] ?
[oracle@rac1 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 19 20:40:50 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 746586112 bytes
Fixed Size 1221636 bytes
Variable Size 201329660 bytes
Database Buffers 541065216 bytes
Redo Buffers 2969600 bytes
SQL>

[oracle@rac1 flash_recovery_area]$ . oraenv
ORACLE_SID = [ORCL1] ? dbdemo
[oracle@rac1 flash_recovery_area]$ rman target sys/oracle@SUNU auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 19 20:42:06 2011

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

connected to target database: SUNU (DBID=555109830)
connected to auxiliary database: DBDEMO (not mounted)

RMAN> duplicate target database to dbdemo;

[oracle@rac1 dbdemo]$ . oraenv
ORACLE_SID = [dbdemo] ?
[oracle@rac1 dbdemo]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 19 21:20:23 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
DBDEMO

6 comments:

கா.பழனியப்பன் said...

I have gone though almost all your post. It is very help full. You are doing fabulous job.
Looking for good post regarding oracle pre installation (kernel parameter setting).
Almost all interviewers asking the above questions and unable to satisfy them.
It will be very help full if you publish post regarding kernel parameter setting in detail.

கா.பழனியப்பன் said...

I have gone though almost all your post. It is very help full. You are doing fabulous job.
Looking for good post regarding oracle pre installation (kernel parameter setting).
Almost all interviewers asking the above questions and unable to satisfy them.
It will be very help full if you publish post regarding kernel parameter setting in detail.

sneha said...

hi ,this is really good to see detailed description regarding the RMAN cloning in archve log mode,seems to be very helpful.
Thanks
Sneha
Oracle Fusion Middleware

sneha said...

i am enthusiatic to learn oracle,this seems to be a intresting tool in oracle for database modeling
Regards
Sneha
Oracle Fusion Middleware

sneha said...

hi ,this is really good to see detailed description regarding the RMAN cloning in archve log mode,seems to be very help ful.

Regards,
Sneha
Oracle Fusion Middleware

Manoj Kumar said...

Hi Rajesh, I tried this database cloning using RMAN by refering your document. It was really helpful to me.Good work and Thanks.....

 
Share/Bookmark