In 11g version this action can really be fully generalized regarding several improvements in which two are especially highlighted (as can be seen on previous picture):
- no need to connect to target database (all data are read from RMAN backup files directly). This gives ability to run DUPLICATE in any situation, with or without target database available!
- backup location may be anywhere you like (not necessary as it was in RMAN backup configuration on target database). If you look in mine topic (ORA-19870, ORA-19505, ORA-27041 on Windows 2008/2003) you might see some problems that DBA may face working with 10g on Windows. According this you'll appreciate this features a lot.
1. Configuration/environment
Here are the main data to understand next example:- The whole example is made on Windows 2008R2 x64 with Oracle 11gR2 x64 database.
- Target database (one that is cloned) is "XE" and auxiliary (new replica) is "ORCL".
- Target and auxiliary box are the same box.
- ORACLE_HOME is the same what meant that directory structure for auxiliary datafiles are different.
- Target database is in archive log with enabled flashback option.
2. Pre steps
Rather this steps is not necessary at all, I'll show them to ensure that the whole procedure exists only on shown actions.This is why I have deleted all current backups, archived logs and backup sets, but in real case you do not have to do that. Shortly commands for that are:
C:\>SET ORACLE_SID=XE rman target / nocatalog crosscheck archivelog all; crosscheck backup; crosscheck backupset; delete noprompt archivelog all; delete noprompt backup; delete noprompt backupset;
3. Backup target database
First we have to make full database backup with RMAN:C:\>SET ORACLE_SID=XE C:\>rman target / nocatalog RMAN> backup database plus archivelog delete input; Starting backup at 24.08.10 current log archived allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=138 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=460 RECID=197 STAMP=727908938 input archived log thread=1 sequence=461 RECID=198 STAMP=727908940 input archived log thread=1 sequence=462 RECID=199 STAMP=727908945 input archived log thread=1 sequence=463 RECID=200 STAMP=727909072 channel ORA_DISK_1: starting piece 1 at 24.08.10 channel ORA_DISK_1: finished piece 1 at 24.08.10 piece handle=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\BACKUPSET\2010_08_24\O1_MF_ANNNN_TAG20100824T205752_6785KJP0_.BKP tag=TAG20100824T205752 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_08_24\O1_MF_1_460_6785FBWS_.ARC RECID=197 STAMP=727908938 archived log file name=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_08_24\O1_MF_1_461_6785FDW0_.ARC RECID=198 STAMP=727908940 archived log file name=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_08_24\O1_MF_1_462_6785FKXH_.ARC RECID=199 STAMP=727908945 archived log file name=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_08_24\O1_MF_1_463_6785KJ2W_.ARC RECID=200 STAMP=727909072 Finished backup at 24.08.10 Starting backup at 24.08.10 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=C:\ORACLE\PRODUCT\ORADATA\XE\SYSAUX01.DBF input datafile file number=00001 name=C:\ORACLE\PRODUCT\ORADATA\XE\SYSTEM01.DBF input datafile file number=00003 name=C:\ORACLE\PRODUCT\ORADATA\XE\UNDOTBS01.DBF input datafile file number=00006 name=C:\ORACLE\PRODUCT\ORADATA\XE\USERS01.DBF input datafile file number=00004 name=C:\ORACLE\PRODUCT\ORADATA\XE\APEX.DBF input datafile file number=00007 name=C:\ORACLE\PRODUCT\ORADATA\XE\TOOLS01.DBF input datafile file number=00005 name=C:\ORACLE\PRODUCT\ORADATA\XE\OWB.DBF channel ORA_DISK_1: starting piece 1 at 24.08.10 channel ORA_DISK_1: finished piece 1 at 24.08.10 piece handle=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\BACKUPSET\2010_08_24\O1_MF_NNNDF_TAG20100824T205754_6785KLLV_.BKP tag=TAG20100824T205754 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55 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 including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 24.08.10 channel ORA_DISK_1: finished piece 1 at 24.08.10 piece handle=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\BACKUPSET\2010_08_24\O1_MF_NCSNF_TAG20100824T205754_6785O6MY_.BKP tag=TAG20100824T205754 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24.08.10 Starting backup at 24.08.10 current log archived using channel ORA_DISK_1 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=464 RECID=201 STAMP=727909193 channel ORA_DISK_1: starting piece 1 at 24.08.10 channel ORA_DISK_1: finished piece 1 at 24.08.10 piece handle=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\BACKUPSET\2010_08_24\O1_MF_ANNNN_TAG20100824T205954_6785OBRQ_.BKP tag=TAG20100824T205954 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_08_24\O1_MF_1_464_6785O9TG_.ARC RECID=201 STAMP=727909193 Finished backup at 24.08.10 RMAN>
4. Creating PIT point
To identified a time when to restore to, I'll create a table t1 with one record which will hold sysdate of the moment when the record was inserted. This will PIT point.c:\>sqlplus "/ as sysdba" SQL> create table t1 (a_date date); Table created. SQL> insert into t1 values (sysdate); 1 row created. SQL> commit; Commit complete. SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') now_is from dual; NOW_IS ------------------- 24.08.2010 21:03:16 SQL> drop table t1 cascade constraints; Table dropped. SQL>Now let us create some aditional archived log files that can cover this PIT:
SQL> alter system switch logfile; System altered. SQL>
5. Backup previously created archived logs
Because in first backup I deleted all archived logs, all that happened later should be backed up additionally:C:\>SET ORACLE_SID=XE C:\>rman target / nocatalog RMAN> backup archivelog all; Starting backup at 24.08.10 current log archived allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=139 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=465 RECID=202 STAMP=727909720 input archived log thread=1 sequence=466 RECID=203 STAMP=727909770 channel ORA_DISK_1: starting piece 1 at 24.08.10 channel ORA_DISK_1: finished piece 1 at 24.08.10 piece handle=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\BACKUPSET\2010_08_24\O1_MF_ANNNN_TAG20100824T210931_67867C7Y_.BKP tag=TAG20100824T2109 31 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 24.08.10 RMAN>
6. Check backup files and original backup location
Backup files:c:\oracle\product\flash_recovery_area\XE\BACKUPSET\2010_08_24>dir
Volume in drive C has no label.
Volume Serial Number is 084F-8D27
Directory of c:\oracle\product\flash_recovery_area\XE\BACKUPSET\2010_08_24
24.08.2010. 21:09 <DIR> .
24.08.2010. 21:09 <DIR> ..
24.08.2010. 20:57 441.856 O1_MF_ANNNN_TAG20100824T205752_6785KJP0_.BKP
24.08.2010. 20:59 12.288 O1_MF_ANNNN_TAG20100824T205954_6785OBRQ_.BKP
24.08.2010. 21:09 1.452.032 O1_MF_ANNNN_TAG20100824T210931_67867C7Y_.BKP
24.08.2010. 20:49 9.928.704 O1_MF_NCSNF_TAG20100824T204912_678519H6_.BKP
24.08.2010. 20:59 9.928.704 O1_MF_NCSNF_TAG20100824T205754_6785O6MY_.BKP
24.08.2010. 20:59 2.174.009.344 O1_MF_NNNDF_TAG20100824T205754_6785KLLV_.BKP
6 File(s) 2.195.772.928 bytes
2 Dir(s) 7.442.173.952 bytes free
c:\oracle\product\flash_recovery_area\XE\BACKUPSET\2010_08_24>
Archived log files:c:\oracle\product\flash_recovery_area\XE\ARCHIVELOG\2010_08_24>dir
Volume in drive C has no label.
Volume Serial Number is 084F-8D27
Directory of c:\oracle\product\flash_recovery_area\XE\ARCHIVELOG\2010_08_24
24.08.2010. 21:09 <DIR> .
24.08.2010. 21:09 <DIR> ..
24.08.2010. 21:08 1.444.864 O1_MF_1_465_67865R4R_.ARC
24.08.2010. 21:09 6.144 O1_MF_1_466_67867BP4_.ARC
2 File(s) 1.451.008 bytes
2 Dir(s) 7.442.173.952 bytes free
c:\oracle\product\flash_recovery_area\XE\ARCHIVELOG\2010_08_24>
7. Copy backup files to different location
To prove that backup can be used from non default backup location, I'll copy all files from point 6. to new location "d:\Temp\all_backup_data>".d:\Temp\all_backup_data>dir
Volume in drive D is New Volume
Volume Serial Number is 1E97-BEEF
Directory of d:\Temp\all_backup_data
24.08.2010. 21:20 <DIR> .
24.08.2010. 21:20 <DIR> ..
24.08.2010. 21:08 1.444.864 O1_MF_1_465_67865R4R_.ARC
24.08.2010. 21:09 6.144 O1_MF_1_466_67867BP4_.ARC
24.08.2010. 20:57 441.856 O1_MF_ANNNN_TAG20100824T205752_6785KJP0_.BKP
24.08.2010. 20:59 12.288 O1_MF_ANNNN_TAG20100824T205954_6785OBRQ_.BKP
24.08.2010. 21:09 1.452.032 O1_MF_ANNNN_TAG20100824T210931_67867C7Y_.BKP
24.08.2010. 20:49 9.928.704 O1_MF_NCSNF_TAG20100824T204912_678519H6_.BKP
24.08.2010. 20:59 9.928.704 O1_MF_NCSNF_TAG20100824T205754_6785O6MY_.BKP
24.08.2010. 20:59 2.174.009.344 O1_MF_NNNDF_TAG20100824T205754_6785KLLV_.BKP
24.08.2010. 21:20 12 spfile.ora
9 File(s) 2.197.223.948 bytes
2 Dir(s) 52.156.399.616 bytes free
d:\Temp\all_backup_data>
In this way you can do the same when auxiliary box is not the same as target as well.8. Pre cloning steps
Before we start duplicate, on Windows platform I have to:- set new environment:
d:\>SET ORACLE_SID=ORCL d:\>SET ORACLE_HOME=c:\oracle\product\11.2.0\db_1
- create %ORACLE_HOME%\DATABASE\initORCL.ora file with only one line content:
db_name=ORCL
But I prefer to add two lines more:
job_queue_processes=0 compatible=11.1.0
First one would stop any job that might want to start after clone (what we do not in many cases) and second will prevent "ORA-19726: cannot plug data [string] at level [string]" into database running at compatibility level string"
All others init.ora entries for cloned database will be done automatically by RMAN duplicate command!
- create new ORCL instance by creating it through Windows service
d:\>oradim -new -sid %ORACLE_SID% -intpwd newpwd -startmode M Instance created. d:\>
If you are on Linux this step must be skipped.
- Start instance in nomount mode with previously create pfile.
d:\>sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.1.0 Production on Uto Kol 24 21:25:51 2010 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='%ORACLE_HOME%\database\initORCL.ora'; ORACLE instance started. Total System Global Area 217157632 bytes Fixed Size 2174320 bytes Variable Size 159384208 bytes Database Buffers 50331648 bytes Redo Buffers 5267456 bytes SQL>
9. RMAN duplicate
Now new instance is ready to be cloned. Important part is at the very beginning of starting RMAN:c:\>rman auxiliary /where you see that RMAN is not connected to target database as it was in the past! The rest of the RMAN script that will be used for cloning looks like:
RUN {
ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
SET UNTIL TIME "to_date( '24.08.2010 21:03:16','dd.mm.yyyy hh24:mi:ss')";
DUPLICATE DATABASE TO "ORCL"
DB_FILE_NAME_CONVERT =('c:\oracle\product\oradata\XE\','C:\oracle\product\oradata\ORCL\')
PFILE='c:\oracle\product\11.2.0\db_1\database\initORCL.ora'
LOGFILE
GROUP 1 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO01.LOG') SIZE 50M,
GROUP 2 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO02.LOG') SIZE 50M,
GROUP 3 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO03.LOG') SIZE 50M
BACKUP LOCATION 'd:\Temp\all_backup_data'
;
RELEASE CHANNEL aux1;
}
Where: - SET UNTIL TIME represent the same moment as sysdate previously inserted in table t1.
- DB_FILE_NAME_CONVERT option remap directories
- BACKUP LOCATION sets dynamically new backup location (different from original!)
c:\>rman auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Uto Kol 24 21:29:55 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: ORCL (not mounted)
---------------------------------------
RMAN> RUN {
2> ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
3> SET UNTIL TIME "to_date( '24.08.2010 21:03:16','dd.mm.yyyy hh24:mi:ss')";
4> DUPLICATE DATABASE TO "ORCL"
5> DB_FILE_NAME_CONVERT =('c:\oracle\product\oradata\XE\','C:\oracle\product\oradata\ORCL\')
6> PFILE='c:\oracle\product\11.2.0\db_1\database\initORCL.ora'
7> LOGFILE
8> GROUP 1 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO01.LOG') SIZE 50M,
9> GROUP 2 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO02.LOG') SIZE 50M,
10> GROUP 3 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO03.LOG') SIZE 50M
11> BACKUP LOCATION 'd:\Temp\all_backup_data'
12> ;
13> RELEASE CHANNEL aux1;
14> }
allocated channel: aux1
channel aux1: SID=96 device type=DISK
executing command: SET until clause
Starting Duplicate Db at 24.08.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 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
allocated channel: aux1
channel aux1: SID=95 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''XE'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from 'D:\TEMP\all_backup_data\O1_MF_NCSNF_TAG20100824T205754_6785O6MY_.BKP';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''XE'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
allocated channel: aux1
channel aux1: SID=95 device type=DISK
Starting restore at 24.08.10
channel aux1: restoring control file
channel aux1: restore complete, elapsed time: 00:00:01
output file name=C:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\CTL1ORCL.ORA
Finished restore at 24.08.10
database mounted
contents of Memory Script:
{
set until scn 6897551;
set newname for datafile 1 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF";
set newname for datafile 3 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF";
set newname for datafile 4 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF";
set newname for datafile 5 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF";
set newname for datafile 6 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF";
set newname for datafile 7 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF";
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
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24.08.10
channel aux1: starting datafile backup set restore
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00001 to C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
channel aux1: restoring datafile 00002 to C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
channel aux1: restoring datafile 00003 to C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
channel aux1: restoring datafile 00004 to C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF
channel aux1: restoring datafile 00005 to C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF
channel aux1: restoring datafile 00006 to C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
channel aux1: restoring datafile 00007 to C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF
channel aux1: reading from backup piece D:\TEMP\ALL_BACKUP_DATA\O1_MF_NNNDF_TAG20100824T205754_6785KLLV_.BKP
channel aux1: piece handle=D:\TEMP\ALL_BACKUP_DATA\O1_MF_NNNDF_TAG20100824T205754_6785KLLV_.BKP tag=TAG20100824T205754
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:02:45
Finished restore at 24.08.10
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=727913123 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=727913123 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=727913123 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=727913124 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=727913124 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=727913124 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=727913124 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF
contents of Memory Script:
{
set until time "to_date( '24.08.2010 21:03:16','dd.mm.yyyy hh24:mi:ss')";
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 24.08.10
starting media recovery
archived log for thread 1 with sequence 465 is already on disk as file D:\TEMP\ALL_BACKUP_DATA\O1_MF_1_465_67865R4R_.ARC
channel aux1: starting archived log restore to default destination
channel aux1: restoring archived log
archived log thread=1 sequence=464
channel aux1: reading from backup piece D:\TEMP\ALL_BACKUP_DATA\O1_MF_ANNNN_TAG20100824T205954_6785OBRQ_.BKP
channel aux1: piece handle=D:\TEMP\ALL_BACKUP_DATA\O1_MF_ANNNN_TAG20100824T205954_6785OBRQ_.BKP tag=TAG20100824T205954
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:00:01
archived log file name=C:\ORACLE\PRODUCT\11.2.0\DB_1\RDBMS\ARC0000000464_0697456783.0001 thread=1 sequence=464
channel clone_default: deleting archived log(s)
archived log file name=C:\ORACLE\PRODUCT\11.2.0\DB_1\RDBMS\ARC0000000464_0697456783.0001 RECID=3 STAMP=727913126
archived log file name=D:\TEMP\ALL_BACKUP_DATA\O1_MF_1_465_67865R4R_.ARC thread=1 sequence=465
media recovery complete, elapsed time: 00:00:05
Finished recover at 24.08.10
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount pfile= 'c:\oracle\product\11.2.0\db_1\database\initORCL.ora';
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
allocated channel: aux1
channel aux1: SID=95 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO01.LOG' ) SIZE 50 M ,
GROUP 2 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO02.LOG' ) SIZE 50 M ,
GROUP 3 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO03.LOG' ) SIZE 50 M
DATAFILE
'C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF'
CHARACTER SET EE8MSWIN1250
contents of Memory Script:
{
set newname for tempfile 1 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF",
"C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF",
"C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF",
"C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF",
"C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF",
"C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to C:\ORACLE\PRODUCT\ORADATA\ORCL\TEMP01.DBF in control file
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF RECID=1 STAMP=727913146
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF RECID=2 STAMP=727913146
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF RECID=3 STAMP=727913146
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF RECID=4 STAMP=727913146
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF RECID=5 STAMP=727913146
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF RECID=6 STAMP=727913146
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 24.08.10
released channel: aux1
RMAN>
10. Checking
After duplicate finish, database is up and running.D:\>sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.1.0 Production on Uto Kol 24 22:11:28 2010 Copyright (c) 1982, 2010, 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 instance_name, status, to_char(STARTUP_TIME,'dd.mm.yyyy hh24:mi:ss') from v$instance; INSTANCE_NAME STATUS TO_CHAR(STARTUP_TIM ---------------- ------------ ------------------- orcl OPEN 24.08.2010 22:05:44 SQL>Let us check for t1 table and it's content:
SQL> select to_char(a_date,'dd.mm.yyyy hh24:mi:ss') was_pit_before from t1; WAS_PIT_BEFORE ------------------- 24.08.2010 21:03:06 SQL>Voila!
The End
In one of mine previous posts (Clone 11g ORACLE_HOME (different box, different path) on Linux) I have described how is easy to clone ORACLE_HOME in 11g on Linux. With this topic I think this subject is fully covered and may be use full for someone facing first time with it.Cheers!
Saving this example to my notes :)
ReplyDeleteThanks for sharing!
regards!
Marko,
ReplyDeleteMine impression was the same using this feature even on Windows env.
Rg,
Damir Vadas
http://damir-vadas.blogspot.com
Very nice post!
ReplyDeleteCongratulations.
@Albuquerque,
ReplyDeleteThank you.
This site is almost all "how to" with some mine DBA tricks...
You're welcome!
Damir Vadas
http://damir-vadas.blogspot.com/