Hidden Options helpful while bouncing OPMN/Apache services in R12.

**************************
Apps : 12.1.3
DB : 11.2.0.4
Activity : Apache bounce after Profile option Change.
**************************

Requirement:

Customer sometimes asks to bounce Apache services when they change any profile options or any configurations done in their regular activities and requested for Apache bounce to get these new changes reflected.

a) Perform Health checks related to Apache

b) We can bring down all the Apache related services using below command “stopall”. By default this is not mentioned in its help. But can be used to quickly bring down and bring back.

Detailed execution is given below.

What is meant by Hidden option of “stopall”?

$ adapcctl.sh

You are running adapcctl.sh version 120.7.12010000.2

adapcctl.sh: too few arguments specified.

adapcctl.sh {start|stop|status}

Note: Here you cannot see “stopall” option . But still we can utilize it to quickly bring down Apache related services..

>> Stopping all the Apache Services using “Stopall” command:

$ adapcctl.sh stopall

You are running adapcctl.sh version 120.7.12010000.2

opmnctl: stopping opmn and all managed processes...

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /ramug/mtlog/KIRANG_jagannayak09/logs/appl/admin/log/adapcctl.txt for more information ...

>> Starting all the Apache Services using below command:

$ adapcctl.sh startall

You are running adapcctl.sh version 120.7.12010000.2

opmnctl: starting opmn and all managed processes...

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /ramug/mtlog/KIRANG_jagannayak09/logs/appl/admin/log/adapcctl.txt for more information ...

Forms.ear File Deploying issues...

**********************************************************

Some issues during Deploying forms.ear file during CPU patching in R12.

**********************************************************

Application: 12.1.3

Database : Release 11.2.0.2.0 Production on Fri Sep 9 10:16:46 2011

Activity : 11.2.0.2 UPgrade & 12.1.3 Upgrade.

Description:

Actual issue occured in CPU patching for Oct'10.

Deploy Forms 10.1.2 .ear files on EBSO R12 instance

Script to deploy :

perl $FND_TOP/bin/txkrun.pl -script=CfgOC4JApp -applicationname=forms -runautoconfig=No -oc4jpass=xxxxxxx

*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS

*** Log File = /ramuG/mtlog/RAMUG_kiranginni01/logs/appl/rgf/TXK/txkCfgOC4JApp_Fri_Sep_9_03_37_07_2011.log

Program : /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl started @ Fri Sep 9 03:37:07 2011

*** Log File = /ramuG/mtlog/RAMUG_kiranginni01/logs/appl/rgf/TXK/txkCfgOC4JApp_Fri_Sep_9_03_37_07_2011.log

Errors encountered running /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl

*******FATAL ERROR*******

PROGRAM : /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl(/ramuG/applmgr/1200/fnd/12.0.0/bin/txkrun.pl)

TIME : Fri Sep 9 03:37:09 2011

FUNCTION: (eval) [ Level 1 ]

ERRORMSG: Invalid OC4J password for : forms

Used OC4J password as welcome and it didn't worked....

Need to follow below steps to get reset to temporary value ( ) and proceed with script execution:

Steps :

a)

Back up the original system-jazn-data.xml file

cp -p $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml.ori

b) Change oc4jadmin password in file $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml for oc4jadmin user in credentials line as below:

old example lines:

OC4J Administrator

{903}AhyuIQ23dQEuEVA0Mv5B65CKXjWcC4/a

new lines:

OC4J Administrator

!welcome

The "Oc4j Instance password" has now become "welcome" with the above change.

c) Post-deployment step:

Reverting the value back to normal:

cp -p $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml.ori $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml

Step a ==> Done.

Step b ==> Done.

Deployment step:

perl $FND_TOP/bin/txkrun.pl -script=CfgOC4JApp -applicationname=forms -runautoconfig=No -oc4jpass=

Here : = welcome

Terminal O/p as follows :

Issue 2 : >>

*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS

*** Log File = /ramuG/mtlog/RAMUG_kiranginni01/logs/appl/rgf/TXK/txkCfgOC4JApp_Fri_Sep_9_04_58_37_2011.log

Program : /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl started @ Fri Sep 9 04:58:37 2011

*** Log File = /ramuG/mtlog/RAMUG_kiranginni01/logs/appl/rgf/TXK/txkCfgOC4JApp_Fri_Sep_9_04_58_37_2011.log

*****************************************************

Required values for starting OC4J instance "forms":

====================================================

s_formsstatus = enabled

s_forms_nprocs = 1 (value should be greater than 0)

Existing values from the context file:

======================================

s_formsstatus = enabled

s_forms_nprocs = 2

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

*** Values for context variables are VALID ***

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

*****************************************************

Stopping all OPMN processes.

OPMN stopped.

Errors encountered running /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl

*******FATAL ERROR*******

PROGRAM : /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl(/ramuG/applmgr/1200/fnd/12.0.0/bin/txkrun.pl)

TIME : Fri Sep 9 04:58:40 2011

FUNCTION: TXK::Process::run [ Level 3 ]

MESSAGES:

Command error: = 38400, = /ramuG/product/1013/opmn/bin/opmnctl startproc instancename=forms

STACK TRACE

TXK::Error::abort('TXK::Error','HASH(0x9c242bc)') called at /ramuG/applmgr/1200/au/12.0.0/perl/TXK/Common.pm line 299

TXK::Common::doError('TXK::Process=HASH(0xa4d4a70)','Command error: = 38400, = /ramuG/product/1013...','undef') called at /ramuG/applmgr/1200/au/12.0.0/perl/TXK/Common.pm line 314

TXK::Common::setError('TXK::Process=HASH(0xa4d4a70)','Command error: = 38400, = /ramuG/product/1013...') called at /ramuG/applmgr/1200/au/12.0.0/perl/TXK/Process.pm line 449

TXK::Process::run('TXK::Process=HASH(0xa4d4a70)','HASH(0xa387b30)') called at /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl line 1587

TXK::RunScript::execOPMNControl('HASH(0xa5715e0)') called at /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl line 606

require /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl called at /ramuG/applmgr/1200/au/12.0.0/perl/TXK/RunScript.pm line 105

TXK::RunScript::require('TXK::RunScript','/ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl') called at /ramuG/applmgr/1200/au/12.0.0/perl/TXK/Script.pm line 177

eval {...} called at /ramuG/applmgr/1200/au/12.0.0/perl/TXK/Script.pm line 177

TXK::Script::run('TXK::Script=HASH(0xa3ad6d8)','/ramuG/mtlog/RAMUG_kiranginni01/logs/appl/rgf/TXK','/ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl') called at /ramuG/applmgr/1200/fnd/12.0.0/bin/txkrun.pl line 174

$

Getting this Error

Upon Investigation found Note :

$FND_TOP/bin/txkrun.pl -script=DeployForms-c4ws Fails with Fatal Error forms-c4ws Already Exists (Doc ID 1165653.1)

Implementing the solution:

1. To get the /txkDeployForms-c4ws.pl to deploy, rename the file that already exists and run again.

Move the current deployment:

Example:

mv /devapps2/appldev/DPHUB/apps/tech_st/10.1.3/j2ee/forms-c4ws /devapps2/appldev/DPHUB/apps/tech_st/10.1.3/j2ee/forms-c4ws.ori

In our case :

$INST_TOP/ora/10.1.3/j2ee/forms-c4ws is moved and ran below script again :

perl $FND_TOP/bin/txkrun.pl -script=CfgOC4JApp -applicationname=forms -runautoconfig=No -oc4jpass=welcome

*****************************************************

Required values for starting OC4J instance "forms":

====================================================

s_formsstatus = enabled

s_forms_nprocs = 1 (value should be greater than 0)

Existing values from the context file:

======================================

s_formsstatus = enabled

s_forms_nprocs = 2

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

*** Values for context variables are VALID ***

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

*****************************************************

Stopping all OPMN processes.

OPMN stopped.

OPMN started.

Deplolying Application : "forms" onto OC4J instance: "forms"

Application deployed successfully.

Stopping and starting OC4J instances.

Started OC4J instances.

Binding webApp : "forms" with webmodule : "formsweb" for OC4J instance: "forms"

Web application bound successfully.

Stopping OPMN.

OPMN stopped.

Program : /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl completed @ Fri Sep 9 05:06:48 2011

End of /ramuG/applmgr/1200/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl : No Errors encountered

Now, Script successfully deployed…

Now reverting the copied script back to get original Password (This is must for security Concerns)....

c) Post-deployment step: ==> Done.

Reverting the value back to normal:

cp -p $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml.ori $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml

Thread 2:- >>>

==============

In some cases we face issue 3 if "s_formsstatus" is in DISABLED status in $CONTEXT_FILE.

Fix:

a) Take a backup of your $CONTEXT_FILE

b) Modify s_formsstatus to ENABLED

c) Run check Autoconfig : adchkcfg.sh and resolve any differences to preserve your customizations

d) Run Autoconfig to get it populated in the scripts.

e) Now run Deployment script

f) Revert s_formsstatus to DISABLED again (Original value)

g) Run Autoconfig to get it populated in the Scripts.

Thread 3:- >>

================

During check config we had issue that this is hanging. Script hanged after generating the differences.

Solution: you can ignore by pressing CTRL + C and utilize the generated differences to preserve customizations.

Note : once all the above procedure is done and autoconfig is successfully completed, you can see that this bug is rectified either. You can now verify that by running adchkcfg.sh again and see it completing successfully without hanging this time.

Thanks

Kiran Ginni

Issue : "paymxearnexrules.ldt" failed during HR RUP4

Activity : Performing R12.1 HR_PF.K RUP4

Current Apps version : 12.1.2

Database version: 11.1.0.7.0 - 64bit Production

During HRMS RUP4 Patching got below error while applying hrglobal driver.

While running hrglobal.drv "paymxearnexrules.ldt" worker failed.

************* Start of AD Worker session *************

AD Worker version: 12.0.0

AD Worker started at: Sat Aug 27 2011 15:59:40

APPL_TOP is set to /KIRANG/applmgr/1200

************* Start of AD Worker session *************

AD Worker version: 12.0.0

AD Worker started at: Sat Aug 27 2011 15:59:40

APPL_TOP is set to /KIRANG/applmgr/1200

************* Start of AD Worker session *************

AD Worker version: 12.0.0

AD Worker started at: Sat Aug 27 2011 15:59:40

APPL_TOP is set to /KIRANG/applmgr/1200

Program completed successfully

FAILED: file paymxearnexrules.ldt on worker 1 for product pay username APPS.

ATTENTION: All workers either have failed or are waiting:

FAILED: file paymxearnexrules.ldt on worker 1.

ATTENTION: Please fix the above failed worker(s) so the manager can continue.

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

From worker Logs :

Time when worker restarted job: Sat Aug 27 2011 15:59:45

Loading data using FNDLOAD function.

FNDLOAD APPS/***** 0 Y UPLOAD @PAY:patch/115/import/paymxearnexrules.lct

@PAY:patch/115/import/US/loc_mx/paymxearnexrules.ldt - LEG_VIEW=hr_pay_mx_implementation

Connecting to APPS......Connected successfully.

Calling FNDLOAD function.

Returned from FNDLOAD function.

Log file: /KIRANG/applmgr/1200/admin/KIRANG/log/loc_mx_paymxearnexrules_ldt.log

Error calling FNDLOAD function.

Time when worker failed: Sat Aug 27 2011 15:59:46

$ view /KIRANG/applmgr/1200/admin/KIRANG/log/loc_mx_paymxearnexrules_ldt.log

Current system time is Sat Aug 27 15:59:45 2011

From error Logs :

MX_EARN_EXEMPTION_RULES_F"."ELEMENT_CLASSIFICATION_ID")

Error loading seed data for MX_EARN_EXEMPTION_RULES: EFFECTIVE_START_DATE = 01/01/1900,

EFFECTIVE_END_DATE = 31/12/4712, TAX_TYPE = STATE, ELEMENT_CLASSIFICATION_NAME = Supplemental

Earnings:Social Foresight Earnings, STATE_CODE = SIN, ORA-01400: cannot insert NULL into

("HR"."PAY_MX_EARN_EXEMPTION_RULES_F"."ELEMENT_CLASSIFICATION_ID")

Error loading seed data for MX_EARN_EXEMPTION_RULES: EFFECTIVE_START_DATE = 01/01/1900,

EFFECTIVE_END_DATE = 31/12/4712, TAX_TYPE = STATE, ELEMENT_CLASSIFICATION_NAME = Aid for Meals,

STATE_CODE = TAMPS, ORA-01400: cannot insert NULL into

("HR"."PAY_MX_EARN_EXEMPTION_RULES_F"."ELEMENT_CLASSIFICATION_ID")

Error loading seed data for MX_EARN_EXEMPTION_RULES: EFFECTIVE_START_DATE = 01/01/1900,

EFFECTIVE_END_DATE = 31/12/4712, TAX_TYPE = STATE, ELEMENT_CLASSIFICATION_NAME = Aid for Rent,

STATE_CODE = TAMPS, ORA-01400: cannot insert NULL into

("HR"."PAY_MX_EARN_EXEMPTION_RULES_F"."ELEMENT_CLASSIFICATION_ID")

Error loading seed data for MX_EARN_EXEMPTION_RULES: EFFECTIVE_START_DATE = 01/01/1900,

EFFECTIVE_END_DATE = 31/12/4712, TAX_TYPE = STATE, ELEMENT_CLASSIFICATION_NAME = Attendance and

Punctuality Incentives, STATE_CODE = ZAC, ORA-01400: cannot insert NULL into

("HR"."PAY_MX_EARN_EXEMPTION_RULES_F"."ELEMENT_CLASSIFICATION_ID")

Error loading seed data for MX_EARN_EXEMPTION_RULES: EFFECTIVE_START_DATE = 01/01/1900,

EFFECTIVE_END_DATE = 31/12/4712, TAX_TYPE = STATE, ELEMENT_CLASSIFICATION_NAME = Aid for Meals,

STATE_CODE = ZAC, ORA-01400: cannot insert NULL into

("HR"."PAY_MX_EARN_EXEMPTION_RULES_F"."ELEMENT_CLASSIFICATION_ID")

Error loading seed data for MX_EARN_EXEMPTION_RULES: EFFECTIVE_START_DATE = 01/01/1900,

EFFECTIVE_END_DATE = 31/12/4712, TAX_TYPE = STATE, ELEMENT_CLASSIFICATION_NAME = Aid for Rent,

STATE_CODE = ZAC, ORA-01400: cannot insert NULL into

("HR"."PAY_MX_EARN_EXEMPTION_RULES_F"."ELEMENT_CLASSIFICATION_ID")

Concurrent request completed

Current system time is Sat Aug 27 15:59:46 2011

As per Note : 1301759.1 "" If you are NOT using MX Legislation, then simply skip this

error and continue on. "".

WE are not using MX , so ignoring and proceeding further.

**************************

Summary:

**************************

Issue : paymxearnexrules.ldt worker failed during HRMS Patching

Details :-

ATTENTION: All workers either have failed or are waiting:

FAILED: file paymxearnexrules.ldt on worker 1.

Error :

Error loading seed data for MX_EARN_EXEMPTION_RULES: EFFECTIVE_START_DATE = 01/01/1900,

EFFECTIVE_END_DATE = 31/12/4712, TAX_TYPE = STATE, ELEMENT_CLASSIFICATION_NAME = Aid for Meals,

STATE_CODE = ZAC, ORA-01400: cannot insert NULL into

("HR"."PAY_MX_EARN_EXEMPTION_RULES_F"."ELEMENT_CLASSIFICATION_ID")

Error loading seed data for MX_EARN_EXEMPTION_RULES: EFFECTIVE_START_DATE = 01/01/1900,

EFFECTIVE_END_DATE = 31/12/4712, TAX_TYPE = STATE, ELEMENT_CLASSIFICATION_NAME = Aid for Rent,

STATE_CODE = ZAC, ORA-01400: cannot insert NULL into

("HR"."PAY_MX_EARN_EXEMPTION_RULES_F"."ELEMENT_CLASSIFICATION_ID")

Solution :

Ignoring the issue and proceeding further, as MX Legislations not installed in system..

Skip the worker using adctrl and proceed with further execution of the hrglobal driver...

Ref SR/Note : 1301759.1

******************************

Gather Schema Statistics Failed after Major Upgrades >> Few Common Issues..

Issue: Customer reported "Gather Schema Statistics" concurrent program Erroring out after Major Upgrades as part of PMP.

Troubleshooting:

Reviewed the history and found that request ran successfully earlier 2weeks to this Task.

Req ID : 9065020 Gather Schema Statistics

Error logs :-

ORACLE error 6550 in FDPSTP

Cause: FDPSTP failed due to ORA-06550: line 1, column 7:

PLS-00307: too many declarations of 'GATHER_SCHEMA_STATS' match this call

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

.

After searching Metalink found below related note :

Gather Schema Statistics Fails with PLS-00307: Too Many Declarations Of GSS (Doc ID 951033.1)

Reviewed and worked accordingly

SQL> select ARGUMENT_TEXT from fnd_concurrent_requests where request_id = '9065020';

ARGUMENT_TEXT

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

ALL, 25, 3, NOBACKUP

Compared to the list of parameters to the successful Request ID, and found that there are many other parameters .

Also took double confirmation comparing with that of Production Instance

Request ID with parameters ALL, 10, 4, NOBACKUP, , LASTRUN, GATHER AUTO, , Y ==>> for Gather Schema Statistics IN Prod

Workaround 1:-

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

Ran new request with complete parameters similar to that of Production...

9087325 ==> ALL, 25, 3, NOBACKUP, , LASTRUN, GATHER AUTO, , Y

Now request went bit further and failed again...

******************************************************

****** AGAIN Request Failed , But for diff error *****

******************************************************

New Error Information:

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

Unable to correctly update the history table - fnd_stats_hist.

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

Unable to correctly update the history table - fnd_stats_hist.

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

Unable to correctly update the history table - fnd_stats_hist.

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

Unable to correctly update the history table - fnd_stats_hist.

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

Unable to correctly update the history table - fnd_stats_hist.

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

ORA-20005: object statistics are locked (stattype = ALL)

+---------------------------------------------------------------------------+

End of log messages from FND_FILE

+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+

Executing request completion options...

Finished executing request completion options.

+---------------------------------------------------------------------------+

Concurrent request completed

Current system time is 28-AUG-2011 16:51:59

+--------------------------------------------------------------------------

Researched again for this new error in Metalink... and found below note

Workaround 2:

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

Solution: Support Note 781813.1

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.

Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

SQL> select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1; 2 3 4

TABLE_NAME COLUMN_NAME COUNT(*)

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

JE_FR_DAS_010 TYPE_ENREG 2

JE_FR_DAS_010_NEW TYPE_ENREG 2

JE_BE_LINE_TYPE_MAP SOURCE 2

JG_ZZ_SYS_FORMATS_ALL_TL JGZZ_EFT_TYPE 2

JE_BE_LOGS DECLARATION_TYPE_CODE 2

JE_CH_PAYMENT_REF PAYMENT_TYPE 2

JG_ZZ_SYS_FORMATS_ALL_B JGZZ_EFT_TYPE 2

JE_BE_VAT_REP_RULES LINE_TYPE 2

JE_BE_VAT_REP_RULES SOURCE 2

JE_BE_VAT_REP_RULES VAT_REPORT_BOX 2

10 rows selected.

-- Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS

where table_name = '&TABLE_NAME'

and column_name = '&COLUMN_NAME'

and rownum=1;

-- Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS

where (table_name, column_name) in

(

select hc.table_name, hc.column_name

from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

where hc.table_name ='&TABLE_NAME'

and hc.table_name= tc.table_name (+)

and hc.column_name = tc.column_name (+)

and tc.column_name is null

);

Deleted all the duplicates .. IN my case I dont have any obsolete rows.. SO next queue is not useful this time...

Verification:

SQL> select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1; 2 3 4

no rows selected

SQL> sho user

USER is "APPS"

9087344 == > Gather Schema statistics >>> Failed again....

******************************************************

****** AGAIN Request Failed , But for diff error *****

******************************************************

After research found that

ORACLE error 20005 in FDPSTP

Cause: FDPSTP failed due to ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "APPS.FND_STATS", line 806

ORA-06512: at line 1

.

General Procedure:

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

Check all the tables which come under Locked status :

select owner,table_name from dba_tab_statistics where stattype_locked is not null;

For each and every table , unlock using below query

dbms_stats.unlock_table_stats(OWNER,TABLE);

Finally Now execute the Gather Schema Statistics and see its getting successful.

Ex : 9087399 ==> Gather Schema Statistics >>> Completed Normal

Thanks

Kiran Ginni