Source Database - TESTDB
Source Host - oel6
Destination Database - TESTDBCP
Destination Host - oel6
Database Version:- 11.2.0.4
Step 1:- Take the Level 0 or full database backup of source database
[oracle@oel6 backup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 18 19:55:23 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (DBID=2675922698)
RMAN> run
{
allocate channel c1 device type disk format '/u01/app/backup/Level0_%d_%s_%p.bak';
backup as compressed backupset incremental level 0 database plus archivelog;
}2> 3> 4> 5>
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=25 device type=DISK
Starting backup at 18-OCT-15
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=28 STAMP=893447911
input archived log thread=1 sequence=31 RECID=29 STAMP=893454553
input archived log thread=1 sequence=32 RECID=30 STAMP=893454728
input archived log thread=1 sequence=33 RECID=31 STAMP=893454799
channel c1: starting piece 1 at 18-OCT-15
channel c1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/backup/Level0_TESTDB_46_1.bak tag=TAG20151018T215320 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
Finished backup at 18-OCT-15
Starting backup at 18-OCT-15
channel c1: starting compressed incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/testdb/datafile/system.256.892594189
input datafile file number=00002 name=+DATA/testdb/datafile/sysaux.257.892594189
input datafile file number=00003 name=+DATA/testdb/datafile/undotbs1.258.892594189
input datafile file number=00004 name=+DATA/testdb/datafile/users.259.892594189
channel c1: starting piece 1 at 18-OCT-15
channel c1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/backup/Level0_TESTDB_47_1.bak tag=TAG20151018T215330 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:25
channel c1: starting compressed incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 18-OCT-15
channel c1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/backup/Level0_TESTDB_48_1.bak tag=TAG20151018T215330 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-OCT-15
Starting backup at 18-OCT-15
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=34 RECID=32 STAMP=893454913
channel c1: starting piece 1 at 18-OCT-15
channel c1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/backup/Level0_TESTDB_49_1.bak tag=TAG20151018T215514 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-OCT-15
released channel: c1
RMAN>
Step 2:- Create pfile, spfile and password file for destination database
[oracle@oel6 ~]$ . oraenv
ORACLE_SID = [testdb] ? testdbcp
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel6 ~]$ cd $ORACLE_HOME/dbs
[oracle@oel6 dbs]$ cp inittestdb.ora inittestdbcp.ora
[oracle@oel6 dbs]$ cp orapwtestdb orapwtestdbcp
Modify the inittestdbcp.ora file contents from testdb to testdbcp
[oracle@oel6 dbs]$ cat inittestdbcp.ora
testdbcp.__db_cache_size=268435456
testdbcp.__java_pool_size=4194304
testdbcp.__large_pool_size=71303168
testdbcp.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
testdbcp.__pga_aggregate_target=159383552
testdbcp.__sga_target=473956352
testdbcp.__shared_io_pool_size=0
testdbcp.__shared_pool_size=121634816
testdbcp.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/testdbcp/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/testdbcp/controlfile/Current.270.893371645','+DATA/testdbcp/controlfile/Current.271.893371645'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_create_online_log_dest_2='+FRA'
*.db_domain=''
*.db_name='testdbcp'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=5368709120
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbcpXDB)'
*.open_cursors=300
*.pga_aggregate_target=157286400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=471859200
*.undo_tablespace='UNDOTBS1'
[oracle@oel6 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 18 20:02:01 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oel6 dbs]$
Note:- "cluster_database" should be set to "false" in case of RAC.
Step 3:- Start the database in nomount state
[oracle@oel6 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 18 20:02:01 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2254344 bytes
Variable Size 197134840 bytes
Database Buffers 268435456 bytes
Redo Buffers 4005888 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oel6 dbs]$
Note:- For RAC, the instance should be in nomount state on one node and shutdown on rest of the nodes
Step 4:- Run duplicate command
[oracle@oel6 dbs]$ rman target sys/<sys_passwd>@testdb
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 18 21:56:00 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (DBID=2675922698)
RMAN> connect auxiliary /
connected to auxiliary database: TESTDBCP (not mounted)
RMAN> duplicate target database to testdbcp;
Starting Duplicate Db at 18-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/testdbcp/controlfile/current.272.893454999'', ''+DATA/testdbcp/controlfile/current.273.893454999'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''TESTDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''TESTDBCP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/testdbcp/controlfile/current.272.893454999'', ''+DATA/testdbcp/controlfile/current.273.893454999'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''TESTDBCP'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 471830528 bytes
Fixed Size 2254344 bytes
Variable Size 197134840 bytes
Database Buffers 268435456 bytes
Redo Buffers 4005888 bytes
Starting restore at 18-OCT-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/backup/Level0_TESTDB_48_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/app/backup/Level0_TESTDB_48_1.bak tag=TAG20151018T215330
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA/testdbcp/controlfile/current.272.893454999
output file name=+DATA/testdbcp/controlfile/current.273.893454999
Finished restore at 18-OCT-15
database mounted
contents of Memory Script:
{
set until scn 964099;
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;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 18-OCT-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/backup/Level0_TESTDB_47_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/app/backup/Level0_TESTDB_47_1.bak tag=TAG20151018T215330
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:16
Finished restore at 18-OCT-15
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=893455352 file name=+DATA/testdbcp/datafile/system.274.893455095
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=893455354 file name=+DATA/testdbcp/datafile/sysaux.275.893455095
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=893455355 file name=+DATA/testdbcp/datafile/undotbs1.276.893455097
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=893455358 file name=+DATA/testdbcp/datafile/users.277.893455097
contents of Memory Script:
{
set until scn 964099;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 18-OCT-15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 34 is already on disk as file +FRA/testdb/archivelog/2015_10_18/thread_1_seq_34.273.893454913
archived log file name=+FRA/testdb/archivelog/2015_10_18/thread_1_seq_34.273.893454913 thread=1 sequence=34
media recovery complete, elapsed time: 00:00:04
Finished recover at 18-OCT-15
Oracle instance started
Total System Global Area 471830528 bytes
Fixed Size 2254344 bytes
Variable Size 197134840 bytes
Database Buffers 268435456 bytes
Redo Buffers 4005888 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''TESTDBCP'' 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
sql statement: alter system set db_name = ''TESTDBCP'' 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 471830528 bytes
Fixed Size 2254344 bytes
Variable Size 197134840 bytes
Database Buffers 268435456 bytes
Redo Buffers 4005888 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDBCP" RESETLOGS ARCHIVELOG
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
'+DATA/testdbcp/datafile/system.274.893455095'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/testdbcp/datafile/sysaux.275.893455095",
"+DATA/testdbcp/datafile/undotbs1.276.893455097",
"+DATA/testdbcp/datafile/users.277.893455097";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/testdbcp/datafile/sysaux.275.893455095 RECID=1 STAMP=893455468
cataloged datafile copy
datafile copy file name=+DATA/testdbcp/datafile/undotbs1.276.893455097 RECID=2 STAMP=893455471
cataloged datafile copy
datafile copy file name=+DATA/testdbcp/datafile/users.277.893455097 RECID=3 STAMP=893455472
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=893455468 file name=+DATA/testdbcp/datafile/sysaux.275.893455095
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=893455471 file name=+DATA/testdbcp/datafile/undotbs1.276.893455097
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=893455472 file name=+DATA/testdbcp/datafile/users.277.893455097
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 18-OCT-15
RMAN>
Destination database is created in Read Write mode.
[oracle@oel6 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 18 20:19:34 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select name , open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TESTDBCP READ WRITE
Step 5:- Start the database instances
[oracle@oel6 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 18 20:19:34 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size
2254344 bytes
Variable Size
197134840 bytes
Database Buffers
268435456 bytes
Redo Buffers
4005888 bytes
Database mounted.
Database opened.
SQL>
For RAC, set "cluster_database" to "true" and start all the instances of the database.
Step 6:- Add the tns entry for destination database to tnsnames.ora file
[oracle@oel6 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)
TESTDBCP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdbcp)
)
)
Step 7:- Reload the listener
[oracle@oel6 admin]$ . oraenv
ORACLE_SID = [testdbcp] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel6 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-OCT-2015 20:55:02
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@oel6 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-OCT-2015 20:55:11
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 18-OCT-2015 19:44:01
Uptime 0 days 1 hr. 11 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel6/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbcp" has 1 instance(s).
Instance "testdbcp", status READY, has 1 handler(s) for this service...
Service "testdbcpXDB" has 1 instance(s).
Instance "testdbcp", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oel6 admin]$
