Duplicate database from ASM to non ASM
Download PDF Document from here
Duplicate Database from ASM to Non pdf Right click on thir URL, Save Target as & then Download it.
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",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) 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 + LOG.TOTAL + CONTROL.TOTAL)/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 150
18.59375 1333.59375
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.256.736179685
input datafile file number=00002 name=+DATA/azardb/datafile/sysaux.257.736179685
input datafile file number=00005 name=+DATA/azardb/datafile/example.269.736179971
input datafile file number=00003 name=+DATA/azardb/datafile/undotbs1.258.736179685
input datafile file number=00004 name=+SPTEST/azardb/datafile/users.256.737964169
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
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/control01.ctl
*.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='11.2.0.0.0'
Step 4: startup nomount for AUX database
[oracle@localhost ~]$ export ORACLE_SID=AUX [oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:14:09 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 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.2.0.1.0 - 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
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 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''AZARDB'' comment=
''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 db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
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.256.736179685";
set newname for datafile 2 to
"/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685";
set newname for datafile 3 to
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685";
set newname for datafile 4 to
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169";
set newname for datafile 5 to
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
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
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.256.736179685
channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256.737964169
channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/datafile/example.269.736179971
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.256.736179685
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971
contents of Memory Script:
{
set until scn 971599;
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.279.737969947
archived log for thread 1 with sequence 13 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 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 db_unique_name scope=spfile";
shutdown clone immediate;
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 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
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 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/azardb/onlinelog/group_1.263.736179919' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/azardb/onlinelog/group_2.265.736179925' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/azardb/onlinelog/group_3.267.736179929' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/AUX/azardb/datafile/system.256.736179685'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685",
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685",
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169",
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955 in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 RECID=3 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 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.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971
[oracle@localhost ~]$ export ORACLE_SID=AUX
[oracle@localhost ~]$ rman target sys/Admin123@azardb auxiliary /
Recovery Manager: Release 11.2.0.1.0 - 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
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 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''AZARDB'' comment=
''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 db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
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.256.736179685";
set newname for datafile 2 to
"/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685";
set newname for datafile 3 to
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685";
set newname for datafile 4 to
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169";
set newname for datafile 5 to
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
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
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.256.736179685
channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256.737964169
channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/datafile/example.269.736179971
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.256.736179685
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971
contents of Memory Script:
{
set until scn 971599;
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.279.737969947
archived log for thread 1 with sequence 13 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 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 db_unique_name scope=spfile";
shutdown clone immediate;
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 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
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 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/azardb/onlinelog/group_1.263.736179919' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/azardb/onlinelog/group_2.265.736179925' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/azardb/onlinelog/group_3.267.736179929' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/AUX/azardb/datafile/system.256.736179685'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685",
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685",
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169",
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955 in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 RECID=3 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 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.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971
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 [oracle@localhost ~]$ sqlplus SQL*Plus: Release 11.2.0.1.0 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.2.0.1.0 - 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.256.736179685 /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 /u01/app/oracle/AUX/azardb/datafile/users.256.737964169 /u01/app/oracle/AUX/azardb/datafile/example.269.736179971 SQL>
Possible Errors:
You can get some errors , cann’t restore datafile & onlineredo log files, in this case you need to create directory ,It should be indicate.
azardb/datafile
azardb/onlinelog
5 Comments
Post a comment
DBA 
Very Very Nice . so clear .. Thanks alot
Mohanad Awad
Hi Mohamed,
Very good explanation… How can i do this from host 1 (source) to host 2 (target)… Thanks…
Omar Villarroel
will it not ask the ASM disk group and ASM directory names while restore.
i got below error.
channel ORA_DISK_4: reading from backup piece /u01/home/oracle/admin/xxx/rman_bkup/xxx_241434_20170803_oqsb06gu_1_1.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /rman/rman3/oracle/hpwbff202/xxx/backupset/xxx_241433_20170803_opsb06gt_1_1.bkp
ORA-19504: failed to create file “+xxx_DATA/xxx/htl_i_l_02.dbf”
Hi Mohamed, your document was very handy but I ran into a problem with the AUX db after I tried to start it on the new host. I copied over the spfile & controlfile and the other files were left on a shared nfs mount. I had to change the permissions on the nfs mount in order for the db to read them. The problem I ran into was:
WARNING: failed to start ASMB (ASM instance not found)
Wed Dec 13 10:50:10 2017
Errors in file /u01/app/oracle/diag/rdbms/aux/AUX/trace/AUX_asmb_17277.trc:
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
WARNING: ASMB exiting with error
Starting background process ASMB
Wed Dec 13 10:50:13 2017
ASMB started with pid=24, OS id=17283
WARNING: failed to start ASMB (connection failed) state=0x1 sid=”
WARNING: ASMB exiting with error
Wed Dec 13 10:50:13 2017
Errors in file /u01/app/oracle/diag/rdbms/aux/AUX/trace/AUX_lgwr_17245.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: ‘+RECO_DG/AUX/ONLINELOG/group_5.2648.962644621’
ORA-17503: ksfdopn:2 Failed to open file +RECO_DG/AUX/ONLINELOG/group_5.2648.962644621
ORA-15001: diskgroup “RECO_DG” does not exist or is not mounted
ORA-29701: unable to connect to Cluster Synchronization Service
ORA-00312: online log 5 thread 1: ‘+DATA_DG/AUX/ONLINELOG/group_5.4232.962644619’
ORA-17503: ksfdopn:2 Failed to open file +DATA_DG/AUX/ONLINELOG/group_5.4232.962644619
ORA-15001: diskgroup “DATA_DG” does not exist or is not mounted
it was still looking for the ASM. If I start the db on the original host it was fine but when I started it on the new host it failed.
Any suggestions, I’m still trying to test it but I thought I’d reach out to you and ask.
thanks
CJ
This is a very helpful guide for backing up and restoring an oracle database.