Clone database using cold backup
1) Create one database ‘PROD’ using dbca
[oracle@oracle10g ~]$ export ORACLE_SID=prod [oracle@oracle10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Fri Jul 5 19:38:02 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> select open_mode,log_mode,name from v$database; OPEN_MODE LOG_MODE NAME ---------- ------------ --------- READ WRITE ARCHIVELOG PROD
2) Create a pfile for ‘CLONE’ database
SQL> create pfile='$ORACLE_HOME/dbs/initclone.ora from spfile; File created.
3) Make the necessary changes in pfile of ‘CLONE’ database as shown below
clone.__db_cache_size=188743680 clone.__java_pool_size=4194304 clone.__large_pool_size=4194304 clone.__shared_pool_size=83886080 clone.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/clone/adump' *.background_dump_dest='/u01/app/oracle/admin/clone/bdump' *.compatible='10.2.0.1.0' *.control_files='/u01/app/oracle/oradata/clone/control01.ctl','/u01/app/oracle/oradata/clone/control02.ctl','/u01/app/oracle/oradata/clone/control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/clone/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='clone' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=cloneXDB)' *.job_queue_processes=10 *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=300 *.pga_aggregate_target=94371840 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=285212672 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/clone/udump'
4) Fire the following command to backup ‘PROD’ database controlfile to trace file.
SQL> alter database backup controlfile to trace;
After firing the above command open the latest trace file generated in udump folder.Copy the following content and paste it in a text file.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/prod/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/prod/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/prod/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/prod/system01.dbf',
'/u01/app/oracle/oradata/prod/undotbs01.dbf',
'/u01/app/oracle/oradata/prod/sysaux01.dbf',
'/u01/app/oracle/oradata/prod/users01.dbf',
'/u01/app/oracle/oradata/prod/example01.dbf'
CHARACTER SET WE8ISO8859P1
;
Change the name of the text file as control.sql.
Open the control.sql file and make the necessary changes
Open the control.sql file and make the necessary changes
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/clone/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/clone/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/clone/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/clone/system01.dbf',
'/u01/app/oracle/oradata/clone/undotbs01.dbf',
'/u01/app/oracle/oradata/clone/sysaux01.dbf',
'/u01/app/oracle/oradata/clone/users01.dbf',
'/u01/app/oracle/oradata/clone/example01.dbf'
CHARACTER SET WE8ISO8859P1
;
5) Shutdown the ‘PROD’ database
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
6) Make necessary directories for clone database
$mkdir /u01/app/oracle/oradata/clone $mkdir /u01/app/oracle/admin/clone $mkdir /u01/app/oracle/admin/clone/adump $mkdir /u01/app/oracle/admin/clone/bdump $mkdir /u01/app/oracle/admin/clone/cdump $mkdir /u01/app/oracle/admin/clone/udump
7) Copy all the datafiles and redolog files from ‘PROD’ database directory to ‘CLONE’ database directory
8) Fire following command to connect to CLONE database
[oracle@oracle10g ~]$ export ORACLE_SID=clone [oracle@oracle10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Fri Jul 5 20:38:58 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
9) Run the control.sql script on clone database
SQL> @/home/oracle/Desktop/control.sql 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 Control file created.
10) Fire the following command to open the database
SQL> alter database open resetlogs; Database altered.
