Wednesday, July 6, 2011

Error: ORA-20002: 3108: Root process could not be determined for item

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    Error: ORA-20002: 3108: Root process could not be determined for item ..
On 11.5.10.2 in Production:
When attempting to change the status of a task in Service Request form,
the following error occurs:

ERROR
-----------------------
Failed to Call CompleteActivity to proceed the task workflow. Error: ORA-20002: 3108: Root process could not be determinted for item 'XXBPA/497010'. There may be a problem with the item type selector function, or you may be required to supply a starting process name.

-- Steps To Reproduce:
The issue can be reproduced at will with the following steps:
1. Query for SR with tasks
2. Go to Tasks tab
3. Change task status > save
Above error will occur

Solution:
Work Around:
The issue could be in Workflow Business Event Subscription.

Create and start process was not working. For a particular itemkey , run the create and start process manually

Then all the processes have started working


    

Get Oracle Certifications for all Exams
Free Online Exams.com

Fatal error in Accounting Entries Exceptions Report

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:   
Fatal error in Accounting Entries Exceptions Report

Log files:
Payables_Accounting_Process_280609.txt reports fatal error for IMDAAD LLC 102736
Account From Date: 01-JUN-09 Submit Transfer to GL: Yes Summarize Report: No
Account To Date: 28-JUN-09 Submit Journal Import: No Invoice Batch Name:
Document Class: All Validate Accounts: Yes Payment Batch Name:

Solution:
Execute the following steps::
1. Download undoatg.zip from ftp://ftp.oracle.com/support/hidden/patchsets/AP/scripts/undoatg.zip
If you already have a copy of this script from a previous SR verify that you have version
undoatgh 1.8 or higher. If not download a new copy. The version can be found on the first
line of the undoatgh.sql script.

2. Stop all users from using concurrent or on-line accounting until this process is finished.

3. Create a table of the events to undo.

create table SR_7662297_992 as
select distinct accounting_event_id
from ap_accounting_events_all
where accounting_event_id in (134530, 134531, 134572)
and source_id in (117733);

4. Undo the Events and report for later GL manipulation

Run UndoAtgh.sql
Enter Org ID: 358
Enter option 1 for Display Selected Event Details
Enter option 1 to display AP Accounting Data
Enter option 3 for Accounting Events in an Existing Table
Enter table name = SR_7662297_992
Undo the Events Now: Y
Enter Y to Sweep to Currently Open Period
Enter the Currently Open Period Name: XXXXXX (any valid period that's currently open)
Enter N to not delete events IDs (You will only get this prompt on 11i.AP.K and above)

COMMIT; <<< the script does not self-commit


5. Run the Payables Accounting process with the following parameters:

Account from date:
Account to date:
Document class: ALL
Validate accounts: Y
Debug: Y

If your concurrent process does not show a debug parameter, please use note 198799.1 to
display this parameter and generate a debug log of the Accounting process.

Verify there are no exceptions on the output.


6. Allow users to run the accounting process again.

7. Upload a fresh aplist for invoice_id 117733
Relevant Research => Analysis



   
Get Oracle Certifications for all Exams
Free Online Exams.com

bkp_restore.sh -m backup_instance_online "Another active BR session is in progress, please try later"

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    bkp_restore.sh -m backup_instance_online "Another active BR session is in progress, please try later"

The command "bkp_restore.sh -m backup_instance_online" fails with the following error :
"Another active BR session is in progress, please try later"
This message appears even though there is no other bkp_restore.sh is currently running.

Symptoms:

While running online application server backup I notice the following error
another active BR session is in progress, please try later.

Before I noticed that previous week backup is still showing on the processes.


Solution:

Kill the already existing backup and proceed for a new run of the backup script.

References:

http://download.oracle.com/docs/cd/B14099_19/core.1012/b13995/br_rec.htm#i1011125


    
Get Oracle Certifications for all Exams
Free Online Exams.com

Error in Workflow WCAPPRV/2009-164 ORA-01403: no data found ORA-01403: no data found

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com



Problem Description: 
An Error occurred in the following Workflow.

Item Type = WCAPPRV
Item Key = 2009-164
User Key =

Error Name = 100
Error Message = ORA-01403: no data found
ORA-01403: no data found
Error Stack = 
Wf_Engine_Util.Function_Call(POS_WCAPPROVE_PVT.SET_STARTUP_VALUES, WCAPPRV, 2009-164, 211371, RUN)

Activity Id = 211371
Activity Label = START_OF_APPROVE_WC:SET_WC_STARTUP_VALUES
Result Code = #EXCEPTION
Notification Id = 
Assigned User =




Solution:
just abort the workflow with workflow admin then when the next purge will run it should disappear.



     
Get Oracle Certifications for all Exams
Free Online Exams.com

Changing Automatic Offsets after transactions have already been created and accounted

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    Changing Automatic Offsets after transactions have already been created and accounted

Symptoms:
Changing Automatic Offsets after transactions have already been created and accounted can cause issues:
ENABLING/DISABLING AUTOMATIC OFFSETS
Current functionality within Oracle Payables allows the value of the Automatic Offset option in the
Payables Option form to be changed at any time. This is because Oracle does recognize that there
could be a business operational change that results in a one-time need to enable or disable
Automatic Offsets. Therefore, Oracle has tried to accommodate for this scenario instead of disallowing
changes to this option that would require a separate operating unit to be created.

However the ability to change this setting is not to be misused and 'toggled' for any reason.
Should you choose to 'toggle' this option, neither Oracle Support nor Oracle Development will support
any problems caused by this. Incorrect accounting or incorrect trial balance accounts are examples
of problems that may arise because of this. You will have to contact Oracle Consulting to have the
accounting and/or trial balance data corrected.


Solution:
If your business is in urgent need for switching the option, you have to:
1- account all your periods and close all of them .
2- Make sure that the reconciliation between Ap and Gl is is perfect, and no in stuck issues , you can use Note 452536.1 & Note 344367.1 .
3-Choose a new fresh period that, HAVE NO TRANSACTIONS YET , to switch the option .
(Switching while in the middle of transactions process cause them to stop accounting, which will be out of support).
4-Review, well, the impact of the change in your business needs.
5-You can search more resources concerning the subject Via Metalink to have additional information .


     
Get Oracle Certifications for all Exams
Free Online Exams.com

SQL Query To Get History Information Of A Metric From Repository Database in Grid Control

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    
SQL Query To Get History Information Of A Metric From Repository Database in Grid Control

Solution:
You have to write custom query for the requirement specified. Please refer the following note that provides an example on how to create custom report:
Note 557372.1 - How to create a tabular Report for CPU / Memory usage of Hosts over a period of time

"MGMT$METRIC_DAILY" is the table used to store the metric details of the target. You have to query the table "MGMT$METRIC_DAILY" to get the required information.
For example, for CPU usage% per database, you can use the metric "Average Instance CPU (%)" available under database metrics and following query can be used in the report to get CPU% per database instance:

SELECT rollup_timestamp "Date", average "CPU Usage"
FROM MGMT$METRIC_DAILY
WHERE target_name ='<database instance name>'
AND metric_name = 'wait_bottlenecks'
AND metric_column = 'avg_user_cpu_time_pct'
AND rollup_timestamp <= <date>;

Similarly you can verify the metric that will give the desired output and can query the MGMT$METRIC_DAILY for getting the required information.

For available database metrics, refer the following link:
http://download.oracle.com/docs/cd/B16240_01/doc/em.102/b25986.pdf

Also you can use any of the repository views available in the following documentation to get the required results: This is available at :
http://download-east.oracle.com/docs/cd/B16240_01/doc/nav/portal_booklist.htm
Oracle® Enterprise Manager Extensibility 10g Release 5 (10.2.0.5),
Chapter 9 - Management Repository Views

To get the correct metric name, metric column details related to database:

1. Login to grid control.
2. Navigate to Targets --> Databases.
3. Go to respective database home page.
3. Click on "All Metrics" link under 'Related Links".
4. Click on the required metric, the following link will be displayed in the browser:

https://hostname.oracle.com:1159/em/console/monitoring/metricDetail$
ctxType=Databases$keyValue=MGMT*_TABLESPACE$target=agni$metric=problemTbsp$
pageType=byDay$type=oracle*_database$metricColumn=pctUsed

From this link, it is clear that:

target type = oracle_database
target name = agni
metric name = problemTbsp
metric column = pctUsed
Key Value = MGMT_TABLESPACE

Similar steps can be followed to retrieve the other metric information related to other target types as well.


MGMT_VIEW user is responsible for generating reports in grid control. MGMT_VIEW user has access to the repository views as specified in the following document:
http://download.oracle.com/docs/cd/B16240_01/doc/em.102/b40007/toc.htm
Reference: Chapter 9 - Management Repository Views

To create reports on other tables, grant permissions to MGMT_VIEW user as MGMT_VIEW is responsible for creating reports in grid control.
SQL> GRANT SELECT on SYSMAN.<table_name> to MGMT_VIEW;

And then create the reports in the grid control.
References:

Note 557372.1 - How to create a tabular Report for CPU / Memory usage of Hosts over a period of time
Note: 828994.1 - Sample SQL Query To Get History Information Of A Metric From Repository Database


    
Get Oracle Certifications for all Exams
Free Online Exams.com

Problem Description: Error in Workflow PAXWFHRU/806 3205: 'PASYSADMIN' is not a valid role or user name.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    
Problem Description: Error in Workflow PAXWFHRU/806 3205: 'PASYSADMIN' is not a valid role or user name. 

Error in Workflow POAPPRV/74-778 ORA-06512: at line 

Error in Workflow POAPPRV/239-233 3103: Attribute 'DOCUMENT_ID' does not exist for item 'POAPPRV/239-233'.

Error in Workflow WCAPPRV/2009-164 ORA-01403: no data found ORA-01403: no data found






An Error occurred in the following Workflow.

Item Type = PAXWFHRU
Item Key = 806
User Key =

Error Name = WFNTF_ROLE
Error Message = 3205: 'PASYSADMIN' is not a valid role or user name.
Error Stack =
Wf_Notification.Send(PASYSADMIN, PAXWFHRU, MSG_ASSIGNMENT_UPD_FAIL, WF_ENGINE.CB)
Wf_Engine_Util.Notification_Send(PAXWFHRU, 806, 189353, PAXWFHRU:MSG_ASSIGNMENT_UPD_FAIL)
Wf_Engine_Util.Notification(PAXWFHRU, 806, 189353, RUN)

Activity Id = 189353
Activity Label = PROCESS_ASSIGNMENT_CHANGES:NTF_ASSIGNMENT_UPD_FAIL
Result Code = #EXCEPTION
Notification Id =
Assigned User =



Solution:
As per note Error In Workflow PAXWFHRU/1194 3205: 'PASYSADMIN' Is Not A Valid Role Or User Name. Where is PASYSADMIN Defined? (Doc ID 1320112.1):

Oracle® Projects Implementation Guide Release 12.1 states:

Implementing the PA: HR Related Updates Workflow
You need to define the PASYSADMIN user. The PASYSADMIN user will receive notifications sent by the PA: HR Related Updates workflow processes. For information on defining users, see System Administration Setup Tasks, Oracle Applications System Administrator's Guide – Configuration.

So it sounds like a user is created in the System Administrator responsibility | Security | User | Define User name = PASYSADMIN that has an active email address. Once defined, then these workflows should complete successfully.
References:
Error In Workflow PAXWFHRU/1194 3205: 'PASYSADMIN' Is Not A Valid Role Or User Name. Where is PASYSADMIN Defined? (Doc ID 1320112.1):



     

Get Oracle Certifications for all Exams
Free Online Exams.com

APP-AR-42536 this transaction is associated with at least one or end-dated GL account. Please correct the account.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    APP-AR-42536 this transaction is associated with at least one or end-dated GL account. Please correct the account.
Correct the receivable account assignment (receivable A/C CC is valid in GL)

Auto invoice Import program is not recognizing valid receivable code combination 6005-0000-121100-000000-0000-000-000-000
System is not allowing completing transaction today
error message: "APP-AR-42536 this transaction is associated with at
least one or end-dated GL account. Please correct
the account."

We were not able to enter single AR Receipt, AR Transaction and could not import AR transactions so delayed the period closing.

Symptoms:
>Submit Auto Invoice master Program
>Open transaction 13514 & click "Complete" Button
Log files:
Autoinvoice_Import_Program_020709.txt
Contains:

RAXTRX module: Auto invoice Import Program

Error calling afpoget() - AR_ALLOW_TRX_LINE_EXEMPTIONS

Solution:
Correct the receivable account assignment (receivable A/C CC is valid in GL




    
Get Oracle Certifications for all Exams
Free Online Exams.com

R12.0.6+ : Purchasing PO Approval Data Collection Test

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    
R12.0.6+ : Purchasing PO Approval Data Collection Test 

This test collects all the data related to approvals for any purchasing document type.
Use this test whenever a data collection of approvals data is required specific to a purchase order document. No validation will be done on the data collected, therefore no error or warning messages will be provided. Usage: To execute the test, do the following: 1. Start Oracle eBusiness Suite 2. Connect to a responsibility that has Support Diagnostics enabled (see Note 358831.1 for details) 3. Select the Diagnostic Tools menu option 4. Select Application
No validation will be done on the data collected, therefore no error or warning messages will be provided.
EBS Applicable Releases: 12.0.6 +
Application Name: Purchasing
Test Group: Data Collectors
Test Name: PO Approval
This test was last released on: 05-NOV-08

To execute the test, do the following:
  1. Start Oracle E-Business Suite
  2. Connect to responsibility Application Diagnostics
  3. Select the Diagnose menu option
  4. Click button Select Application and select Application "Purchasing"
  5. Scroll down to group "Data Collectors"
  6. Select test name "PO Approval"
  7. Input Parameters (* required)
Responsibility Id (Mandatory)  
Operating Unit Id (Mandatory)  
Document Type (Mandatory)  
Document Number (Mandatory)  
Release Number (Optional)  
  1. Output contains
Document Header Values
PO_DOCUMENT_TYPES_VL
PO_ACTION_HISTORY
WF_ITEMS
WF_ITEMS Child Processes
Workflow Status for each Item_type and Item_key
Workflow Document Managers
FND_USER
AK_WEB_USER_SEC_ATTR_VALUES
WF_USERS
WF_ROLES
WF_USER_ROLES
WF_LOCAL_USERS
PER_PEOPLE_F
PER_ASSIGNMENTS_F
PO_EMPLOYEE_HIERARCHIES ( if AME is not used )
Approval Rules assigned to Job ( if AME is not used )
Approval Rules assigned to Position ( if AMEis not used )
User Notification Rules
WF_RESOURCES
AME Setup Details ( if AME is used )

References:


ID 732171.1

    

Get Oracle Certifications for all Exams
Free Online Exams.com

How can Can We Use Hot Patch

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    
How can Can We Use Hot Patch 

If you use hotpatch and a file in the patch is in use by the application, it will not be updated
by the patch as the file will be locked and will not be over-written by the copy portion of the
patch driver

During a patch application the recommended option is to Enable Maintenance node. If the Maintenance Mode is not enable you will get an error like :

sqlplus -s &un_apps/***** @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE 
Error: 
Program exited with status 3 
Cause: The program terminated, returning status code 3. 
Action: Check your installation manual for the meaning of this code on this operating system. 

An error occurred while running adsetmmd.sql. 
Continue as if it were successful [No] : Yes 
Could not enable Maintenance Mode. 
Review the messages above, then press [Return] to continue.

...

To apply a patch without maintenance mode enabled use this syntax :

 adpatch options=hotpatch  



References:
Note  375130.1


    
Get Oracle Certifications for all Exams
Free Online Exams.com

How to run Java Mailer Diagnostics Test.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    

How to run Java Mailer Diagnostics Test.
To execute the test, do the following:
  1. Login to Oracle E-Business Suite
  2. Select the responsibility "Oracle Diagnostics Tool" (see Note 358831.1 for details)
  3. Select application "Application Object Library" from the "Application" list of values
  4. Click the "Advanced" tab
  5. Scroll down to group "Setup"
  6. Select test name "Workflow Java Mailer"
  7. Input Parameters (* required)
APPS Username *  
APPS Password *  
Account  
Inbound Password (conditionally required)  
Inbound Server  
Outbound Server  
Reply To  
  1. Output contains
Technology Stack Requirements
JDK Requirements
Generic Service Manager (GSM)
Email Setup
IMAP/SMTP Connectivity
Concurrent Programs
Logfiles
Patches
Recommendations
References

This test is available in Oracle Diagnostics 2.5 which introduces a new security model for test execution. See Note 409141.1 for more details.
If this test is not available or any of the above steps fail, click on the "Installation Guide" link in the "Reference & Resource Menu". 



References:
Note: 274764.1


    

Get Oracle Certifications for all Exams
Free Online Exams.com

Workflow notification mailer gets down frequently. An unexpected RuntimeException or Throwable occurred -> java.lang.StackOverflowError

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    Workflow notification mailer gets down frequently. An unexpected RuntimeException or Throwable occurred -> java.lang.StackOverflowError

Symptoms:
The inbound processing is currently disabled and it is working fine. If the inbound processing enabled then it goes down frequently.

Workflow notification getting deactivated periodically.

 Inbound notification is not getting processed.

Log files:
Mailer log file:
---------------------

[May 16, 2009 4:49:41 PM GMT+04:00]:1242478181396:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1::-1:-1:UNEXPECTED:[SVC-GSM-WFMLRSVC-77086-10006 : oracle.apps.fnd.cp.gsc.Processor.run()]:An unexpected RuntimeException or Throwable occurred -> java.lang.StackOverflowError
[May 18, 2009 12:50:40 PM GMT+04:00]:1242636640373:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1::-1:ERROR:[SVC-GSM-WFMLRSVC-77269-10006 : oracle.apps.fnd.cp.gsc.Processor.performError(ProcessorException)]:Maximum number of errors have occurred for this processing thread.

FNDCPGSC73666.txt

## Detail 0 ##
JBO-30003: The application pool (dwcp1576oracle.apps.fnd.wf.worklist.server.WorklistAM) failed to checkout an application module due to the following exception:
oracle.jbo.JboException:JBO-29000:Unexpected exception caught: oracle.apps.fnd.framework.OAException, msg=Application: FND, Message Name: SQL_PLSQL_ERROR. Tokens: ROUTINE = AppsConnectionManager.appsInitialize(int,int,int,int,Connection):-1,-1,-1,0,oracle.jdbc.driver.OracleConnection@1f11cbf; REASON = java.sql.SQLException: ORA-20001: Oracle error -6508: ORA-04061: existing state of package body "APPS.FND_PROFILE" has been invalidated
ORA-04065: not executed, altered or dropped package body "APPS.FND_PROFILE"
ORA-06508: PL/SQL: could not find program unit being called: "APPS.FND_PROFILE"
has been detected in FND_GLOBAL.INITIALIZE.
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 101
ORA-06512: at "APPS.FND_GLOBAL", line 1147
ORA-06512: at "APPS.FND_GLOBAL", line 620

Solution:
1.
Make sure the following patches are applied: Patch.6720592. And Patch.4747557 and Patch 6940918 and Patch 6218989 and Patch 6441940
Bug.6720592: NOTIFICATION MAILER TIME OUT MESSAGES,
NEEDS RESTART AND MAILER PERFORMANCE ISSUE AFTER RUP5
Oracle consolidated many workflow performance fixes into this one
patch 6720592 and it includes all the objects required so it can be applied to RUP4 and above.
It is considered kind of a baseline for workflow after RUP4 or RUP5 or RUP6.

Patch 4747557 :11.5.10.CU2: 4725438: STACKOVERFLOWEXCEPTION WHEN PROCESSING NOTIFICATION RESPONSES
Patch 6940918 1OFF:11IRU4:6511028: WORKFLOW CONTAINERS CONSUMING LOT OF TEMP TABLE SPACE
Patch 6218989 1OFF:6032403:ATG RUP5:11.5.10.2.5:SESSION TIMEOUT IN ICX_SESSIONS
Patch 6441940 MAILER WITH NON-NULL CORR ID DOES NOT PROCESS MESSAGES AFTER ATG PF.H RUP 5

2.
1. set the following Mailer parameters as recommended.

Processor Close on Read timeout -- Set it to Y
Inbound thread count -- Set it to 1.

2. Bounce the WF Notification Mailer.
3. Run the following Concurrent request:

Name: "Move messages from Exception to Normal Queue of Workflow Agent"
Parameter Value: WF_NOTIFICATION_OUT as the Agent Name.
4. Re-test the issue.

3. If the instance is not patches to ATG.RUP.5 or 6 then edit the workflow mailer service / click on the advanced button / increase the inbound and outbound threads to 2 / click next to complete and save and re test
References:
Bug.6720592: NOTIFICATION MAILER TIME OUT MESSAGES,
NEEDS RESTART AND MAILER PERFORMANCE ISSUE AFTER RUP5
Article-ID: Note 340402.1
Title: JMAILER java.lang.StackOverflowError - keeps crashing

Article-ID: Note 305574.1
Title: Mailer errors out with JAVA.LANG.STACKOVERFLOWERROR

Article-ID: Note 735812.1
Title: Performance Issues Caused by WF_EVENT_OJMSTEXT_QH.DEQUEUE
Procedure



      
Get Oracle Certifications for all Exams
Free Online Exams.com

workflow notification mailer is consuming 100%CPU performance degradation till loss of service javax.mail.MessagingException: 451 Timeout waiting for client input

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    workflow notification mailer is consuming 100%CPU performance degradation till loss of service javax.mail.MessagingException: 451 Timeout waiting for client input
Workflow Mailer java process is consuming 100% of CPU on Apps server which degrades the performance and bounce is needed.
javax.mail.MessagingException: 451 Timeout waiting for client input

Log files:
wf_output.log

Filename = <a href="http://sew.oraclecorp.com/SEW/fileDownload?forceDownload=0&fileURL=https%3A%2F%2Fgtcr.oracle.com%2Fgtcr-dir%2Fgtcr_546379931%2F7583240.992%2Fwf_output.zip"target="_blank">https://gtcr.oracle.com/gtcr-dir/gtcr_546379931/7583240.992/wf_output.zip</a>

MT+04:00]:1241533971006:Thread[outboundThreadGroup1,5,outboundThreadGroup]:0:-1:Servername:IPAddress:-1:-1:ERROR:[SVC-GSM-WFMLRSVC-73035-10006 : oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.send(Message)]:Problem encountered when sending to {[["USERNAME" <Email>]]} -> javax.mail.MessagingException: 451 Timeout waiting for client input

[May 5, 2009 6:32:51 PM GMT+04:00]:1241533971007:Thread[outboundThreadGroup1,5,outboundThreadGroup]:0:-1:Servername:IPAddress:-1:-1:ERROR:[SVC-GSM-WFMLRSVC-73035-10006 : oracle.apps.fnd.wf.mailer.SMTPMessageHandler.sendMessages(String)]:MessagingException -> javax.mail.MessagingException: 451 Timeout waiting for client input

[May 5, 2009 6:33:28 PM GMT+04:00]:1241534008921:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1:Servername:IPAddress:-1:-1:ERROR:[SVC-GSM-WFMLRSVC-73035-10006 : oracle.apps.fnd.wf.mailer.IMAPInboundProcessor.readNewMessages()]:Connection to the INBOX has been lost

[May 14, 2009 12:54:27 AM GMT+04:00]:1242248067600:Thread[outboundThreadGroup1,5,outboundThreadGroup]:0:-1:Servername:IPAddress:-1:-1:ERROR:[SVC-GSM-WFMLRSVC-73490-10006 : oracle.apps.fnd.wf.mailer.SMTPMessageHandler.sendMessages(String)]:Not sending notification 20985431 because could not locate the row in the notification table
[May 14, 2009 12:54:32 AM GMT+04:00]:1242248072987:Thread[outboundThreadGroup1,5,outboundThreadGroup]:0:-1:Servername:IPAddress:-1:-1:UNEXPECTED:[fnd.wf.mailer.URLMIMETagListener]:oracle.apps.fnd.wf.mailer.URLMIMETagListener.editStyleRef(HTMLTagEvent, String) Making a connection to {http://sid.dubaiworld.ae:80/OA_HTML/cabo/images/cache/cmbts.gif)} returned {404 - Not Found}. Leaving as a reference
[May 14, 2009 2:25:01 AM GMT+04:00]:1242253501108:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1:Servername:IPAddress:-1:-1:ERROR:[SVC-GSM-WFMLRSVC-73490-10006 : oracle.apps.fnd.wf.mailer.IMAPInboundProcessor.moveMessage(Message, String)]:Source folder {INBOX} is not open.
[May 14, 2009 2:25:24 AM GMT+04:00]:1242253524409:Thread[outboundThreadGroup1,5,outboundThreadGroup]:0:-1:Servername:IPAddress:-1:-1:ERROR:[SVC-GSM-WFMLRSVC-73490-10006 : oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.send(Message)]:Problem encountered when sending to {[["ABDULLA, KHALID ABDULREDHA ABDULLA" <Khaled.AbdulRedha@jafza.ae>]]} -> javax.mail.MessagingException: 451 Timeout waiting for client input

[May 8, 2009 10:10:47 PM GMT+04:00]:1241806247678:Thread[Thread-0,1,main]:0:-1:Servername:IPAddress:-1:-1:ERROR:[fnd.common.ErrorStack]:java.sql.SQLException: ORA-01033: ORACLE initialization or shutdown in progress

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.O3log.receive1st(O3log.java:410)
at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.java:260)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:371)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:551)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:351)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at oracle.apps.fnd.security.ConnectionManager.dbConnect(ConnectionManager.java:1194)
at oracle.apps.fnd.security.AppsConnectionManager.localAppsConnect(AppsConnectionManager.java:976)
at oracle.apps.fnd.security.AppsConnectionManager.localAppsConnect(AppsConnectionManager.java:868)
at oracle.apps.fnd.security.AppsConnectionManager.localAppsConnect(AppsConnectionManager.java:857)
at oracle.apps.fnd.security.AppsConnectionManager.makeGuestConnection(AppsConnectionManager.java:586)
at oracle.apps.fnd.security.DBConnObj.<init>(DBConnObj.java:246)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:274)
at oracle.apps.fnd.common.Pool.createObject(Pool.java:1287)
at oracle.apps.fnd.common.Pool.createAvailableObject(Pool.java:2225)
at oracle.apps.fnd.common.Pool.resize(Pool.java:2103)
at oracle.apps.fnd.common.Pool.run(Pool.java:1985)
at java.lang.Thread.run(Thread.java:534)

[May 8, 2009 10:10:47 PM GMT+04:00]:1241806247678:Thread[Thread-0,1,main]:0:-1:Servername:IPAddress:-1:-1:ERROR:[fnd.common.ErrorStack]:SECURITY_APPL_LOGIN_FAILED

alert.log

Completed: ALTER DATABASE BACKUP CONTROLFILE TO '/backup1/sidbak/onln_bkp/ctrlSID2.ctl.162.1304'
Wed May 13 23:59:10 2009
Some indexes or index [sub]partitions of table HR.PER_EMPDIR_PEOPLE have been marked unusable
Thu May 14 00:03:47 2009
Some indexes or index [sub]partitions of table HR.PER_EMPDIR_ASSIGNMENTS have been marked unusable
Thu May 14 00:03:52 2009
Some indexes or index [sub]partitions of table HR.PER_EMPDIR_ORGANIZATIONS have been marked unusable
Thu May 14 00:03:56 2009
Some indexes or index [sub]partitions of table HR.PER_EMPDIR_PHONES have been marked unusable
Thu May 14 00:04:27 2009
Some indexes or index [sub]partitions of table HR.PER_EMPDIR_POSITIONS have been marked unusable
Thu May 14 00:04:28 2009
Thread 2 advanced to log sequence 69442
Current log# 8 seq# 69442 mem# 0: /u02/sid/siddata/log08.dbf
Thu May 14 00:04:30 2009
Some indexes or index [sub]partitions of table HR.PER_EMPDIR_JOBS have been marked unusable
Thu May 14 00:04:32 2009
Some indexes or index [sub]partitions of table HR.PER_EMPDIR_LOCATIONS have been marked unusable
Thu May 14 00:04:36 2009
Some indexes or index [sub]partitions of table HR.PER_EMPDIR_LOCATIONS_TL have been marked unusable
Thu May 14 01:30:23 2009
Thread 2 advanced to log sequence 69443
Current log# 10 seq# 69443 mem# 0: /u02/sid/siddata/log10.dbf
Thu May 14 01:34:32 2009
Thread 2 advanced to log sequence 69444
Current log# 9 seq# 69444 mem# 0: /u02/sid/siddata/log09.dbf
Thu May 14 02:25:00 2009
ORA-01555 caused by SQL statement below (SQL ID: dsphq1qsnymv8, Query Duration=1242253500 sec, SCN: 0x010e.b4f05697):
Thu May 14 02:25:00 2009
BEGIN WF_EVENT_OJMSTEXT_QH.DEQUEUE(:1, :2, :3); END;

Solution:
You are experiencing Bug.6511028 - WORKFLOW SERVICE CONTAINER CONSUMING A LOT OF TEMPORARY SEGEMENTS
The patch for your version which is 11.5.10 + ATG RUP4 is patch# 6940918

Other work around might help as below:
-          Caused by the failure of Mailer in processing the notifications
-          Run Ready messages in WF_DEFERRED queue manually
-          reduce the chatting between the SMTP server and java mailer by going to OAM, Mailer Configuration, Step 2 - check the box for Processor Close on Read Timeout

- Run the workflow deferred agent listener from sqlplus in order to process all the events
a. Source APPSORA.env so you can run sqlplus from any location and use the $FND_TOP env variable:

. APPSORA.env

b. Login to sqlplus as the apps user:

sqlplus apps/apps

c. Make a script of the following and run it logged in as apps. It will create a file called
debug.log showing what it is doing as it dequeues. Failing events will also be enqueued on the
WF_ERROR queue:

================Begin Script========================

@$FND_TOP/patch/115/sql/wfdbginit.sql 1 %
spool debug.log;
exec wf_log_pkg.wf_debug_flag := true;
exec fnd_log.g_current_runtime_level := 1;

begin

wf_event.listen(p_agent_name => 'WF_DEFERRED');
end;
/
col module format a15 ;
col message_text format a40;

commit;
@$FND_TOP/patch/115/sql/wfdbgprnt.sql wf.plsql.%

select fnd_debug_util.get_trace_file_name() tracefile from dual;

spool off;

================End Script===========================

Note: This script will take time because there are several events in the WF_DEFERRED queue.

B. Rebuild and clean.

a. Shutdown the Workflow Mailer Service and Workflow Agent Listener Service then run the following:

sqlplus apps/apps @$FND_TOP/sql/wfevqcln.sql WF_DEFERRED oracle.apps.wf.notification.%

OR based on your patch level

sqlplus apps/apps @$FND_TOP/sql/wfevqcln.sql WF_DEFERRED oracle.apps.wf.notification.% 0 (CU 2 and above)

b. Conditionally required for ATG RUP4 and above Oracle Alert users:

NOTE: APPLSYS is assumed to be the schema storing the Workflow tables. Please substitute the correct schema name if necessary.

1. Backup the Oracle Alert messages that are unprocessed on the WF_NOTIFICATION_OUT.

ASSUMPTION: The corrupt messages are not belonging to Oracle Alert.

create table APPLSYS.AQ$WF_NOTIFICATION_OUT_BAK as select * from APPLSYS.AQ$WF_NOTIFICATION_OUT where CORR_ID like
'APPS:ALR%' and msg_state in ('READY','WAIT');

2. Confirm that all Oracle Alert messages have been backup:

set linesize 155;
set pagesize 200;
set verify off;
SELECT COUNT(*),
substr(corr_id,1,45) corr_id,
msg_state
FROM applsys.aq$wf_notification_out
WHERE corr_id LIKE 'APPS:ALR%'
AND msg_state IN('READY', 'WAIT')
GROUP BY corr_id,
msg_state;

Compare:

set linesize 155;
set pagesize 200;
set verify off;
SELECT COUNT(*),
substr(corr_id,1,45) corr_id,
msg_state
FROM applsys.aq$wf_notification_out_bak
WHERE corr_id LIKE 'APPS:ALR%'
AND msg_state IN('READY', 'WAIT')
GROUP BY corr_id,
msg_state;


3. Drop the WF_NOTIFICATION_OUT queue to remove all the other messages:

sqlplus apps/apps

Exec dbms_aqadm.stop_queue( queue_name => 'APPLSYS.WF_NOTIFICATION_OUT', wait => FALSE);

exec dbms_aqadm.drop_queue_table( queue_table => 'APPLSYS.WF_NOTIFICATION_OUT', force => TRUE);

4. Recreate WF_NOTIFICATION_OUT as it need to exist for $FND_TOP/patch/115/sql/wfntfqup.sql to run successfully. See the steps :

i. Run the following to recreate the JMS queues:

sqlplus APPSusr/APPSpwd @wfjmsqc2.sql FNDusr FNDpwd

Example:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfjmsqc2.sql applsys apps


ii. Add the subscribers:

sqlplus APPSusr/APPSpwd @wfmqsubc2.sql FNDusr FNDpwd

Example Syntax:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfmqsubc2.sql APPLSYS APPS

c. Required by ALL: Now run to repopulate the WF_NOTIFICATION_OUT queue:

sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup APPSusr APPSpw FNDusr

Example Syntax:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfntfqup APPS apps APPLSYS


5. Required for Oracle Alert: Make the following into a script and run it from the apps schema. It will ONLY put Oracle Alert messages that are unprocessed on the WF_NOTIFICATION_OUT queue.


===============Begin Script=============================

set serveroutput on size 100000;
declare
x_out_queue varchar2(80) := 'APPLSYS.WF_NOTIFICATION_OUT';
x_enqueue_options dbms_aq.enqueue_options_t;
x_message_properties dbms_aq.message_properties_t;
x_msgid RAW(16);
i number := 0;
p_event SYS.AQ$_JMS_TEXT_MESSAGE;

cursor msg is
select * from APPLSYS.AQ$WF_NOTIFICATION_OUT_BAK
where msg_state in ('READY','WAIT');

begin

for m1 in msg loop

x_out_queue := 'APPLSYS.'||m1.queue;
x_message_properties.correlation := m1.corr_id;
--x_message_properties.original_msgid := m1.msg_id;
x_message_properties.priority := m1.msg_priority;

x_message_properties.recipient_list(1) := sys.aq$_agent(m1.consumer_name,
null,
0);
if (m1.msg_state = 'WAIT') and (m1.delay > sysdate) then
x_message_properties.delay := ((sysdate - m1.delay)*86400);
else
x_message_properties.delay := 0;
end if;
--dbms_output.put_line('msg.msgid = ' || m1.msg_id);

DBMS_AQ.ENQUEUE(
queue_name => x_out_queue,
enqueue_options => x_enqueue_options,
message_properties => x_message_properties,
payload => m1.user_data,
msgid => x_msgid); /* OUT*/

--dbms_output.put_line('x_msgid = ' || x_msgid);

if i = 20 then
i := 0;
commit;
else
i := i + 1;
end if;
end loop;

commit;
exception
when others then
dbms_output.put_line('sqlerrm = ' || sqlerrm);
end;
/

=========End Script======================================


6. Confirm that all Oracle Alert messages are back on the wf_notification_out queue:

set linesize 155;
set pagesize 200;
set verify off;
SELECT COUNT(*),
substr(corr_id,1,45) corr_id,
msg_state
FROM applsys.aq$wf_notification_out
WHERE corr_id LIKE 'APPS:ALR%'
AND msg_state IN('READY', 'WAIT')
GROUP BY corr_id,
msg_state;

d. Create Index on WF Queue Tables

NOTE: The index tablespace can be determined by running the following select. Please ignore any ora-0955 errors as the only missing index is WF_NOTIFICATION_OUT_N1. Use the same tablespace_name as the one that belongs to WF_NOTIFICATION_IN_N1:

select tablespace_name, index_name from dba_indexes where index_name like 'WF_%_N1';

sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfqidxc FNDusr FNDpwd tablespace_name

OR based on your patch level

sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfqidxc2 FNDusr FNDpwd tablespace_name

Example Syntax:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfqidxc APPLSYS APPS USER_IDX

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfqidxc2 APPLSYS APPS APPS_TS_QUEUES

e. Work Flow Histograms Creation Script.

sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfhistc FNDusr

Example Syntax:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfhistc APPLSYS

f. Then restart the Workflow Mailer Service and Workflow Agent Listener Service.


References:
Note.567762.1 Gen EVENTS.P-1151 Ext/Pub Performance Issues with Workflow Service Container
Bug 6511028 - WORKFLOW SERVICE CONTAINER CONSUMING A LOT OF TEMPORARY SEGEMENTS
Bug 6940918 1OFF:11IRU4:6511028 : WORKFLOW CONTAINERS CONSUMING LOT OF TEMP TABLE SPACE


    
Get Oracle Certifications for all Exams
Free Online Exams.com

EXTRA PAYMENT SCHEDULE LINES FOR INVOICES causing FRM-40202: field must be entered when we are querying some invoices in production 11i.AP.N

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    EXTRA PAYMENT SCHEDULE LINES FOR INVOICES causing FRM-40202: field must be entered when we are querying some invoices in production 11i.AP.N

FRM-40202: field must be entered when we are querying some invoices in production.
Those old invoices which are validated and accounted have source 'Manual Invoice Entry'.
Those invoices have got one extra payment schedule line having due_date,gross_amount,payment_priority,amount_remaining as NULL and PAYMENT_STATUS_FLAG = 'N'.
We do not have any idea how come system created these lines and how come above mandatory columns are null for them.

Solution:
create backup of AP_PAYMENT_SCHEDULES_ALL

delete from AP_PAYMENT_SCHEDULES_ALL
where AMOUNT_REMAINING is null
and GROSS_AMOUNT is null and
INVOICE_ID=&invoice_id;

Now query the problematic invoice and check whether invoice is displaying correct data or not.

References:
Refer BUG # 4939397 for more information on this


    
Get Oracle Certifications for all Exams
Free Online Exams.com