Tuesday, December 14, 2010

RMAN - Duplicate Database on the same host

RMAN - Duplicate Database on the same host


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

No comments:

Post a Comment