Clone Oracle Database

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/CLHB/Default.aspx
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#i1008564
https://sites.google.com/a/dbaref.com/www/clone-oracle-database/creatingaduplicatedatabaseonanewhostusingrmanduplicatefeature
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10734/rcmrecov.htm
http://gavinsoorma.com/2010/03/create-standby-database-using-11g-duplicate-from-active-database/
http://www.oraclemasters.in/?p=1198
http://www.dbapundits.com/blog/step-by-step/oracle-11g-active-database-duplication/
http://www.dba-oracle.com/t_active_database_duplication_add.htm
http://www.orafaq.com/node/2383

RMAN Progress monitor SQL.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK

1. Change DBNAME not DBID (in NID command) so that your old backup works after the RMAN duplicate. If you are changing DBID then you need to re-register DB in RMAN catalog.
2. To avoid ORA-25153: Temporary Tablespace is Empty after RMAN duplicate check for tempfiles and re-add to TEMP tablespace after duplicate

If the database uses locally-managed temporary tablespaces, then add new tempfiles to these tablespaces. For example:

SQL>ALTER TABLESPACE temp  ADD TEMPFILE '/u03/PRODDB/oradata/temp01.dbf' REUSE";
SQL>ALTER TABLESPACE temp  ADD TEMPFILE '/u03/PRODDB/oradata/temp02.dbf' REUSE";
SQL> alter tablespace temp add TEMPFILE '/mnt/u03/RPTDB/oradata/temp02.dbf' size 500M;
Tablespace altered.
SQL>  alter tablespace temp add TEMPFILE '/mnt/u03/RPTDB/oradata/temp01.dbf' size 500M;
Tablespace altered.
SQL>

Temp file after duplicate db

posted Mar 19, 2011, 6:29 PM by Sachchida Ojha

SQL> select tablespace_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
TEMP
TEMP
SQL> select temporary_tablespace from dba_users where username='ADMN';
TEMPORARY_TABLESPACE
------------------------------
TEMP
SQL> select count(*) from dba_temp_files;
  COUNT(*)
----------
         2
SQL> desc dba_temp_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(257)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             CHAR(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;
FILE_NAME
------------------------------
TABLESPACE_NAME
-----------------------------
/u03/PRODDB/oradata/temp02.dbf
TEMP

/u03/PRODDB/oradata/temp01.dbf
TEMP

clone/duplicating an 11g database on the same machine using the new active database duplication

posted Mar 19, 2011, 6:20 PM by Sachchida Ojha   [ updated Mar 19, 2011, 6:24 PM ]

Here is a complete example of duplicating an 11g database on the same machine (Linux) using the new, aforementioned active database duplication:

1. Create any needed directories
mkdir /u03/app/oracle/oradata/duptest
mkdir /u03/app/oracle/admin/duptest/adump
2. Create hard-coded network entries for your new, auxiliary database

Addition to SID_LIST_LISTENER in listener.ora:
(SID_DESC =
(GLOBAL_DBNAME = duptest.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = duptest)
)
Addition to tnsames.ora:
DUPTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1523))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = duptest.colestock.test)
)
)
3. Create a passwordfile for new, auxiliary database (this MUST match the TARGET)
cd $ORACLE_HOME/dbs
orapwd file=orapwduptest password=password

4. Create 'dummy' parameter file for auxiliary instance

[/u03/app/oracle/product/db/11.1.0.6/dbs nf@rac2]$ more initduptest.ora
db_name=duptest
control_files=/u03/app/oracle/oradata/duptest/control01.ctl, /u03/app/oracle/ora
data/duptest/control02.ctl, /u03/app/oracle/oradata/duptest/control03.ctl
5. Add relevant entry to oratab on non-Windows environments
[/u03/app/oracle/product/db/11.1.0.6/dbs nf@rac2]$ grep duptest /etc/oratab
duptest:/u03/app/oracle/product/db/11.1.0.6:N
6. 'Nomount' the auxiliary instance in preparation for duplication

[/u03/app/oracle/product/db/11.1.0.6/dbs duptest@rac2] $ export ORACLE_SID=duptest
[/u03/app/oracle/product/db/11.1.0.6/dbs duptest@rac2] $ . oraenv
[/u03/app/oracle/product/db/11.1.0.6/dbs duptest@rac2] $ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 14 11:03:52 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 150667264 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 6758400 bytes
SQL> exit
7. Duplicate the database using RMAN
rman target=sys/password@nf auxiliary=sys/password@duptest
duplicate target database to duptest
2> from active database
3> db_file_name_convert '/nf/','/duptest/'
4> spfile
5> parameter_value_convert '/nf/','/duptest/'
6> set log_file_name_convert '/nf/','/duptest/'
7> set log_archive_dest_1='';

If successful, you should see output similar to the following:

Starting Duplicate Db at 14-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK

contents of Memory Script:
{
backup as copy reuse
file '/u03/app/oracle/product/db/11.1.0.6/dbs/spfilenf.ora' auxiliary format
'/u03/app/oracle/product/db/11.1.0.6/dbs/spfileduptest.ora' ;
sql clone "alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfileduptest.ora''";
}
executing Memory Script

Starting backup at 14-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=116 device type=DISK
Finished backup at 14-JAN-11

sql statement: alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfileduptest.ora''

contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPTEST'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u03/app/oracle/admin/duptest/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u03/app/oracle/oradata/duptest/control01.ctl'', ''/u03/app/oracle/oradata/duptest/control02.ctl'',
''/u03/app/oracle/oradata/duptest/control03.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/nf/'', ''/duptest/'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
'''' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

sql statement: alter system set db_name = ''DUPTEST'' comment= ''duplicate'' scope=spfile

sql statement: alter system set audit_file_dest = ''/u03/app/oracle/admin/duptest/adump'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/u03/app/oracle/oradata/duptest/control01.ctl'',
''/u03/app/oracle/oradata/duptest/control02.ctl'', ''/u03/app/oracle/oradata/duptest/control03.ctl''
comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/nf/'', ''/duptest/'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = '''' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 422670336 bytes

Fixed Size 1300352 bytes
Variable Size 406849664 bytes
Database Buffers 8388608 bytes
Redo Buffers 6131712 bytes

contents of Memory Script:
{
set newname for datafile 1 to
"/u03/app/oracle/oradata/duptest/system01.dbf";
set newname for datafile 2 to
"/u03/app/oracle/oradata/duptest/sysaux01.dbf";
set newname for datafile 3 to
"/u03/app/oracle/oradata/duptest/undotbs01.dbf";
set newname for datafile 4 to
"/u03/app/oracle/oradata/duptest/users01.dbf";
set newname for datafile 5 to
"/u03/app/oracle/oradata/duptest/flashback_data01.dbf";
set newname for datafile 6 to
"/u03/app/oracle/oradata/duptest/ts201.dbf";
set newname for datafile 7 to
"/u03/app/oracle/oradata/duptest/ts301.dbf";
set newname for datafile 8 to
"/u03/app/oracle/oradata/duptest/ts401.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u03/app/oracle/oradata/duptest/system01.dbf" datafile
2 auxiliary format
"/u03/app/oracle/oradata/duptest/sysaux01.dbf" datafile
3 auxiliary format
"/u03/app/oracle/oradata/duptest/undotbs01.dbf" datafile
4 auxiliary format
"/u03/app/oracle/oradata/duptest/users01.dbf" datafile
5 auxiliary format
"/u03/app/oracle/oradata/duptest/flashback_data01.dbf" datafile
6 auxiliary format
"/u03/app/oracle/oradata/duptest/ts201.dbf" datafile
7 auxiliary format
"/u03/app/oracle/oradata/duptest/ts301.dbf" datafile
8 auxiliary format
"/u03/app/oracle/oradata/duptest/ts401.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

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

executing command: SET NEWNAME

Starting backup at 14-JAN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u03/app/oracle/oradata/nf/undotbs01.dbf
output file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf tag=TAG20110114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:28
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u03/app/oracle/oradata/nf/users01.dbf
output file name=/u03/app/oracle/oradata/duptest/users01.dbf tag=TAG20110114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:37
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u03/app/oracle/oradata/nf/system01.dbf
output file name=/u03/app/oracle/oradata/duptest/system01.dbf tag=TAG20110114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u03/app/oracle/oradata/nf/sysaux01.dbf
output file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf tag=TAG20110114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:57
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u03/app/oracle/oradata/nf/ts201.dbf
output file name=/u03/app/oracle/oradata/duptest/ts201.dbf tag=TAG20110114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u03/app/oracle/oradata/nf/flashback_data01.dbf
output file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf tag=TAG20110114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u03/app/oracle/oradata/nf/ts301.dbf
output file name=/u03/app/oracle/oradata/duptest/ts301.dbf tag=TAG20110114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u03/app/oracle/oradata/nf/ts401.dbf
output file name=/u03/app/oracle/oradata/duptest/ts401.dbf tag=TAG20110114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-JAN-11

sql statement: alter system archive log current
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPTEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u03/app/oracle/oradata/duptest/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u03/app/oracle/oradata/duptest/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u03/app/oracle/oradata/duptest/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u03/app/oracle/oradata/duptest/system01.dbf'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
backup as copy reuse
archivelog like "/u03/app/oracle/oradata/nf/arch/nf_1_277_635081437.arc" auxiliary format
"/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc" ;
catalog clone archivelog "/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc";
switch clone datafile all;
}
executing Memory Script

Starting backup at 14-JAN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=277 RECID=329 STAMP=643982787
output file name=/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-JAN-11

cataloged archived log
archived log file name=/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc RECID=1 STAMP=643982804

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=643982804 file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/ts201.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/ts301.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/ts401.dbf

contents of Memory Script:
{
set until scn 3167561;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-JAN-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=151 device type=DISK

starting media recovery

archived log for thread 1 with sequence 277 is already on disk as file
/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc
archived log file name=/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc thread=1 sequence=277
media recovery complete, elapsed time: 00:00:03
Finished recover at 14-JAN-11

contents of Memory Script:
{
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 422670336 bytes

Fixed Size 1300352 bytes
Variable Size 406849664 bytes
Database Buffers 8388608 bytes
Redo Buffers 6131712 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPTEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u03/app/oracle/oradata/duptest/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u03/app/oracle/oradata/duptest/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u03/app/oracle/oradata/duptest/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u03/app/oracle/oradata/duptest/system01.dbf'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
set newname for tempfile 1 to
"/u03/app/oracle/oradata/duptest/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/sysaux01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/undotbs01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/users01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/flashback_data01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/ts201.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/ts301.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/ts401.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u03/app/oracle/oradata/duptest/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf RECID=1 STAMP=643982828

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf RECID=2 STAMP=643982829

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/users01.dbf RECID=3 STAMP=643982829

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf RECID=4 STAMP=643982829

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/ts201.dbf RECID=5 STAMP=643982829

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/ts301.dbf RECID=6 STAMP=643982829

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/ts401.dbf RECID=7 STAMP=643982829

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=643982828 file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/ts201.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/ts301.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/ts401.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 14-JAN-11

Duplicate an active database without using backups in Oracle 11g

posted Mar 19, 2011, 6:13 PM by Sachchida Ojha

oracle 11g provides a new feature, without RMAN database backup we can clone the database.
I have tested the active database RMAN cloning in my test server.

Environment Details:

Operating system: Windows XP service pack 2
Database Version: 11.1.0.7 (32 bit)
Source database name: troy
Clone database name: clonedb

How its work?

In an active duplication process, target database online image copies and archived redo log files were copied through the auxiliary instance service name. So we no need the target database backup.
Target database must be in archive log mode.
Database duplication process RMAN does the following things

1.Generate the unique DBID for auxiliary database.
2.Copy the data files & archived log files from target database to auxiliary database.
3.Recreate the new control files for auxiliary database.
4.Recreates the online redo log files.
5.Restart the auxiliary instance.
6.Open the database with RESETLOGS.

ACTIVE Database RMAN cloning in same server.

1.Configure the network files.

Listener configuration:

SID_LIST_LISTENER11G=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=troy)
(SID_NAME=troy)
(ORACLE_HOME=D:\Oracle\app\product\11.1.0\db_1)
)
(SID_DESC=
(GLOBAL_DBNAME=clonedb)
(SID_NAME=clonedb)
(ORACLE_HOME=D:\Oracle\app\product\11.1.0\db_1)
)
)
)

TNS configuration:

TROY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = troy)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = troy)
)
)

CLONEDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = troy)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clonedb)
)
)

2.Create the instance using oradim utility ( applicable for windows platform)

C:\>oradim -new -sid clonedb
Instance created.

3.Create a password file for clonedb database using orapwd utility

C:\>orapwd file=D:\Oracle\app\product\11.1.0\db_1\dbs\orapwCLONEDB password=clonedbadmin entries=20

4.Create the init. ora file for clonedb database.

Troy database: We create the pfile for clonedb from troy database & edit the parameter.

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\ORACLE\APP\PRODUCT\11.1.0\D
B_1\DATABASE\SPFILETROY.ORA

SQL> create pfile='D:\Oracle\app\product\admin\clonedb\pfile\initclonedb.ora'
from spfile;

File created.

Init parameter change “clonedb” instead of troy & use to start the Clonedb.

clonedb.__db_cache_size=130023424
clonedb.__java_pool_size=12582912
clonedb.__large_pool_size=4194304
clonedb.__oracle_base='D:\Oracle\app\product'#ORACLE_BASE set from environment
clonedb.__pga_aggregate_target=192937984
clonedb.__sga_target=348127232
clonedb.__shared_io_pool_size=0
clonedb.__shared_pool_size=184549376
clonedb.__streams_pool_size=8388608
*.audit_file_dest='D:\Oracle\app\product\admin\clonedb\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='D:\Oracle\app\product\oradata\clonedb\control01.ctl','D:\Oracle\app\product\oradata\clonedb\control02.ctl','D:\Oracle\app\product\oradata\clonedb\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='clonedb'
*.diagnostic_dest='D:\Oracle\app\product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
*.log_archive_dest='D:\Oracle\app\product\archive\clonedb'
*.log_archive_start=TRUE
*.memory_target=538968064
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTBS1'
db_file_name_convert = ('D:\ORACLE\APP\PRODUCT\ORADATA\TROY','D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB')
log_file_name_convert = ('D:\ORACLE\APP\PRODUCT\ORADATA\TROY','D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB')

5.Create the appropriate folder for clonedb database (admin/oradata folders etc..)

6.Startup the clone database in mount stage

C:\>set oracle_sid=clonedb

C:\>sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 28 14:21:08 2010

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 398462052 bytes
Database Buffers 130023424 bytes
Redo Buffers 5828608 bytes

7.Connect the target & auxiliary database using RMAN

C:\>rman target sys/troyadmin@troy

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Apr 28 14:26:18 2010

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

connected to target database: TROY (DBID=3876493159)

RMAN> connect auxiliary sys/clonedbadmin@clonedb

connected to auxiliary database: CLONEDB (not mounted)

RMAN> duplicate target database to clonedb from active database nofilenamecheck;

Starting Duplicate Db at 28-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=152 device type=DISK

contents of Memory Script:
{
set newname for datafile 1 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01.DBF";
set newname for datafile 3 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS01.DBF";
set newname for datafile 4 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.DBF";
set newname for datafile 5 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE01.DBF";
set newname for datafile 6 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.DBF";
backup as copy reuse
datafile 1 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSTEM01.DBF" datafile
2 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01.DBF" datafile
3 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS01.DBF" datafile
4 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.DBF" datafile
5 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE01.DBF" datafile
6 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.DBF" ;
sql 'alter system archive log current';
}
executing Memory Script

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 backup at 28-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\SYSTEM01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSTEM01.DBF tag=TAG20100428T142723 RECID=0 S
TAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\SYSAUX01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01.DBF tag=TAG20100428T142723 RECID=0 S
TAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\EXAMPLE01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE01.DBF tag=TAG20100428T142723 RECID=0
STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\MANI01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.DBF tag=TAG20100428T142723 RECID=0 STA
MP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\UNDOTBS01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS01.DBF tag=TAG20100428T142723 RECID=0
STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\USERS01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.DBF tag=TAG20100428T142723 RECID=0 ST
AMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-APR-10

sql statement: alter system archive log current
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONEDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
backup as copy reuse
archivelog like "D:\ORACLE\APP\PRODUCT\ARCHIVE\TROY\ARC00025_0715865130.001" auxiliary format
"D:\ORACLE\APP\PRODUCT\ARCHIVE\CLONEDB\ARC00025_0715865130.001" ;
catalog clone archivelog "D:\ORACLE\APP\PRODUCT\ARCHIVE\CLONEDB\ARC00025_0715865130.001";
switch clone datafile all;
}
executing Memory Script

Starting backup at 28-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=25 RECID=9 STAMP=717517736
output file name=D:\ORACLE\APP\PRODUCT\ARCHIVE\CLONEDB\ARC00025_0715865130.001 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 28-APR-10

cataloged archived log
archived log file name=D:\ORACLE\APP\PRODUCT\ARCHIVE\CLONEDB\ARC00025_0715865130.001 RECID=1 STAMP=7
17517739

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=717517739 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01
.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=717517739 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS0
1.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=717517739 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.
DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=717517740 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE0
1.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=717517740 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.D
BF

contents of Memory Script:
{
set until scn 1422661;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 28-APR-10
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 25 is already on disk as file D:\ORACLE\APP\PRODUCT\ARCHIVE\
CLONEDB\ARC00025_0715865130.001
archived log file name=D:\ORACLE\APP\PRODUCT\ARCHIVE\CLONEDB\ARC00025_0715865130.001 thread=1 sequen
ce=25
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-APR-10

contents of Memory Script:
{
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 535662592 bytes

Fixed Size 1348508 bytes
Variable Size 398462052 bytes
Database Buffers 130023424 bytes
Redo Buffers 5828608 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONEDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
set newname for tempfile 1 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\TEMP01.DBF";
set newname for tempfile 2 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\TEMP02.DBF";
switch clone tempfile all;
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.DBF";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\TEMP01.DBF in control file
renamed tempfile 2 to D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\TEMP02.DBF in control file

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01.DBF RECID=1 STAMP=717517768

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS01.DBF RECID=2 STAMP=717517768

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.DBF RECID=3 STAMP=717517769

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE01.DBF RECID=4 STAMP=717517769

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.DBF RECID=5 STAMP=717517769

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=717517768 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01
.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=717517768 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS0
1.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=717517769 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.
DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=717517769 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE0
1.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=717517769 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.D
BF

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 28-APR-10

8.Verify the clonedb database.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- ----------
CLONEDB READ WRITE

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\Oracle\app\product\archive\clonedb
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1

Active database duplication is better way to clone the database, since don’t you have any prior backup.

How do I clone a database from one machine to another - when the filesystems are different ?

posted Mar 17, 2011, 11:04 AM by Sachchida Ojha

Cloning a database is normally done with the following sequence:

  1. Copy the datafiles to the target node
  2. Produce a CREATE CONTROLFILE command for the cloned database
  3. Start the target database in NOMOUNT mode
  4. Run the create controlfile command created in (2)
  5. Issue "recover database using backup controlfile until cancel"
  6. Provide the recovery processe with archived redo logs until you are happy
  7. Cancel the recovery and issue "alter database open resetlogs"
  8. Re-create or re-add any TEMPFILES used for temporary tablespaces

This is all quite straightforward even if you have to rename the database, and any of the files in the database. The secret is in step (2). On the source database, you can run

SQL> alter database backup controlfile to trace

at which point you will get a full "create controlfile" command dumped out to the user_dump_dest directory. It will look something like

CREATE CONTROLFILE REUSE DATABASE "SOURCEDB" NORESETLOGS ARCHIVELOG
...
LOGFILE
GROUP 1 '/disk1/log1.dbf' SIZE 50M,
...
DATAFILE
'/disk2/system01.dbf',
'/disk3/rbs01.dbf',
...
'/diskN/last_file01.dbf'
CHARACTER SET WE8ISO8859P1;

For cloning this database, you will alter

  • The REUSE to SET
  • The SOURCEDB to NEWDB (that is, the new name of the target database)
  • The NORESETLOGS to RESETLOGS

For each file listed under the DATAFILE clause, if you will be renaming the file as part of the cloning process, then you simply update the file name to the new name in the CREATE CONTROLFILE command.

Clone database using cold backup

posted Mar 17, 2011, 10:59 AM by Sachchida Ojha

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another.  It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
    NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 240
    MAXINSTANCES 1
    MAXLOGHISTORY 113
    LOGFILE
    GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
    '/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
    GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
    '/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
    DATAFILE
    '/u01/oradata/oldlsq/system01.dbf',
    '/u01/oradata/oldlsq/mydatabase.dbf'
    ;
    # Recovery is required if any of the datafiles are restored
    # backups, or if the last shutdown was not normal or immediate.
    RECOVER DATABASE
    # Database can now be opened normally.
    ALTER DATABASE OPEN;
    

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

    rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
    rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
    rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
    rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
    

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

    CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New:

    CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

    # Recovery is required if any of the datafiles are restored
    # backups, or if the last shutdown was not normal or immediate.
    RECOVER DATABASE
    # Database can now be opened normally.
    ALTER DATABASE OPEN;
    

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

    DATAFILE
    '/u01/oradata/oldlsq/system01.dbf',
    '/u01/oradata/oldlsq/mydatabase.dbf'

New:

    DATAFILE
    '/u01/oradata/newlsq/system01.dbf',
    '/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

    cd $DBA/admin
    mkdir newlsq
    cd newlsq
    mkdir bdump
    mkdir udump
    mkdir cdump
    mkdir pfile


STEP 8: Copy-over the old init.ora file

    rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database

    @db_create_controlfile.sql

STEP 10: Place the new database in archivelog mode

Clone a Database Using Hot Backup

posted Mar 17, 2011, 8:47 AM by Sachchida Ojha   [ updated Mar 17, 2011, 10:31 AM ]

This document gives you a detailed flow of how to backup the database in a 24x7 operation and use it for cloning a Development or Test database. By using the System switching of Log files option, it reduces the amount of archive logs to be applied and reduces the time of recovery (the goal).Most sites running Oracle databases need the latest production databases to be cloned for the development and test teams to work on. These sites need to be refreshed periodically with the production database and Application. Most DBAs use Cold Backup procedures to clone a database. Although cold backups are the most common backup procedures for database cloning, there may be situations where no downtime is available. Hot Backups can be performed when the database is up and in Archive log mode.

Definitions and Assumptions

Source Database: The active production instance/database to be cloned.
Target Database: The cloned database, typically a test/development database.

Cloning steps

Switching Logs


We need to make sure that all the present online logs are switched and archived, so that the time to do the media recovery to the cloned database is as small as possible.
Query the number of log groups that exists in the database and switch as many times as there are log groups.

Select group#,archived,status from v$log;
 
SQL> Select group#,archived,status from v$log;


GROUP# ARC STATUS
---------- --- ----------------
1 NO CURRENT
2 YES UNUSED
3 YES UNUSED
4 YES UNUSED
5 YES INACTIVE

SQL> SQL>

We can see from the example above, we need to switch logs to archive the online logs.

alter system switch logfile;
System altered.

Make sure that no log group shows a “STALE” status.

Create the destination directory structures as you would like to move the data files and redo log files. Also create the dump directory to hold the t
arget database dumps (udump, bdump cdump, adump etc).Let’s note down the last archive log file from the archive log destination directory or from the alert log and then do another

Alter system switch logfile;

This would give us the current log that got archived by switching and also a checkpoint occurs. Remember that switching leads to check pointing and check
pointing not necessarily need lead to a log switch. So the data files all get consistent since the current scn’s are stamped. We do the switch to the number
of log groups we have. That means the first archive log file that was switched might be your full file size and the
subsequent ones would have lesser size depending on the data.

Copy source database files to the target

The next step is to copy all the data files of the source database to the destination database directories.

Before starting to copy the files of a particular tablespace, we need to put that tablespace into hot backup mode.

By putting a tablespace into hot backup mode, we will ensure two things.

1) The first time a block is changed in a data file that is in hot backup mode, the entire block is written to the redo log files, not just
the changed bytes. Normally only the changed bytes (a redo vector) are written. In hot backup mode, the entire block is logged the first time.
This is because you can get into a situation where the process copying the data file and DBWR are working on the same block simultaneously.

2) The data file headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets
the recovery process understand what archive redo log files might be needed to fully recover this file.

To limit the effect of this additional logging, you should ensure you only place one tablespace at a time in backup mode and bring the
tablespace out of backup mode as soon as you have backed it up. This will reduce the number of blocks that may have to be logged to the
minimum possible.

List out all the tablespaces present in the system and their corresponding data files.

SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
TEMP ONLINE TEMPORARY
DRSYS ONLINE PERMANENT
TOOLS ONLINE PERMANENT
USERS ONLINE PERMANENT
XDB ONLINE PERMANENT
ATA_DATA ONLINE PERMANENT
ATA_DATA_CHARGE ONLINE PERMANENT
ATA_DATA_NE ONLINE PERMANENT
ATA_IC_LOB ONLINE PERMANENT

TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
ATA_INDEX ONLINE PERMANENT
ATA_INDEX_CHARGE ONLINE PERMANENT
ATA_INDEX_NE ONLINE PERMANENT
ATA_TMP ONLINE TEMPORARY
PERFSTAT ONLINE PERMANENT
TPSYSADM_DATA ONLINE PERMANENT
TPSYSADM_INDEX ONLINE PERMANENT
FORECASTING ONLINE PERMANENT
ARCHIVED_CHARGE_DATA ONLINE PERMANENT
ARCHIVED_CHARGE_IDX ONLINE PERMANENT
ARCHIVED_NE_DATA ONLINE PERMANENT

TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
ARCHIVED_NE_IDX ONLINE PERMANENT

23 rows selected.

SQL>
select tablespace_name,file_name from dba_data_files order by tablespace_name;

TABLESPACE_NAME FILE_NAME
-------------------------------- ----------------------------------------------------------
ARCHIVED_CHARGE_DATA /u08/SVPRD1B/oradata/archived_charge_data.dbf
ARCHIVED_CHARGE_DATA /u08/SVPRD1B/oradata/archived_charge_data02.dbf
ARCHIVED_CHARGE_IDX /u09/SVPRD1B/oradata/archived_charge_idx01.dbf
ARCHIVED_NE_DATA /u08/SVPRD1B/oradata/archived_ne_data02.dbf
ARCHIVED_NE_DATA /u08/SVPRD1B/oradata/archived_ne_data03.dbf
ARCHIVED_NE_DATA /u08/SVPRD1B/oradata/archived_ne_data01.dbf
ARCHIVED_NE_IDX /u09/SVPRD1B/oradata/archived_ne_idx01.dbf
ATA_DATA /u08/SVPRD1B/oradata/ata_data01.dbf
ATA_DATA_CHARGE /u08/SVPRD1B/oradata/ata_data_charge01.dbf
ATA_DATA_NE /u08/SVPRD1B/oradata/ata_data_ne01.dbf
ATA_IC_LOB /u08/SVPRD1B/oradata/ata_ic_lob01.dbf

TABLESPACE_NAME FILE_NAME
-------------------------------- ----------------------------------------------------------
ATA_INDEX /u09/SVPRD1B/oradata/ata_index01.dbf
ATA_INDEX_CHARGE /u09/SVPRD1B/oradata/ata_index_charge01.dbf
ATA_INDEX_NE /u09/SVPRD1B/oradata/ata_index_ne01.dbf
DRSYS /u02/SVPRD1B/oradata/drsys01.dbf
FORECASTING /u08/SVPRD1B/oradata/forecasting.dbf
PERFSTAT /u04/SVPRD1B/oradata/perfstat01.dbf
SYSTEM /u02/SVPRD1B/oradata/system01.dbf
TOOLS /u08/SVPRD1B/oradata/tools01.dbf
TPSYSADM_DATA /u08/SVPRD1B/oradata/tpsysadm_data01.dbf
TPSYSADM_INDEX /u09/SVPRD1B/oradata/tpsysadm_index01.dbf
UNDOTBS1 /u04/SVPRD1B/oradata/undotbs01.dbf

TABLESPACE_NAME FILE_NAME
-------------------------------- ----------------------------------------------------------
USERS /u08/SVPRD1B/oradata/users01.dbf
XDB /u03/SVPRD1B/oradata/xdb01.dbf

Now put each tablespace into hot backup mode, copy it’s corresponding data files to the destination directory, and then bring out the tablespace from the hot backup mode, one tablespace at a time.
Alter tablespace ARCHIVED_CHARGE_DATA begin backup;
!cp /u08/SVPRD1B/oradata/archived_charge_data.dbf  /mnt/u08/SVPRD1B/oradata/archived_charge_data.dbf
Alter tablespace ARCHIVED_CHARGE_DATA end backup;

It is better to do the above exercise using a script, so that manual intervention, and hence, chance of committing an error, is also less. Following is a script that you can use to generate a hot backup copy script from any database.

================================================================
set serveroutput on
set heading off
set feedback off
spool backup.sql
declare
fname varchar2(55);
tname varchar2(55);
tname1 varchar2(55);
cursor tspaces is
select tablespace_name,file_name from v$datafile,sys.dba_data_files
where enabled like '%WRITE%' and file# = file_id order by 1
begin
dbms_output.enable(32000);
dbms_output.put_line('spool hotback');
open tspaces;
fetch tspaces into tname,fname;
tname1 := tname;
dbms_output.put_line('alter tablespace '||tname||' begin backup;');
while tspaces%FOUND loop
if tname1 != tname then
dbms_output.put_line('alter tablespace '||tname1||' end backup;');
dbms_output.put_line('alter tablespace '||tname||' begin backup;');
tname1 := tname;
end if;
dbms_output.put_line('!cp '||fname||' /mnt/u08/SVPRD1B/oradata/');
--Change the line above line with os specific copy command.
fetch tspaces into tname,fname;
end loop;
dbms_output.put_line('alter tablespace '||tname1||' end backup;');
close tspaces;
dbms_output.put_line('spool off');
end;
/
spool off
set heading on
set feedback on
set serveroutput off

================================================================

Switch logs again


Once again, once the copy is complete, switch log groups the required number of times. In this way we need to copy over only those archive files starting with the ones that we noted down when we started the log switch to the ones we have when we did the log switch after the backup. These would be very few and hence the recovery would also be faster when we
apply these archive files. We would also have a set of archive files that we are sure of and consistency can thus be assured.

Backup the Control File.

Now we need a control file creation script for the target database. The best way to achieve this is to backup the source control file to trace with the following command.

alter database backup controlfile to trace;

Database altered.

Copy the appropriate archive logs and online redo logs to the target location.

Prepare Initialization Parameter File

Make appropriate changes to the initialization parameter file of the target db to reflect the new file locations and new database.
Create appropriate target dump locations (adump, bdump, udump etc).

Creating Service and control files in target

Set the sid to the target database.

If the target host is windows, then you will need to create database
service control with the oradim command. Use the following syntax to
create the service.
Oradim –new –sid <target db name> -intpwd <sys password> -startmode manual

start a sqlplus session as sysdba and start the instance in nomount mode with target init file.

Open the control file trace backup. Towards the end, you’ll find
controlfile creation statement with resetlogs option .Following is an
example:

CREATE CONTROLFILE reuse DATABASE "DB1" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2920
LOGFILE
GROUP 1 'C:\FLIRT\REDO1\REDO01.LOG' SIZE 5M,
GROUP 2 'C:\FLIRT\REDO2\REDO02.LOG' SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'C:\FLIRT\DBFILES\SYSTEM01.DBF',
'C:\FLIRT\DBFILES\UNDOTBS01.DBF',
'C:\FLIRT\DBFILES\SYSAUX01.DBF',
'C:\FLIRT\DBFILES\USERS01.DBF',
'C:\FLIRT\DBFILES\FLIRT01.DBF',
'C:\FLIRT\DBFILES\DGOD01.DBF'
CHARACTER SET WE8MSWIN1252
;

Change the first line of the statement to look like this :
CREATE CONTROLFILE set DATABASE "DB2" RESETLOGS ARCHIVELOG ……..
Also change the file locations of the database mentioned in the statement to reflect the target file location. After all the changes to the statement, it should look like following :
CREATE CONTROLFILE set DATABASE "DB2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2920
LOGFILE
GROUP 1 'C:\DB2\REDO1\REDO01.LOG' SIZE 5M,
GROUP 2 'C:\DB2\REDO2\REDO02.LOG' SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'C:\DB2\DBFILES\SYSTEM01.DBF',
'C:\DB2\DBFILES\UNDOTBS01.DBF',
'C:\DB2\DBFILES\SYSAUX01.DBF',
'C:\DB2\DBFILES\USERS01.DBF',
'C:\DB2\DBFILES\flirt01.DBF',
'C:\DB2\DBFILES\DGOD01.DBF'
CHARACTER SET WE8MSWIN1252
;
Go the sqlplus session and execute the above (changed) controlfile creation statement to create the controlfiles. Controlfile should be created without any errors.

Apply Media Recovery


Now you need to apply media recovery to make the database consistent. Execute the following statement.
RECOVER DATABASE USING BACKUP CONTROLFILE;
You may need to apply the online redo logs to make the media recovery complete.
After media recovery is complete open the database in restelogs mode.
Code :

Alter database open resetlogs;

Add tempfiles to the temporary tablespaces.
Code :

ALTER TABLESPACE TEMP ADD TEMPFILE
'C:\valen\DBFILES\TEMP01.DBF'
SIZE 20971520 REUSE AUTOEXTEND OFF;

Cloning is complete.





Make a clone of your database with RMAN (on the same server as the target database) - windows

posted Mar 16, 2011, 6:35 PM by Sachchida Ojha

Before execution of the clone script I

1. Created the directories for the datafiles, controlfiles, redologfiles for the new clone database

2. I opened the target database with db_name = UTF8. UTF8 is the database which I would like to clone to a database with db_name = CLONE

3. I copied and renamed the passwordfile 

4. I created the windows service oracleserviceclone ( with oradim )

5. I made the pfile / spfile for the new database with db_name = CLONE. Please note I had to take away the OMF entry for the controlfiles. There are still some issues left with OMF redolog files and OMF controlfiles.

C:\> SET ORACLE_SID=CLONE

C:\>sqlplus sys/utf as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 12 13:41:09 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount;
-- I startup nomount the clone database
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 473959760 bytes
Database Buffers 46137344 bytes
Redo Buffers 2940928 bytes

C:\>lsnrctl services listener1
-- I check the services of the listener

It is time for the real work right now

C:\>rman target "sys/utf@to_brussels"
-- Connection to target database
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Feb 10 16:44:46 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: UTF8 (DBID=1828263075)

RMAN> connect auxiliary sys/utf
-- Connection to clone database
connected to auxiliary database: CLONE (not mounted)

I used this script in order to invoke the cloning work

RUN

set newname for datafile 1 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1DT8JGN2_.DBF';
set newname for datafile 2 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1M08DN7D_.DBF';
set newname for datafile 3 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1NBJ85GR_.DBF';
set newname for datafile 4 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1DT8LVYG_.DBF';
set newname for datafile 5 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1JOTM2S4_.DBF';
set newname for datafile 6 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1OH2VGQP_.DBF';
set newname for datafile 7 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1OH2XWW4_.DBF';
set newname for datafile 8 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1V1OPZWO_.DBF';
set newname for datafile 9 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1WKM1WSD_.DBF';
set newname for tempfile 1 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_TEMP_1P47MLKR_.TMP';
DUPLICATE TARGET DATABASE TO "CLONE"
LOGFILE
GROUP 1 ('C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO') SIZE 10M,
-- I made an error in the path name
GROUP 2 ('C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO02.RDO') SIZE 10M,
-- I made an error in the path name
GROUP 3 ('C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO03.RDO') SIZE 10M;
-- I made an error in the path name
}

This is the output of the script. Please note that only at the final end I had do some manual work, since I made a mistake at the level of the path for the online redo logs.

executing command: SET NEWNAME
using target database control file instead of recovery catalog

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
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting Duplicate Db at 12-FEB-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=157 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=156 devtype=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: sid=155 devtype=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: sid=154 devtype=DISK

contents of Memory Script:
{
set until scn 10616265;
set newname for datafile 1 to
"C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1DT8JGN2_.DBF";
set newname for datafile 2 to
"C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1M08DN7D_.DBF";
set newname for datafile 3 to
"C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1NBJ85GR_.DBF";
set newname for datafile 4 to
"C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1DT8LVYG_.DBF";
set newname for datafile 5 to
"C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1JOTM2S4_.DBF";
set newname for datafile 6 to
"C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1OH2VGQP_.DBF";
set newname for datafile 7 to
"C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1OH2XWW4_.DBF";
set newname for datafile 8 to
"C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1V1OPZWO_.DBF";
set newname for datafile 9 to
"C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1WKM1WSD_.DBF";
restore
check readonly
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
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 12-FEB-06
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1OH2VGQP_.DBF
restoring datafile 00008 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1V1OPZWO_.DBF
channel ORA_AUX_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS86P37_.BKP
channel ORA_AUX_DISK_2: starting datafile backupset restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1DT8JGN2_.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1NBJ85GR_.DBF
channel ORA_AUX_DISK_2: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS87YMF_.BKP
channel ORA_AUX_DISK_3: starting datafile backupset restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
restoring datafile 00005 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1JOTM2S4_.DBF
restoring datafile 00007 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1OH2XWW4_.DBF
channel ORA_AUX_DISK_3: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS87FFC_.BKP
channel ORA_AUX_DISK_4: starting datafile backupset restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
restoring datafile 00002 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1M08DN7D_.DBF
channel ORA_AUX_DISK_4: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS8GYHF_.BKP
channel ORA_AUX_DISK_4: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS8GYHF_.BKP tag=TAG20060210T151853
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:09:08
channel ORA_AUX_DISK_4: starting datafile backupset restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
restoring datafile 00004 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1DT8LVYG_.DBF
restoring datafile 00009 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1WKM1WSD_.DBF
channel ORA_AUX_DISK_4: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS86JRL_.BKP
channel ORA_AUX_DISK_2: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS87YMF_.BKP tag=TAG20060210T151853
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:11:24
channel ORA_AUX_DISK_3: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS87FFC_.BKP tag=TAG20060210T151853
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:11:31
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS86P37_.BKP tag=TAG20060210T151853
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:12:17
channel ORA_AUX_DISK_4: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS86JRL_.BKP tag=TAG20060210T151853
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:07:04
Finished restore at 12-FEB-06
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 4090
LOGFILE
GROUP 1 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO' ) SIZE 10 M ,
GROUP 2 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO02.RDO' ) SIZE 10 M ,
GROUP 3 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO03.RDO' ) SIZE 10 M
DATAFILE
'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1YYG2RXX_.DBF'
CHARACTER SET AL32UTF8


contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

released channel: ORA_AUX_DISK_1
released channel: ORA_AUX_DISK_2
released channel: ORA_AUX_DISK_3
released channel: ORA_AUX_DISK_4
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=582216382 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1YYG2S9N_.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=582216382 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1YYG2T33_.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=582216382 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1YYGMYBV_.DBF
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=582216382 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1YYG2SHW_.DBF
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=582216383 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1YYG2RVQ_.DBF
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=582216383 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1YYG2S9Y_.DBF
datafile 8 switched to datafile copy
input datafile copy recid=7 stamp=582216383 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1YYG2T8Q_.DBF
datafile 9 switched to datafile copy
input datafile copy recid=8 stamp=582216383 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1YYGMZ6K_.DBF

contents of Memory Script:
{
set until scn 10616265;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 12-FEB-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=158 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=154 devtype=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: sid=155 devtype=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: sid=156 devtype=DISK

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=4407
channel ORA_AUX_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_ANNNN_TAG20060210T153431_1YS9F8S9_.BKP
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_ANNNN_TAG20060210T153431_1YS9F8S9_.BKP tag=TAG20060210T153431
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:27
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DUPLICAT\ARCHIVELOG\2006_02_12\O1_MF_1_4407_1YYH1YVL_.ARC thread=1 sequence=4407
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DUPLICAT\ARCHIVELOG\2006_02_12\O1_MF_1_4407_1YYH1YVL_.ARC recid=1 stamp=582216415
media recovery complete, elapsed time: 00:00:08
Finished recover at 12-FEB-06

contents of Memory Script:
{
shutdown clone;
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 524288000 bytes

Fixed Size 1249968 bytes
Variable Size 473959760 bytes
Database Buffers 46137344 bytes
Redo Buffers 2940928 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 4090
LOGFILE
GROUP 1 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO' ) SIZE 10 M ,
GROUP 2 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO02.RDO' ) SIZE 10 M ,
GROUP 3 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO03.RDO' ) SIZE 10 M
DATAFILE
'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1YYG2RXX_.DBF'
CHARACTER SET AL32UTF8


contents of Memory Script:
{
set newname for tempfile 1 to
"C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_TEMP_1P47MLKR_.TMP";
switch clone tempfile all;
catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1YYG2S9N_.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1YYG2T33_.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1YYGMYBV_.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1YYG2SHW_.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1YYG2RVQ_.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1YYG2S9Y_.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1YYG2T8Q_.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1YYGMZ6K_.DBF";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_TEMP_1P47MLKR_.TMP in control file

cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1YYG2S9N_.DBF recid=1 stamp=582216453

cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1YYG2T33_.DBF recid=2 stamp=582216454

cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1YYGMYBV_.DBF recid=3 stamp=582216455

cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1YYG2SHW_.DBF recid=4 stamp=582216455

cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1YYG2RVQ_.DBF recid=5 stamp=582216456

cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1YYG2S9Y_.DBF recid=6 stamp=582216457

cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1YYG2T8Q_.DBF recid=7 stamp=582216458

cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1YYGMZ6K_.DBF recid=8 stamp=582216459

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=582216453 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1YYG2S9N_.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=582216454 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1YYG2T33_.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=582216455 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1YYGMYBV_.DBF
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=582216455 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1YYG2SHW_.DBF
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=582216456 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1YYG2RVQ_.DBF
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=582216457 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1YYG2S9Y_.DBF
datafile 8 switched to datafile copy
input datafile copy recid=7 stamp=582216458 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1YYG2T8Q_.DBF
datafile 9 switched to datafile copy
input datafile copy recid=8 stamp=582216459 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1YYGMZ6K_.DBF

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/12/2006 14:47:44
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-00344: unable to re-create
online log 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

At the end, something went wrong , I made an error in the directory paths of the online redo logs as such they couldn' t have been created by the alter database open resetlogs. Let us take a look and let us correct in a traditional way

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------

C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO03.RDO
C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO02.RDO
C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO

SQL> alter database rename file 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO03.RDO' to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO03.RDO';
Database altered.

SQL> alter database rename file 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO' to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO01.RDO';
Database altered.

SQL> alter database rename file 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO02.RDO' to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO02.RDO';
Database altered.

SQL> alter database open resetlogs;
Database altered.

My database with db_name UTF8 has been copied, cloned on the same server, other directories, other db_name and offcourse other dbid, let us explore some well known v$-views 

SQL> select dbid,name from v$database;

DBID NAME
---------- ---------
781032125 CLONE

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------------

C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1YYG2RXX_.DBF
C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1YYG2S9N_.DBF
C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1YYG2T33_.DBF
C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1YYGMYBV_.DBF
C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1YYG2SHW_.DBF
C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1YYG2RVQ_.DBF
C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1YYG2S9Y_.DBF
C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1YYG2T8Q_.DBF
C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1YYGMZ6K_.DBF

9 rows selected.

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------------------

C:\ORACLE\ORADATA\CLONE\DUPLICAT\CONTROLFILE\O1_MF_1YYH15D2_.CTL
C:\ORACLE\FLASH_RECOVERY_AREA\DUPLICAT\CONTROLFILE\O1_MF_1YYH16VG_.CTL

SQL> select member from v$logfile;

MEMBER
-------------------------------------------------------------------------

C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO03.RDO
C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO02.RDO
C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO01.RDO

SQL> select name from v$tempfile;

NAME
-------------------------------------------------------------------------

C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_TEMP_1YYHPXV3_.TMP

Duplicate Oracle 9i database - same server

posted Mar 16, 2011, 4:01 PM by Sachchida Ojha   [ updated Mar 17, 2011, 4:39 AM ]

1. Create password file

va-ibill01:SVPRD1B:cd /u01/SVPRD1B/app/oracle/product/92/dbs


va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs

$orapwd file=/u01/SVPRD1B/app/oracle/product/92/dbs/orapwDUP password=password  entries=10

$ls orapwDUP
orapwDUP

1. Update listener. ora file and restart the listener

$cd $ORACLE_HOME/network/admin

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/network/admin
$ls
libnk59.exp   libnk59.imp   libnrad9.exp  libnrad9.imp  listener.ora  samples       shrept.lst    snmp_ro.ora   snmp_rw.ora   sqlnet.ora    tnsnames.ora

$vi listener.ora
"listener.ora" 16 lines, 430 characters
# LISTENER.ORA Network Configuration File: /u01/SVPRD1B/app/oracle/product/92/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = va-ibill01)(PORT = 1521))
      )
    )
  )
DUP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =  va-ibill01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DUP)
    )
  )


INBOUND_CONNECT_TIMEOUT_LISTENER = 0

Save file.

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/network/admin

$lsnrctl reload

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production on 16-MAR-2011 21:08:16

Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/network/admin

$cd /u01/SVPRD1B/app/oracle/product/92/dbs

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs
$ls
autocf_SVPRD1B_c-3443740097-20100902-0d.f  initSVPRF1B.ora                            orapwSVPRD1B                               snapcf_SVPRD1B.f
autocf_SVPRD1B_c-3443740097-20110228-02.f  initdw.ora                                 reorg418.sql                               spfileSVPRD1B.ora
init.ora                                   lkSVPRD1B                                  reorg418.sql.orginal
initSVPRD1B.ora                            orapwDUP                                   reorg421.sql

3. Create new init.ora file for DUP database.

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs

$cp initSVPRD1B.ora initDUP.ora

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs

Update the initDUP.ora file.

a) Updatelocation of new control files.
b) get location of the data files  from prod and use DB_FILE_NAME_CONVERT to point new location
c)Get location of all log files from prod and use  LOG_FILE_NAME_CONVERT to point new location

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/mnt/u02/SVPRD1B/oradata/system01.dbf
/mnt/u04/SVPRD1B/oradata/undotbs01.dbf
/mnt/u02/SVPRD1B/oradata/drsys01.dbf
/mnt/u08/SVPRD1B/oradata/tools01.dbf
/mnt/u08/SVPRD1B/oradata/users01.dbf
/mnt/u03/SVPRD1B/oradata/xdb01.dbf
/mnt/u09/SVPRD1B/oradata/archived_ne_idx01.dbf
/mnt/u08/SVPRD1B/oradata/archived_ne_data02.dbf
/mnt/u08/SVPRD1B/oradata/archived_ne_data03.dbf
/mnt/u08/SVPRD1B/oradata/ata_data01.dbf
/mnt/u08/SVPRD1B/oradata/ata_data_charge01.dbf

NAME
--------------------------------------------------------------------------------
/mnt/u08/SVPRD1B/oradata/ata_data_ne01.dbf
/mnt/u08/SVPRD1B/oradata/ata_ic_lob01.dbf
/mnt/u09/SVPRD1B/oradata/ata_index01.dbf
/mnt/u09/SVPRD1B/oradata/ata_index_charge01.dbf
/mnt/u09/SVPRD1B/oradata/ata_index_ne01.dbf
/mnt/u04/SVPRD1B/oradata/perfstat01.dbf
/mnt/u08/SVPRD1B/oradata/tpsysadm_data01.dbf
/mnt/u09/SVPRD1B/oradata/tpsysadm_index01.dbf
/mnt/u08/SVPRD1B/oradata/forecasting.dbf
/mnt/u08/SVPRD1B/oradata/archived_charge_data.dbf
/mnt/u09/SVPRD1B/oradata/archived_charge_idx01.dbf

NAME
--------------------------------------------------------------------------------
/mnt/u08/SVPRD1B/oradata/archived_charge_data02.dbf
/mnt/u08/SVPRD1B/oradata/archived_ne_data01.dbf

24 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/mnt/u02/SVPRD1B/oradata/control01.ctl
/mnt/u03/SVPRD1B/oradata/control02.ctl
/mnt/u04/SVPRD1B/oradata/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/mnt/u05/SVPRD1B/oradata/redo05a.log
/mnt/u06/SVPRD1B/oradata/redo05b.log
/mnt/u05/SVPRD1B/oradata/redo04a.log
/mnt/u06/SVPRD1B/oradata/redo04b.log
/mnt/u05/SVPRD1B/oradata/redo03a.log
/mnt/u06/SVPRD1B/oradata/redo03b.log
/mnt/u05/SVPRD1B/oradata/redo02a.log
/mnt/u06/SVPRD1B/oradata/redo02b.log
/mnt/u05/SVPRD1B/oradata/redo01a.log
/mnt/u06/SVPRD1B/oradata/redo01b.log

10 rows selected.

SQL>

Minimum required parameter in init.ora file.

*.compatible='9.2.0.8.0'
*.control_files='/mnt/u02/SVPRD1B/oradata/control01.ctl','/mnt/u03/SVPRD1B/oradata/control02.ctl','/mnt/u04/SVPRD1B/oradata/control03.ctl'
# Convert file names to allow for different directory structure.

DB_FILE_NAME_CONVERT=
('/u01/SVPRD1B/','/mnt/u01/SVPRD1B/','
/u02/SVPRD1B/','/mnt/u02/SVPRD1B/',
'/u03/SVPRD1B/','/mnt/u03/SVPRD1B/',
'/u04/SVPRD1B/','/mnt/u04/SVPRD1B/',
'/u05/SVPRD1B/','/mnt/u05/SVPRD1B/',
 '/u06/SVPRD1B/','/mnt/u06/SVPRD1B/',
'/u07/SVPRD1B/','/mnt/u07/SVPRD1B/',
'/u08/SVPRD1B/','/mnt/u08/SVPRD1B/',
'/u09/SVPRD1B/','/mnt/u09/SVPRD1B/')

LOG_FILE_NAME_CONVERT=('/u05/SVPRD1B/oradata/','/mnt/u05/SVPRD1B/oradata/','/u06/SVPRD1B/oradata/','/mnt/u06/SVPRD1B/oradata/')

*.db_block_size=8192
*.db_name='DUP'

Save the file

va-ibill01:DUP:/u01/SVPRD1B/app/oracle/product/92/dbs
$ORACLE_SID=DUP; export ORACLE_SID

va-ibill01:DUP:/u01/SVPRD1B/app/oracle/product/92/dbs
$sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Mar 16 21:42:01 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile='/u01/SVPRD1B/app/oracle/product/92/dbs/initDUP.ora';

File created.

SQL>STARTUP FORCE NOMOUNT;


SQL>EXIT

$ORACLE_SID=DUP; export ORACLE_SID

$rman TARGET sys/duck_p6nts@SVPRD1B CATALOG rman/rman@rman AUXILIARY /

RUN
{
  ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE sbt;
  ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE sbt;
  DUPLICATE TARGET DATABASE TO DUP;
}


Open another window and monitor the progress.

va-ibill01:SVPRD1B:/mnt/u02/SVPRD1B/oradata
$export ORACLE_SID=DUP;

va-ibill01:DUP:/mnt/u02/SVPRD1B/oradata
$sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Mar 16 23:53:25 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK   2    3    4    5    6    7  ;

       SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK %_COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
        11          1                      1                    6196962    8038400      77.09

SQL>

Creating Duplicate Database from Rman Using the Backups done at OS level

posted Mar 16, 2011, 12:43 PM by Sachchida Ojha

Creating Duplicate database from Rman using the backups that are done at OS level .

The backup that is used in the  example is a cold backup of the database

Below the procedure with an example:

Assumed database names:

Primary Database SID: PROD
Duplicate Database SID: AUX
Rman Catalog SID: RMAN

1) Perform a cold backup of the primary database

- SQL> Shutdown immediate;

[oracle@test-br data]$ cp /home/oracle/PROD/data/sys01.dbf /home/oracle/PROD/data/backup/sys01.dbf
[oracle@test-br data]$ cp /home/oracle/PROD/data/undotbs.dbf /home/oracle/PROD/data/backup/undotbs.dbf
[oracle@test-br data]$ cp /home/oracle/PROD/data/users_9i.dbf /home/oracle/PROD/data/backup/users_9i.dbf
[oracle@test-br data]$ cp /home/oracle/PROD/data/test01.dbf /home/oracle/PROD/data/backup/test01.dbf


2) Connect to target database and recovery catalog from rman
% Rman target / catalog rman/rman@con_catalog

3) Startup the database
RMAN> startup

4) Catalog the datafile copies
RMAN>catalog datafilecopy '/home/oracle/PROD/data/backup/sys01.dbf';

cataloged datafile copy
datafile copy filename=/home/oracle/PROD/data/backup/sys01.dbf recid=9 stamp=617219529

RMAN> catalog datafilecopy '/home/oracle/PROD/data/backup/undotbs.dbf';

cataloged datafile copy
datafile copy filename=/home/oracle/PROD/data/backup/undotbs.dbf recid=10 stamp=617219556

RMAN> catalog datafilecopy '/home/oracle/PROD/data/backup/users_9i.dbf';

cataloged datafile copy
datafile copy filename=/home/oracle/PROD/data/backup/users_9i.dbf recid=11 stamp=617219574

RMAN> catalog datafilecopy '/home/oracle/PROD/data/backup/test01.dbf';

cataloged datafile copy
datafile copy filename=/home/oracle/PROD/data/backup/test01.dbf recid=12 stamp=617219589

5) Now create a pfile for the duplicate instance

# +----------------------------------------+
# | FILE : initAUX.ora |
# | DATABASE NAME : AUX |
# +----------------------------------------+
# Set the below to location of the clone Duplicate database / name of
# clone database.

audit_file_dest =/oradata/aux/adump
background_dump_dest =/oradata/aux/bdump
core_dump_dest =/oradata/aux/cdump
user_dump_dest =/oradata/aux/udump
db_name ="AUX"
instance_name =AUX

# Set the below to the location of the duplicate clone control file.

control_files ='/home/oracle/aux/c1.ctl'


# Set the below for the from and to location for all data files / redo
# logs to be cloned.

log_file_name_convert=('/home/oracle/PROD/redo/','/home/oracle/aux/')
db_file_name_convert=('/home/oracle/PROD/data/','/home/oracle/aux/')

#Set the below to the same as the production target

undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
db_block_size = < >
compatible = 9.2.0
6) Startup nomount the auxiliary database

$ export ORACLE_SID=aux1
$ sqlplus "/as sysdba"
SQL> Startup nomount

7) Now connect to target,catalog and auxiliary instances from Rman

$ rman target sys/sys@con_tar catalog rman/rman@con_cat auxiliary /

Recovery Manager: Release 9.2.0.7.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: PROD (DBID=4282295901)
connected to recovery catalog database
connected to auxiliary database: aux1 (not mounted)

8) Perform a log switch

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

9) Perform the duplicate

+ RMAN> duplicate target database to aux1;

Creating a Duplicate Database on a New Host using RMAN duplicate feature

posted Mar 16, 2011, 12:32 PM by Sachchida Ojha

Duplicate database from server A to server B (Non ASM)

Assumed database names:

Primary Database SID:       PROD
Duplicate Database SID:    AUX
RMAN Catalog SID:          RMAN

====================================================================================
Steps

1.  Backup the primary database.

2.  Determine how much disk space will be required.

3.  Ensuring you have enough space on your target server.

4.  Making the backup available for the duplicate process.

5.  Creating the init.ora & administration directories for the duplicate database.

6.  Ensuring SQL*NET connections to primary database and RMAN catalog are working.

7.  Prepare RMAN duplicate script.

8.  Execute the RMAN script.

=====================================================================================

1. Backup of the primary database.

Host A (Target)

Prior to creating a duplicate database you must have a backup of the target database. In this example we assume the backup media is disk. Find appropriate disk space on host A and take a full backup including archive logs and current controlfile (Figure 1a). If you are planning to duplicate a subset of the database refer to (Figure 1b) this illustrates the RMAN command to backing up certain tablespaces.

[oracle@linux] export ORACLE_SID=PROD

[oracle@linux] rman target=/ catalog=rman/rman@RMAN

RMAN> run {
        allocate channel d1 type disk;
        backup format '/backups/PROD/df_t%t_s%s_p%p' database;
        sql 'alter system archive log current';
        backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
        release channel d1;
      }

       Figure 1a - This command will perform a full database backup including archivelogs and the current controlfile.


[oracle@linux] export ORACLE_SID=PROD

[oracle@linux] rman target=/ catalog=rman/rman@RMAN

RMAN> run {
        allocate channel d1 type disk;
        backup format '/backups/PROD/df_t%t_s%s_p%p'
               tablespace SYSTEM, SYSAUX, UNDO, USERS;
        sql 'alter system archive log current';
        backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
        release channel d1;
     }

    Figure 1b- This command will perform a tablespace backup ( SYSTEM, SYSAUX, UNDO & USERS) including archive logs and the current controlfile.

2. Determine how much disk space will be required.

Host A(Target) -

After deciding what you will be duplicating, calculate the entire space this will require on the new host. The full database calculation (Figure 2a) will calculate the entire space required whereas (figure 2b) allows you to enter the tablespace names in order to calculate the space required. Figure 2c provides a sample output.

Note: sql valid for version 10g only.
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;
     Figure 2a - 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
     where tablespace_name in('SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS')) DF,
    (select sum(b.bytes) TOTAL from v$log b) LOG,
    (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
     Figure 2b - Calculate space for list of tablespaces within primary database.

DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
             900              150             20.34375    1070.34375
     Figure 2c - Sample output of space calculation.

3. Ensuring you have enough space on your target server.

Host B (Aux)

Prior to starting the duplicate process you must ensure you have enough disk space within file system to hold the database.

[oracle@linux] df -kh
Filesystem         Size     Used     Avail     Use%     Mounted on
/dev/vg01/root     9.9G     2.8G      6.6G      30%              /
/dev/sda1          145M      16M      122M      12%          /boot
none               999M        0      999M       0%       /dev/shm
/dev/vg01/tmp      2.0G     383M      1.5G      20%           /tmp
/dev/vg01/u01       20G      12G      7.0G      62%           /u01
/dev/vg01/u02      4.9G    1010M      3.6G      22%           /u02
/dev/vg01/backups  5.9G     1.2G      4.4G      22%       /backups
/dev/vg01/oradata   15G      13G      2.0G      87%       /oradata
     Figure 3a - Sample output showing the space available on your filesystem.

Compare the results received from this query with the output from 2a or 2b and ensure you have enough diskspace for your duplicate database.


4. Making the backup available for the duplicate process.

If your backup resides on disk you will need to copy this back up from host A to host B. Ensure you place it in the same directory as where it was created. In the example below (figure 4a) the backup piece resides in ‘/backups/PROD’ these files need to be copied into the same directory on host B.

Furthermore also ensure than all archive log backups have also been moved and any archive logs in the archive log directory that may be require for the recovery. The archive logs required will depend on the point in time of the recovery.

RMAN> list backup;

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
22 Full 529M DISK 00:00:51 2006/05/16 11:12:54
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111203
Piece Name: /backups/PROD/df_t590584323_s23_p1
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/system01.dbf
2 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/undotbs01.dbf
3 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/sysaux01.dbf
4 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/users01.dbf


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
24 48M DISK 00:00:06 2006/05/16 11:13:07
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111301
Piece Name: /backups/PROD/al_t590584381_s25_p1

List of Archived Logs in backup set 24
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 78 1344750 2006/05/15 04:55:34 1353208 2006/05/15 10:00:19
1 79 1353208 2006/05/15 10:00:19 1353213 2006/05/15 10:00:20
1 80 1353213 2006/05/15 10:00:20 1371564 2006/05/15 22:00:11
1 81 1371564 2006/05/15 22:00:11 1373291 2006/05/15 22:00:59
1 82 1373291 2006/05/15 22:00:59 1381066 2006/05/16 03:00:05
1 83 1381066 2006/05/16 03:00:05 1390685 2006/05/16 09:03:00
1 84 1390685 2006/05/16 09:03:00 1393870 2006/05/16 11:13:00
1 85 1393870 2006/05/16 11:13:00 1393929 2006/05/16 11:13:00
     Figure 4a - A list backup showing the backup pieces that need to be copied across Host B

5. Creating the init.ora & administration directories for the duplicate database.

Host B(AUX)

Create the pfile [initAUX.ora] parameter file in the $ORACLE_HOME/dbs directory for the auxiliary database. An example is shown below (figure 5a) with bare minimum settings. Note you may require to set parameters which are the same as your production database (refer to you primary init.ora)

# +----------------------------------------+
# | FILE : initAUX.ora                     |
# | DATABASE NAME : AUX                    |
# +----------------------------------------+
# Set the below to location of the clone Duplicate database / name of
# clone database.

audit_file_dest =/oradata/AUX/adump
background_dump_dest =/oradata/AUX/bdump
core_dump_dest =/oradata/AUX/cdump
user_dump_dest =/oradata/AUX/udump
db_name ="AUX"
instance_name =AUX

cluster_database=false (in case the production is a rac environment)



# Set the below to the location of the duplicate clone control file.

control_files =('/oradata/AUX/control01.ctl','/oradata/AUX/control02.ctl','/oradata/AUX/control03.ctl')



# Set the below for the from and to location for all data files / redo
# logs to be cloned.

db_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
log_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
#Set the below to the same as the production target

undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
db_block_size = 8192
compatible = 10.2.0.1.0
     Figure 5a - Sample initAUX.ora with minimal settings

Following the creation of the initAUX.ora startup nomount the auxiliary instance.

[oracle@linux]export ORACLE_SID=AUX

[oracle@linux] sqlplus '/as sysdba'

SQLPLUS> startup nomount;
Figure 5b - startup nomount the AUX instance.


6. Ensuring SQL*NET connections to primary database and RMAN catalog are working.

Host B(AUX)

Ensure the production target database is open or at least mounted. If using a catalog database this must also be open. Test your SQL*NET connections: From Host B you must be able to connect to the primary DB as sysdba and make an rman connection to RMAN. Perform a basic test using sqlplus. When you have successfully connected exit sqlplus and move onto the next step.

[oracle@linux]% sqlplus ‘sys/oracle@PROD as sysdba’

[oracle@linux]% sqlplus rman/rman@RMAN   (not mandatory)
     Figure 6a - SQL*NET connections

7. Prepare RMAN duplicate script.

In a working directory on Host B create an RMAN script file duplicate.rcv. The example below (figure 7a) shows the command for a complete duplicate (figure 7b) skips the tablespaces which are not required in the duplicate and (figure 7b) provide the syntax required for a point in time duplicate.

run {
  allocate auxiliary channel C1 device type disk;
  duplicate target database to AUX;
}
     Figure 7a - Sample duplicate command.

run {
  allocate auxiliary channel C1 device type disk;
  duplicate target database to AUX skip tablespace ABC, XYZ;
}
     Figure 7b- Sample duplicate script omitting optional tablespaces;

run {
  set until time "to_date('Jan 01 2000 12:00:00','Mon DD YYYY HH24:MI:SS')";
  allocate auxiliary channel C1 device type disk;
  duplicate target database to AUX;
}
      Figure 7c- Sample duplicate script to a point in time.

8. Execute the RMAN script.

Start RMAN, connect to the production target, the catalog instance and also the auxiliary clone. Run the RMAN duplicate script as shown below. Before doing this ensure that the Oracle SID environment variable is set to the duplicate clone database.

[oracle@linux] export ORACLE_SID=AUX

[oracle@linux] rman target sys/sys@PROD catalog rman/rman@rman auxiliary /

RMAN> @duplicate.rcv

1-10 of 15