Create duplicate database using rman

You want to use RMAN to create a duplicate database on the same server by using RMAN backups.

Source database name is MYTEST

Target (duplicate) database name is MYDB

This is the simple steps to create a duplicate database

You can read from here http://mohamedazar.wordpress.com/2010/05/25/create-duplicate-database/

Step 1: Configure Listener.ora and tnsnames.ora files

tnsnames.ora file

MYTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest)
)
)

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb)
)
)

listener.ora file

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = mytest)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mytest)
)
(SID_DESC =
(SID_NAME = mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mydb)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)

Step 2: create pfile for target database from source database

SQL> create pfile from spfile;

File created.

Step 3: modified parameter file for target database (duplicate) here log_file_name_convert and db_file_name_convert and control file location is important, further your choice.

*.audit_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/adump’
*.compatible=’10.2.0.1.0′
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’mydb’
*.control_files=’/home/oracle/oracle/product/10.2.0/oradata/mydb/control01.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/mydb/control02.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/mydb/control03.ctl’
*.db_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,'/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.log_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,'/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.db_recovery_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=mydbXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=285212672
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

Step 4: Create password file

[oracle@localhost ~]$orapwd file=orapwmydb password=oracle entries=5

Step 5: start your target database nomount stage

[oracle@localhost ~]$ export ORACLE_SID=mydb
[oracle@localhost ~]$ rlwrap sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Tue May 25 18:22:09 2010

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

Enter user-name: sys/oracle@mydb as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> startup nomount pfile=/home/oracle/oracle/product/10.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> exit

Step 6: From source database

SQL> alter system switch logfile;

System altered.

SQL>

Step 7: Backup database and archivelog Source database

[oracle@localhost root]$ rlwrap rman target sys/azar@mytest

Recovery Manager: Release 10.2.0.1.0 – Production on Tue May 25 19:56:10 2010

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

connected to target database: MYTEST (DBID=2419076664)

RMAN> backup database plus archivelog;

Starting backup at 25-MAY-10
current log archived

input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/sysaux01.dbf
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAY-10
channel ORA_DISK_1: finished piece 1 at 25-MAY-10

(skipped)

Starting Control File and SPFILE Autobackup at 25-MAY-10
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_25/o1_mf_s_719956316_5zr01f85_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAY-10

Step 8: Connect auxiliary database

RMAN> connect auxiliary sys/oracle@mydb;

connected to auxiliary database: MYDB (not mounted)

Step 9: Create duplicate database.

RMAN> duplicate target database to mydb;

Starting Duplicate Db at 25-MAY-10
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/10.2.0/oradata/mydb/system01.dbf”;
set newname for datafile 2 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
set newname for datafile 3 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
set newname for datafile 4 to
“/home/oracle/oracle/product/10.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/10.2.0/oradata/mydb/system01.dbf
skipping datafile 2; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
skipping datafile 3; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
skipping datafile 4; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 25-MAY-10
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/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.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/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956582 filename=/home/oracle/oracle/product/10.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 25-MAY-10
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/10.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/10.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 25-MAY-10

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/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1

contents of Memory Script:
{
set newname for tempfile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.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/10.2.0/oradata/mydb/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf recid=1 stamp=719956593

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf recid=2 stamp=719956593

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.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/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

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

database opened
Finished Duplicate Db at 25-MAY-10

RMAN>

Step 10: My duplicate database succesfully created.

Enter user-name: sys/oracle@mydb as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select status from v$instance;

STATUS
————
OPEN

SQL> select name from V$database;

NAME
———
MYDB

SQL>

Views: 2155

Comment

You need to be a member of Oracle Community to add comments!

Join Oracle Community

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins

Badge

Loading…

Blog Posts

Packt Publishing announces "Advanced Oracle PL/SQL Developer's Guide"

My fondness for database development is flaring up again. Gladly I would like to share the announcement of my upcoming book "Advanced Oracle PL/SQL Developer's Guide - Second Edition" by Packt Publishing. The area of the focus is same i.e. Oracle PL/SQL but this version is largely revamped to whisk with Oracle Database 12c features and applications. While the first edition will always remain close to my heart, the second edition is a sheer investment of my experience attained so…

Continue

Posted by Saurabh K. Gupta on July 27, 2015 at 9:41pm — 1 Comment

What Benefits Does an MCSE Certification Carry?

MCSE certifications are the most popular certifications around the world. And, their popularity isn't without reason. In the following post I have highlighted the benefits that you can derive from an MCSE certification, so as to help you gauge how useful it can be for your career. Read on to know.…

Continue

Posted by Michael Warne on May 28, 2015 at 1:32am

Default Login Form customize

Dear All, I want to customize oracle 10g default login page. How can I do this. I attached the login page for better understanding.

Posted by Shakaouth Shanto on May 19, 2015 at 10:00pm

Live SAP Hybris Online Training Classes at VirtualNuggets

Web URL      :  http://www.virtualnuggets.com/sap-hybris.html

Email              :  info@virtualnuggets(dot)com

Contact           :  +1-7076668949(USA)         +91-8885560202(India) 

 

Live SAP Hybris Online Training Classes at VirtualNuggets

 

SAP Hybris is a standout amongst the most widely recognized e-commerce platforms       on the Internet…

Continue

Posted by Vasu Buddi on May 12, 2015 at 11:14pm

© 2015   Created by Steve Karam.

Badges  |  Report an Issue  |  Terms of Service