Programming | SQL » Mohamed Azar - Duplicate database from ASM to non ASM database using RMAN

Duplicate Database from ASM to Non- ASM Database Using RMAN Mohamed Azar. Oracle DBA http://mohamedazar.wordpresscom 1 Mohamed Azar | http://mohamedazar.wordpresscom Step 1: Determine how much disk space will be required. SQL> select name from v$database; NAME --------AZARDB Calculate total space for all datafiles within database SQL> select DF.TOTAL/1048576 "DataFile Size Mb",LOGTOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOGTOTAL + CONTROLTOTAL)/1048576 "Total Size Mb" from dual,(select sum(abytes) TOTAL from dba data files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ; DataFile Size Mb Redo Log Size Mb ---------------- ---------------Control File Size Mb Total Size Mb -------------------- ------------1310 150 18.59375 1478.59375 Calculate space for list of datafiles within primary database SQL> select

DF.TOTAL/1048576 "DataFile Size Mb", LOG.TOTAL/1048576 "Redo Log Size Mb", CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOGTOTAL + CONTROLTOTAL)/1048576 "Total Size Mb" from dual, (select sum(a.bytes) TOTAL from dba data files a where tablespace name in (SYSTEM,SYSAUX,UNDO,USERS)) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ; DataFile Size Mb Redo Log Size Mb ---------------- ---------------Control File Size Mb Total Size Mb -------------------- ------------1165 18.59375 2 150 1333.59375 Mohamed Azar | http://mohamedazar.wordpresscom Step 2: backup Source database RMAN> run{ 2> allocate channel d1 type disk; 3> backup format /backups/df t%t s%s p%p database; 4> sql alter system archive log current; 5> backup format /backups/al t%t s%s p%p archivelog all; 6> release channel d1; 7> } released channel: ORA DISK 1 allocated channel: d1 channel

d1: SID=44 device type=DISK Starting backup at 17-DEC-10 channel d1: starting full datafile backup set channel d1: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/azardb/datafile/system.256736179685 input datafile file number=00002 name=+DATA/azardb/datafile/sysaux.257736179685 input datafile file number=00005 name=+DATA/azardb/datafile/example.269736179971 input datafile file number=00003 name=+DATA/azardb/datafile/undotbs1.258736179685 input datafile file number=00004 name=+SPTEST/azardb/datafile/users.256737964169 channel d1: starting piece 1 at 17-DEC-10 channel d1: finished piece 1 at 17-DEC-10 piece handle=/backups/df t737969795 s6 p1 tag=TAG20101217T073635 comment=NONE channel d1: backup set complete, elapsed time: 00:02:27 channel d1: starting full datafile backup set channel d1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel d1: starting piece 1 at 17-DEC-10

channel d1: finished piece 1 at 17-DEC-10 piece handle=/backups/df t737969943 s7 p1 tag=TAG20101217T073635 comment=NONE channel d1: backup set complete, elapsed time: 00:00:01 Finished backup at 17-DEC-10 sql statement: alter system archive log current Starting backup at 17-DEC-10 current log archived channel d1: starting archived log backup set channel d1: specifying archived log(s) in backup set input archived log thread=1 sequence=3 RECID=1 STAMP=736268182 input archived log thread=1 sequence=4 RECID=2 STAMP=736345263 input archived log thread=1 sequence=5 RECID=3 STAMP=736438711 input archived log thread=1 sequence=6 RECID=4 STAMP=737551583 input archived log thread=1 sequence=7 RECID=5 STAMP=737880523 3 Mohamed Azar | http://mohamedazar.wordpresscom input archived log thread=1 sequence=8 RECID=6 STAMP=737897929 input archived log thread=1 sequence=9 RECID=7 STAMP=737962069 input archived log thread=1 sequence=10 RECID=8 STAMP=737969217 input archived log thread=1 sequence=11

RECID=9 STAMP=737969220 input archived log thread=1 sequence=12 RECID=10 STAMP=737969948 input archived log thread=1 sequence=13 RECID=11 STAMP=737969948 channel d1: starting piece 1 at 17-DEC-10 channel d1: finished piece 1 at 17-DEC-10 piece handle=/backups/al t737969948 s8 p1 tag=TAG20101217T073908 comment=NONE channel d1: backup set complete, elapsed time: 00:00:15 Finished backup at 17-DEC-10 released channel: d1 RMAN> Step 3: Create pfile & edit some required parameters SQL> create pfile=/backups/initAUX.ora from spfile; File created. SQL> Edit& Save *.audit file dest =/u01/app/oracle/AUX/adump *.core dump dest =/u01/app/oracle/AUX/cdump *.db name ="AUX" *.instance name =AUX *.control files =/u01/app/oracle/AUX/control01ctl *.db file name convert =("+DATA","/u01/app/oracle/AUX","+SPTEST","/u01/app/oracle/AUX") *.log file name convert =("+DATA","/u01/app/oracle/AUX") *.undo management =AUTO

*.undo retention =10800 *.undo tablespace=UNDOTBS1 *.db block size=8192 *.compatible=112000 Step 4: startup nomount for AUX database [oracle@localhost ~]$ export ORACLE SID=AUX [oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2010 Production on Fri Dec 17 09:14:09 2010 Copyright (c) 1982, 2009, Oracle. 4 All rights reserved. Mohamed Azar | http://mohamedazar.wordpresscom Connected to: Oracle Database 11g Enterprise Edition Release 11.2010 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> startup nomount pfile=/backups/initAUX.ora; ORACLE instance started. Total System Global Area 146472960 bytes Fixed Size Variable Size 1335080 bytes 92274904 bytes Database Buffers Redo Buffers 50331648 bytes 2531328 bytes SQL> Step 5:Create duplicate database from here [oracle@localhost ~]$ export ORACLE SID=AUX [oracle@localhost ~]$ rman target sys/Admin123@azardb auxiliary / Recovery Manager: Release 11.2010 -

Production on Fri Dec 17 09:18:34 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: AZARDB (DBID=1655311948) connected to auxiliary database: AUX (not mounted) RMAN> run{ 2> allocate auxiliary channel C1 device type disk; 3> duplicate target database to AUX; 4> } using target database control file instead of recovery catalog allocated channel: C1 channel C1: SID=20 device type=DISK Starting Duplicate Db at 17-DEC-10 contents of Memory Script: { sql clone "create spfile from memory"; } executing Memory Script sql statement: create spfile from memory 5 Mohamed Azar | http://mohamedazar.wordpresscom contents of Memory Script: { shutdown clone immediate; startup clone nomount; } executing Memory Script Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers allocated channel: C1

146472960 bytes 1335080 bytes 92274904 bytes 50331648 bytes 2531328 bytes channel C1: SID=18 device type=DISK contents of Memory Script: { sql clone "alter system set AZARDB comment= db name = Modified by RMAN duplicate scope=spfile"; sql clone "alter system set db unique name = AUX comment= Modified by RMAN duplicate scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone primary controlfile; alter clone database mount; } executing Memory Script sql statement: alter system set db name = AZARDB comment= Modified by RMAN duplicate scope=spfile sql statement: alter system set duplicate scope=spfile db unique name = AUX comment= Modified by RMAN Oracle instance shut down Oracle instance started Total System Global Area 6 146472960 bytes Mohamed Azar | http://mohamedazar.wordpresscom Fixed Size 1335080 bytes Variable Size Database Buffers Redo Buffers allocated channel: C1 92274904 bytes 50331648 bytes 2531328 bytes channel

C1: SID=18 device type=DISK Starting restore at 17-DEC-10 channel C1: starting datafile backup set restore channel C1: restoring control file channel C1: reading from backup piece /backups/df t737969943 s7 p1 channel C1: piece handle=/backups/df t737969943 s7 p1 tag=TAG20101217T073635 channel C1: restored backup piece 1 channel C1: restore complete, elapsed time: 00:00:03 output file name=/u01/app/oracle/AUX/control01.ctl Finished restore at 17-DEC-10 database mounted contents of Memory Script: { set until scn 971599; set newname for datafile 1 to "/u01/app/oracle/AUX/azardb/datafile/system.256736179685"; set newname for datafile 2 to "/u01/app/oracle/AUX/azardb/datafile/sysaux.257736179685"; set newname for datafile 3 to "/u01/app/oracle/AUX/azardb/datafile/undotbs1.258736179685"; set newname for datafile 4 to "/u01/app/oracle/AUX/azardb/datafile/users.256737964169"; set newname for datafile 5 to

"/u01/app/oracle/AUX/azardb/datafile/example.269736179971"; restore clone database ; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME 7 Mohamed Azar | http://mohamedazar.wordpresscom executing command: SET NEWNAME Starting restore at 17-DEC-10 channel C1: starting datafile backup set restore channel C1: specifying datafile(s) to restore from backup set channel C1: restoring datafile 00001 to /u01/app/oracle/AUX/azardb/datafile/system.256736179685 channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257736179685 channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258736179685 channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256737964169 channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/datafile/example.269736179971 channel

C1: reading from backup piece /backups/df t737969795 s6 p1 channel C1: piece handle=/backups/df t737969795 s6 p1 tag=TAG20101217T073635 channel C1: restored backup piece 1 channel C1: restore complete, elapsed time: 00:01:27 Finished restore at 17-DEC-10 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=10 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/system.256736179685 datafile 2 switched to datafile copy input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257736179685 datafile 3 switched to datafile copy input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258736179685 datafile 4 switched to datafile copy input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256737964169 datafile 5 switched to datafile copy input datafile copy RECID=14

STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/example.269736179971 contents of Memory Script: { set until scn 8 971599; Mohamed Azar | http://mohamedazar.wordpresscom recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 17-DEC-10 starting media recovery archived log for thread 1 with sequence 12 is already on disk as file +DATA/azardb/archivelog/2010 12 17/thread 1 seq 12.279737969947 archived log for thread 1 with sequence 13 is already on disk as file +DATA/azardb/archivelog/2010 12 17/thread 1 seq 13.280737969949 archived log file name=+DATA/azardb/archivelog/2010 12 17/thread 1 seq 12.279737969947 thread=1 sequence=12 archived log file name=+DATA/azardb/archivelog/2010 12 17/thread 1 seq 13.280737969949 thread=1 sequence=13 media recovery complete, elapsed time: 00:00:00 Finished recover at 17-DEC-10 contents of Memory Script: { shutdown clone immediate; startup clone nomount; sql clone

"alter system set db name = AUX comment= Reset to original value by RMAN scope=spfile"; sql clone "alter system reset shutdown clone immediate; db unique name scope=spfile"; startup clone nomount; } executing Memory Script database dismounted Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 146472960 bytes Fixed Size Variable Size 1335080 bytes 92274904 bytes Database Buffers 50331648 bytes 9 Mohamed Azar | http://mohamedazar.wordpresscom Redo Buffers 2531328 bytes allocated channel: C1 channel C1: SID=18 device type=DISK sql statement: alter system set db name = AUX comment= Reset to original value by RMAN scope=spfile sql statement: alter system reset db unique name scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area Fixed Size 146472960 bytes 1335080 bytes Variable Size Database Buffers

92274904 bytes 50331648 bytes Redo Buffers allocated channel: C1 2531328 bytes channel C1: SID=18 device type=DISK sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG MAXLOGFILES MAXLOGMEMBERS MAXDATAFILES MAXINSTANCES MAXLOGHISTORY LOGFILE 16 3 100 8 292 GROUP 1 ( /u01/app/oracle/AUX/azardb/onlinelog/group 1.262736179919, /u01/app/oracle/AUX/azardb/onlinelog/group 1.263736179919 ) SIZE 50 M REUSE, GROUP 2 ( /u01/app/oracle/AUX/azardb/onlinelog/group 2.264736179921, /u01/app/oracle/AUX/azardb/onlinelog/group 2.265736179925 ) SIZE 50 M REUSE, GROUP 3 ( /u01/app/oracle/AUX/azardb/onlinelog/group 3.266736179927, /u01/app/oracle/AUX/azardb/onlinelog/group 3.267736179929 ) SIZE 50 M REUSE DATAFILE /u01/app/oracle/AUX/azardb/datafile/system.256736179685 CHARACTER SET WE8MSWIN1252 contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/AUX/azardb/tempfile/temp.268736179955"; switch clone tempfile all; catalog clone

datafilecopy "/u01/app/oracle/AUX/azardb/datafile/sysaux.257736179685", "/u01/app/oracle/AUX/azardb/datafile/undotbs1.258736179685", "/u01/app/oracle/AUX/azardb/datafile/users.256737964169", "/u01/app/oracle/AUX/azardb/datafile/example.269736179971"; 10 Mohamed Azar | http://mohamedazar.wordpresscom switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/AUX/azardb/tempfile/temp.268736179955 in control file cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257736179685 RECID=1 STAMP=737976112 cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258736179685 RECID=2 STAMP=737976112 cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256737964169 RECID=3 STAMP=737976112 cataloged datafile copy datafile copy file

name=/u01/app/oracle/AUX/azardb/datafile/example.269736179971 RECID=4 STAMP=737976113 datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257736179685 datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258736179685 datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256737964169 datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/datafile/example.269736179971 contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Duplicate database was successfully created Step 6: Check Duplicate Database [oracle@localhost ~]$ export ORACLE SID=AUX 11 Mohamed Azar | http://mohamedazar.wordpresscom [oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2010 Production on Fri Dec 17 09:25:45 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2010 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$database; NAME --------AUX SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------/u01/app/oracle/AUX/azardb/datafile/system.256736179685 /u01/app/oracle/AUX/azardb/datafile/sysaux.257736179685 /u01/app/oracle/AUX/azardb/datafile/undotbs1.258736179685 /u01/app/oracle/AUX/azardb/datafile/users.256737964169 /u01/app/oracle/AUX/azardb/datafile/example.269736179971 SQL> Possible Errors: You can get some errors , cannt restore datafile & onlineredo log files, in this case you need to create directory ,It should be indicate. azardb/datafile azardb/onlinelog 12 Mohamed Azar |

http://mohamedazar.wordpresscom