ORA-03114: not connected to ORACLE

There are multiple scenarios that cause this error. Check the following list of solutions:

Lets devide it into two scenarios where we get this error.

1.DB Connection issue :-

1. Verify there is no firewall or router setting which terminates connections which are active for longer than x minutes.

2. Verify there is no firewall or router setting which terminates connections which are idle for longer than x minutes.

3. Verify the Oracle configuration. Let the database send a packet every x minutes, so that the firewall, router, etc does not close the connection due to being 'idle'.

1. The Oracle documentation lists the following details for the  sqlnet.ora parameter: SQLNET.EXPIRE_TIME 

Purpose:
Use parameter SQLNET.EXPIRE_TIME to specify the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

Limitations on using this terminated connection detection feature are: 
- It is not allowed on bequeathed connections. 
- Though very small, a probe packet generates additional traffic that may downgrade network performance. 
- Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.

Default:  0
Minimum Value: 0
Recommended Value: 10
Example:
SQLNET.EXPIRE_TIME=10

Perform the following test:

• Stop your Oracle database instance
• Sdit the $ORACLE_HOME/network/admin/sqlnet.ora file and add SQLNET.EXPIRE_TIME=3
• Restart the Oracle database instance
• Do a full connect with the Oracle WP driver via ODBCTest as SYSTEM/MANAGER
• Execute the following stored procedure: {call dbms_lock.sleep(3000)} (sleeps for 50 minutes). Adapt the value to your needs.

2. Check the Oracle setting 'connection idle time limit.

• This can be set in the Oracle Enterprise Manager Console.
• Select the database you are using and click down to the "security\profile\\idle time".
• Setting this to something lower than 60 minutes will cause the user to be disconnected after that XX minutes of inactivity.

2. Code Bug :-

Yes, There could be a code bug which is causing the issue. you just need to monitor the session to get the sql_id which is causing wait event to get fired again and again.

Use the below query to get the sql details and everything related to the concurrent program:-

  SELECT fcr.request_id "Req ID",
         parent_request_id "Parent",
         DECODE (fcpt.user_concurrent_program_name,
                 'Check Periodic Alert', 'Alert: ' || fcr.description,
                 fcpt.user_concurrent_program_name)
            "Name",
         r.responsibility_key "Responsibility",
         gvs.module "Module",
         gvs.sql_id "SQL ID",
         gvs.event "Wait event",
         fcr.priority "Priority",
         fcr.requested_start_date "Requested Start",
         fcr.actual_start_date "Actual Start",
         fcr.actual_completion_date "Actual End",
         DECODE (fcr.status_code,
                 'C', 'Complete',
                 'E', 'Error',
                 'R', 'Running',
                 'I', 'Pending',
                 'G', 'Warning',
                 'Q', 'Scheduled',
                 'D', 'Cancelled',
                 'W', 'Waiting',
                 'X', 'Terminated',
                 status_code)
            "Status",
         fcr.phase_code,
         fcr.status_code,
         DECODE (fcr.hold_flag, 'Y', 'Hold', 'Normal') "Hold?",
         ROUND (
              (  DECODE (fcr.actual_completion_date,
                         NULL, SYSDATE,
                         fcr.actual_completion_date)
               - actual_start_date)
            * 24
            * 60,
            2)
            "Time(m)",
         ROUND (
              (  DECODE (fcr.actual_completion_date,
                         NULL, SYSDATE,
                         fcr.actual_completion_date)
               - actual_start_date)
            * 24
            * 60
            * 60,
            2)
            "Time(s)",
         argument_text "Arguments",
         ROUND (
              (  DECODE (fcr.actual_start_date,
                         NULL, SYSDATE,
                         fcr.actual_start_date)
               - requested_start_date)
            * 24
            * 60
            * 60,
            1)
            "Start Delay (s)",
         r.responsibility_key "Responsibility",
         fu.user_name "User",
         gvs.inst_id "Inst",
         gvs.sid "SID",
         gvs.serial# "Serial#",
         gvs.sql_id "SQL ID",
         gvs.event "Wait event",
         gvs.status "S.Status",
         gvs.service_name "Service",
         gvs.sql_child_number "SQL Child",
         gvs.last_call_et "Call time",
         connstr1 "Conn Str",
         gvs.module "Module",
         argument_text "Arguments",
         fcr.priority_request_id "Priority Req ID",
         fcr.logfile_node_name,
         fcr.oracle_session_id,
         fcr.oracle_process_id spid,
         fcr.concurrent_program_id,
         fcr.status_code,
         fcp.os_process_id applmgr_pid,
         fu.user_id,
         fcr.concurrent_program_id "Prog ID",
         queue_id,
         fcqt.user_concurrent_queue_name queue_name,
         fcr.logfile_name,
         fcr.enable_trace
    FROM apps.fnd_concurrent_requests fcr,
         apps.fnd_concurrent_programs_tl fcpt,
         apps.fnd_user fu,
         apps.fnd_responsibility r,
         gv$session gvs,
         apps.fnd_concurrent_queues fcq,
         apps.fnd_concurrent_queues_tl fcqt,
         apps.fnd_concurrent_processes fcp
   WHERE     fcpt.application_id = fcr.program_application_id
         AND fcpt.concurrent_program_id = fcr.concurrent_program_id
         AND fcr.oracle_session_id = gvs.audsid(+)
         AND r.responsibility_id = fcr.responsibility_id
         AND fcr.requested_by = fu.user_id
         AND fcr.controlling_manager = fcp.concurrent_process_id(+)
         AND fcp.concurrent_queue_id = fcq.concurrent_queue_id(+)
         AND fcq.concurrent_queue_id = fcqt.concurrent_queue_id(+)
         AND fcr.parent_request_id = :p_request_id
ORDER BY 10 DESC NULLS FIRST, 5 DESC;


Get the sql details from the above query for the running concurrent program and check the sql which is firing again and again and going in the wait event.

SELECT * FROM gv$sql where sql_id = :p_sql_id;

get the sql details and you will see the sql which is causing the performance issue and analyse the same.

Why FND in Oracle apps??

FND stands for Foundation!!

FND means Foundation. It means, this is the schema where all the foundation/"apps tech stack" tables are created.

For example, following entities can be classified as Foundation to apps.

1. Apps Security related tables, to name a few FND_USER, FND_RESPONSIBILITY, FND_SECURITY_GROUPS, fnd_application etc...


2. Apps validation related tables.
i.e. FND_FLEX_VALUES, FND_FLEX_VALIDATION_EVENTS, FND_FLEX_VALUE_SETS, fnd_lookups, FND_LOOKUP_VALUES 

3. All the workflow related tables.

4. Concurrent manager related tables.
i.e. fnd_concurrent_requests, fnd_concurrent_programs

5. Apps standard API packages i.e. FND_SEQNUM.assign_doc_seq, fnd_global.user_id, fnd_file.put_line

Restart or Bounce Apache in Oracle Apps 11i

The blow scripts are very handy for bouncing the Apache, specially when working on Self Service Applications. 

Please find the two commands that can be used for bouncing the Apache

$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop
$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start

Of course this needs to be done in Middle Tier of Oracle Applications.

In case you have modified any java or class file in OAF ( Oracle Applications
Framework ), then Apache bounce becomes mandatory for those changes to take effect.

In case you modify and load the XML Document in Oracle Framework, then
it is noticed, for those XML changes to take effect, complete bounce of Middle
Tier is required in Oracle Apps.

If your client is still stuck with AK Developer, then Apache bounce will be required
after akload has been executed.

How to create Concurrent Program in Oracle Applications

Below are simple steps to create

1. Executable
2. Concurrent program and then
3. Attach Concurrent Program with Request Group.

Login as Sysadmin or Application Developer

Create Executable :- 

Navigate to Concurrent > Executable.Define your executable as shown below.

Create Concurrent Program

• Navigate to concurrent program (CP) screen.
• Eneter Name for CP.Short Name should be same as short name of executable.(It's Best Practice).
• Check the enable check box.Select executable name from LOV.
• Select the output format for CP.

• Define Parameter. For each Parameter
  1.Select the Value Set .
  2.Default Type.
  3.Default value for Set
  4.Default Value.

• After Saving the Concurrent Program, Attach it with requied Request Set.
• Choose the group where you want to attach CP
• Add Concurent program

System Profile options Types and uses

hierarchy and Levels of Profile options :-

Profile categories, or hierarchy types, enable system administrators to group and set profile options according to their business needs or the needs of the installation. You can view and set a profile option at the levels of its hierarchy type. As a system administrator, you can use the System Profile Values window in Oracle Applications Forms to set up profile options for your user community. If you change a user profile option value, your change takes effect as soon as your users log on again or change responsibilities, and bounce the apache server. When you set up a user profile, you provide Oracle Applications with standard information (such as printer) that describes a user, Responsibility, application, or site. You can set up values for user profile options at each of the following profile levels.

• Site: Option settings pertain to all users at an installation site.
• Application: Option settings pertain to all users of any Responsibility associated with the application.
• Responsibility: Option settings pertain to all users currently signed on under the Responsibility.
• User: Option settings pertain to an individual user, identified by their application user name.

The values you set up at each level provide run-time values for each user's profile options. An option's run-time value becomes the highest level setting for that option.

When you can set a profile option at more than one level, the priorities from lowest to highest are:

• Site has the lowest priority.
• Application supersedes Site.
• Responsibility supersedes Application.
• User has the highest priority.

For example, values at the Application, Responsibility, or User levels can override a value at the Site level. A value at the User level has the highest priority and overrides values at any other level.

For example, for a given user, assume the printer option is set only at the Site and Responsibility levels. When the user logs on, the printer option assumes the value set at the Responsibility level, since it is the highest level setting for that option.

Note: As a System Administrator, you should set site-level option values before specifying profile options at the other levels after the installation of Oracle Applications.

The options specified at the site-level work as defaults until the same options are specified at other levels. Application users can use the Personal Profile Values window to set their own personal profile options at the user level. Not all profile options are visible to users. End-users might not be able to update some visible profile options.

Debug Workflow Notifications and Workflow problems

Troubleshooting Workflow Notification Mailer Issues

Troubleshooting Workflow Notification Mailer Issues
Troubleshooting Workflow Notification Mailer Issues
Find Workflow Notification Mailer is up and Running?

1. Check if WF mailer component is running.

SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = ‘WF_MAILER’;

Restart the WF mailer with the help of DBAs if WF mailer is not running.

2. If WF mailer is running then perform the below steps

Find the Failed One’s?

Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS=’FAILED’;

Check pending e-mail notification that was pending for process.

Sql> SELECT COUNT(*), message_name FROM wf_notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘MAIL’
GROUP BY message_name;

Sql> SELECT * FROM wf_notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘SENT’
ORDER BY begin_date DESC

Check the Workflow notification has been sent or not?

select mail_status, status from wf_notifications where notification_id= :p_notification_id

–If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
–If mail_status is SENT, its means mailer has sent email
–If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is “Don’t send email”
–Notification preference of user can be set by user by logging in application + click on preference + the notification preference

1. Verify whether the message is processed in WF_DEFERRED queue

select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()=  notification id

2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue

select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = ‘
To check what all mails have went and which all failed ?

Select from_user,to_user,notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS where status = ‘OPEN’;

Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date
from WF_NOTIFICATIONS where status = ‘OPEN’;

Users complain that notifications are stuck ?

Use the following query to check to see whatever the users are saying is correct

SQL> select message_type, count(1) from wf_notifications
where status=’OPEN’ and mail_status=’MAIL’ group by message_type;

E.g o/p of query –

MESSAGE_Type COUNT(1)
——– ———-
POAPPRV 40 — 40 mails of Po Approval not sent —
INVTROAP 12
REQAPPRV 9
WFERROR 10 — 10 mails have error

If Mail not received by User ?

select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’name,name’ ;

Status – Active
Notification_preference-> Mailtext (Should not be disabled)
Email Address should not be null

Notification not sent waiting to be mailed ?

IF user preference is not setup correctly :- 

1. Log into applications as the sysadmin user.

2. In the upper right hand corner of the screen, click on Preferences.

3. Within the Notifications section for Email Style, select a valid option other than: Do not send
me email or Disabled.

4. Now run run the Synchronize WF LOCAL tables request.

5. Retest the issue after the Synchronize WF LOCAL tables request is complete.

6. Migrate the solution as appropriate to other environments.

SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS
where status = ‘OPEN’ and mail_status = ‘MAIL’;
To debug the notification id ?

Below are the oracle important note to debug workflow notifications:-
******************************

Note: 1054215.1 – How to Check if the Workflow Mailer is Running
Note: 415516.1 – How to Check Whether Notification Mailer is Working or Not

Note: 831982.1 – 11i/R12 – A guide for troubleshoting Workflow Notification Emails – Inbound and Outbound
Note: 1012344.7 – Notifications Not Being Sent In Workflow
Note: 560472.1 – Workflow Mailers Not Sending Notifications

Create a system profile in Oracle Apps R12

Below are the Steps to create custom Profile Option:-

1. Log in to Oracle apps.

2. Navigate to Application Developer Responsibility-->Profile

3.Create new profile option:-

Enter all mandatory fields on Profile form to create new profile option.

Name :- Enter name of the profile option.

Application :- Enter name of the application.

User Profile Name :- Name of the profile option for users.

Description :- Description for Profile Option(Optional)

Hierarchy Type :-  Their are 4 hierarchy types available in Oracle apps which are as follows.

1. Security :- Profile Option is Visible,Updatable at Site,Application,Responsibility andUser level.

2. Server    :- Profile Option is Visible,Updatable at Site,Server and User level.

3. Server-Responsibility :- Profile Option is Visible,Updatable atSite,Server+Responsibility and   User level.

4. Organization :- Profile Option is Visible,Updatable at Site,Organization and User level.

Hierarchy Type Access Level :- Hierarchy type levels are used to set the access level of the profile option i.e. at which level you can access this profile option.There are total 7 levels.Hierarchy type access levels depends on your hierarchy type i.e. which hierarchy type you have selected.

4. Create lookup for the profile option values :- 

reate Lookup for Profile Option Values

Navigate to Application Developer Responsibility -->Application -->Lookups --> Application Object Library.

To get profile option values you have to create new Lookup or have to use any existing Lookup or you can hardcore values in SQL validation section on PROFILE form. 

Enter all the mandatory fields on LOOKUP form.

Type :- Enter Lookup type.

Meaning :- Enter meaning of the Lookup(Optional).

Application :- Enter name of the application.

Description :- Enter description of the Lookup(Optional).

Under Access Level section select one option from the following :-

User :- User can insert and update Lookup values.

Extensible :- User can add additional values and modify them but cannot modify predefined values.

System :- User cannot do anything if access level is System.

Under Line section enter the following fields :-

Code :- Code of Lookup value.

Meaning :- Meaning of Lookup value.

Description :- Enter description of  Lookup value(Optional).

From Date :- Enter Start date of Lookup value.

To Date :- Enter End date of  Lookup value.

Enable :- Check Enable check box if you want to enable this Lookup value.

Save it.

After creating profile . Lets set value to profile option.

Set Profile Option Value

1. Navigate to System Administrator Responsibility --> Profile --> System.

2. On SYSTEM PROFILE OPTION form check the check box where you want to set the profile option that you just created i.e. on Site,Responsibility,User,Organization,Application,Server level.

3. Check Profile with No Values check box if you want to query those Profile options which don't have any value on the level you have selected.

4. From PROFILE LOV select profile option that you have created and click on FIND button.

Select Profile Option Value and save you work.

Select one value :-

Autoinvoice Date Issues (So many dates in interface table)

Resolving Autoinvoice Date Issues

There are just so many dates and so many ways to derive all the dates.  Receivables Transactions have General Ledger (GL) Date (GL_DATE), Transaction Date(TRX_DATE), Ship Date(ship_date_actual), Sales Order Date(sales_order_date), and Rule Start dates to name a few.  There are also several ways to get your dates, with Autoinvoice you can enter your GL Date and Transaction Date in the columns of RA_INTERFACE_LINES_ALL table or you can allow the AutoInvoice process to derive the dates.

If the dates are not provided in the interface tables they will be derived based on your setups.  Date Derivation of the General Ledger date and Transaction date during the process of AutoInvoice depends on the following date columns in the RA_INTERFACE_LINES_ALL table:

• GL_DATE
• TRX_DATE
• SHIP_DATE_ACTUAL
• SALES_ORDER_DATE
• RULE_START_DATE 

AutoInvoice determines the General Ledger date for invoices using the following criteria:

• Does a GL date exist for this invoice in the interface table?
• Does the invoice use rules?
• What is the setting of the Derive Date option for this Transaction Batch Source (Yes or No)?   (Receivables Responsibility > Setup > Transaction > Sources)
• What is the setting of the GL Date in a Closed Period option for this Transaction Batch Source (Adjust or Reject)?  (Receivables Responsibility > Setup > Transaction > Sources)

If you elected to derive the rule start date, AutoInvoice first uses the ship date in the interface table. If the ship date does not exist, AutoInvoice uses the sales order date. If the sales order date does not exist, AutoInvoice uses the date you entered in the Run AutoInvoice window.  The following diagram illustrates this process.

How to find locked tables in Oracle DB

Below are the queries to find Locked Rows in Oracle DB tables, It will show you all the tables locked, to find your table un-comment  the condition in the query and enter your table name in CAPS :).

For RAC DB :- 

SELECT c.owner,

       c.object_name,

       c.object_type,

       b.sid,

       b.serial#,

       b.status,

       b.osuser,

       b.machine

  FROM gv$locked_object a, gv$session b, dba_objects c

 WHERE     b.sid = a.session_id

       AND a.object_id = c.object_id

--       AND c.object_name = &p_object_name

For Standalone DB :-

SELECT c.owner,

       c.object_name,

       c.object_type,

       b.sid,

       b.serial#,

       b.status,

       b.osuser,

       b.machine

  FROM v$locked_object a, v$session b, dba_objects c

 WHERE     b.sid = a.session_id

       AND a.object_id = c.object_id

--       AND c.object_name = &p_object_name

Query to find runtime of a concurrent program

The following query finds total run-time (in minutes) for a concurrent program. Thus, with a little

modification to this query, you can track which concurrent programs take (very) long time

 to complete

and may need performance tuning.

Input the concurrent program name (tl.user_concurrent_program_name, see below)

according to your search criteria and run the query.

SELECT /*+ rule */

       rq.parent_request_id                   "Parent Req. ID",

       rq.request_id                          "Req. ID",

       tl.user_concurrent_program_name        "Program Name",

       rq.actual_start_date                   "Start Date",

       rq.actual_completion_date              "Completion Date",

       ROUND((rq.actual_completion_date -

           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"

  FROM applsys.fnd_concurrent_programs_tl  tl,

       applsys.fnd_concurrent_requests     rq

 WHERE tl.application_id        = rq.program_application_id

   AND tl.concurrent_program_id = rq.concurrent_program_id

   AND tl.LANGUAGE              = USERENV('LANG')

   AND rq.actual_start_date IS NOT NULL

   AND rq.actual_completion_date IS NOT NULL

   AND tl.user_concurrent_program_name = :P_user_Concurrent_prog_name

 ORDER BY rq.request_id DESC;

Current Running SQLs for a Concurrent Program

Current Running SQL for a Concurrent Program

1. gV$session.

SELECT C.sql_text
,C.module
FROM APPS.fnd_concurrent_requests A
,gV$SESSION B
,gV$SQLAREA C
WHERE A.oracle_session_id = B.audsid
AND B.sql_hash_value = C.hash_value
AND A.request_id = :p_request_id;

Pass the Request ID for the above Query.

SELECT SQLT.hash_value
,SQLT.sql_text
,VSES.username
,VSES.module
,VSES.command
FROM gv$sqltext SQLT
,gv$session VSES
,APPS.fnd_concurrent_requests FCONC
WHERE SQLT.hash_value = VSES.sql_hash_value
AND FCONC.oracle_session_id = VSES.audsid
AND FCONC.request_id = :p_request_id
ORDER BY SQLT.piece

2. V$session :-

SELECT C.sql_text
,C.module
FROM APPS.fnd_concurrent_requests A
,V$SESSION B
,V$SQLAREA C
WHERE A.oracle_session_id = B.audsid
AND B.sql_hash_value = C.hash_value
AND A.request_id = :p_request_id;

Pass the Request ID for the above Query.

SELECT SQLT.hash_value
,SQLT.sql_text
,VSES.username
,VSES.module
,VSES.command
FROM v$sqltext SQLT
,v$session VSES
,APPS.fnd_concurrent_requests FCONC
WHERE SQLT.hash_value = VSES.sql_hash_value
AND FCONC.oracle_session_id = VSES.audsid
AND FCONC.request_id = :p_request_id
ORDER BY SQLT.piece