Cold
Cloning is one the reliable methods that is done using the Cold Backup. The
drawback of this method is that the database has to be shutdown while taking
the cold backup.
Cosiderations-
Source
Database Name:
orcl
Clone
Database Name:
testdb
Steps
to be followed-
#Create
pfile
#Create
controlfile
#Shutdown
the dataabase
#Copy
the datafile to the new location
#Start
the db in nomount using the newly created pfile
#Execute
the controlfile which is created
#Recover
the database
#Open
the database in resetlogs
Create
the pfile in the default location.
SQL>
show parameter pfile
NAME TYPE
VALUE
------------------------------------
----------- ------------------------------
spfile string
/home/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora
SQL>
create pfile='/home/app/oracle/product/10.2.0/db_1/dbs/inittestdb.ora' from
spfile;
File
created.
Make
the parameter changes
[oracle@localhost
dbs]$ vi inittestdb.ora
orcl.__db_cache_size=201326592
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=71303168
orcl.__streams_pool_size=0
*.audit_file_dest='/home/app/oracle/admin/orcl/adump'
*.background_dump_dest='/home/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/app/oracle/oradata/orcl/control01.ctl','/home/app/oracle/oradata/orcl/control02.ctl','/home/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/home/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/home/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=orclXDB)'
*.job_queue_processes=10
*.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='/home/app/oracle/admin/orcl/udump'
Create
the dump folder
[oracle@localhost
~]$ mkdir /home/app/oracle/admin/testdb
[oracle@localhost
~]$ cd /home/app/oracle/admin/testdb
[oracle@localhost
testdb]$ pwd
/home/app/oracle/admin/testdb
[oracle@localhost
testdb]$ mkdir adump bdump cdump udump
[oracle@localhost
testdb]$ pwd
/home/app/oracle/oradata/testdb
[oracle@localhost
testdb]$ cd /home/app/oracle/flash_recovery_area
[oracle@localhost
flash_recovery_area]$ mkdir testdb
[oracle@localhost
flash_recovery_area]$ cd testdb
[oracle@localhost
testdb]$ pwd
/home/app/oracle/flash_recovery_area/testdb
[oracle@localhost
testdb]$
[oracle@localhost
dbs]$ vi inittestdb.ora
[oracle@localhost
dbs]$ cat inittestdb.ora
testdb.__db_cache_size=201326592
testdb.__java_pool_size=4194304
testdb.__large_pool_size=4194304
testdb.__shared_pool_size=71303168
testdb.__streams_pool_size=0
*.audit_file_dest='/home/app/oracle/admin/testdb/adump'
*.background_dump_dest='/home/app/oracle/admin/testdb/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/app/oracle/oradata/testdb/control01.ctl','/home/app/oracle/oradata/testdb/control02.ctl','/home/app/oracle/oradata/testdb/control03.ctl'
*.core_dump_dest='/home/app/oracle/admin/testdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='testdb'
*.db_recovery_file_dest='/home/app/oracle/flash_recovery_area/testdb/'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=testdbXDB)'
*.job_queue_processes=10
*.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='/home/app/oracle/admin/testdb/udump'
Create
the directory to store the datafile and redolog file.
Check
the datafile and redolog file names
SQL>
select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/app/oracle/oradata/orcl/system01.dbf
/home/app/oracle/oradata/orcl/undotbs01.dbf
/home/app/oracle/oradata/orcl/sysaux01.dbf
/home/app/oracle/oradata/orcl/users01.dbf
/home/app/oracle/oradata/orcl/example01.dbf
SQL>
select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/app/oracle/oradata/orcl/redo03.log
/home/app/oracle/oradata/orcl/redo02.log
/home/app/oracle/oradata/orcl/redo01.log
[oracle@localhost
~]$ cd /home/app/oracle/oradata/
[oracle@localhost
oradata]$ ls
orcl
[oracle@localhost
oradata]$ mkdir testdb
[oracle@localhost
oradata]$ cd testdb
[oracle@localhost
testdb]$ pwd
/home/app/oracle/oradata/testdb
Create
the controlfile script.
SQL>
alter database backup controlfile to trace as '/home/oracle/testdbctl.sql';
Database
altered.
Make
necessary changes in the controlfile script.
[oracle@localhost
dbs]$ cat /home/oracle/testdbctl.sql
CREATE
CONTROLFILE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/app/oracle/oradata/testdb/redo01.log' SIZE 50M,
GROUP 2
'/home/app/oracle/oradata/testdb/redo02.log'
SIZE 50M,
GROUP 3
'/home/app/oracle/oradata/testdb/redo03.log'
SIZE 50M
DATAFILE
'/home/app/oracle/oradata/testdb/system01.dbf',
'/home/app/oracle/oradata/testdb/undotbs01.dbf',
'/home/app/oracle/oradata/testdb/sysaux01.dbf',
'/home/app/oracle/oradata/testdb/users01.dbf',
'/home/app/oracle/oradata/testdb/example01.dbf'
CHARACTER
SET WE8ISO8859P1
;
Shutdown
the database
SQL>
shutdown immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
Copy
the datafiles and redologfile(/home/app/oracle/oradata/orcl/) to the new
directory(/home/app/oracle/oradata/testdb).
[oracle@localhost
orcl]$ pwd
/home/app/oracle/oradata/orcl
[oracle@localhost
orcl]$ cp *.dbf /home/app/oracle/oradata/testdb/
Export
the sid parameter and start the database in nomount state
[oracle@localhost
~]$ export ORACLE_SID=testdb
[oracle@localhost
dbs]$ sqlplus / as sysdba
SQL*Plus:
Release 10.2.0.1.0 - Production on Wed Jun 26 10:20:06 2013
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
Connected
to an idle instance.
SQL>
startup nomount pfile='$ORACLE_HOME/dbs/inittestdb.ora';
ORACLE
instance started.
Total
System Global Area 285212672 bytes
Fixed
Size 1218992 bytes
Variable
Size 79693392 bytes
Database
Buffers 201326592 bytes
Redo
Buffers 2973696 bytes
Execute
the controlfile script which is created
SQL>
@/home/oracle/testdbctl.sql
Control
file created.
Apply
the log file which is at current state in the old database(orcl)
SQL>
select * from v$log where status='CURRENT';
GROUP# THREAD#
SEQUENCE# BYTES MEMBERS ARC STATUS
----------
---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
-------------
---------
3
1 7 52428800 1 NO
CURRENT
502135 05-MAR-13
Recover
the database now and apply the current logfile.
SQL>
recover database using backup controlfile until cancel;
ORA-00279:
change 501827 generated at 20/06/2013 08:37:10 needed for thread 1
ORA-00289:
suggestion :
/home/app/oracle/flash_recovery_area/testdb/SCHOOL/archivelog/2013_20_06/o1_mf_1
_6_%u_.arc
ORA-00280:
change 501827 for thread 1 is in sequence #6
Specify
log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/app/oracle/oradata/orcl/redo03.log
Log
applied.
Media
recovery complete.
Open
the database in reset logs.
SQL>
alter database open resetlogs;
Database
altered.
Check
the database state.
SQL>
select name,open_mode from v$database;
NAME OPEN_MODE
---------
----------
TESTDB READ WRITE
