Database Consultant (Passionate about Database & Cloud Technologies)

Database Architect,Core DBA ,APPSDBA,Mysql DBA,MongoDB,PostgreSQL,MariaDB,Installations,Upgrades on all Flavors of UNIX (LINUX,SOLARIS,HP(AIX)). Oracle E-Business Suite R12 (Upgrades,Patching,Cloning) AWS upgrades and implementation expert. OCI (Oracle Cloud Infrastructure) Architect, Exadata (Oracle Database Exadata Cloud at Customer(Exacc)),Superclusters ,Autonomous Databases, AWS RDS Customs , Sql Tuning Expert. ***Never Stop sharing,Learning and Growing***

Sunday, November 24, 2019

OPMN FAILED TO START

OPMN FAILED TO START (unexpected exit: status 4200)

After Applying OCTOBER 2019 CPU PATCH ON EBS R12 SYSTEM.

When we try to bring up the services.

Error on starting opmn services

opmnctl startall
opmnctl startall: starting opmn and all managed processes...
[2019-11-21T21:25:37-08:00] [opmn] [ERROR:1] [] [internal] /u01/**/appl**/fs2/FMW_Home/webtier/opmn/bin/opmn: unexpected exit: status 4200
opmnctl startall: opmn failed to start.

For solution followed the steps as suggested.

Additional Steps to Configure OHS/OPMN 11.1.1.9 Components for Oracle E-Business Suite Release 12.2 After Applying the July 2019 and Later FMW Security Patch(Doc ID 2555355.1)

SOLUTION

Source ENV

2. Set an alias for the correct orapki.
alias orapki=$FMW_HOME/oracle_common/bin/orapki
Verify using which orakpi

3. Create a new wallet with an acceptable self-signed certificate in $HOME/ss(You can use any location. Preferred is $HOME/ss).
Eg:
$ mkdir ~/ss
$ cd ~/ss
$ orapki wallet create -wallet . -auto_login_only
$ orapki wallet add -wallet . -dn "CN=FMWSmallCircleOfTrust" -asym_alg RSA -keysize 2048 -sign_alg sha256 -self_signed -validity 3652 -auto_login_only
$ orapki wallet display -wallet .

4. Go to the instance directory.
$ cd $FMW_HOME/webtier/instances/$iName

5. Find the default wallets used by this instance.
Eg:
$ find . -name cwallet.sso
./config/OPMN/opmn/wallet/cwallet.sso
./config/OHS/EBS_web/proxy-wallet/cwallet.sso
./config/OHS/EBS_web/keystores/default/cwallet.sso

6. Verify that each wallet only contains the self-signed certificate.
$ find . -name cwallet.sso | while read w ;do echo -e "\n$w"; orapki wallet display -nologo -wallet $w ;done
If it is a default wallet, each wallet should list one "User Certificate" and an identical "Trusted Certificate", as shown in the following example:
$./config/OPMN/opmn/wallet/cwallet.sso
Requested Certificates:
User Certificates:
Subject: CN=Self-Signed Certificate for EBS_web_OHS1\20,OU=OAS,O=ORACLE,L=REDWOODSHORES,ST=CA,C=US
Trusted Certificates:
Subject: CN=Self-Signed Certificate for EBS_web_OHS1\20,OU=OAS,O=ORACLE,L=REDWOODSHORES,ST=CA,C=US

7. Copy the new SHA-256 signed wallet to all default wallet locations.
$ find . -name cwallet.sso | while read w ; do echo $w; cp -p ~/ss/cwallet.sso $w ; done
This avoids modifying the OPMN and OHS configuration files to point to a different wallet (or wallet directory).

With the new wallet in place, OPMN and OHS should be able to start.

You can use adopmnctl.sh start ;

adapcctl.sh start to verify startup of just OPMN and OHS.

Alternatively, you can run adstrtal.sh to attempt to start everything simultaneously.

5. Retest the issue.

Services are up and running.

Saturday, November 23, 2019

fs_clone failing (while running txkADOPPreparePhaseSynchronize.pl )

fs_clone failed :[UNEXPECTED]Error occurred running"perl/oradata/applmgr/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl

When attempting to run FSclone,
the following error occurs.

Error can be seen in adop_<TIME_STAMP>.log:

[UNEXPECTED]Error occurred running "perl <DIR>/<SID>/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl -contextfile=<DIR>/<SID>/fs1/inst/apps/<CONTEXT_NAME>/appl/admin/<CONTEXT_NAME>.xml -patchcontextfile=<DIR>/<SID>/fs2/inst/apps/<CONTEXT_NAME>/appl/admin/<CONTEXT_NAME>.xml -promptmsg=hide -console=off -mode=create -sessionid=3 -timestamp=20151223_192751 -outdir=<DIR>/<SID>/fs_ne/EBSapps/log/adop/3/fs_clone_20151223_192751/<CONTEXT_NAME>"
[UNEXPECTED]occurred during CLONE Patch File System from Run File System, running command: "perl <DIR>/<SID>/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl -contextfile=<DIR>/<SID>/fs1/inst/apps/<CONTEXT_NAME>/appl/admin/<CONTEXT_NAME>.xml -patchcontextfile=<DIR>/<SID>/fs2/inst/apps/<CONTEXT_NAME>/appl/admin/<CONTEXT_NAME>.xml -promptmsg=hide -console=off -mode=create -sessionid=3 -timestamp=20151223_192751 -outdir=<DIR>/<SID>/fs_ne/EBSapps/log/adop/3/fs_clone_20151223_192751/<CONTEXT_NAME>"

The context variable s_invPtrLoc is not getting populated in patch context file. FSCloneApplyAppsTier log prints correct value for s_invPtrLoc but the value is missing in patch context file.

Context file customization is present.

From stage logs(FSCloneStage.log) below can be seen:

Copying <DIR>/fs1/EBSapps/appl/ad/12.0.0/admin/template/custom/adxmlctx.tmp to <DIR>/fs1/EBSapps/comn/adopclone_<SID>/context/apps/adxmlctx.tmp

The adxmlctx.tmp is being copied from custom folder.

Custom folder has old version of adxmlct.tmp (version 120.401.12020000.9.1202010.10)
While the actual version is 120.401.12020000.54

During clonecontext the template being used is from the stage which have the older version. Due that reason the patch context file is not getting created properly.

That's the reason the version of context files which can be seen in FND_OAM_CONTEXT_FILES are different between RUN and PATCH filesystems.

RUN fs version : 120.401.12020000.54
PATCH fs version : 120.401.12020000.9.1202010.10

SOLUTION -:

FSClone fails while running txkADOPPreparePhaseSynchronize.pl (Doc ID 2094689.1)

· ls -ltr $AD_TOP/admin/template/custom/adxmlctx.tmp

· mv $AD_TOP/admin/template/custom/adxmlctx.tmp $AD_TOP/admin/template/custom/adxmlctx.tmpbkp

· Remove the file $AD_TOP/admin/template/custom/adxmlctx.tmp
Run fs_clone with option force=yes

adop phase=fs_clone force=yes

Wednesday, November 13, 2019

Update Profile values in Oracle APPS

Script to update profile values in oracle apps

How to Change Profile Option Value Without Forms? (Doc ID 943710.1)

Profile Option Values can be changed from backend using FND_PROFILE.SAVE procedure.

Syntax:
FND_PROFILE.SAVE(<Profile_Option_Name>, <Profile_Option_Value>, <Level SITE/APPL/RESP/USER>, <Level_Value>, <Level_Value_App_id>);

We normal change only at site level/user level

1. For site level , we just need (Profile option name and its value)

2. For user level , We need (Profile option name, value and the user id which we get from fnd_user)

FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'SITE');
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'USER', USERID);

Solution 1

----------

Declare

value Boolean;

Begin

value := fnd_profile.save('NAME','VALUE','SITE');

End;

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

Solution 2

----------

Declare

Result Boolean;

BEGIN
Result:= fnd_profile.SAVE ('NAME','VALUE','SITE');
IF Result
THEN
DBMS_OUTPUT.put_line ('Profile Updated');
ELSE
DBMS_OUTPUT.put_line ('Profile Not Updated');
END IF;
COMMIT;
END;

Query to get the profile option values from backend

Set linesize 1000

set pages 9999

col name format a30

col USER_PROFILE_OPTION_NAME format a30

col LEVEL format a10

col CONTEXT format a10

col VALUE format a70

SELECT po.profile_option_name "NAME", po.user_profile_option_name,

DECODE (TO_CHAR (pov.level_id),

'10001', 'SITE',

'10002', 'APP',

'10003', 'RESP',

'10005', 'SERVER',

'10006', 'ORG',

'10004', 'USER',

'???'

) "LEVEL",

DECODE (TO_CHAR (pov.level_id),

'10001', '',

'10002', app.application_short_name,

'10003', rsp.responsibility_key,

'10005', svr.node_name,

'10006', org.NAME,

'10004', usr.user_name,

'???'

) "CONTEXT",

pov.profile_option_value "VALUE"

FROM apps.fnd_profile_options_vl po,

apps.fnd_profile_option_values pov,

apps.fnd_user usr,

apps.fnd_application app,

apps.fnd_responsibility rsp,

apps.fnd_nodes svr,

apps.hr_operating_units org

WHERE 1 = 1

AND pov.application_id = po.application_id

AND pov.profile_option_id = po.profile_option_id

AND usr.user_id(+) = pov.level_value

AND rsp.application_id(+) = pov.level_value_application_id

AND rsp.responsibility_id(+) = pov.level_value

AND app.application_id(+) = pov.level_value

AND svr.node_id(+) = pov.level_value

AND org.organization_id(+) = pov.level_value

and po.profile_option_name like 'ICX_FORMS_LAUNCHER';

Friday, November 8, 2019

R12 PATCHING TECHNOLOGY COMPONENTS

Oracle E-Business Suite 12.2 Patching Technology Components Guide

(Doc ID 1355068.1)

Oracle E-Business Suite Release 12.2 utilizes multiple technology components.
Each component has its own Oracle Home, which can be patched individually.

Article describes the technology patching tools and how are they used in EBS R12 (Release 12.2)

Patching Oracle E-Business Suite Release 12.2 Technology Components

Oracle Homes

Oracle E-Business Suite Release 12.2 uses the following Oracle Homes:

· Oracle Fusion Middleware

Oracle WebLogic Server (WLS) 10.3.6
OHS (WebTier) 11.1.1 and Oracle Common (Common Modules)

· Application Server (OracleAS)

Forms and Reports 10.1.2

· Oracle Database

Oracle Database Server 11.2 or 12.0

Note: Oracle E-Business Suite Release 12.2 uses multiple Oracle Homes to accommodate the various technology components. When applying a patch, you must ensure the environment is set to point to the correct Oracle Home.

Application EBS Technology Patching Cycle: Fusion Middleware

Application tier technology patches are applied to the patch file system using a native patching tool such as OPatch or SmartUpdate, and the changes are then propagated to the run file system as part of an Online Patching cycle

The following is an overview of the process to follow when applying a technology patch within Oracle E-Business Suite**.

Note: EBS_ROOT is the base directory of your Oracle E-Business Suite Release 12.2 installation.

Download any required technology patches and unzip them into the $NE_BASE/EBSapps/patch directory.
Prepare the system for patching:

$ source <EBS_ROOT>/EBSapps.env3 run
$ adop phase=prepare

Prepare to apply technology patches to the Oracle Home on the patch file system:

$ source <EBS_ROOT>/EBSapps.env3 patch

Refer to Table 1 below for details about applying technology patches application on the application tier.

Optionally, apply any Oracle E-Business Suite patches planned for this patching cycle:

$ adop phase=apply patches=<patch_list>

After all patches have been successfully applied, complete the patching cycle:

$ adop phase=finalize
$ adop phase=cutover
$ source <EBS_ROOT>EBSapps.env3 run
$ adop phase=cleanup

Synchronize the technology level between patch and run file systems:

$ adop phase=fs_clone

Note: The above overview does not apply if patches are being applied in hotpatch or downtime mode, where technology patches are applied directly to the run file system outside an online patching cycle. If you are installing or upgrading to Release 12.2, you may have been referred to this document from My Oracle Support Knowledge Document 1617461.1 Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2. If you are following Path A of that document, note that it uses hot patching, so you will not use the patching cycle shown above until later in the installation or upgrade process.

Table 1 - Fusion Middleware Oracle Home

Oracle Home	Patch Installation Instructions	Inventory Command for Patch Installation Verification	Patch De-installation Instructions	Notes
WebLogic Server	Review the patch Readme Follow the Readme instructions, for example by running the commands: `$ cd $FMW_HOME/utils/bsu` `$ bsu.sh³` `-prod_dir=<path>/FMW_Home/wlserver_10.3` `-patchlist=<patchID¹>` `-verbose -install`	Run the commands: `$ cd $FMW_HOME/utils/bsu` `$ bsu.sh³ -report` `-bea_home=<pathto>/FMW_Home -output_format=text`	Follow the patch Readme instructions, for example by running the command: `$ ./bsu.sh³ -remove -patchlist=<patchID¹> -prod_dir=<path>/FMW_Home/wlserver_10.3 -verbose`	Oracle Smart Update Applying Patches to Oracle WebLogic Server Chapter 8: Using the Command-Line Interface More information on how to apply WebLogic Server (WLS) patches using smart update is available in My Oracle Support Knowledge Document 876004.1. More information on how to locate and download patches for WebLogic is available in My Oracle Support Knowledge Document 1302053.1.
Web Tier and Utilities (OHS)	Review the patch Readme Set ORACLE_HOME to the required FMW Oracle Home.² Include $ORACLE_HOME/OPatch in the PATH Change directory to the patch directory Follow the Readme instructions, for example by running the command: $ opatch apply	Set ORACLE_HOME to the required FMW Oracle Home.² For example: `$ export ORACLE_HOME =` `$IAS_ORACLE_HOME` Include $ORACLE_HOME/OPatch in the PATH Run the command: `$ opatch lsinventory`	Follow the Readme instructions, for example by running the command: `$ opatch rollback -id <patchnumber>`	More information on OPatch is available in My Oracle Support Knowledge Document 293369.1.
Oracle Fusion Middleware Common	Review the patch Readme Set ORACLE_HOME to the required FMW Oracle Home.² Include $ORACLE_HOME/OPatch in the PATH Change directory to the patch directory Follow the Readme instructions, for example by running the command: `$ opatch apply`	Set ORACLE_HOME to the required FMW Oracle Home.² For example: `$ export ORACLE_HOME =` `$FMW_HOME/oracle_common` Include $ORACLE_HOME/OPatch in the PATH. Run the command: `$ opatch lsinventory`	Follow the Readme instructions, for example by running the command: `$ opatch rollback -id <patchnumber>`	More information on OPatch is available in My Oracle Support Knowledge Document 293369.1.
Forms and Reports 10.1.2	Review the patch Readme Source the Application Server Oracle Home Environment Include $ORACLE_HOME/OPatch in the PATH Change directory to the patch directory Follow the Readme instructions, for example by running the command: `$ opatch apply`	Source the OracleAS Oracle Home environment. Include $ORACLE_HOME/OPatch in the PATH. Run the command: `$ opatch lsinventory`	Follow the Readme instructions, for example by running the command: `$ opatch rollback -id <patchnumber>`	More information on OPatch is available in My Oracle Support Knowledge Document 293369.1. Additional actions specific to E-Business Suite may be necessary after patch install or rollback. Refer to My Oracle Support Knowledge:Document 437878.1 Steps 4.1 and 4.2.

¹ PatchID is a combination of letters and numbers. As a general rule the PatchID is the name of the jar file without the .jar extension.
² SetOracleCommon.env and SetWebtier.env are provided within the $FMW_HOME directory scripts that set the Oracle Home for patching purposes.
³On Windows, command (.cmd) scripts such EBSapps.cmd and bsu.cmd are the equivalent of the shell scripts mentioned for UNIX platforms.

Note: You may verify the installation of any WebLogic Patch by examing the inventory output from:

$ cd $FMW_HOME/utils/bsu
$ bsu.sh -report

Database Oracle Home

The Database Oracle Home is not a part of the the online patching process within Oracle E-Business Suite 12.2. We strongly recommend applying required database patches as soon as possible, or you may experience issues.

If you plan to combine applying the required database patches as part of your next online patching cycle, you can apply them after the cutover phase of that cycle:

On application tier primary node:

$source <INSTALL_BASE>/EBSapps.env4 run

$ adop phase=prepare
$ adop phase=apply patches=1111,2222
$ adop phase=finalize
$ adop phase=cutover mt_restart=no

On database node:

$ source <INSTALL_BASE>/<sid_machine>.env
$ $ORACLE_HOME/appsutil/scripts/<sid_machine>/addbctl.sh stop
$ $ORACLE_HOME/OPatch/opatch apply <Database Patch >
$ $ORACLE_HOME/appsutil/scripts/<sid_machine>/addbctl.sh start

On application tier primary node:

$ source <INSTALL_BASE>/EBSapps.env4 run
$ adstrtal.sh -mode=allnodes
$ adop phase=cleanup

Table 2 - Database Oracle Home

Oracle Home	Patch Installation Instructions	Inventory Command for Patch Installation Verification	Patch De-installation Instructions	Notes
Database (RDBMS)	Review the patch Readme Source the Database Oracle Home environment Include $ORACLE_HOME/OPatch in the PATH Change directory to the patch directory Follow the Readme instructions, for example by running the command: `$ opatch apply`	Source the Database Oracle Home environment Include $ORACLE_HOME/OPatch in the PATH Run the command: `$ opatch lsinventory`	Follow the Readme instructions, for example by running the command: `$ opatch rollback -id <patchnumber>`	More information on OPatch is available in My Oracle Support Knowledge Document 293369.1.

Database Consultant (Passionate about Database & Cloud Technologies)

Sunday, November 24, 2019

OPMN FAILED TO START

Saturday, November 23, 2019

fs_clone failing (while running txkADOPPreparePhaseSynchronize.pl )

fs_clone failed :[UNEXPECTED]Error occurred running"perl/oradata/applmgr/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl

Wednesday, November 13, 2019

Update Profile values in Oracle APPS

Script to update profile values in oracle apps

Query to get the profile option values from backend

Query to get the profile option values from backend

Friday, November 8, 2019

R12 PATCHING TECHNOLOGY COMPONENTS

Oracle Homes

· Oracle Fusion Middleware

· Application Server (OracleAS)

Application EBS Technology Patching Cycle: Fusion Middleware

Table 1 - Fusion Middleware Oracle Home

Database Oracle Home

Table 2 - Database Oracle Home

Ebusiness Suite R12

Oracle Certified Professional