This site has been destroyed by Google forced upgrade to new way of WEB site.
All files links are not working. Many images has been lost in conversation.
Have to edit 190 pages manually. Will try to do ASAP but for this I need time ...
THANK YOU GOOGLE !

Sunday, September 5, 2010

Irresistable DB Duplicate on 11g

What I like in Oracle and especially it's database technology is ability to recognize user's needs and help them as much as possible in shortest possible period. This thought is not based on Oracle Support (aka Metalink) but on new features that comes with new releases leaving all good untouched. One of the most commented topic in database technology is RMAN and its duplicate command-ability to clone any Oracle database. However, I feel free to write another post on mentioned subject in a little different way: to create out of the box solution for any duplicate situation that might come in ad hock situation.

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):
  1. 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!
  2. 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:
  1. set new environment:
    d:\>SET ORACLE_SID=ORCL
d:\>SET ORACLE_HOME=c:\oracle\product\11.2.0\db_1
  2. 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!
  3. 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.
  4. 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!)
Here the whole log of RMAN duplicate operation: 
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!
at Sunday, September 05, 2010

4 comments :

  1. Marko SuticSeptember 5, 2010 at 12:35 PM

    Saving this example to my notes :)

    Thanks for sharing!

    regards!

    ReplyDelete
  2. Damir VadasSeptember 5, 2010 at 12:53 PM

    Marko,
    Mine impression was the same using this feature even on Windows env.
    Rg,
    Damir Vadas
    http://damir-vadas.blogspot.com

    ReplyDelete
  3. AlbuquerqueSeptember 8, 2010 at 11:04 AM

    Very nice post!
    Congratulations.

    ReplyDelete
  4. Damir VadasSeptember 8, 2010 at 11:15 AM

    @Albuquerque,
    Thank you.
    This site is almost all "how to" with some mine DBA tricks...
    You're welcome!

    Damir Vadas
    http://damir-vadas.blogspot.com/

    ReplyDelete

Subscribe to: Post Comments ( Atom )

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.

Sign by Danasoft - Get Your Sign