Cloning a database in Oracle 10g Using RMAN in Windows OS on same system

I found so many links and tutorials to clone a database in Linux but not so much for cloning in windows. So here we will learn how to clone a database on windows platform on the same system. Steps are as follow. My main database is orcl and I will make cloned database by name clone.

1.) Your first step is to check if orcl is in archive log mode or not by login as sysdba and check.

Z:\>set oracle_sid=orcl

Z:\>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 6 14:21:42 2009

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

Enter password:

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

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 155
Next log sequence to archive 157
Current log sequence 157
SQL>
Here my database is in archive log mode. If it is not we can make by command.

SQL> alter database archivelog;

Database altered.

2.) Second step is to take backup of your database using rman. Default backup directory is flash_recovery_area. Commands are like.

Z:\>rman target sys@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 6 14:25:01 2009

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

target database Password:
connected to target database: ORCL (DBID=1214533430)

RMAN> backup database plus archivelog;


Starting backup at 06-JUL-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset.

And it goes on.

3.) Now we have done with preparing our database for cloning next step is to clone the database. Fist we will make password file for new database but keep in mind password for sys user must be same as of orcl database.

Z:\>orapwd file=D:\oracle\product\10.2.0\db_1\database\pwdCLONE.ora password=****

You can find pwdCLONE.ora file in database directory. It will be used as password file for new database.

4.) Next step is to change tnsname.ora and listener.ora for new clon database entries. Like

Edit your listener.ora file and add following lines
(SID_DESC =
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = clone)

to your existing file.
My existing listener.ora file is as.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
)
)

My new listener file will be like

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = clone)
)
)


Next we will add in our tnsname.ora file like

clone =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = htss27.HTSS.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(sid = clone)
)
)

and check using.

Z:\>tnsping clone

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 06-JUL-2009 14:56:39

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

Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = htss27.HTSS.com)(PORT = 1521)) (CONNECT_DATA
OK (30 msec)

i.e. it’s working.

5.) Now we will make directories like clone in admin and sub directories like adump, bdump, cdump, dpdump and udump in it and clone in oradata folder etc. Next we will create initialization parameter file for clone database using command. It will be done in orcl database like

Z:\>sqlplus sys@orcl as sysdba

SQL> create pfile='D:\oracle\product\10.2.0\db_1\database\INITclone.ora' from spfile;

File created.

Modify this pfile to include parameters DB_NAME, CONTROL_FILES, DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT. Like

This is my current INITclone.ora file

orcl.__db_cache_size=192937984
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=79691776
orcl.__streams_pool_size=0

*.audit_file_dest='D:\oracle\product\10.2.0\admin\orcl\adump'

*.audit_trail='DB'

*.background_dump_dest='D:\oracle\product\10.2.0\admin\orcl\bdump'

*.compatible='10.2.0.1.0'

*.control_files='D:\oracle\product\10.2.0\oradata\orcl\control01.ctl','D:\oracle\product\10.2.0\oradata\orcl\control02.ctl','D:\oracle\product\10.2.0\oradata\orcl\control03.ctl'

*.core_dump_dest='D:\oracle\product\10.2.0\admin\orcl\cdump'

*.cursor_sharing='SIMILAR'

*.db_block_size=8192

*.db_domain='world'

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=95420416

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=286261248

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='D:\oracle\product\10.2.0\admin\orcl\udump'

My modified file is like

orcl.__db_cache_size=192937984
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=79691776
orcl.__streams_pool_size=0

*.audit_file_dest='D:\oracle\product\10.2.0\admin\clone\adump'

*.audit_trail='DB'

*.background_dump_dest='D:\oracle\product\10.2.0\admin\clone\bdump'

*.compatible='10.2.0.1.0'

*.control_files='D:\oracle\product\10.2.0\oradata\clone\control01.ctl','D:\oracle\product\10.2.0\oradata\clone\control02.ctl','D:\oracle\product\10.2.0\oradata\clone\control03.ctl'

*.core_dump_dest='D:\oracle\product\10.2.0\admin\clone\cdump'

*.cursor_sharing='SIMILAR'

*.db_block_size=8192

*.db_domain='world'

*.db_file_multiblock_read_count=16

*.db_name='clone'

*.db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=95420416

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=286261248

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='D:\oracle\product\10.2.0\admin\clone\udump'

DB_FILE_NAME_CONVERT=(D:\oracle\product\10.2.0\oradata\orcl,D:\oracle\product\10.2.0\oradata\clone)
log_file_name_convert='D:\oracle\product\10.2.0\oradata\orcl', 'D:\oracle\product\10.2.0\oradata\clone'

this is our clone database initialization parameter file.

6.) Next step is to create and start the windows service using command

Z:\>oradim -new -sid clone -startmode auto -pfile 'D:\oracle\product\10.2.0\db_1\database\INITclone.ORA';
Instance created.

Next to start the database instance
Z:\>set oracle_sid=clone
SQL> startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\INITclone.ora';
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 92275392 bytes
Database Buffers 188743680 bytes
Redo Buffers 7139328 bytes
SQL> create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\INITclone.ora';

File created.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 92275392 bytes
Database Buffers 188743680 bytes
Redo Buffers 7139328 bytes
SQL>

Don’t try to user startup mount because we don’t have control files so it will return error.

7.) Next we will restore our orcl backups to clone database to make a clone.

Z:\>rman target sys@orcl auxiliary sys/a1234@clone

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 6 16:27:11 2009

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

target database Password:
connected to target database: ORCL (DBID=1214533430)
connected to auxiliary database: CLONE (not mounted)

8.) Now command to clone the target database.

RMAN> run {
2> allocate auxiliary channel ch1 type disk;
3> duplicate target database to 'clone';
4> }

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: sid=156 devtype=DISK

Starting Duplicate Db at 06-JUL-09

………………………………………………..
…………………………………………………
………………………………………………….

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=691519285 filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=691519286 filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=691519286 filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\USERS01.DBF

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

database opened
Finished Duplicate Db at 06-JUL-09

You are done with your job.

1 comment:

  1. Ran through this about 10 times was just about to post that it didn't work then realizes a typo...worked a charm thanks for this

    ReplyDelete