Create A Production (Full or Partial) Duplicate On The Same Host

Applies to:
Oracle Server Enterprise Edition: 9.X,10.X
Goal
How To Create A Production (Full or Partial) Duplicate On The Same Host using RMAN
Solution
Primary DB : ORCL
Clone DB : AUX
Production Database should be archive enabled.
Startup mount;
alter database archivelog;
alter database open;
archive log list;
Recovery catalog for RMAN
Creating the Recovery Catalog Owner
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:
% sqlplus '/ as sysdba'SQL> CREATE USER rman IDENTIFIED BY rman DEFAULT TABLESPACE tools TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tools; SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman;
Creating the Recovery Catalog
% rman catalog rman/rman@ORCLRMAN> CREATE CATALOG;
Registering the target database
% rman TARGET / CATALOG rman/rman@ORCLRMAN> REGISTER DATABASE;
Reference : Note:452529.1
Reference : RMAN: How to Query the RMAN Recovery Catalog ( Note:98342.1 )
Example Source Listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /newpart//product/10.2.0/)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /newpart//product/10.2.0)
(SID_NAME = ORCL)
)
(SID_DESC =
(GLOBAL_DBNAME = AUX)
(ORACLE_HOME = /newpart//product/10.2.0)
(SID_NAME = AUX)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
Example Source tnsnames.ora
AUX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = AUX)(UR=A)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
Note : Make use of netca and netmgr to configure listener and tnsnames
# Find Production Database Files:
SQL> select name from v$datafile;
Create the Auxiliary Database directories needed
cd $ORACLE_HOME/dbs
create parameter file initAUX.ora
db_file_name_convert = ('/old/path1', '/new/path1',
'/old/path2', '/new/path2',
'/old/path3', '/new/path3')
log_file_name_convert = ('/old/path1', '/new/path1',
'/old/path2', '/new/path2',
'/old/path3', '/new/path3')
eg:-
db_name = aux
db_block_size = 8192
compatible = 10.2.0.1.0
remote_login_passwordfile = exclusive
control_files = ('/newpart/oradata/aux/control01.ctl',
'/newpart/oradata/aux/control02.ctl')
db_file_name_convert = ('/newpart/oradata/orcl',
'/newpart/oradata/aux')
log_file_name_convert = ('/newpart/oradata/orcl',
'/newpart/oradata/aux')
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
# create a passwordfile for remote connections as sysdba
% orapwd password= file=orapwAUX
% sqlplus /nolog
SQL> connect / as sysdba
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora
SQL> exit
Start the Duplication
ORACLE_SID=AUX; export ORACLE_SID # ksh
sqlplus /nolog
SQL> connect / as sysdba
Connected to an idle instance
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora
SQL> exit
# Set your SID back to the TARGET for duplication.
> rman trace.log
Recovery Manager: Release 10.2.0.1.0 - Production
Copyright (c) Oracle. All rights reserved.
RMAN> connect target
connected to target database: V10GREL4 (DBID=2510891965)
RMAN>backup database;
RMAN>sql 'alter system switch logfile';
RMAN> connect auxiliary sys/pwd@AUX
connected to auxiliary database: AUX (not mounted)
RMAN> duplicate target database to AUX device type disk;
Once this is done, login to duplicate database with alter database open resetlogs.