Thursday, June 5, 2008

RMAN - Duplicate Database on the same host

Print this post

 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=<sys_pwd> 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: