Clarity

Expand all | Collapse all

Oracle inactive Sessions

  • 1.  Oracle inactive Sessions

    Posted 03-27-2013 06:36 AM
    Hi,

    This is first time use of this community. I need help with Oracle Inactive sessions being waiting on event Sql*Net Message From Client for hours and days.

    To clear those sessions out I need to restart the BG (Background Engine) and sometimes the restart is not clearing all sessions out then I have to kill the Oracle Server Process of the sessions.

    The long running sessions with the above Event can cause the BG to stale and the Heartbeats are not being synced.

    Please shed some light.


    Clarity 12.05
    Oracle DB 11.1.0.7 64bit , RAC 3 nodes


  • 2.  RE: Oracle inactive Sessions

    Posted 03-27-2013 06:43 AM
    Any screenshot?

    Martti K:


  • 3.  RE: Oracle inactive Sessions

    Posted 03-27-2013 06:51 AM
    Not sure that I would come to the same conclusion that an INACTIVE sql session is the cause of any problems - the application (APP and BG) will always maintain SQL connections to the database and if nothing is happening in the APP or BG I would expect those SQL sessions to be inactive.

    Note I'm not saying that you don't have a problem (!!!) - I'm just saying that an inactive SQL session (waiting on some client-side event) should not necessarily be seen as an indication of a problem... and certainly if you go around randomly killing such sessions without fully understanding them then you are perhaps likely to be causing a problem rather than resolving one?


  • 4.  RE: Oracle inactive Sessions

    Posted 03-27-2013 07:10 AM
    I will upload a screen shot as soon as I get it.

    I'm a senior Oracle DBA and I know the Event in question is an Idle Event, sometimes can be caused by network problems but we have checked the connection between App Servers and DB Servers.

    The point I'm new to this team and I told'em this Event is not harmful but I'v seen the BG is not working when the number of sessions are increasing with the Event in question or Wait_Time is increasing and back to life when just clearing the sessions out.

    We clear the sessions by restarting the BG and sometimes by killing the Oracle Server process of those sessions.

    I suspect the JDBC but I can't confirm.


  • 5.  RE: Oracle inactive Sessions

    Posted 03-27-2013 07:17 AM
    I recall in a older Clarity version (7 or 8) I had instances of the number of SQL connections generated by the APP increasing exponentially when the system was "under load" (and thus making it worse and worse) - this was a bug/problem that got fixed with a patch or better DB management (I don't recall, this was a number of years ago).

    Are you seeing multiple (increasing numbers of) inactive sessions happening as the BG grinds to a halt? I'd want to check out the application's BG logs to see if there is an traceable reason why this was occurring.... and of course raise this with CA Support as a priority.


  • 6.  RE: Oracle inactive Sessions

    Posted 03-29-2013 08:43 AM
    I uploaded an Excel sheet with output of my sql query showing the Inactive sessions also I uploaded the BG logs from both nodes.

    Thank you a lot for help

    Attachment(s)

    xlsx
    Inactive_sessions.xlsx   8 KB 1 version
    txt
    bg-niku.log.node1.txt   2.32 MB 1 version
    txt
    bg-niku.log.node2.txt   491 KB 1 version


  • 7.  RE: Oracle inactive Sessions

    Posted 03-29-2013 09:15 AM
    Those BG logs are rather full of errors "Did not get exactly one rowset with 0 or 1 row back" (I'm sure you would have seen that!)

    So I rather think that your Oracle session problems are a symptom of an underlying problem with your BG/processes - I think you need to concentrate on fixing that first rather than clearing the DB end.

    Are CA Support getting you to look at anything specific (and if you have not raised this with support, you really should do!)

    --

    EDIT : I can only find one reference to that error message on the message boards; Error in NSA Log file bg-niku.log and that thread isn't very useful (apart from suggesting that you have a corrupt running process/action items somewhere).

    I would perhaps try to work out why the data associated with this;
      DBResultSet: 0 No of rows: 3
        Row: 0 No of columns: 3
          Column Name: status_code Value: BPM_AIS_RUNNING
          Column Name: id Value: 6783439
          Column Name: step_action_id Value: 5155229
        Row: 1 No of columns: 3
          Column Name: status_code Value: BPM_AIS_RUNNING
          Column Name: id Value: 6783445
          Column Name: step_action_id Value: 5155229
        Row: 2 No of columns: 3
          Column Name: status_code Value: BPM_AIS_RUNNING
          Column Name: id Value: 6783450
          Column Name: step_action_id Value: 5155229
    is "broken" since it is the same data repeated over and over in the logs.... :wacko:

    --

    EDIT2 : There is something in the "fixed bugs" lists that looks like the same problem; CLRT-46861 (S2) - Action items for the Idea’s Approver doesn’t always work. - the release notes contains the same BG error that you are having. I can't find a detailed description of the bug though.

    But if you search for that CLRT ref in the KB (i.e. the Support site) you will find it "fixed" in 12.0.6 I think - you say you are 12.0.5 ?

    ** Contact support and see if they have a workaround for CLRT-46861 then! **


  • 8.  RE: Oracle inactive Sessions

    Posted 04-01-2013 06:57 AM
    Thank you a lot Dave for your help.

    Where can I find any info/details about CLRT-46861 ?


  • 9.  RE: Oracle inactive Sessions

    Posted 04-01-2013 08:14 AM

    ahmad.b wrote:

    Where can I find any info/details about CLRT-46861?
    If you search on the support site then you will not find much more information than I have posted above (which I found just by searching on the error messages from your BG logs files)....so raise a case with CA support (like I have suggested several times!).

    Dave wrote:

    ** Contact support and see if they have a workaround for CLRT-46861 then! **


  • 10.  RE: Oracle inactive Sessions

    Posted 04-01-2013 09:22 AM
    Thank you Dave again a lot

    I just wanted to show more info/detail about it to the Service Owner of Clarity, I know you saved me a lot of time searching for the issue in question and finding the remedy.


  • 11.  RE: Oracle inactive Sessions

    Posted 04-01-2013 09:50 AM
    @Dave - I guess, you are talkin' abt this -

    When creating a process where on some step the 'Idea Approver' should receive an Action Item, the user will NOT receive the Action Item unless he satisfies the following condition

    srm_resources.id = srm_resources.user_id = cmn_sec_users.id

    If the Approver does not comply with this condition, the Action Item is sent to a user with SRM_RESOURCES.USER_ID equal to the Approver's SRM_RESOURCES.ID (SRM_RESOURCES.USER_ID is not always equal to SRM_RESOURCES.ID). If there's no user with such USER_ID, the step stays on a "Ready to execute action" status, and the AI is not sent, or the step fails with error "BPM-0543: An error occurred when sending the action item".


    It's fixed in "13.0.0.6316"

    NJ


  • 12.  RE: Oracle inactive Sessions

    Posted 04-01-2013 12:17 PM

    navzjoshi00 wrote:

    @Dave - I guess, you are talkin' abt this...
    .
    It's fixed in "13.0.0.6316"
    :unsure:

    Dave,earlier wrote:

    But if you search for that CLRT ref in the KB (i.e. the Support site) you will find it "fixed" in 12.0.6 I think - you say you are 12.0.5 ?