Oracle Apps DBA Workflow Mailer Tips

 Dear All,

In this post will share couple of Workflow mailer related information as part of Oracle Apps DBA Job.

1 Set Override Address

 Set the Override Address to the required address:

Run the following script @$FND_TOP/sql/afsvcpup.sql from sqlplus as apps user

 

SQL> @$FND_TOP/sql/afsvcpup.sql

 

Component Id Component Name                 Component Status Type            Containe

------------ ------------------------------ ---------------- --------------- --------

       10000 ECX Inbound Agent Listener     STOPPED          WF_AGENT_LISTEN GSM

       10001 ECX Transaction Agent Listener STOPPED          WF_AGENT_LISTEN GSM

       10002 Workflow Deferred Agent Listen STOPPED          WF_AGENT_LISTEN GSM

       10003 Workflow Deferred Notification STOPPED          WF_AGENT_LISTEN GSM

       10004 Workflow Error Agent Listener  STOPPED          WF_AGENT_LISTEN GSM

       10005 Workflow Inbound Notifications STOPPED          WF_AGENT_LISTEN GSM

       10006 Workflow Notification Mailer   DEACTIVATED_SYST WF_MAILER       GSM

       10020 Web Services IN Agent          STOPPED          WF_JAVA_AGENT_L GSM

       10021 Web Services OUT Agent         STOPPED          WF_DOCUMENT_WEB GSM

       10022 Workflow Java Deferred Agent L STOPPED          WF_JAVA_AGENT_L GSM

       10023 Workflow Java Error Agent List STOPPED          WF_JAVA_AGENT_L GSM

       10040 WF_JMS_IN Listener(M4U)        STOPPED          WF_JAVA_AGENT_L GSM

       10041 Workflow Inbound JMS Agent Lis STOPPED          WF_AGENT_LISTEN GSM

 

 

Enter Component Id: enter id corresponds to Workflow Notification Mailer

 

Enter Component Id: 10006

 

For prompt Enter the Comp Parameter Id to update enter id corresponds to “Test Address”

 

Comp Param Id Parameter Name         Default Value    Value        Req Reload

-------------------------------------------------------------------------------------

10093          Test Address           NONE                          N   Y

 

Enter the Comp Param Id to update : 10093

 

You have selected parameter : Test Address

Current value of parameter  :

 

Enter the desired test email address at the next prompt, for example:

 

Enter a value for the parameter  : RACSINFOTECH@gmail.com

 

Gmail account details:

 

User - RACSINFOTECH@gmail.com

Password – racsinfo12

 

1.2Update existing notifications

 

Update the notifications in the WF_NOTIFICATIONS table so they are not sent from the cloned environment:

 

update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

 

1.3Rebuild the WF_NOTIFICATION_OUT queue

 

Purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table:

 

@$FND_TOP/patch/115/sql/wfntfqup.sql

 

1.4Update Mailer Settings

 

 

 

 

 

1.1.5Known Problem - Workflow Services problem

 

This has happened a couple of times. If the Services don’t start up it will be worth performing the below steps:

 

Unable to start the following Service Instances for Generic Service Component Container: 
-Workflow Agent Listener Service
-Workflow Document Web Services Service
-Workflow Mailer Service 

Error Codes:
Could not start Service Component Container -> oracle.apps.fnd.cp.gsc.SvcComponentContainerException: BES system could not establish connection to the control queue after 180 seconds 

 

The cause of your issue is that queue wf_control is corrupt, and WF Services can't read data from it. 
To resolve the issue, proceed with steps below : 

1- Stop the Conc Managers 
2- Drop/Recreate queeu wf_control : 

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfctqrec.sql APPLSYS apps

3- Start the Conc Managers 
4- Verify the issue 

Thanks,

Srini

RMAN-06136: Oracle error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed

 Dear All,

In this post i am going to show you RMAN Duplicate database clone errors fixup on Oracle database 19C .

while im doing RAC to Non RAC clone i faced this error on Target server .

[oracle@uatdb01 ~]$ rman auxiliary /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 6 09:06:18 2024

Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: UATDB (not mounted)

RMAN> duplicate database to 'UATDB' backup location '/u01/Backup' nofilenamecheck;

Starting Duplicate Db at 06-MAR-24

searching for database ID

found backup of database ID 1143261986

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 03/06/2024 09:09:00

RMAN-05501: aborting duplication of target database

RMAN-06136: Oracle error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed

ORA-00349: failure obtaining block size for '+DATA'

ORA-29701: unable to connect to Cluster Synchronization Service

ORA-29701: unable to connect to Cluster Synchronization Service

ORA-29701: unable to connect to Cluster Synchronization Service

Solution for the above error :

Add below values in parameter file on target server ..  update the correct path for your datafile and logfiles location in target side parameter .. that will resolve the below error .

*.cluster_database=FALSE

#*.control_files='/u01/app/oracle/oradata/UATDB/control01.ctl','/u01/app/oracle/fast_recovery_area/UATDB/control02.ctl'
*.db_file_name_convert='+DATA/RACSDB/DATAFILE','/u01/app/oracle/oradata/UATDB'
*.log_file_name_convert='+DATA/RACSDB/ONLINELOG','/u01/app/oracle/oradata/UATDB','+ARCH/RACSDB/ONLINELOG','/u01/app/oracle/oradata/UATDB'

After that restart the duplicate command 

RMAN> duplicate database to 'UATDB' backup location '/u01/Backup' nofilenamecheck;

Starting Duplicate Db at 06-MAR-24

searching for database ID

found backup of database ID 1143261986

contents of Memory Script:

{

   sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

contents of Memory Script:

{

   sql clone "alter system set  control_files =

  ''/u01/app/oracle/fast_recovery_area/UATDB/controlfile/o1_mf_lyhstm01_.ctl'' comment=

 ''Set by RMAN'' scope=spfile";

   sql clone "alter system set  db_name =

 ''RACSDB'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''UATDB'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   restore clone primary controlfile from  '/u01/Backup/c-1143261986-20240306-00';

   alter clone database mount;

}

executing Memory Script

sql statement: alter system set  control_files =   ''/u01/app/oracle/fast_recovery_area/UATDB/controlfile/o1_mf_lyhstm01_.ctl'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''RACSDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''UATDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

Starting restore at 06-MAR-24

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/fast_recovery_area/UATDB/controlfile/o1_mf_lyhstm01_.ctl

Finished restore at 06-MAR-24

database mounted

released channel: ORA_AUX_DISK_1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

RMAN-05158: WARNING: auxiliary (tempfile) file name +DATA/RACSDB/TEMPFILE/temp.264.1159692461 conflicts with a file used by the target database

RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:

{

   set until scn  7634718;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/UATDB/system.257.1159692337";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665";

   set newname for datafile  7 to

 "/u01/app/oracle/oradata/UATDB/users.260.1159692389";

   restore

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 06-MAR-24

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/UATDB/system.257.1159692337

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/UATDB/sysaux.258.1159692373

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/UATDB/users.260.1159692389

channel ORA_AUX_DISK_1: reading from backup piece /u01/Backup/1143261986-20240306-022l0f0a_2_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/Backup/1143261986-20240306-022l0f0a_2_1_1 tag=TAG20240306T075553

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15

Finished restore at 06-MAR-24

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/system.257.1159692337

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387

datafile 5 switched to datafile copy

input datafile copy RECID=9 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665

datafile 7 switched to datafile copy

input datafile copy RECID=10 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/users.260.1159692389

contents of Memory Script:

{

   set until scn  7634718;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 06-MAR-24

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=46

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=2 sequence=36

channel ORA_AUX_DISK_1: reading from backup piece /u01/Backup/1143261986-20240306-032l0f2d_3_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/Backup/1143261986-20240306-032l0f2d_3_1_1 tag=TAG20240306T075701

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_1_46_lyhsxpdr_.arc thread=1 sequence=46

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_2_36_lyhsxpgb_.arc thread=2 sequence=36

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_1_46_lyhsxpdr_.arc RECID=2 STAMP=1162891151

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_2_36_lyhsxpgb_.arc RECID=1 STAMP=1162891150

media recovery complete, elapsed time: 00:00:00

Finished recover at 06-MAR-24

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''UATDB'' comment=

 ''Reset to original value by RMAN'' scope=spfile";

   sql clone "alter system reset  db_unique_name scope=spfile";

}

executing Memory Script

sql statement: alter system set  db_name =  ''UATDB'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "UATDB" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP     1 ( '/u01/app/oracle/oradata/UATDB/group_1.257.1159692457', '/u01/app/oracle/oradata/UATDB/group_1.262.1159692455' ) SIZE 200 M  REUSE,

  GROUP     2 ( '/u01/app/oracle/oradata/UATDB/group_2.258.1159692457', '/u01/app/oracle/oradata/UATDB/group_2.263.1159692455' ) SIZE 200 M  REUSE

 DATAFILE

  '/u01/app/oracle/oradata/UATDB/system.257.1159692337'

 CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE

  INSTANCE 'i2'

  GROUP     3 ( '/u01/app/oracle/oradata/UATDB/group_3.266.1159692729', '/u01/app/oracle/oradata/UATDB/group_3.259.1159692729' ) SIZE 200 M  REUSE,

  GROUP     4 ( '/u01/app/oracle/oradata/UATDB/group_4.267.1159692729', '/u01/app/oracle/oradata/UATDB/group_4.260.1159692729' ) SIZE 200 M  REUSE

contents of Memory Script:

{

   set newname for tempfile  1 to

 "+DATA";

   switch clone tempfile all;

   catalog clone datafilecopy  "/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373",

 "/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387",

 "/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665",

 "/u01/app/oracle/oradata/UATDB/users.260.1159692389";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373 RECID=1 STAMP=1162891171

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387 RECID=2 STAMP=1162891171

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665 RECID=3 STAMP=1162891171

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/users.260.1159692389 RECID=4 STAMP=1162891171

datafile 3 switched to datafile copy

input datafile copy RECID=1 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373

datafile 4 switched to datafile copy

input datafile copy RECID=2 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387

datafile 5 switched to datafile copy

input datafile copy RECID=3 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665

datafile 7 switched to datafile copy

input datafile copy RECID=4 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/users.260.1159692389

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Cannot remove created server parameter file

Finished Duplicate Db at 06-MAR-24

RMAN> 

Do the post restore steps .. 

Thanks,
Srini

Difference between dbtechstack,dbtier and dbconfig

When running adcfgclone on database node we had three modes in which it can be executed.
 
perl adcfgclone.pl dbTier
 
 It will configure the ORACLE_HOME on the target database tier node and  recreate the controlfiles.
 This is specially used in case of standby database and/or hot backups. It will take care of all the steps. 
 
perl adcfgclone.pl dbTechStack
 
It will configure the ORACLE_HOME on the target database tier node only. Relink the oracle home.
 
The below steps has to be performed manually
1. Create the Target Database control files.
2. Start the Target System Database in open mode
3. Run the library update script against the Database
cd $RDBMS_ORACLE_HOME/appsutil/install/[CONTEXT NAME]
sqlplus "/ as sysdba" @adupdlib.sql [libext]
 Where [libext] should be set to 'sl' for HP-UX, 'so' for any other UNIX platform,
or 'dll' for Windows.
 
perl adcfgclone.pl dbconfig
 
It is used to configure the database with  context file.Database should be in open mode.
 
cd $RDBMS_ORACLE_HOME/appsutil/clone/bin
perl adcfgclone.pl dbconfig target_context_file
Where Target Context File is:
$RDBMS_ORACLE_HOME/appsutil/target_context_file.xml

 

Thanks 

Srini

Cloning a Single-Node System To a Multi-Node System

  Introduction
 This document describes a step-by-step approach for cloning an Oracle Applications 11i which is AutoConfig enabled using Rapid Clone from one to three nodes, it includes Port Selection, Forms Server, Reports Server, Apache Server and Concurrent Processing, also all the scripts or programs which are used to startup up and shutdown all the services. The cloning word means to do a functional copy of an existing environment. Simply copying the application directories doesn’t mean that our new environment will work properly, we need to do some additional steps or tasks to have a functional environment.

CLONE – Single Server to Two Node Server
 TARGET Server – DEV

Apps Tier

devcomn

devappl

devora

TARGET Server – DEV

DB Tier

devdb

devdata

SOURCE Server – PROD

DB Tier

prodb

proddata

Apps Tier

prodcomn

prodappl
 


Some of the reasons to do a cloning are:

• To create a test environment from an existing production environment to test some patches or to reproduce any production issues.

• To keep a test environment with the most current information of a production environment.

• To move any existing environment to other servers.

In this Cloning demonstration, we will clone our single node instance "PROD" to Two-node as one node for database server and second node is for Application. That means all application services will reside on one node and database on a separate node. Our source node is ERP and target node is also ERP. And our source database node is PROD and Target database node is DEV. The TARGET directory structure of both the node is same as Source that is /d01/oracle.
 Cloning prerequisite steps:
  We should remember that the clone application system and existing Production application system must have same component versions & operating system type. And also we cannot clone from windows to linux.
  Login as Applications file user & set the environment file on source node.



su applmgr

cd /d01/oracle/prodappl
 . ./APPSORA.env

 Login to database tier as oracle user and set the environment on source node.



su oracle

cd /d01/oracle/proddb/9.2.0
 . ./PROD_erp.env 



Prepare the source system
 (a) Prepare the source system database tier for cloning
Log on to the source system as the ORACLE user and run the following commands:

$ cd $ORACLE_HOME/appsutil/scripts/PROD_erp
 .perl /adpreclone.pl dbTier 



(b) Prepare the source system application tier for cloning
  Log on to the source system as the applmgr user and run the following commands.

$ cd $COMMON_TOP/admin/scripts/PROD_erp
 $ perl adpreclone.pl appsTier 



Copy the Source Node File System
   Log on to the source system application tier nodes as the APPLMGR user.



•Shut down the application tier server processes as shown below

cd $COMMON_TOP/admin/scripts/PROD_erp
 ./adstpall.sh apps/appspassword

•Copy the following application tier directories from the source node to the target application tier node:
cd /d01/oracle

prodappl

prodcomn

prodora

scp –pr proadappl applmgr@target_server:/d01/oracle

scp –pr proadcomn applmgr@target_server:/d01/oracle

scp –pr proadora applmgr@target_server:/d01/oracle

Once copied –

- Check the ownership as required

- Rename the directory

cd /d01/oracle

mv prodappl devappl

mv prodcomn devcomn
 mv prodora devora

 Copy the database tier file system



Log on to the source system database Tier as the ORACLE user.
 •Perform a normal shutdown of the source system database
 cd $RDBMS_ORACLE_HOME/appsutil/scripts/PROD_erp

./addbctl.sh stop
 •Copy the database (DBF) files from the source to the target system

•Copy the source database ORACLE_HOME to the target system as shown below:
  cd /d01/oracle
 scp –pr proaddb oracle@target_server:/d01/oracle
scp –pr proaddata oracle@target_server:/d01/oracle
Once copied –


- Check the ownership as required
 - Rename the directory


cd /d01/oracle

mv proddb devdb

mv proddata devdata
 •Start up the source Applications system database and application tier processes



Configure the Target System
  Operating System of Target should be same as Source. Operating system should have all pre-requisite packages required for Oracle R11i before configuring the Target System. Execute the following commands to configure the target system. You will be prompted for the target system specific values (SID, Paths, Ports, etc).
  Log on to target node as oracle user and run the following command and input your values to each prompt as shown below :



$ cd /proddb/9.2.0/appsutil/clone/bin

$ perl adcfgclone.pl dbTier
 Enter APPS Password :



b. Configure the target system application tier server nodes
   Log on to the target system as the APPLMGR user and type the following commands and specify your values to each prompt as shown below.



$ cd /d01/oracle/devcomn/clone/bin

$ perl adcfgclone.pl appsTier
 Enter the APPS password



Above screenshots showing that all application services are started successfully. That means, we have done cloning successfully.
 Finishing Tasks
 Post clone steps vary from client to client, here is the basic change.

Profile Option Name Changes at Site Level after Cloning
 Site Name-> PROD, Change it to "DEV – Clone of PROD as of Dec 14 2010"

DEV – Clone of PROD as of 14-Dec-10
 Login to Oracle Apps as SYSADMIN



 Select System Administrator responsibility
 

 Profile –> System

Now Oracle Apps Instance of DEV : check it from frontend

Thanks
Srini

Cloneing steps ...

Run preclone on DB tier

1.login to RDBMS oracle home

go to $ORACLE_HOME/appsutil/scripts/CONTEXT_NAME/perl adpreclone.pl dbTier

you should keep apps password ready at this time.

after you done with preclone, tar or copy entire oracle home to target

2. Run preclone on Apps or middle tier

cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME

perl adpreclone.pl appsTier

you should keep apps password ready at this time.

3.Copy the Database Tier File System

create controle file using(alter database backup controlefile to trace), you can find this in udump directory

shutdown database

copy all datafiles to target

4.Copy source file system to target file system

Copy or tar appl/comn/ora directoroies to target

5.Configure db tier

untar copied oracle home binaries in target, then run below command

cd RDBMS ORACLE_HOME/appsutil/clone/bin

perl adcfgclone.pl dbTier

here this script will complete with error, do not worry it will create new context file

2.5 Configure apps/middle tier

urnar files appl/comn/ora


perl adcfgclone.pl appsTier
Enter the APPS password [APPS]:


First Creating a new context file for the cloned system.
The program is going to ask you for information about the new system:


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

Do you want to use a virtual hostname for the target node (y/n) [n] ?:

Target system database SID [OFMSINST]:(new database name)

Target system domain name [xxxx]:

Target system database server node [xxxxx]:

Target system database domain name [xxxx]:

Does the target system have more than one application tier server node (y/n) [n]
?:y

Does the target system application tier utilize multiple domain names (y/n) [n]
?:

Target system concurrent processing node [xxxx]:

Target system administration node [xxxx]:

Target system forms server node [xxxx]:oraapp01

Target system web server node [xxxx]:oraapp01

Is the target system APPL_TOP divided into multiple mount points (y/n) [n] ?:

Target system APPL_TOP mount point [u01/inst/appl]:/u02/regt/appl ( new appl top location)

Target system COMMON_TOP directory [u01/inst/comn]:/u02/regt/comn (new common top location)

Target system 8.0.6 ORACLE_HOME directory [u01/inst/ora/8.0.6]:/u02/regt/ora/8.0.6 (new 806 oh location)

Target system iAS ORACLE_HOME directory [u01/inst/ora/iAS]:/u02/regt/ora/iAS (new IAS home location)

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

Location of the JDK on the target system [opt/java1.5]:

Target system JRE_TOP [opt/java1.5]:

Clone Context uses the same port pool mechanism as the Rapid Install
Once you choose a port pool, Clone Context will validate the port availability.

Enter the port pool number [0-99]:


Check below metalink notes, it will help you.


Cloning E-Business Suite Using Hot Backup for Minimal Downtime of Source Environment. [ID 362473.1]



FAQ: Cloning Oracle Applications Release 11i [ID 216664.1]

Descriptive Checklist for performing Rapid Clone with 11i/R12 [ID 811715.1]
Cloning SSO-Enabled Environments in E-Business Suite [ID 1123843.1]

NOTE:230672.1 - Cloning Oracle Applications Release 11i with Rapid Clone
NOTE:316806.1 - Oracle Applications Installation Update Notes, Release 11i (11.5.10.2)
NOTE:364565.1 - Troubleshooting RapidClone issues with Oracle Applications 11i
NOTE:405565.1 - Oracle Applications Release 12 Installation Guidelines
NOTE:406982.1 - Cloning Oracle Applications Release 12 with Rapid Clone
NOTE:603104.1 - Troubleshooting RapidClone issues with Oracle Applications R1

Thanks
Srini

Refreshing VS Cloning an e-Business Suite Environment

Just a quick note on refreshing vs cloning, what each of them means and when you should perform them.

What is Refreshing?

A refresh is where the data in the target environment has been synchronized with a copy of production. This is done by taking a copy of the production database and restoring it to the target environment.

What is Cloning?

Cloning means that an identical copy of production has been taken and restore to the target environment. This is done by taking both a copy of the production database as well as all of the application files.

When should you Clone or Refresh?

There are a couple of scenarios when cloning should be performed:

1. Building a new environment.
 
2. Patches or other configuration changes have been made to the target environment so that they are now out of sync.

3. Beginning of development cycles. Before major development efforts take place, its wise to re-clone dev, test environments so that your 100% positive that the environments are in sync.

There is only one scenario in which you should refresh an environment:

1. Your 100% confident that the environments are in sync and need an updated copy of the production data in order to reproduce issues.

Technically, if proper change control processes are being followed, test and production environments should be identical. So in the case of test, you should be able to get away with performing refreshes. However, to ease concerns and for comfort levels, test environments are usually re-cloned at the beginning of new development cycles as well.

Thanks
Srini

Refreshing an 11i Database using Rman

In order to facilitate troubleshooting we maintain a test environment which is a nightly copy of our 11i production environment. Since this environment is usually used to test data fixes it has to be as up to date as possible. To perform the database refresh we use rman's duplicate feature.

The goal of this article isn't just to provide the entire set of scripts and send you on your way. I think its safe to say that most EBS environments aren't identical, so its not like you could take them and execute with no issues. Instead i'll highlight the steps we follow and some of the key scripts.

NOTE: This doesn't include any pre-setup steps such as, if this is the first time duplicating the database make sure you have the parameters db_file_name_convert and log_file_name_convert specified in your test environments init file.

• Step 1: Shutdown the test environment. If you are using 10g then remove any tempfiles. In 10g, rman now includes tempfile information and if they exist you will encounter errors. Check this previous post. Startup the database in nomount mode.
  
• Step 2: Build a Rman Script. There are a couple of ways to recover to a point in time and we have decided to use SCN numbers. Since this process needs to be automated, we query productions rman catalog and determine the proper SCN to use and build an rman script. Here it is:
  

  set feedback off
  set echo off
  set serverout on
  spool $HOME/scripts/prod_to_vis.sql
  declare
  vmax_fuzzy number;
  vmax_ckp number;
  scn number;
  db_name varchar2(3) := 'VIS';
  log_file_dest1 varchar2(30) := '/dbf/visdata/';
  begin
  select max(absolute_fuzzy_change#)+1,
       max(checkpoint_change#)+1
       into vmax_fuzzy, vmax_ckp
  from rc_backup_datafile;
  if vmax_fuzzy > vmax_ckp then
  scn := vmax_fuzzy;
  else
  scn := vmax_ckp;
  end if;
  dbms_output.put_line('run {');
  dbms_output.put_line('set until scn '||to_char(scn)||';');
  dbms_output.put_line('allocate auxiliary channel ch1 type disk;');
  dbms_output.put_line('allocate auxiliary channel ch2 type disk;');
  dbms_output.put_line('duplicate target database to '||db_name);
  dbms_output.put_line('logfile group 1 ('||chr(39)||log_file_dest1||'log01a.dbf'||chr(39)||',');
  dbms_output.put_line(chr(39)||log_file_dest1||'log01b.dbf'||chr(39)||') size 10m,');
  dbms_output.put_line('group 2 ('||chr(39)||log_file_dest1||'log02a.dbf'||chr(39)||',');
  dbms_output.put_line(chr(39)||log_file_dest1||'log02b.dbf'||chr(39)||') size 10m;}');
  dbms_output.put_line('exit;');
  end;
  /
  spool off;
  
  This script produces a spool file called, prod_to_vis.sql:
  
  run {                                                                        
  set until scn 14085390202;                                                   
  allocate auxiliary channel ch1 type disk;                                    
  allocate auxiliary channel ch2 type disk;                                    
  duplicate target database to VIS                                             
  logfile group 1 ('/dbf/visdata/log01a.dbf',                          
  '/dbf/visdata/log01b.dbf') size 10m,                                 
  group 2 ('/oradata/dbf/visdata/log02a.dbf',                                  
  '/dbf/visdata/log02b.dbf') size 10m;}                                
  exit; 

  Note: Our production nightly backups are on disk which are NFS mounted to our test server.
  
  
• Step 3: Execute the rman script. Launch rman, connect to the target, catalog, auxiliary and execute the script above:
  
  ie.
  rman> connect target sys/syspasswd@PROD catalog rmancat/catpasswd@REPO auxiliary /
  
  You may want to put some error checking around rman to alert you if it fails. We have a wrapper script which supplies the connection information and calls the rman script above. Our refresh is critical so if it fails we need to be paged.
  

  rman @$SCRIPTS/prod_to_vis.sql
  if [ $? != 0 ]
  then
       echo Failed
       echo "RMAN Dupcliate Failed!"|mailx -s "Test refresh failed" pageremail@mycompany.com
       exit 1
  fi

  
  
• Step 4: If production is in archivelog mode but test isn't, then mount the database and alter database noarchivelog;
  
• Step 5: If you are using a hotbackup for cloning then you need to execute adupdlib.sql. This updates libraries with correct OS paths. (Appendix B of Note:230672.1)
  
• Step 6: Change passwords. For database accounts such as sys, system and other non-applications accounts change the passwords using alter user. For applications accounts such as apps/applsys, modules, sysadmin, etc use FNDCPASS to change their passwords.
  
  ie. To change the apps password:
  
  FNDCPASS apps/<production appspassword=> 0 Y system/<system_passwd> SYSTEM applsys <new apps passwd>
  
• Step 7: Run autoconfig.
  
• Step 8: Drop any database links that aren't required in the test environment, or repoint them to the proper test environments.
  
• Step 9: Follow Section 3: Finishing Tasks of Note:230672.1
  
  • Update any profile options which have still reference the production instance.
    
    Example:
    
    UPDATE FND_PROFILE_OPTION_VALUES SET
    profile_option_value = REPLACE(profile_option_value,'PROD','TEST')
    WHERE profile_option_value like '%PROD%
    Specifically check the FND_PROFILE_OPTION_VALUES, ICX_PARAMETERS, WF_NOTIFICATION_ATTRIBUTES and WF_RESOURCES tables and look for production hostnames and ports. We also update the forms title bar with the date the environment was refreshed:
    
    UPDATE apps.FND_PROFILE_OPTION_VALUES SET
    profile_option_value = 'TEST:'||' Refreshed from '||'Production: '||SYSDATE
    WHERE profile_option_id = 125
    ;
  • Cancel Concurrent requests. We don't need concurrent requests which are scheduled in production to keep running in test. We use the following update to cancel them. Also, we change the number of processes for the standard manager.
    
    update fnd_concurrent_requests
    set phase_code='C',
    status_code='D'
    where phase_code = 'P'
    and concurrent_program_id not in (
    select concurrent_program_id
    from fnd_concurrent_programs_tl
    where user_concurrent_program_name like '%Synchronize%tables%'
    or user_concurrent_program_name like '%Workflow%Back%'
    or user_concurrent_program_name like '%Sync%responsibility%role%'
    or user_concurrent_program_name like '%Workflow%Directory%')
    and (status_code = 'I' OR status_code = 'Q');
    
    update FND_CONCURRENT_QUEUE_SIZE
    set min_processes = 4
    where concurrent_queue_id = 0;
  
  
• Step 10: Perform any custom/environment specific steps. We have some custom modules which required some modifications as part of cloning.
  
• Step 11: Startup all of the application processes. ($S_TOP/adstrtal.sh)
  
  NOTE: If you have an application tier you may have to run autoconfig before starting up the services.

Hopefully this article was of some use even tho it was pretty vague at times. If you have any questions feel free to ask. Any corrections or better methods don't hesitate to leave a comment either.

 

Thanks

Srini

R12 - Cloning from an RMAN backup using duplicate database

 

Since most DBA's are using rman for their backup strategy I thought I would put together the steps to clone from an rman backup. The steps you follow are pretty much the same as described in Appendix A: Recreating the database control files manually in Rapid Clone in Note 406982.1 - Cloning Oracle Applications Release 12 with Rapid Clone.
Here are the steps:

1. Execute preclone on all tiers of the source system. This includes both the database and application tiers. (For this example, TEST is my source system.)
   
   For the database execute: $ORACLE_HOME/appsutil/scripts/<context>/adpreclone.pl dbTier
   Where context name is of the format <sid>_<hostname>
   
   For the application tier: $ADMIN_SCRIPTS_HOME/adpreclone.pl appsTier
   
   
2. Prepare the files needed for the clone and copy them to the target server.
   
   • Take a FULL rman backup and copy the files to the target server and place them in the identical path. ie. if your rman backups go to /u01/backup on the source server, place them in /u01/backup on the destination server. To be safe, you may want to copy some of the archive files generated while the database was being backed up. Place them in an identical path on the target server as well.
     
   • Application Tier: tar up the application files and copy them to the destination server. The cloning document referenced above ask you to take a copy of the $APPL_TOP, $COMMON_TOP, $IAS_ORACLE_HOME and $ORACLE_HOME. Normally I just tar up the System Base Directory, which is the root directory for your application files.
   • Database Tier: tar up the database $ORACLE_HOME.
     
     ex. from a single tier system. The first tar file contains the application files and the second is the database $ORACLE_HOME
     
     [oratest@myserver TEST]$ pwd
     /u01/TEST
     [oratest@myserver TEST]$ ls
     apps db inst
     [oratest@myserver TEST]$ tar cvfzp TEST_apps_inst_myserver.tar.gz apps inst
     .
     .
     [oratest@myserver TEST]$ tar cvfzp TEST_dbhome_myserver.tar.gz db/tech_stNotice for the database $ORACLE_HOME I only added the db/tech_st directory to the archive. The reason is that the database files are under db/apps_st and we don't need those.
   • Copy the tar files to the destination server, create a directory for your new environment, for example /u01/DEV. (For the purpose of this article I will be using /u01/DEV as the system base for the target envrionment we are building and myserver is the server name.)
     
   • Extract each of the tar files with the command tar xvfzp
     
     Ex. tar xvfzp TEST_apps_inst_myserver.tar.gz
     
3. Configure the target system.
   
   • On the database tier execute adcfgclone.pl with the dbTechStack parameter.
     
     For example. /u01/DEV/db/tech_st/10.2.0/appsutil/clone/bin/adcfgclone.pl dbTechStack
     
     By passing the dbTechStack parameter we are tell the script to configure only the necessary $ORACLE_HOME files such as the init file for the new environment, listener.ora, database environment settings file, etc. It will also start the listener.
     
     You will be prompted the standard post cloning questions such as the SID of the new environment, number of DATA_TOPS, Oracle Home location, port settings, etc.
     
     Once this is complete goto /u01/DEV/db/tech_st/10.2.0 and execute the environment settings file to make sure your environment is set correctly.
     
     [oradev@myserver 10.2.0] . ./DEV_myserver.env
     
4. Duplicate the source database to the target.
   
   • In order to duplicate the source database you'll need to know the scn value to recover to. There are two wasy to do this. The first is to login to your rman catalog, find the Chk SCN of the files in the last backupset of your rman backup and add 1 to it.
     
     Ex. Output from a rman> List backups
     .
     .
     List of Datafiles in backup set 55729
     File LV Type Ckp SCN Ckp Time Name
     ---- -- ---- ---------- --------- ----
     7 1 Incr 5965309363843 15-JUN-09 /u02/TEST/db/apps_st/data/owad01.dbf
     ..
     So in this case the SCN we would be recovery to is 5965309363843 + 1 = 5965309363844.
     
     The other method is to login to the rman catalog via sqlplus and execute the following query:
     
     select max(absolute_fuzzy_change#)+1,
     max(checkpoint_change#)+1
     from rc_backup_datafile;
     
     Use which ever value is greater.
     
   • Modify the db_file_name_convert and log_file_name convert parameters in the target init file. Example:
     
     db_file_name_convert=('/u02/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/',
     '/u01/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/')
     
     log_file_name_convert=(/u02/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/',
     '/u01/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/')
   • Verify you can connect to source system from the target as sysdba. You will need to add a tns entry to the $TNS_ADMIN/tnsnames.ora file for the source system.
     
   • Duplicate the database. Before we use rman to duplicate the source database we need to start the target database in nomount mode.
     
     Start rman:
     
     rman target sys/<syspass>@TEST catalog rman/rman@RMAN auxiliary /
     
     If there are no connection errors duplicate the database with the following script:
     
     run {
     set until scn 5965309363844;
     allocate auxiliary channel ch1 type disk;
     allocate auxiliary channel ch2 type disk;
     duplicate target database to DEV }
     The most common errors at this point are connection errors to the source database and rman catalog. As well, if the log_file_name_convert and db_file_name_convert parameters are not set properly you will see errors. Fix the problems, login with rman again and re-execute the script.
     
     When the rman duplicate has finished the database will be open and ready to proceed with the next steps.
     
     
   • Execute the library update script:
     
     cd $ORACLE_HOME/appsutil/install/DEV_myserver where DEV_myserver is the <context_name> of the new environment.
     
     sqlplus "/ as sysdba"@adupdlib.sql
     
     If your on linux replace with so, HPUX with sl and for windows servers leave blank.
     
   • Configure the target database
     
     cd $ORACLE_HOME/appsutil/clone/bin/adcfgclone.pl dbconfig
     
     Where is $ORACLE_HOME/appsutil/DEV_myserver.xml
     
5. Configure the application tier.
   
   cd /u01/DEV/apps/apps_st/comn/clone/bin
   perl adcfgclone.pl appsTier
   
   You will be prompted the standard cloning questions consisting of the system base directories, which services you want enabled, port pool, etc. Make sure you choose the same port pool as you did when configuring the database tier in step 3.
   
   Once that is finished, initialize your environment by executing
   
   . /u01/DEV/apps/apps_st/appl/APPSDEV_myserver.env
   
   
   
6. Shutdown the application tier.
   
   cd $ADMIN_SCRIPTS_HOME
   ./adstpall.sh apps/<source apps pass>
   
   
7. Login as apps to the database and execute:
   
   exec fnd_conc_clone.setup_clean;
   
   I don't believe this step is necessary but if you don't do this you will see references to your source environment in the FND_% tables. Every time you execute this procedure you need to run autoconfig on each of the tiers (db and application). We will get to that in a second.
   
   
8. Change the apps password. Chances are you don't want to have the same apps password as the source database, so its best to change it now while the environment is down.
   
   With the apps tier environment initialized:
   
   FNDCPASS apps/<source apps pass> 0 Y system/<source system pass>> SYSTEM APPLSYS <new apps pass>
   
9. Run autoconfig on both the db tier and application tier.
   
   db tier:
   cd $ORACLE_HOME/appsutil/scripts/DEV_myserver
   ./adautocfg.sh
   
   Application Tier
   cd $ADMIN_SCRIPTS_HOME
   ./adautocfg.sh
   
10. If there are no errors with autoconfig start the application. Your already in the $ADMIN_SCRIPTS_HOME so just execute:
    
    ./adstrtal.sh apps/<new apps pass>
    
11. Login to the application and perform any post cloning activities. You may want to override the work flow email address so that notifications goto a test/dev mailbox instead of users. We always change the colors and site_name profile options, etc. More details can be found in Section 3: Finishing tasks of the R12 cloning document referenced earlier.
    

Thats it, hopefully now you have successfully cloning an EBS environment using rman duplicate

 

Thanks

Srini