Cloning Oracle Database 9i, 10g using RMAN in few easy steps.

**************** Cloning of the Oracle Database ********************
On production
1) Make sure controlfile autobackup is on and take the incremental level 0 backup;
bash-3.00$ rman
Recovery Manager: Release 10.2.0.1.0 – Production on Thu Jul 23 11:08:48 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: ORCL (DBID=1205560727)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO NONE;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/rmanbkp/rman/dmp/%F’;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO ‘%F’; # default
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE ‘SBT_TAPE’ TO 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u02/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f’; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/rmanbkp/rman/dmp/%F’;
RMAN> configure channel device type disk format ‘/rmanbkp/rman/dmp/Lev0_%s%D%M%Y’;
RMAN> backup incremental level 0 database filesperset 20;
After taking the backup connect to sqlplus and switch logfile 2 or 3 times.
Sql> alter system switch logfile;——————3 times
2) Copy listener, spfile, pfile, tnsnames to the test server.
bash-3.00$ scp listener.ora tnsnames.ora sqlnet.ora oracle@10.0.11.31:/u02/app/oracle/10.2.0/network/admin
bash-3.00$ scp orapworcl initorcl.ora spfileorcl.ora oracle@10.0.11.31: /u02/app/oracle/10.2.0/dbs/
3) Copy the rmanbackup from production to test server(same directory structure).
bash-3.00$ scp c-1205560727-20090723-00 Lev0_216723072009 oracle@10.0.11.31:/rmanbkp/rman/dmp/
4) Login with sqlplus and fire the following query on production
bash-3.00$ sqlplus “/ as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jul 23 11:44:43 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$datafile;
NAME
——————————————————————————–
/u02/app/oracle/oradata/orcl/system01.dbf
/u02/app/oracle/oradata/orcl/undotbs01.dbf
/u02/app/oracle/oradata/orcl/sysaux01.dbf
/u02/app/oracle/oradata/orcl/users01.dbf
/u02/app/oracle/oradata/orcl/example01.dbf
/u02/app/oracle/oradata/orcl/swx01.dbf
/u02/app/oracle/oradata/orcl/swx02.dbf
/u02/app/oracle/oradata/orcl/swx03.dbf
/u03/app/oracle/oradata/orcl/users02.dbf
9 rows selected.
SQL> select name from v$controlfile;
NAME
——————————————————————————–
/u02/app/oracle/oradata/orcl/control01.ctl
/u04/app/oracle/oradata/orcl/control02.ctl
/u05/app/oracle/oradata/orcl/control03.ctl
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
/u02/app/oracle/oradata/orcl/redo03.log
/u02/app/oracle/oradata/orcl/redo02.log
/u02/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/redo01b.log
/u03/app/oracle/oradata/orcl/redo02b.log
/u01/app/oracle/oradata/orcl/redo03b.log
6 rows selected.
ON TEST SERVER
1) First create all the directory structure for datafile,controlfile,redolog file,udump,bdump,cdump on the test server
bash-3.00$ mkdir –p /u02/app/oracle/oradata/orcl/ ——create one by one for all datafile,controlfile and redologfile
2) Go to the dbs folder and open the pfile.and create directory for udump,bdump,cdump
bash-3.00$ cd $ORACLE_HOME/dbs
bash-3.00$ more spfileorcl.ora
)M”
orcl.__db_cache_size=905969664
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=301989888
orcl.__streams_pool_size=0
*.audit_file_dest=’/u02/app/oracle/admin/orcl/adump’
*.background_dump_dest=’/u02/app/oracle/admin/orcl/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u02/app/oracle/oradata/orcl/control01.ctl’,’/u04/app/oracle/oradata/orcl/control02.ctl’,’/u05/app/oracle/oradata/orcl/control03.ctl’
*.core_dump_dest=’/u02/app/oracle/admin/orcl/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’orcl’
*.db_recovery_file_dest=’/u02/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’LOCATION=/u05/app/oracle/archive’
*.log_archive_dest_2=’SERVICE=swxstdby’
*.log_archive_dest_state_2=’DEFER’
*.open_cursors=300
*.pga_aggregate_target=418381824
*.processes=300
*.remote_login_passwordfile=’EXCLUSIVE’
*.remote_os_authent=true
*.sessions=335
*.sga_target=1256194048
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u02/app/oracle/admin/orcl/udump’
bash-3.00$ mkdir -p /u02/app/oracle/admin/orcl/bdump————same for udump,adump and cdump
3) Now open the listener file and change the IP of the server.
4) Now start the database in nomount mode.
bash-3.00$ export ORACLE_SID=test
You have new mail in /var/mail//swx
bash-3.00$ sqlplus “/ as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jul 23 11:55:15 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
5) After starting the database in nomount mode go to rman prompt from different putty terminal.
bash-3.00$ export ORACLE_SID=test
bash-3.00$ rman
Recovery Manager: Release 10.2.0.1.0 – Production on Thu Jul 23 11:57:32 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database (not started)
RMAN> restore controlfile from ‘/rmanbkp/rman/dmp/c-1205560727-20090723-00’;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
Gives some error don’t worry just copy the archive log from production to the same archive log location in the test server and again start recovery. This procedure can be used when you want to clone your database for creating a physical standby database.

Category: RMAN

Tags:

8 comments

  1. Anil if you have different directory structure you need to use db_file_name_convert and log_file_name_convert parameters.

    You have to use the parameters like below

    db_file_name_convert=(,)
    log_file_name_convert=(,)

    1. thankx sir, but if rman backup transfer prod to dev at different directory structure then uses the parameter db_file_name_convert=(,), log_file_name_convert=(,) . is datafile recover and restore.

  2. But i prefer to keep the directory structure as the source database. it is that much simpler. No need to enter datafile names and redo log file names by db_file_name_convert and log_file_name_convert parameters,

  3. Precisely how much time did it take u to write “Cloning Oracle Database 9i,
    10g using RMAN in few easy steps. | Easy Oracle DBA”? It includes a lot of really good details.
    Thanks ,Iesha

    1. Not much time to write but a very long time to learn. This is why I want to make it easy for my peers. So they dont have to go through the steep learning curve I had to 🙂

  4. Hi Andrew,this is very interesting. I had a siliamr efect at a customer’s site recently with 10gR2 where I encountered an object that didi not exist any more .It was possible to describe it but a SELECT COUNT(*) returned an ORA-600.There were no extents in DBA_EXTENTS and it was not possible to DROP it: also ORA-600.Since my customer did not use RMAN we ended up with a full export except this one object and full import into a new DB.Obviousely the Data Dictionary was corrupted.I guess it’s a BUG!Or is it a feature?=;-)Lutz

Leave a Reply

Article by: Shadab Mohammad