**************** 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.