Oracle AppsDBAs World: Create A Production (Full or Partial) Duplicate On The Same Host

Reference:
-------------
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@ORCL

RMAN> CREATE CATALOG;

Registering the target database

% rman TARGET / CATALOG rman/rman@ORCL

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

posted by Senthil Kumaran @ 10:44 AM

0 Comments: