Oacore Services Showing Error While Starting

 Dear Folks,

We faced issue recently that OACORE services were started as ADMIN than in RUNNING mode.

Error in log file.

Failed to load webapp: /OA_HTML because of DeploymentException: java.lang.NullPointerException

at weblogic.servlet.internal.WebAppModule.prepare(WebAppModule.java:397)

Fix

==

$FND_TOP/bin/txkrun.pl -script=ChkEBSDependecies -server=ALL_SERVERS

Compile JSP

cd $FND_TOP/patch/115/bin

ojspCompile.pl --compile --flush

stop and start oacore

admanagedsrvctl.sh stop oacore_server1

admanagedsrvctl.sh start oacore_server1

admanagedsrvctl.sh stop oacore_server3

admanagedsrvctl.sh start oacore_server3

Thanks.

Deduction report performance in R12 instance.

 Dear Folks,

One of our customer had an experience with program "Deduction Report" which taking long time to complete. Investigated AWR report during the time period and didn't have anything to drill down. Hence we lodged an SR with oracle and they give simple SQL which simply change VIEW on problematic table.

But before execute this SQL in prod. Make sure it is tested in non-prod instance.

Fix : 

===

OWNER      OBJECT_NAME     CREATED     OBJECT_TYPE     LAST_DDL_TIME  STATUS

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

APPS      PAY_US_DEDUCTIONS_RE 25-NOV-08    VIEW         15-AUG-20    VALID

        PORT_RBR_V

SQL> select name from v$database;

NAME

---------

KTMI

SQL> @PAY_US_DEDUCTIONS_REPORT_RBR_V1.sql

View created.

SQL>

OWNER      OBJECT_NAME          CREATED     OBJECT_TYPE     LAST_DDL_TIME  STATUS

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

APPS      PAY_US_DEDUCTIONS_REPORT_RBR_V 25-NOV-08    VIEW         03-DEC-20    VALID

SQL : PAY_US_DEDUCTIONS_REPORT_RBR_V1.sql

====

CREATE OR REPLACE FORCE VIEW "APPS"."PAY_US_DEDUCTIONS_REPORT_RBR_V" ("CONSOLIDATION_SET_ID", "CONSOLIDATION_SET_NAME", "PAYROLL_ID", "PAYROLL_NAME", "PAYROLL_EFFECTIVE_START_DATE", "PAYROLL_EFFECTIVE_END_DATE", "PAYROLL_ACTION_ID", "PAYROLL_ACTION_EFFECTIVE_DATE", "PAYROLL_ACTION_DATE_EARNED", "TIME_PERIOD_ID", "PERIOD_NAME", "PERIOD_NUM", "PERIOD_TYPE", "PERIOD_START_DATE", "PERIOD_END_DATE", "ASSIGNMENT_ACTION_ID", "ASSIGNMENT_ID", "TAX_UNIT_ID", "GRE", "PERSON_ID", "EMPLOYEE_NUMBER", "FULL_NAME", "ORGANIZATION_ID", "ORGANIZATION_NAME", "LOCATION_ID", "LOCATION_CODE", "LOCATION_DESCRIPTION", "ASSIGNMENT_SEQUENCE", "ASSIGNMENT_NUMBER", "ELEMENT_TYPE_ID", "ELEMENT_NAME", "ELEMENT_DESCRIPTION", "BUSINESS_GROUP_ID", "PRIMARY_BALANCE_ID", "HOURS_BALANCE_ID", "CLASSIFICATION_ID", "CLASSIFICATION_NAME", "PRIMARY_BALANCE", "NOT_TAKEN_BALANCE", "ARREARS_BALANCE", "ACCRUED_BALANCE", "TOTAL_OWED") AS 

  SELECT /*+ ordered no_concat index(paa PAY_ASSIGNMENT_ACTIONS_N50) use_nl(ppa pap) 

           use_nl(ppa pcs) use_nl(ppa ptp) use_nl(ppa paa paas papp) use_nl(paa prb)

           use_nl(paa hou1) use_nl(paas hou) use_nl(paas hrl) use_hash(pet)

           use_hash(pba) use_nl(pdb) use_nl(pbad) */  

        ppa.consolidation_set_id consolidation_set_id

       ,pcs.consolidation_set_name consolidation_set_name

       ,ppa.payroll_id payroll_id

       ,pap.payroll_name payroll_name

       ,pap.effective_start_date payroll_effective_start_date

       ,pap.effective_end_date payroll_effective_end_date

       ,ppa.payroll_action_id payroll_action_id

       ,ppa.effective_date payroll_action_effective_date

       ,ppa.date_earned payroll_action_date_earned

       ,ptp.time_period_id time_period_id

       ,ptp.period_name period_name

       ,ptp.period_num period_num

       ,ptp.period_type period_type

       ,ptp.start_date period_start_date

       ,ptp.end_date period_end_date

       ,paa.assignment_action_id assignment_action_id

       ,paa.assignment_id assignment_id

       ,paa.tax_unit_id tax_unit_id

       ,hou1.name gre

       ,paas.person_id person_id

       ,papp.employee_number employee_number

       ,papp.full_name full_name

       ,paas.organization_id organization_id

       ,hou.name organization_name

       ,paas.location_id location_id

       ,hrl.location_code location_code

       ,hrl.description location_description

       ,paas.assignment_sequence assignment_sequence

       ,paas.assignment_number assignment_number

       ,pet.element_type_id element_type_id

       ,pet.element_name element_name

       ,pet.description element_description

       ,pba.business_group_id business_group_id

       ,pet.element_information10 primary_balance_id

       ,pet.element_information11 hours_balance_id

       ,pec.classification_id classification_id

       ,pec.classification_name classification_name

       ,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)

                                                   ,'ASG_GRE_RUN'

                                                   ,paa.assignment_action_id

                                                   ,paa.assignment_id

                                                   ,NULL

                                                   ,paa.tax_unit_id

                                                   ,pba.business_group_id

                                                   ,NULL) primary_balance

       ,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)

                                                    || ' NOT TAKEN'

                                                   ,'ASG_GRE_RUN'

                                                   ,paa.assignment_action_id

                                                   ,paa.assignment_id

                                                   ,NULL

                                                   ,paa.tax_unit_id

                                                   ,pba.business_group_id

                                                   ,NULL) not_taken_balance

       ,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)

                                                    || ' ARREARS'

                                                   ,'ASG_GRE_RUN'

                                                   ,paa.assignment_action_id

                                                   ,paa.assignment_id

                                                   ,NULL

                                                   ,paa.tax_unit_id

                                                   ,pba.business_group_id

                                                   ,NULL) arrears_balance

       ,decode (sign (to_number (decode (pet.element_information11

                                        ,NULL

                                        ,NULL

                                        ,pay_us_dedn_pkg.pay_us_tot_owed (paa.assignment_id

                                                                         ,pet.element_type_id

                                                                         ,ppa.effective_date

                                                                         ,ppa.date_earned))))

               ,NULL

               ,NULL

               ,- 1

               ,NULL

               ,0

               ,NULL

               ,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)

                                                            || ' ACCRUED'

                                                           ,'ASG_GRE_ITD'

                                                           ,paa.assignment_action_id

                                                           ,paa.assignment_id

                                                           ,NULL

                                                           ,paa.tax_unit_id

                                                           ,pba.business_group_id

                                                           ,NULL

                                                           ,pec.classification_name

                                                           ,'ENTRY_ITD'

                                                           ,NULL

                                                           ,pet.element_type_id)) accrued_balance

       ,to_number (decode (pet.element_information11

                          ,NULL

                          ,NULL

                          ,pay_us_dedn_pkg.pay_us_tot_owed (paa.assignment_id

                                                           ,pet.element_type_id

                                                           ,ppa.effective_date

                                                           ,ppa.date_earned))) total_owed

FROM    pay_payroll_actions ppa

       ,pay_consolidation_sets pcs

       ,pay_all_payrolls_f pap

       ,per_time_periods ptp

       ,pay_assignment_actions paa

       ,hr_all_organization_units hou1

       ,per_all_assignments_f paas

       ,hr_organization_units hou

       ,hr_locations_all hrl

       ,per_all_people_f papp

       ,pay_run_balances prb

       ,pay_balance_attributes pba

       ,pay_bal_attribute_definitions pbad

       ,pay_defined_balances pdb

       ,pay_element_types_f pet

       ,pay_element_classifications pec

WHERE   ppa.action_type IN ('Q','R','V'

                           ,'B','I')

AND     ppa.action_status = 'C'

AND     ppa.consolidation_set_id = pcs.consolidation_set_id

AND     ppa.payroll_id IS NOT NULL

AND     ppa.payroll_id = pap.payroll_id

AND     nvl (ppa.date_earned

            ,ppa.effective_date) BETWEEN pap.effective_start_date

                                                      AND     pap.effective_end_date

AND     ppa.time_period_id = ptp.time_period_id

AND     paa.payroll_action_id = ppa.payroll_action_id

AND     paa.action_status = 'C'

AND     paa.tax_unit_id = hou1.organization_id

AND     paas.assignment_id = paa.assignment_id

AND     nvl (ppa.date_earned

            ,ppa.effective_date) BETWEEN paas.effective_start_date

                                                      AND     paas.effective_end_date

AND     paas.organization_id = hou.organization_id

AND     paas.location_id = hrl.location_id

AND     pbad.attribute_name IN ('PAY_US_PRE_TAX_DEDUCTIONS','PAY_US_AFTER_TAX_DEDUCTIONS','PAY_US_EMPLOYER_LIABILITY')

AND     pbad.legislation_code = 'US'

AND     pba.attribute_id = pbad.attribute_id

AND     prb.assignment_action_id = paa.assignment_action_id

AND     prb.defined_balance_id = pba.defined_balance_id

AND     pba.defined_balance_id = pdb.defined_balance_id

AND     pba.business_group_id = pet.business_group_id

AND     (

                nvl (translate (pet.element_information10

                               ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

                               ,'01234567890000000000000000000000000000000000000000000000000000')

                    ,'0') = pdb.balance_type_id

        OR      nvl (translate (pet.element_information11

                               ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

                               ,'01234567890000000000000000000000000000000000000000000000000000')

                    ,'0') = pdb.balance_type_id

        OR      nvl (translate (pet.element_information12

                               ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

                               ,'01234567890000000000000000000000000000000000000000000000000000')

                    ,'0') = pdb.balance_type_id

        OR      nvl (translate (pet.element_information13

                               ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

                               ,'01234567890000000000000000000000000000000000000000000000000000')

                    ,'0') = pdb.balance_type_id

        )

AND     nvl (ppa.date_earned

            ,ppa.effective_date) BETWEEN pet.effective_start_date

                                                      AND     pet.effective_end_date

AND     pec.legislation_code = 'US'

AND     pec.classification_name IN ('Pre-Tax Deductions','Involuntary Deductions','Voluntary Deductions'

                                   ,'Employer Liabilities')

AND     pet.classification_id = pec.classification_id

AND     paas.person_id = papp.person_id

AND     nvl (ppa.date_earned

            ,ppa.effective_date) BETWEEN papp.effective_start_date

                                                      AND     papp.effective_end_date

GROUP BY ppa.consolidation_set_id

        ,pcs.consolidation_set_name

        ,ppa.payroll_id

        ,pap.payroll_name

        ,pap.effective_start_date

        ,pap.effective_end_date

        ,ppa.payroll_action_id

        ,ppa.effective_date

        ,ppa.date_earned

        ,ptp.time_period_id

        ,ptp.period_name

        ,ptp.period_num

        ,ptp.period_type

        ,ptp.start_date

        ,ptp.end_date

        ,paa.assignment_action_id

        ,paa.assignment_id

        ,paa.tax_unit_id

        ,hou1.name

        ,paas.person_id

        ,papp.employee_number

        ,papp.full_name

        ,paas.organization_id

        ,hou.name

        ,paas.location_id

        ,hrl.location_code

        ,hrl.description

        ,paas.assignment_sequence

        ,paas.assignment_number

        ,pet.element_type_id

        ,pet.element_name

        ,pet.description

        ,pba.business_group_id

        ,pet.element_information10

        ,pet.element_information11

        ,pec.classification_id

        ,pec.classification_name;

Thanks.

ORA-20001: APP_FND_01972 Error in FND_USER_REPS_GROUPS_API

 Dear Folks,

One of our customer had an experience with following error when he was trying to add/remove end date on a responsibility to user.

Oracle error-20001: ORA-20001: APP_FND_01972 Errron in FND_USER_REPS_GROUPS_API. Update_Assignment:

FIX :- 

Oracle error-20001:APP-FND-01972: When Trying To End Date A Responsibility Assigned To A User (Doc ID 1987250.1)

1. Run the program "Workflow Directory Services User/Role Validation" once with the following parameters:

Fix Dangling User/Roles

Default Batchsize=10000

Fix Dangling User/Roles=Yes

Add Missing User/Role Assignments=No

2. After it completes, run program "Workflow Directory Services User/Role Validation" again with the following parameter:

Add Missing User/Role Assignments

Default Batchsize=10000

Fix Dangling User/Roles=No

Add Missing User/Role Assignments=Yes

TNS-12547: TNS:lost contact

Dear Team, 

Recently we have experienced connectivity issue between application node to DB node on database port which set to 1526.

Though listener is up and running on db node, we couldn't able to connect to apps and says "TNS lost Contact". 

Tried to ping from application node and eventually failed and telnet connection immediately closed.

NONPROD [******* ~]$ telnet kialampnldb01.******.**** 1526

Trying 10.174.129.6...

Connected to kialampnldb01.******.****.

Escape character is '^]'.

Connection closed by foreign host.

NONPROD [******* ~]$ tnsping KAMPK

TNS Ping Utility for Linux: Version 10.1.0.5.0 - Production on 06-NOV-2020 21:26:48

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:

/u01/app/*******/KAMPK/fs1/inst/apps/KAMPK_kialampnlap01/ora/10.1.2/network/admin/sqlnet_ifile.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=kialampnldb01.******.****)(PORT=1526)) (CONNECT_DATA= (SERVICE_NAME=KAMPK) (INSTANCE_NAME=KAMPK)))

TNS-12547: TNS:lost contact

NONPROD [******* ~]$

===========

Hence, its clearly know, we might have unwanted entries in sqlnet.ora file. Hence we checked on DB node and found below two entries.

tcp.validnode_checking = yes

tcp.invited_nodes=(kialampnldb01.******.****)

We commeneted above in sqlnet.ora and restarted listener then application was able to connect to DB and able to telnet.

To know about tcp parameters in sqlnet.oracle. kindly see below oracle document.

https://docs.oracle.com/en/database/oracle/oracle-database/18/netrf/parameters-for-the-sqlnet-ora-file.html#GUID-5C3AB641-7541-4CE9-BC9E-BA5DD30616A8

5.2.75 TCP.INVITED_NODES

FRM-40735: PRE-BLOCK TRIGGER RAISED UNHANDLED EXCEPTION ORA-4068

 Dear Folks,

The title error we have encountered when we tried to access the form (Orders, Returns)

Navigation - Order Management Super User--> Orders, Returns--> Order Organizer

It may due to  schema / PL/SQL code timestamp mismatches in your environment.

ML - 981240.1

++++++++++

1. set _disable_fast_validate=TRUE in init.ora file and restart the DB in upgrade mode.

2. Invalidate all the objects - @$ORACLE_HOME/rdbms/admin/utlirp.sql

3. Recompile all the objects - @$ORACLE_HOME/rdbms/admin/utlrp.sql

4. Restart DB in normal mode. 

5. Confirm there are no more timestamp mismatches with the query.

$ORACLE_HOME/rdbms/admin/utldtchk.sql

After above work around. We are able to open form.

Thanks.

Sreeharsha

Log file locations in Oracle Apps 11i/R12

Log file locations in Oracle Apps 11i/R12

++++++++++++++++++++++++++++++++++++++

                                Database Tier Logs are

Alert Log File location:

$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

 

                                     Trace file location:

$ORACLE_HOME/admin/SID_Hostname/udump

 

                                         Application Tier Logs

Start/Stop script log files location:

$COMMON_TOP/admin/log/CONTEXT_NAME/ 

 

                                       OPMN log file location

$ORACLE_HOME/opmn/logs/ipm.log

                                   Apache, Jserv, JVM log files locations:

$IAS_ORACLE_HOME/Apache/Apache/logs/ssl_engine_log

$IAS_ORACLE_HOME/Apache/Apache/logs/ssl_request_log

$IAS_ORACLE_HOME/Apache/Apache/logs/access_log

$IAS_ORACLE_HOME/Apache/Apache/logs/error_log

$IAS_ORACLE_HOME/Apache/JServ/logs

 

                                      Concurrent log file location:

$APPL_TOP/admin/PROD/log or $APPLLOG/$APPLCSF 

                                           Patch log file location:

$APPL_TOP/admin/PROD/log

                                          Worker Log file location:

$APPL_TOP/admin/PROD/log

 

                                          AutoConfig log files location:

Application Tier:

$APPL_TOP/admin/SID_Hostname/log//DDMMTime/adconfig.log

 

Database Tier:

$ORACLE_HOME/appsutil/log/SID_Hostname/DDMMTime/adconfig.log

 

                                              Error log file location:

Application Tier:

$APPL_TOP/admin/PROD/log

 

Database Tier :

$ORACLE_HOME/appsutil/log/SID_Hostname

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

In Oracle Applications R12, the log files are located in $LOG_HOME (which translates to $INST_TOP/logs)

Below list of log file locations could be helpful for you:

                                  Concurrent Request logs files

$LOG_HOME/appl/conc – > location for concurrent requests log and out files

$LOG_HOME/appl/admin – > location for mid tier startup scripts log files

 

Apache Logs (10.1.3 Oracle Home which is equivalent to iAS Oracle Home – Apache, OC4J and OPMN)

$LOG_HOME/ora/10.1.3/Apache – > Location for Apache Error and Access log files

$LOG_HOME/ora/10.1.3/j2ee – > location for j2ee related log files

$LOG_HOME/ora/10.1.3/opmn – > location for opmn related log files

 

Forms & Reports related logs (10.1.2 Oracle home which is equivalent to 806 Oracle Home)

$LOG_HOME/ora/10.1.2/forms

$LOG_HOME/ora/10.1.2/reports

 

Startup/Shutdown Log files location:

$INST_TOP/apps/$CONTEXT_NAME/logs/appl/admin/log

 

Patch log files location:

$APPL_TOP/admin/$SID/log/ 

 

                      Clone and AutoConfig log files location in Oracle E-Business Suite Release 12 

 

                                               Logs for the adpreclone.pl are located: 

On the database tier: 

RDBMS $ORACLE_HOME/appsutil/log/< context >/StageDBTier_< timestamp >.log 

 

On the application tier: 

$INST_TOP/admin/log/StageAppsTier_< timestamp >.log 

Thanks.

ORA-06502: PL/SQL: numeric or value error: character to number conversion error 11i forms.

Dear Folks,

We have come across this issue in one of our EBS client runs on 11i version and  when we tried to open any forms eventually failing with below error immediately.

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

So, we got to know the actual issue when we directly accessed the forms.

https://ebsprod.kalicut-sensors.com/dev60cgi/f60cgi

We logged into using SYSADMIN and selected one of the responsibilities and below pop-pop window appeared. .

 

Hence, we pull off the package "FND_TIMEZONES and looked for line 42 & 55 as shown in above screenshot and identified  it throws error at below code.

function get_client_timezone_code return varchar2 is

begin

  return get_code(fnd_profile.VALUE ('CLIENT_TIMEZONE_ID'));

 end;

=======

So, precisely, it was issue with the "CLIENT_TIMEZONE_ID".

Since, we aren't having forms at this time, We looked when this profile got changed

Some one who had happened to be use apps password got to change the profile option value from backend (DB).

Below query we shows us "The profiles has changed recently for SYSADMIN"

column profile format a40

column value format a20

column level format a15

column app format a20

column resp format a20

column user format a20

select substr(fpo.user_profile_option_name,1,60) profile,

substr(fpov.profile_option_value,1,75) value,

fpov.profile_option_id,

decode(fpov.level_id,10001,

'SITE',10002,

'APPLICATION',10003,

'RESPONSIBILITY',

10004,'USER',10005,'SERVER')"level",

fa.application_short_name app,

substr(fr.responsibility_name,1,20)"responsibility",

substr(fu.user_name,1,10)"user"

from fnd_profile_option_values fpov,

fnd_profile_options_vl fpo,

fnd_application fa,

fnd_responsibility_vl fr,

fnd_user fu,

fnd_logins fl

where fpo.profile_option_id=fpov.profile_option_id

and fa.application_id(+)=fpov.level_value

and fr.application_id(+)=fpov.level_value_application_id

and fr.responsibility_id(+)=fpov.level_value

and fu.user_id(+)=fpov.level_value

and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN

and fpo.user_profile_option_name in('Client Timezone') and fu.USER_NAME='SYSADMIN';

So, some one wrongly updated the value of this profile for SYSADMIN user.

PROFILE                 VALUE        PROFILE_OPTION_ID level      APP

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

responsibility                        user

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

Client Timezone             '                 3773 USER      FND

                               SYSADMIN

It shows above value  -- Colon ('). It shouldn't be this value.

Hence, we compare this profile with other non-prod instances and it was showed EMTPY value.

As soon as we updated above profile value to correct value. We were able to open forms.

Command we used - It changes profile option value at user level.

++++++++++++++

declare

retval boolean;

cursor cur is select * from fnd_user where user_name= 'SYSADMIN';

begin

for rec in cur

loop

dbms_output.put_line ( rec.user_name );

retval:=fnd_profile.save('CLIENT_TIMEZONE_ID','','USER',rec.user_id);

end loop;

end;

/ 

Thanks.