Cloning A Database On The Same Server Using Rman Duplicate From Active Database

To clone a database using Rman we used to require an existing Rman backup,
on 11g we can clone databases using the “from active” database option.

In this case we do not require an existing backup, the active datafiles will be used as
the source for the clone.

In order to clone with the source database open it must be on archivelog mode.
Otherwise we can make the clone mounting the source database, as shown in
this example.




These are the steps required to complete the clone:

Configure The Network:

Create A Password File For The New Database:

Create An Init.Ora For The New Database:

Create The Admin Directory For The New Database:

Shutdown And Startup Mount The Source Database:

Startup Nomount The New Database:

Connect To The Target (Source) And Auxiliary (New Clone) Databases Using RMAN:

Execute The Duplicate Command:

Remove The Old Pfile:

Check The New Database:




1>>

Configure The Network
We need to configure the network so that there is connectivity to the target
(source) database and to the database we want to create (the clone)
Listener file:
# listener.ora Network Configuration File:
/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.
ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = redpanda)
(ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
(SID_NAME = redpanda)
)
(SID_DESC =
(GLOBAL_DBNAME = rmancat)
(ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
(SID_NAME = rmancat)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = redpanda))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = rmancat))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx1)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /oracle/app



Tnsnames file:
redpanda =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = redpanda)
)
)
rmancat =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rmancat)
)
)

****************************************************

2>>>

Create A Password File For The New Database:

[oracle@lnx1 dbs]$ orapwd file=orapwredpanda password=oracle



**********

3>>>


Create An Init.Ora For The New Database
Create the pfile using create pfile from spfile from the source database, then
edit it changing all occurrences of old database name to new name
*.audit_file_dest='/oracle/app/admin/redpanda/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
#*.control_files='’
*.db_block_size=8192
*.db_create_file_dest='/oracle/app/oradata'
*.db_domain=''
*.db_name='redpanda'
*.db_recovery_file_dest='/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=redpandaXDB)'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

************************************************


4>>


Create The Admin Directory For The New Database:

mkdir –p /oracle/app/admin/redpanda/adump


Shutdown And Startup Mount The Source Database:

This is required if the source database is in no archivelog mode.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount
ORACLE instance started.



Startup Nomount The New Database:::

SQL> startup nomount
ORACLE instance started.
Connect To The Target (Source) And Auxiliary (New Clone)
Databases Using Rman


[oracle@lnx1 dbs]$ Rman target sys/oracle@rmancat auxiliary
sys/oracle@redpanda

Recovery Manager: Release 11.2.0.1.0 - Production on Sat
Mar 20 17:00:45 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to target database: RMANCAT (DBID=3799896238, not
open)
connected to auxiliary database: REDPANDA (not mounted)




Execute The Duplicate Command :::::::::



RMAN> DUPLICATE TARGET DATABASE TO REDPANDA FROM ACTIVE
DATABASE;


Starting Duplicate Db at 20-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv87_.ctl'
',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv8j_.ctl'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''RMANCAT'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =''REDPANDA'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format
'/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl';
restore clone controlfile to
'/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9rs
v97_.ctl' from
'/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl';
sql clone "alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl'
',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv97_.ctl'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv87_.ctl'',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv8j_.ctl'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''RMANCAT'' comment=
''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''REDPANDA''
comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
Starting backup at 20-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file
name=/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_rmancat.f
tag=TAG20100320T170124 RECID=3 STAMP=714157285
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 20-MAR-10
Starting restore at 20-MAR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20-MAR-10
sql statement: alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl'',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv97_.ctl'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
database mounted
contents of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_system_5t9jpq6z_.
dbf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_
.dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_sysaux_5t9jpqfm_.
dbf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_undotbs1_5t9jpqj4
_.dbf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_users_5t9jpqlz_.d
bf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-MAR-10
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_
.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf
contents of Memory Script:
{
recover
clone database
noredo
delete archivelog
;
}
executing Memory Script
Starting recover at 20-MAR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
Finished recover at 20-MAR-10
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''REDPANDA'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
sql statement: alter system set db_name = ''REDPANDA'' comment=
''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "REDPANDA"
RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_.d
bf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy
"/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_.dbf
",
"/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bcs_.
dbf",
"/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.dbf
";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to
/oracle/app/oradata/REDPANDA/datafile/o1_mf_temp_%u_.tmp in
control file
cataloged datafile copy
datafile copy file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf RECID=1 STAMP=714157503
cataloged datafile copy
datafile copy file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf RECID=2 STAMP=714157503
cataloged datafile copy
datafile copy file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf RECID=3 STAMP=714157503
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=714157503 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=714157503 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=714157503 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20-MAR-10


*******************************************************

Remove The Old Pfile :

The duplicate database created an spfile that has the new controlfile
names in it, the old pfile, without controlfiles can be removed:


[oracle@lnx1 dbs]$ rm initredpanda.ora
Check The New Database
ORACLE_SID=redpanda
ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1
[oracle@lnx1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 20
17:31:46 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 1
SQL> select name from v$datafile;
NAME
-----------------------------------------------------------
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_
.dbf
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf



/
oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf

SQL> show sga

Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 209718700 bytes
Database Buffers 50331648 bytes
Redo Buffers 2252800 bytes