Using Set Override Address in Worklow Notification Mailer [ATG_PF.H/R12]

What is "Set Override Address"?

"Set Override Address" option is available in ATG_PF.H.Rollup 3 OR Above / R12

As pre Note: 377416.1,

" After ATG.H RU3 , the override address replaces the 'test address'. The purpose of this setting is to provide a test address which will receive ALL outbound notifications from the notification mailer.

The mailer will redirect or 'override' all outbound mail to this address, regardless of the original recipient.

This is useful for testing purposes where the mailer functionality must be confirmed yet where the mass mailing of pending test notifications to a user community is not wanted. "

When "Set Override Address" is set, Workflow Notification Mailer sents all Workflow Notifications / Oracle Alerts related mails to the address that you specified in "Set Override Address" instead of the actual recipients.

We can use this in the following occasions,

1. After cloning, don't want Mailer to sent the Notifications / Alerts to production users
   
2. In Test instance want all Notifications / Alerts to particular Mail address
   

How to set "Set Override Address"?

1. Log into Oracle Applications Manager [OAM]
   
2. Navigate: Site Map / Administration tab / Workflow section / Notification Mailer / View Details / Set Override Address Button
   
3. Give a valid Email Address / Submit
   

[OR]

1. System Administration Responsibility
   
2. Navigate: Workflow Manager / Notification Mailer / View Details / Set Override Address Button
   
3. Give a valid Email Address / Submit
   

Mailer will send an email to the new Override Address that has been entered in OAM, just to verify the email Address is valid.

The email includes a Verification Code, that must be entered in OAM to confirm the new Override Address

How to clear "Set Override Address"?

1. Log into OAM.
   
2. Navigate: Site Map / Administration tab / Workflow section / Notification Mailer / View Details / Set Override Address Button
   
3. Click on button "Clear Override Address"
   

References:

740110.1 How To Clear the Test / Override Address Used By Java Mailer On Post-ATG_PF.H.Rollup 3 Instance?

415721.1 Which tables stores the NEW test address 'set override address' ?

743282.1 Email Notifications are not Sent to Users after Configuring the Workflow Mailer

377416.1 Can Not Set Test Override Email Address In Notification Mailer After Atg.H Rup3

OCM with Oracle Applications R12 in Disconnected Mode

Software Configuration Manager (SCM) now known as Oracle Configuration Manager(OCM) came across dramatic changes from its introduction.

During intial release of Oracle Applications R12.0 OCM is incorprated with Autoconfig and introduced some new autoconfig variables.

Now accroding to Note:727157.1, no more OCM Configuration or OCM management via Autoconfig.

Most of the customers are worried to configure OCM in connected mode, due to security calls.
It requires internet connectivity (with or without proxy) to the OCM installed node.
Connected mode uploads the collected data to metalink via https protocol.

Below are the steps to configure OCM With Oracle Applications R12 in disconnected mode.
In Disconnected mode the Apps DBA or Applications Administrator, has to manually upload the collected data(ocmconfig.jar) by OCM to metalink.

Refer 763142.1 to know the procedure of manually uploading collected data (ocmconfig.jar)

What we are going to do is, remove existing OCM setup/stage files, and install the newly downloaded OCM version

1. Remove Metalink, CSI related information from Context XML file
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

abc@abc.com
IN


16144040
[to]







Verify the same in DB Tier Nodes
++++++++++++++++++++++++
cat $ORACLE_HOME/appsutil/$TWO_TASK.xml | grep -i proxy
cat $ORACLE_HOME/appsutil/$TWO_TASK.xml | grep -i metalink
cat $ORACLE_HOME/appsutil/$TWO_TASK.xml | grep -i country
cat $ORACLE_HOME/appsutil/$TWO_TASK.xml | grep -i systemcsi


Verify the same in Apps Tier Nodes
+++++++++++++++++++++++++
cat $INST_TOP/appl/admin/$TWO_TASK.xml | grep -i proxy
cat $INST_TOP/appl/admin/$TWO_TASK.xml | grep -i metalink
cat $INST_TOP/appl/admin/$TWO_TASK.xml | grep -i country
cat $INST_TOP/appl/admin/$TWO_TASK.xml | grep -i systemcsi

Database Oracle Home OCM Installation
+++++++++++++++++++++++++++++++++++++
echo $ORACLE_HOME
$ORACLE_HOME/ccr/bin/emCCR stop
rm -Rf $ORACLE_HOME/ccr_stage
rm -Rf $ORACLE_HOME/ccr

unzip ocm-Production-AIX-ppc.zip -d $ORACLE_HOME

ls -rlt $ORACLE_HOME/ccr
ls -rlt $ORACLE_HOME/ccr/bin
$ORACLE_HOME/ccr/bin/setupCCR -s -d 123456 abc@abc.com IN

Database Post-Install Steps
+++++++++++++++++++++++++++
$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig -s $ORACLE_SID -r sys

If using EBusiness Suite
++++++++++++++++++++++++
$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh ebs_collectconfig -u apps -w apps

If using Enterprise Manager 10g
+++++++++++++++++++++++++++++++
$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectemrep -e

$ORACLE_HOME/ccr/bin/emCCR status
$ORACLE_HOME/ccr/bin/emCCR collect

ls -rlt $ORACLE_HOME/ccr/hosts/`hostname`/state/upload/ocmconfig.jar

Applications 10.1.2 Oracle Home OCM Installation
+++++++++++++++++++++++++++++++++++++++++++++++++

echo $ORACLE_HOME
rm -Rf $ORACLE_HOME/ccr_stage

ls -lrt $INST_TOP/ocm
rm -Rf $INST_TOP/ocm

unzip ocm-Production-AIX-ppc.zip -d $ORACLE_HOME

ls -rlt $ORACLE_HOME/ccr
ls -rlt $ORACLE_HOME/ccr/bin
$ORACLE_HOME/ccr/bin/setupCCR -s -d 12345 abc@abc.com IN

$ORACLE_HOME/ccr/bin/emCCR status
$ORACLE_HOME/ccr/bin/emCCR collect

ls -rlt $INST_TOP/ora/10.1.2/ccr/state/upload/ocmconfig.jar



Applications 10.1.3 IAS Home OCM Installation
+++++++++++++++++++++++++++++++++++++++++++++

ORACLE_CONFIG_HOME=$IAS_ORACLE_HOME
export ORACLE_CONFIG_HOME

echo $IAS_ORACLE_HOME
rm -Rf $IAS_ORACLE_HOME/ccr_stage

unzip ocm-Production-AIX-ppc.zip -d $IAS_ORACLE_HOME

ls -rlt $IAS_ORACLE_HOME/ccr
ls -rlt $IAS_ORACLE_HOME/ccr/bin
$IAS_ORACLE_HOME/ccr/bin/setupCCR -s -d 12345 abc@abc.com IN

$IAS_ORACLE_HOME/ccr/bin/emCCR status
$IAS_ORACLE_HOME/ccr/bin/emCCR collect

ls -rlt $IAS_ORACLE_HOME/ccr/state/upload



References:
++++++++
727157.1 Upgrading OCM and Switching to Native Integration with Oracle E-Business Suite Release 12
763142.1 How to upload the collection file ocmconfig.jar to My Oracle Support for Oracle Configuration Manager (OCM) running in Disconnected Mode.
250434.1 BULLETIN: Learn More About My Oracle Support Configuration Manager

11i/R12: Not a valid responsibility for the current user

After attaching a responsibility to a user often used to receive,

"XXXX is not a valid responsibility for the current user. Please contact your System Administrator."

And the attached responsibility will not appear in the Home Page until you bounce the apache. It appears from 11i.FND.H to 12.0.4

We ran into many SR's while working with different customers at different patch levels.

According Note: 374309.1 Newly added responsibility is not displayed until Apache is bounced, after applying ATG RUP3 or ATG RUP4,

It is because of delayed processing of changes through the Apache Java Cache mechanism.

Normally when you are adding a responsbility to a user or end date a responsibility it fires considerable number of business events like,

• oracle.apps.fnd.wf.ds.userRole.created
  
• oracle.apps.fnd.user.role.insert
  
• oracle.apps.fnd.wf.ds.userRole.updated, etc.,
  

If the Workflow Deferred Agent Listener and Workflow Java Deferred Agent Listener are not running, then these business events will not get processed.

Then we will face the above said error. Hence, It is necessary to ensure the below Workflow Service Components are up and running (OAM -> Workflow Manager -> Service Components) as one of the verification checks,

• Workflow Deferred Agent Listener
  
• Workflow Java Deferred Agent Listener
  

Sysadmin Zoom functionality using Oracle Forms Personalization

Oracle introduced Forms Personalizations Feature, which was first introduced with FND Patch Set Level H (FND.H). Since then, lots of customizations got moved from CUSTOM.pll.

One of simply achievable functionality is Zoom functionality. Often my developers / APPS sysadmins wants to Navigate in the following flow.

1. Define Users Window -> Responsibilities Details Window -> Request Group Details Window -> Concurrent Program Details Window -> Executable Details Window
[OR]
2. Define Users Window -> Responsibilities Details Window -> Menu Details Window -> Sub Menu Details Windows -> Function Details Window

Download the below ldt file and Upload the personalizations into your test instance using below command.
Datafile (.ldt)

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct sysadmincus.ldt

Now Login into Applications -> Define Users Window -> Query for a User -> Place the cursor on a responsibility -> Tools Menu -> Responsibility Details

It will take you to the Responsibilities Details Window.

When you are in the Responsibilities Window, you can find below menu items

• Tools -> Menu Details
• Tools -> Requeset Group Details

When you are in Request Group Details window, you can find below menu items

• Tools -> Program Details
• Tools -> Requeset Set Details

When you are in Concurrent Program Details window, you can find below menu items

• Tools -> Executable Details

When you are in Concurrent Program Details window, you can find below menu items

• Tools -> Executable Details

When you are in Menu Details window, you can find below menu items

• Tools -> Submenu Details
• Tools -> Function Details
  

After R12 Installation, $ORACLE_HOME/.patch_storage consumes more space.

After R12 installation we find the RDBMS patching backup area ($ORACLE_HOME/.patch_storage) takes more than 5 GB space.
This will increase you backup time and requires more backup space.

Note 550522.1 stats,
From OPatch Version 10.2 Opatch does not backup only the affected modules, it also takes a backup of the complete affected libraries to $ORACLE_HOME/.patch_storage//backup// for faster rollback.
This causes the .patch_storage directory to grow accordingly to the size of the libraries and modules being patched.

Hence after every R12 installation, we can clean up this area. Steps are,

•Login as Database OS User
•Set the Path
$ PATH=$ORACLE_HOME/OPatch:$PATH
$ export PATH
$ which opatch
$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

•Check the size of .patch_storage
$ du -sh $ORACLE_HOME/.patch_storage

•Run opatch cleaning
$ opatch util cleanup -help
$ opatch util cleanup -invPtrLoc $ORACLE_HOME/oraInst.loc

•After cleaning check the .patch_storage size
$ du -sh $ORACLE_HOME/.patch_storage

Note:550522.1 How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space.

Patching Considerations in Windows Environemnts

Recently while applying ATG RUP5 patches in Windows environment, relink activity in copy portion of u driver failed. And then i checked the log file, and found the following message.

ERROR
awk: adrelinknew.sh 4277: not found
grep: adrelinknew.sh 4277: not found
grep: adrelinknew.sh 4277: not found
awk: adrelinknew.sh 4277: not found

I found a Note:4127315.1 in metalink, saying that, we have to place the mksnt executables in PATH before system32 exectables. Because executable 'sort.exe' provided by windows also, available in C:\Winnt\System32 folder. And then i checked my APPSORA.env, it was looking like below.

SET PATH=C:\mksnt;C:\WINDOWS;C:\WINDOWS\system32

As said in Metalink note 'mksnt' directory is before 'system32'. But it is not working. And then i returned to APPSORA.cmd sourced command prompt and typed 'ls'.

It said " 'ls' is not recognized as an internal or external command " and then i opened another command prompt without sourcing APPSORA.cmd found that following mksnt direcotries are in PATH.

C:\mksnt\bin;C:\mksnt\bin\X11;C:\mksnt\mksnt

I copied the same and added infront of 'system32' in APPSORA.env. After setting this, PATH vaiable in my APPSORA.cmd was looking like this,

SET PATH=C:\mksnt\bin;C:\mksnt\bin\X11;C:\mksnt\mksnt;C:\mksnt;C:\WINDOWS;C:\WINDOWS\system32

Now again i sourced the APPSORA.cmd and verified that, executables are in PATH and picking up from mksnt folder.

which sort make awk sed

Applied the patch. Now the relink has been done successfully.

Oracle Applications 11.0.3 to 11.5.10.2 Upgrade

For the past one week we are working in a Oracle Applications 11.0.3 to 11.5.10.2 Upgrade. We are currently in testing phase. So for testing we have taken the copy of 11.0.3 instance and cloned in another machine. And then upgrading the database to 9.2.0.8 and applications to 11.5.10.2.

The current techstack of the 11.0.3 Instance is,
i) Oracle Database 8.1.7.4
ii) Oracle Applications 11.0.3
iii) Oracle Forms and Reports 4i
iv) Oracle Discoverer 3i

After upgrade the techstack will be,
i) Oracle Database 9.2.0.8
ii) Oracle Applications 11.5.10.2 + AD.I.4 + 11i.ATG_PF.H RUP4 + 11i.FIN_PF.G
iii) Oracle Forms and Reports 6i Patchset 18
iv) Oracle Discoverer 4i

Regrading H/W, it is single node installation.
11.0.3 Production server is running Sun Solaris 5.8
The new server will be running Sun Solaris 5.10


References :-
Part No. A96530-02 Oracle9i Database MigrationRelease 2 (9.2)
Part No. B19297-01 Upgrading Oracle Applications Release 11i (11.5.10.2)
Note:216550.1 Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0.8)
Note:139516.1 Using Discoverer 4i with Oracle Applications 11i

I'am Back !!

Happy christmas everybody!! Somewhat one year back i created this blog. But after that i had no time(??) to write something in the blog. I started this blog to write about oracle applications DBA activities with the help of my friends.

Fine, in future you can expect something from this blog.

Purging Junk files from DBTier filesystem.

Often DBA's are facing disk full or excessive rate of disk usage issues. Excessive disk usage are caused by junk and temporary files that are created by various oracle programs that we are running. Also it slows down the full cold backup. Periodically we should cleanup the junk and temporary files from the database tier and apps tier filesystem in order to free up the filesystem and to speed up the backup process. The following discusses some of the areas that are need to be checked.

Database Tier Filesystem

bdump, cdump, udump directories

We can get the bdump, cdump, udump directories using the following SQL.
SELECT name, value FROM v$parameter WHERE name IN ('background_dump_dest','core_dump_dest','user_dump_dest');

From Oracle Manual :-
background_dump_dest stores trace files for the background processes (LGWR, DBWn, and so on) and an alert file alert_sid.log (where sid is the system identifier).
core_dump_dest It is primarily a UNIX platform parameter, which stores core dump files of oracle processes.
user_dump_dest stores the debugging trace files of oracle user processes.

In the above directories, we can keep only 7 days old log, trace files for reference and after proper backup, we can delete other files.

find $ORACLE_HOME/admin/bdump -name *.trc -mtime +7 -ls -exec rm {} \;
find $ORACLE_HOME/admin/cdump -mtime +7 -ls -exec rm {} \;
find $ORACLE_HOME/admin/udump -mtime +7 -ls -exec rm {} \;

Under background_dump_dest, the alert_SID.log will grow slowly. So we need to truncate it.
cd $ORACLE_HOME/admin/bdump;
cp alert_SID.log alert_SID.log_`date +"%d%m%Y%H%M%S"`;
tail -1 alert_SID.log > alert_SID.log;

$ORACLE_HOME/rdbms/audit
Whenever you are logging into database with sysdba priviliges, oracle server will record the session details in this diretory. Here also we can keep only 7 days old aud files for refernce and we can delete other files.

find $ORACLE_HOME/rdbms/audit -mtime +7 -ls -exec rm {} \;


$ORACLE_HOME/network/admin
Here you can find a log file called, sid.log This file is the listener log file. We can also truncate this file.

cd $ORACLE_HOME/network/admin;
cp sid.log sid.log_`date +"%d%m%Y%H%M%S"`;
tail -1 sid.log > sid.log;

Some of the other directories that are need to be checked.
$ORACLE_HOME/appsutil/outbound/
$ORACLE_HOME/appsutil/log/
$ORACLE_HOME/appsutil/out/
$ORACLE_HOME/.patch_storage

References :-
Note:296354.1 How Do You Reduce The Size Of An Alert Log That Is Too Large To Edit while the DB is running?
Note:1012933.6 General Information: Alert Logs and Trace Files

Oracle HRMS for India Installation.

Objective : Install the Oracle HRMS for India and Oracle Payroll for India Legislation.

I have installed Oracle Applications 11.5.10.2 on HP-UX PA RISC 64-Bit. While installing i licensed Oracle Human Resources, Self-Service Human Resources and Oracle Payroll products only. And then applied the following patches,

Advanced Techonology Stack
1. Patch 4712852 - Minipack 11i.AD.I.4
2. Patch 4676589 - 11i.ATG_PF.H Rollup 4 (RUP 4) [ With Pre and Post-Requisties]

Oracle Human Resources Family Pack
Note: 11.5.10.2 comes up with only 11i.HR_PF.H Family Pack. We need to apply the latest available.
1. Patch 3500000 - 11i.HR_PF.K HRMS Family Pack [ With Pre-Requisties]
2. Patch 5055050 - 11I.HR_PF.K RUP 1 HRMS K Rollup Patch 1 [ With Pre-Requisties]
3. Patch 4473462 - ORACLE APPLICATIONS HELP FOR HRMS FAMILY PACK K (July, 2005)

Oracle HRMS for India legislative patches
Note: Always Refer Note:145837.1 Latest HRMS (HR Global) Legislative Data Patch Available for latest HR Global patches.
1. Patch 5713492 - HRGLOBAL: SCHEMA AND DRIVER GLOBAL FILES FOR 11I.HR_PF.K.RUP1
2. Patch 5148398 - INDIA HRMS CONSOLIDATED HRGLOBAL PREREQ D [ With Pre-Requisties]
3. Patch 5501268 - Statutory update to Income Tax rounding effective July 13, 2006

After applying the above patches run Run DataInstall as applmgr user at application tier,
java oracle.apps.per.DataInstall thin
Ex: java oracle.apps.per.DataInstall apps apps thin wiporaapps01:1581:UPG

There Enter into option 1 and choose Global, Oracle HRMS India, Oracle Payroll India to install.
Now choose option 4 to exit. It will ask wheather to save the changes, press Y to save the changes and exit.

Now you have selected the legislations that need to be installed. Run hrglobal.drv to install/update the selected legislations.
Login into application tier as applmgr user
From directory $PER_TOP/patch/115/driver invoke adpatch utility
while the adpatch asking for driver file specify hrglobal.drv

It will install the selected legislations.

***From Note:145837.1 - For customers wishing to also apply translated legislative seed data, please apply the relevant language specific version of the consolidated translation patch 5726592 after successfully completing the hrglobal.drv.

Now you can proceed with other post-installation activities like, Convert to Multi-Org, Configuring Discoverer, Applying latest Family Packs.


References:-
Note:145837.1 Latest HRMS (HR Global) Legislative Data Patch Available
Note:283267.1 APAC HRMS Mandatory Patches
Note:135266.1 Oracle HRMS Product Family - Release 11i Information

What does an Oracle Apps DBA do?

An Oracle Applications DBA's job responsibility includes installing, configuring, maintaining Oracle database and applications, performance optimization and providing support to the developer team. Hence, the DBA often plays a major role in an application team and the IT division of an oraganization.

Why use Oracle?

Oracle Applications - consisting of Oracle Database, Application Server, Oracle HTMLDB - are used in all major industries by companies of all sizes. One of the main reasons why companies opt for oracle is that it can be easily run out of the box with minimum configurations. It is also highly customizable to any business requirements.