Create Duplicate Database Using RMAN
7 min readSource Database Name is PRODDB
Target (duplicate) Database name is MYDB
Source Host Name : server1.localdomain
Target Host Name : server2.localdomain
Put Database in archivelog mode.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Current log sequence 6
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 805309328 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
Step 1: Configure listener.ora and tnsnames.ora files
[oracle@server1 admin]$ cat tnsnames.ora
PRODDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
)
)
mydb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb)
)
)
[oracle@server1 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.localdomain)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = proddb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =proddb)
)
)
[oracle@server1 admin]$ lsnrctl start
[oracle@server1 admin]$ tnsping proddb
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-OCT-2018 13:53:17
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dupdb)))
OK (20 msec)
[oracle@server1 admin]$ tnsping mydb
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-OCT-2018 13:53:17
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dupdb)))
OK (20 msec)
Step 2: Create pfile for target database from Source database
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initmydb.ora' from spfile;
File created.
Step 3: Create password file
[oracle@server1 dbs]$ orapwd file=orapwmydb password=oracle entries=5
Step 4: SCP passwordfile & pfile to target server
[oracle@proddb dbs]$ scp initmydb.ora orapwmydb oracle@server:/u01/app/oracle/product/11.2.0/db_1/dbs
WORK IN TARGET SERVER
Step 5: Below is the parameter file for target database (duplicate) . Here log_file_name_convert , db_file_name_convert and control file location is important.
[oracle@server2 Desktop]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@server2 dbs]$ cat initmydb.ora
mydb.__db_cache_size=452984832
mydb.__java_pool_size=16777216
mydb.__large_pool_size=16777216
mydb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
mydb.__pga_aggregate_target=520093696
mydb.__sga_target=754974720
mydb.__shared_io_pool_size=0
mydb.__shared_pool_size=251658240
mydb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/mydb/adump'
*.audit_trail='db'
*.compatible='11.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/mydb/control01.ctl','/u01/app/oracle/flash_recovery_area/mydb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='mydb'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mydbXDB)'
*.memory_target=1266679808
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/proddb','/u01/app/oracle/oradata/mydb'
*.log_file_name_convert='/u01/app/oracle/oradata/proddb','/u01/app/oracle/oradata/mydb'
Create the necessary directory structure as mention in initdup.ora:
[oracle@server2 dbs]$ mkdir -p /u01/app/oracle/admin/mydb/adump
[oracle@server2 dbs]$ mkdir -p /u01/app/oracle/oradata/mydb
[oracle@server2 dbs]$ mkdir -p //u01/app/oracle/flash_recovery_area/mydb
Step 6: Configure Listener.ora and tnsnames.ora files
[oracle@server2 admin]$ cat listener.ora
PRODDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
)
)
mydb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb)
)
)
[oracle@server1 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = proddb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =proddb)
)
)
[oracle@server2 admin]$ lsnrctl start
[oracle@ server2 admin]$ tnsping mydb
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-OCT-2018 13:53:17
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydb)))
OK (20 msec)
[oracle@ server2 admin]$ tnsping proddb
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-OCT-2018 13:53:17
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydb)))
OK (20 msec)
[oracle@ server2 ~]$ export ORACLE_SID=mydb
[oracle@ server2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1/
[oracle@ server 2 ~]$ sqlplus
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initmydb.ora
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL>
Step 8: Connect auxiliary database
[oracle@ server2 ~]$ rman target sys/oracle@proddb auxiliary sys/oracle@mydb
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Oct 5 18:11:12 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODDB (DBID=728682106)
connected to auxiliary database: MYDB (not mounted)
Step 9: Create duplicate database.
RMAN> duplicate target database to 'MYDB' from active database NOFILENAMECHECK;
Starting Duplicate Db at 5-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of Memory Script:
{
set until scn 470310;
set newname for datafile 1 to
“/home/oracle/oracle/product/11.2.0/oradata/mydb/system01.dbf”;
set newname for datafile 2 to
“/home/oracle/oracle/product/11.2.0/oradata/mydb/undotbs01.dbf”;
set newname for datafile 3 to
“/home/oracle/oracle/product/11.2.0/oradata/mydb/sysaux01.dbf”;
set newname for datafile 4 to
“/home/oracle/oracle/product/11.2.0/oradata/mydb/users01.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
Starting restore at 25-MAY-10
using channel ORA_AUX_DISK_1
skipping datafile 1; already restored to file /home/oracle/oracle/product/11.2.0/oradata/mydb/system01.dbf
skipping datafile 2; already restored to file /home/oracle/oracle/product/11.2.0/oradata/mydb/undotbs01.dbf
skipping datafile 3; already restored to file /home/oracle/oracle/product/11.2.0/oradata/mydb/sysaux01.dbf
skipping datafile 4; already restored to file /home/oracle/oracle/product/11.2.0/oradata/mydb/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 5-OCT-18
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/11.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/11.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/11.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/11.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956582 filename=/home/oracle/oracle/product/11.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956582 filename=/home/oracle/oracle/product/11.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956582 filename=/home/oracle/oracle/product/11.2.0/oradata/mydb/users01.dbf
contents of Memory Script:
{
set until scn 470310;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 5-OCT-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
starting media recovery
archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/11.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc
archive log filename=/home/oracle/oracle/product/11.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 5-OCT-18
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 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/11.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/11.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/11.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/11.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
set newname for tempfile 1 to
“/home/oracle/oracle/product/11.2.0/oradata/mydb/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/home/oracle/oracle/product/11.2.0/oradata/mydb/undotbs01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/11.2.0/oradata/mydb/sysaux01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/11.2.0/oradata/mydb/users01.dbf”;
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /home/oracle/oracle/product/11.2.0/oradata/mydb/temp01.dbf in control file
cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/11.2.0/oradata/mydb/undotbs01.dbf recid=1 stamp=719956593
cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/11.2.0/oradata/mydb/sysaux01.dbf recid=2 stamp=719956593
cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/11.2.0/oradata/mydb/users01.dbf recid=3 stamp=719956593
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956593 filename=/home/oracle/oracle/product/11.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956593 filename=/home/oracle/oracle/product/11.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956593 filename=/home/oracle/oracle/product/11.2.0/oradata/mydb/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 5-OCT-18
RMAN>
Step 10: Duplicate database is successfully created.
SQL> select status from v$instance;
STATUS
————
OPEN
SQL> select name from V$database;
NAME
———
MYDB
