A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.
Let us look the steps involved in cloning a database using the RMAN duplicate command.
Step 1: Backup target database using RMAN
RMAN> run
2> {
3> allocate channel c1 device type disk format '/backups/clnbkp/_%F';
4> backup database plus archivelog;
5> }
Step 2: Configure listener and naming services
For eg: Edit the following in the listener.ora and tnsnames.ora files under $ORACLE_HOME/network/admin directory.
Listener:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=abc123.region.domain.com)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = prod.world)
(ORACLE_HOME = /oracle/app/product/10.2.0)
(SID_NAME = prod)
)
)
Naming service:
clone_prod =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = def123.region.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = clonedb.world)
)
)
Step 3: Check Listener and Naming services configured correctly
$ tnsping clone_prod
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 20-APR-2011 02:48:08
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = def123.region.domain.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = edisipd.world)))
OK (60 msec)
$ lsnrctl reload
$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-APR-2011 02:50:07
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= abc123.region.domain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 15-APR-2011 11:57:28
Uptime 4 days 14 hr. 52 min. 38 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/product/10.2.0/network/admin/listener.ora
Listener Log File /oracle/app/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= abc123.region.domain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "prod.world" has 1 instance(s).
Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
Step 4: Create pfile for clone database from primary database and edit the created pfile
SQL> create pfile=’/oracle/home/pfile/initclonedb.ora’ from spfile;
Edit the create pfile for database name for bdump, udump and cdump directories and all the directory structures as for your clone database
If your directory structures of the databases on the servers is different, then add the following parameters in the pfile created for clone database.
log_file_name_convert = ‘/primary/db/directory/structure/’,’/clone/db/directory/structure/’
db_file_name_convert = ‘/primary/db/directory/structure/’,’/clone/db/directory/structure/’
Step 5: Transfer backup files and created pfile to clone database server.
Transfer the created RMAN database backup file to the clone database server to the same temporary location as of primary server.
Also transfer the created and edited pfile to the $ORACLE_HOME/dbs directory in the clone database server.
Note: You can use ftp, sftp, scp file transfer utilities
Step 6: Configure new instance and create password file in clone database server
C:\> oradim –new –sid clonedb => required only on windows based servers.
$ orapwd file=’$ORACLE_HOME/dbs/pwdclonedb.ora’ password = sys
Note: Password should be same as of primary database
Step 7: Startup clone database in nomount stage
Set the database environment and start the database to nomount and exit using below example
$ . oraenv
ORACLE_SID = [oracle] ? clonedb
$ sqlplus / as sysdba
SQL > startup nomount
SQL> exit
Step 8: Connect RMAN and duplicate the database
$ rman target / auxiliary sys/sys@clone_prod
Note: I have connected RMAN to both primary database and clone database instances from primary database server.
RMAN> duplicate target database to ‘clonedb’;
The duplicate command will clone your primary database and you are done.
Note: In windows based servers, log files will not be created and RMAN seems to hang for a long time. So break the operation and create the logfiles manually using the following
In clone database
SQL> alter database clear logfile group 1;
SQL> alter database clear logfile group 2;
Step 9: Open the cloned database with reset logs option
SQL> alter database open resetlogs;
