RMAN
provides the DUPLICATE command, which uses the backups of the database to
create the clone database. Files are restored to the target database, after
which an incomplete recovery is performed and the clone database is opened
using RESETLOGS option. All the preceding steps are performed automatically by
RMAN without any intervention from the DBA.
Considerations-
Source
Database Name: orcl
Clone
Database Name: testdb
Steps
to be followed-
Make
sure good backup present for the orcl database
create
pfile with changed value
create
the required directory sturcture
Make
entry in the listener and tns file
start
the db in nomount using the newly created pfile
Connect
to RMAN and using duplicate command create the database
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
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]$
Changes
made-
[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'
db_file_name_convert=('/home/app/oracle/oradata/orcl','/home/app/oracle/oradata/testdb')
log_file_name_convert=('/home/app/oracle/oradata/orcl','/home/app/oracle/oradata/testdb')
Make
entry in the listner.ora
[oracle@localhost
admin]$ cat listener.ora
#
listener.ora Network Configuration File:
/home/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
#
Generated by Oracle configuration tools.
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME =
/home/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = testdb)
(ORACLE_HOME =
/home/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER
=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.130)(PORT = 1521))
)
)
Make
entry in the tnsname.ora file
[oracle@localhost
admin]$ cat tnsnames.ora
#
tnsnames.ora Network Configuration File:
/home/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
#
Generated by Oracle configuration tools.
orcl
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.130)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
testdb
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.130)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)
EXTPROC_CONNECTION_DATA
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY =
EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@localhost
admin]$ export ORACLE_SID=testdb
[oracle@localhost
dbs]$ sqlplus / as sysdba
SQL*Plus:
Release 10.2.0.1.0 - Production on Thu Jun 28 03:13:10 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
SQL>
exit
Take
the database and archivelog backup
[oracle@localhost
dbs]$ rman target /
Recovery
Manager: Release 10.2.0.1.0 - Production on Thu Jun 28 11:04:12 2013
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected
to target database: orcl (DBID=4144628158)
RMAN>
backup database plus archivelog;
[oracle@localhost
dbs]$ export ORACLE_SID=testdb
[oracle@localhost
dbs]$ rman target sys/sys@orcl auxiliary /
Recovery
Manager: Release 10.2.0.1.0 - Production on Thu Jun 28 11:04:12 2013
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected
to target database: orcl (DBID=4144628158)
connected
to auxiliary database: testdb (not mounted)
RMAN>duplicate
target database to testdb;
RMAN>exit
[oracle@localhost
dbs]$ export ORACLE_SID=testdb
[oracle@localhost
dbs]$ sqlplus '/ as sysdba'
SQL*Plus:
Release 10.2.0.1.0 - Production on Thu Jun 28 11:04:12 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
SQL>
select name,open_mode from v$database;
NAME OPEN_MODE
---------
----------
TESTDB READ WRITE
