ORACLE DBA: Cloning DataBase Tier in different servers for Oracle Apps R12

RAC to RAC DB Tier cloning

1)OS/DB specifications

OS version is Oracle Solaris 10 and database version is 11.1.0.7, Apps - 12.1.3 Cloning from ERPDEV to ERPPAT

Source - ERPDEV on server0091/93

Target - ERPPAT on server0090/92

Prepare the source system database tier for cloning (at ERPDEV)

Log on to the source system as the ORACLE user, and run the following commands:

$ cd [RDBMS ORACLE_HOME]/appsutil/scripts/[CONTEXT_NAME]
$ perl adpreclone.pl dbTier

Configure Primary Node :

2) Copy RDBMS Home from Source to Target

{server0091:oracle:}: /home/oracle/scripts =>cat cp_db.sh

echo "Copy of RDBMS started at `date`"

cd /u01/app/oracle/product/11.0.1

tar cef - ./* | gzip > /erpdev/orashr/backup/db/db.tar.gz

echo "Copy of RDBMS finished at `date`"

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat =>gunzip db.tar.gs

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat =>tar -xvf db.tar

3) Backup ASM_HOME/network/admin directory in target.

4) Create pairsfile.txt File for Primary Target Node

cd /u01/app/oracle/product/11.0.1_erppat/appsutil/clone and create pairsfile.txt

Set the following values :

s_undo_tablespace=[UNDOTBS1 for Initial Node]

s_dbClusterInst=[Total number of Instances in a cluster e.g. 2]

s_db_oh=[Location of new ORACLE_HOME]

Sample of pairsfile.txt

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat/appsutil/clone =>cat pairsfile.txt

s_undo_tablespace=APPS_UNDOTS1

s_dbClusterInst=2

s_db_oh=/u01/app/oracle/product/11.0.1_erppat

5) Create Context File for Target Primary Node

perl adclonectx.pl \

contextfile=[PATH to OLD Source RAC contextfile.xml] \

template=[NEW ORACLE_HOME]/appsutil/template/adxdbctx.tmp \

pairsfile=[NEW ORACLE_HOME]/appsutil/clone/pairsfile.txt \

initialnode

Sample value and inputs --

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin =>perl adclonectx.pl \

> contextfile=/u01/app/oracle/product/11.0.1_erppat/appsutil/ERPDEV1_server0091.xml \

> template=/u01/app/oracle/product/11.0.1_erppat/appsutil/template/adxdbctx.tmp \

> pairsfile=/u01/app/oracle/product/11.0.1_erppat/appsutil/clone/pairsfile.txt \

> initialnode

Redwood Shores, California, USA

Oracle Applications Rapid Clone

Version 12.0.0

adclonectx Version 120.23.12010000.1

Running:

/u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/../jre/bin/java -Xmx600M -classpath /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/../jlib/ojdbc5.jar:/u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/../jlib/xmlparserv2.jar:/u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/../jlib/java oracle.apps.ad.context.CloneContext -e /u01/app/oracle/product/11.0.1_erppat/appsutil/ERPDEV1_server0091.xml -tmpl /u01/app/oracle/product/11.0.1_erppat/appsutil/template/adxdbctx.tmp -pairsfile /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/pairsfile.txt -initialnode

Enter the APPS password : erpdevapps

Log file located at /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/CloneContext_1028139319.log

Provide the values required for creation of the new Database Context file.

Target System Hostname (virtual or normal) [server0090] :

It is recommended that your inputs are validated by the program.

However you might choose not to validate them under following circumstances:

-If cloning a context on source system for a remote system.

-If cloning a context on a machine where the ports are taken and

you do not want to shutdown the services at this point.

-If cloning a context but the database it needs to connect is not available.

Do you want the inputs to be validated (y/n) [n] ? :

Target Instance is RAC (y/n) [y] :

Target System Database Name : ERPPAT

Target System Port Pool [0-99] : 1

Report file located at /u01/app/oracle/product/11.0.1_erppat/appsutil/out/portpool.lst

Complete port information available at /u01/app/oracle/product/11.0.1_erppat/appsutil/out/portpool.lst

Provide information for the initial RAC node:

Host name [server0090] :

Virtual Host name [null] : server0090-vip

Instance number [1] :

Private interconnect name [server0090] : 192.168.114.23

Target System Base Directory : /u01/app/oracle/product/11.0.1_erppat

Oracle OS User [oracle] :

Oracle OS Group [oinstall] :

Target System utl_file_dir Directory List : /usr/tmp

Number of DATA_TOP's on the Target System [2] : 1

Target System DATA_TOP Directory 1 : +ERPPAT

Do you want to preserve the Display [localhost:12.0] (y/n) ? : n

Target System Display [server0090:0.0] :

New context path and file name [/u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml] :

Do you want to overwrite it (y/n) [n] ? : y

Replacing /u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml file.

Creating the new Database Context file from :

/u01/app/oracle/product/11.0.1_erppat/appsutil/template/adxdbctx.tmp

The new database context file has been created :

/u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml

Log file located at /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/CloneContext_1028139319.log

contextfile=/u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml

Check Clone Context logfile /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/CloneContext_1028139319.log for details.

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin =>

Note: If the most current AutoConfig Template patch as listed in OracleMetalink Note 387859.1 has already been applied to the source system, it is necessary to edit the value of the target system context variable "s_db_listener" to reflect the desired name for the tns listener. The traditionally accepted value is "LISTENER_[HOSTNAME]" but may be any valid value unique to the target host.

Also check and change local_listener and remote_listener value at context file.

6) Detach Existing Oracle Home at Primary Home

./runInstaller -detachHome ORACLE_HOME=/u01/app/oracle/product/11.0.1_erppat

7) Run dbTeckStack at primary node.

perl adclone.pl \

java=<ORACLE_HOME>/ appsutil/clone/jre \

component=dbTechStack \

mode=apply \

stage=OH/appsutil/clone \

method=CUSTOM \

dbctxtg=<ORACLE_HOME>/appsutil/<DB Context File> \

showProgress contextValidated=true

Following is the sample command :

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin =>perl adclone.pl \

> java=/u01/app/oracle/product/11.0.1_erppat/appsutil/clone/jre \

> component=dbTechStack \

> mode=apply \

> stage=/u01/app/oracle/product/11.0.1_erppat/appsutil/clone \

> method=CUSTOM \

> dbctxtg=/u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml \

> showProgress contextValidated=true

Logfile Path -- /u01/app/oracle/product/11.0.1_erppat/appsutil/log/ERPPAT1_server0090/ ApplyDBTechStack_11031815.log

And corresponding OH clone path -- /u01/app/oraInventory/logs/cloneActions2010-11-03_06-15-90PM.log

/u01/app/oracle/product/11.0.1_erppat/appsutil/log/ERPPAT1_server0090/make_11031816.log

/u01/app/oracle/product/11.0.1_erppat/appsutil/log/ERPPAT1_server0090/ohclone.log

8) Create the target database

a) We need to take hot backup from erpdev and save it to local disk using RMAN

run{

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk4 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk5 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk6 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk7 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk8 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

}

b) FTP the backup and archive log files to server0090(ERPPAT) after creating the same directory where the
source db backup files were saved, (alternate is to use ACTIVE DUPLICATION)

c) Create a parameter file for ERPPAT database (auxiliary).

The following parameters need to change.

1) control_files

2) diagnostic_dest

3) sga_target

4) db_create_file_dest (ASM location)

5) db_create_online_log_dest_1 (ASM Location)

6) log_archive_dest (Enabled archive log mode)

7) cluster_database=false

8) _no_recovery_through_resetlogs=TRUE

d) Set ORACLE_HOME and ORACLE_SID accordingly

Create a password file for auxiliary database using below command.

%orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password= xxxxxxxx

e) Start auxiliary database in NO MOUNT state

startup nomount pfile=<path of init.ora>;

g) Create a static listener for auxiliary database and reload, because auxiliary database will not register
itself with the listener.

h) Make sure that backup pieces are on the same location where it were there on production db.
If you don’t have the same location, then make RMAN aware of the changed location.

i) Duplicate Target Database using RMAN Duplicate command

Connect to target and auxiliary
      rman target sys/password_sys@target_db auxiliary sys/password_sys@auxiliary_db
     RMAN> DUPLICATE TARGET DATABASE TO ERPDUP;

Note:-We need to use password file in ERPDEV and also check remote_login_passwordfile=EXCLUSIVE so that we can connect to ERPDEV using sys user through net alias. Please make necessary change in tnsnames.ora

j) Incorporate RAC related change

cluster_database = TRUE in init.ora

and also check the remote_listener and local_listener values are correct

k) Recreate redo logs of thread 2 & Enable second thread and startup the second instance.

ALTER DATABASE ADD LOGFILE THREAD 2

GROUP 3 (‘..../redo03a’,‘…/redo03b’) size 100M,

GROUP 4 (‘..../redo04a’,‘..../redo04b’) size 100M ;

SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;

SQL> startup;

l) Check redo thread and temp files after duplication of database.

select * from v$log;

SELECT * FROM DBA_TEMP_FILES OR SELECT * FROM V$TEMPFILE

m) Check sqlplus connection with primary SID.

n) Check that Actual IP’s are also included, otherwise add.

e.g --

{server0090:oracle:}: /u01/app/oracle/product/asm/network/admin =>cat listener.ora

# listener.ora.server0090 Network Configuration File: /u01/app/oracle/product/asm/network/admin/listener.ora.server0090

# Generated by Oracle configuration tools.

LISTENER_SERVER0090 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = server0090-vip)(PORT = 1591)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = server0090-vip)(PORT = 1922)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = server0090-vip)(PORT = 1923)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1591)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = server0090.domain.domain1)(PORT = 1922)(IP = FIRST))

)

9)Run the library update script against the RAC database.

$ cd [RDBMS ORACLE_HOME]/appsutil/install/[CONTEXT_NAME]

$ sqlplus "/ as sysdba" @adupdlib.sql so

10)Start RDBMS listener and check sqlplus connection with primary SID.

Shutdown ASM listener and start up (if it was up by CRS) newly created RDBMS listener .

11)Configure the primary target database using DBCONFIG : (check TNS_ADMIN)

$ cd [RDBMS ORACLE_HOME]/appsutil/clone/bin

$ perl adcfgclone.pl dbconfig <ORACLE_HOME>/appsutil/DB Context file

$ perl adcfgclone.pl dbconfig /u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml

Logfile- /u01/app/oracle/product/11.0.1_erppat/appsutil/log/ERPPAT1_server0090/ ApplyDatabase_10281449.log

Configure Secondary Node :

Uncompress the archived ORACLE_HOME transferred from the Source System

Uncompress the source system ORACLE_HOME archive to a location matching that present on your target system primary node. The directory structure should match that present on the newly created target system primary node.

$ {server0092:oracle:}: /u01/app/oracle/product/11.0.1_erppat =>tar -xvf db.tar.gz

11) Archive the [ORACLE_HOME]/appsutil directory structure from the new Primary Node

$ cd [ORACLE_HOME]

$ zip -r appsutil_node1.zip appsutil

13 ) Copy appsutil_node1.zip to the Secondary Target Node

Transfer and then expand the appsutil_node1.zip into the secondary target RAC node [NEW ORACLE_HOME].

$ cd [NEW ORACLE_HOME]

$ unzip -o appsutil_node1.zip

14 ) Update pairsfile.txt for the Secondary Target Node

Alter the existing pairsfile.txt (from the first target node) and change the s_undo_tablespace parameter. As this is the second node, the correct value would be UNDOTS2. As an example, the [NEW_ORACLE_HOME]/appsutils/clone/pairsfile.txt would look like:

s_undo_tablespace= APPS_UNDOTS2

s_dbClusterInst=2

s_db_oh=/u01/app/oracle/product/11.0.1_erppat

15) Create a Context File for the Secondary Node

Navigate to [NEW_ORACLE_HOME]/appsutil/clone/bin and run the adclonectx.pl utility as follows:

perl adclonectx.pl \

contextfile=[Path to Existing Context File from the First Node] \

template=[NEW ORACLE_HOME]/appsutil/template/adxdbctx.tmp \

pairsfile=[NEW ORACLE_HOME]/appsutil/clone/pairsfile.txt \

addnode

Where:

contextfile : Full path to the existing context file from the first (primary) node.

template : Full path to the existing database context file template.

pairsfile : Full path to the pairsfile created on last step.

Note—Please perform following step before proceed.

./runInstaller -detachHome ORACLE_HOME=/u01/app/oracle/product/11.0.1_erppat

Also check and change local_listener and remore listener value at context file.

16 ) Configure NEW ORACLE_HOME

Run the commands below to move to the correct directory and continue the cloning process: $ cd [NEW ORACLE_HOME]/appsutil/clone/bin

$ perl adcfgclone.pl dbTechStack [Full path to the database context file created in previous step]

check the logfiles in case completion with warnings.

17) Source the new environment file in the ORACLE_HOME

Run the commands below to move to the correct directory and source the environment:

$ cd [NEW ORACLE_HOME]

$ ./[CONTEXT_NAME].env

18) Modify [SID]_APPS_BASE.ora

Edit the [SID]_APPS_BASE.ora file and change the control file parameter to reflect the correct control file location on the shared storage. This will be the same value as in the [SID]_APPS_BASE.ora on the target system primary node which was just created.

19) Start Oracle RAC Database

Start the database using the following commands:

SQL> connect / as sysdba

SQL> startup

20) Execute AutoConfig on both the node. (Point TNS_ADMIN to ASM Home)

Run AutoConfig to generate the proper listener.ora and tnsnames.ora files:

$ cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME

$ ./adautocfg.sh appspass=[APPS Password]

21) Point TNS_ADMIN to ASM HOME/network/admin, shutdown EBS RDBMS listeners and start ASM listeners at both the tiers.

Check that Actual IP’s are also included, otherwise add.