This discussion is archived
6 Replies Latest reply: Aug 7, 2013 12:30 PM by Mark Kelly RSS

Locked workflow

920802 Newbie
Currently Being Moderated
Hi,


It often happens to me, that workflow is "locked", and time of beeing locked seems to be random. Sometimes it unables work for couple of hours.
Do you know any solution to unlock workflow manually (by user or dba) ?


Thanks in advance,
Paul.
  • 1. Re: Locked workflow
    Brendan Oracle ACE Director
    Currently Being Moderated
    Hi
    It depends on what version of the tool you are using. If you are using 3.0 then yes you will have some issues relating to the workflow freezing etc.
    The issues that cause this are fixed in the latest release 3.1. So download it and everything should work for you
    If you are using 3.1, then that is a different problem and you will need to give more details of the exact version numbers of the tool, database etc
  • 2. Re: Locked workflow
    Brendan Oracle ACE Director
    Currently Being Moderated
    Hi
    It depends on what version of the tool you are using. If you are using 3.0 then yes you will have some issues relating to the workflow freezing etc.
    The issues that cause this are fixed in the latest release 3.1. So download it and everything should work for you
    If you are using 3.1, then that is a different problem and you will need to give more details of the exact version numbers of the tool, database etc
  • 3. Re: Locked workflow
    920802 Newbie
    Currently Being Moderated
    Hi,


    SQL developer:
    Version 3.1.07
    Build MAIN-07.42


    Java(TM) Platform     1.6.0_29
    Oracle IDE     3.1.07.42
    Versioning Support     3.1.07.42


    Database:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    "CORE     11.2.0.2.0     Production"
    TNS for Linux: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production

    Best regards,
    Paul.
  • 4. Re: Locked workflow
    Mark Kelly Oracle ACE
    Currently Being Moderated
    Hi Paul,
    If you are editing a workflow, a db record lock is placed on the workflow.
    One reason for having a locked workflow would be if you have lost network connection between your pc and the db.
    If the db session remains active, the lock is in force and the client can not gain edit control.
    The only way to release the lock would be to kill the db session holding the lock.
    I have included some sql script below that can be used to identify a db session that a lock on a specific workflow.
    You can then kill the session.
    There is also a soft lock that can be placed on a workflow.
    These can be removed by the client by clicking on the lock icon on the workflow editor toolbar.
    Thanks, Mark

    Disconnecting Sessions Locking ODMr Workflows

    Whenever a user wants to edit a workflow, ODMr will acquire a lock on the workflow. Users can run into a situation where a workflow is alread locked and they can not edit that workflow. This can be the result of a client failure that resulted in a orphaned session holding a lock on a workflow. There is no process provided by the UI that will kill a orphaned session that is holding a lock. In the future, we may provide a UI utility to support this, but we wanted to start by just providing some scripts that are useful in dealing with this situation manually. The following steps outline the use of the scripts to resovle this problem.

    1) Make sure the workflow is not running

    This can be simply done by running the following query using your normal user account:

    select COUNT(STATUS)ACTIVE from ODMRSYS.ODMR_USER_PROJECT_WORKFLOW
    WHERE WORKFLOW_NAME = 'InstallationTest'
    AND PROJECT_NAME = 'Project1'
    AND STATUS IN ('ACTIVE','QUEUED')

    It the result is a ACTIVE column displays a "0" then the workflow is not running. If it returns a value other than zero than the workflow is either active of queued to run. If you want to cancel a active workflow, you can do so by right clicking the workflow in the Workflow Jobs interface.

    You can also click on the "Lock" icon displayed in the Workflow Editor toolbar. This attempt to claim a lock on the workflow. It does some additional work where it determines if the metadata lock is actually supported by a object lock. If not, that could be an indication of a failure of some sort. So, it will claim a physical lock and give the user control of the workflow. However, if there is also a physical lock, then the user must proceed to disconnect the session holding that lock.

    Often times the DBA will be running these queries. If that is the case, it is better to use this query as it will return all workflows that have a status other than inactive throughout the system.

    COLUMN user_name FORMAT A30
    COLUMN project_name FORMAT A30
    COLUMN workflow_name FORMAT A30
    COLUMN workflow_id FORMAT A15
    COLUMN last_updated_time FORMAT A30
    COLUMN chain_name FORMAT A30
    COLUMN status FORMAT A30
    select W.status,P.user_name, P.project_name, W.workflow_name, W.workflow_id, W.last_updated_time, W.chain_name
    FROM ODMRSYS.ODMR$WORKFLOWS W, ODMRSYS.ODMR$PROJECTS P
    WHERE STATUS NOT IN ('INACTIVE')
    AND W.project_id = P.project_id

    2) Run a query to obtain all the sessions that have locks on the workflow table.

    You can paste the following script in sql worksheet and click the execute script icon located in the toolbar of the SQL Worksheet editor. This will produce a list of all sessions that have a lock on a row in the ODMR$WORKFLOWS table.

    You must run this as sys (or with an account that has sysdba privileges)

    SET linesize 150;
    SET head ON;
    col sid_serial form a13 col ora_user FOR a15;
    col object_name FOR a35;
    col object_type FOR a10;
    col lock_mode FOR a15;
    col last_ddl FOR a8;
    col status FOR a10;
    BREAK ON sid_serial;
    SELECT l.session_id
    ||','
    ||v.serial# sid_serial,
    l.ORACLE_USERNAME ora_user,
    o.object_name,
    o.object_type,
    DECODE(l.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(l.locked_mode) ) lock_mode,
    o.status,
    TO_CHAR(o.last_ddl_time,'dd.mm.yy') last_ddl
    FROM dba_objects o,
    gv$locked_object l,
    v$session v
    WHERE o.object_id = l.object_id
    AND l.SESSION_ID =v.sid
    AND o.object_name = 'ODMR$WORKFLOWS'
    ORDER BY 2,3;

    The query will return a result similar to the one below:

    SID_SERIAL ORA_USER OBJECT_NAME OBJECT_TYPE LOCK_MODE STATUS LAST_DDL
    190,19439 DMUSER ODMR$WORKFLOWS TABLE Row-X (SX) VALID 13.04.11

    3) Disconnecting the orphaned session

    Take the SID and Serial displayed above and insert it into the ALTER SYSTEM DISCONNECT SESSION command shown below. The trick here is to know which sid,serial combination to kill. You could have many sessions listed that have locks on ODM$WORKFLOWS but you only want to disconnect the orhpaned session. Unfortuntaely V$SESSION does not, in this case, have values in the columns used to determined the row id for the locked row. We will be looking into this further to see if we can perform the lock so this information is loaded at he db. But for now, you need to look at the age of the session, and the user account to determine if you have the right one. Alternatively, you can just shut all your clients down for a particular account, leaving only orphaned sessions.

    Past the command into sql worksheet and execute it.

    ALTER SYSTEM DISCONNECT SESSION '190,19439' IMMEDIATE

    You must run this as sys (or with an account that has sysdba privileges)

    4)Updating Status of Workflow

    We have had cases where the workflow is left in a "QUEUED" state even though it is not scheduled or running. In this case you can use one of the following update scripts.

    Updating all workflows that have queued status, run as SYS.

    update ODMRSYS.ODMR$WORKFLOWS SET STATUS = 'INACTIVE' WHERE STATUS = 'QUEUED';
    commit;

    Updating selected workflows (using workflow id obtained in earlier query) Replace <value> with the workflow_id. If you have multiple you can use the in clause.

    update ODMRSYS.ODMR$WORKFLOWS SET STATUS = 'INACTIVE' WHERE WORKFLOW_ID = <value>
    commit;

    5) The following query is the same query used by the Data Miner, Workflow Manager interface that queries for the status of a job. This might be useful to see what the status of the workflow is from the users perspective:

    This query has to be run as the user. I tried alter sessions set current_schema = "<USER>"; but it does not work. Need to rewrite query so it can also be run from sys.

    SELECT
    WORKFLOW_CHAIN_NAME,
    WORKFLOW_NAME ,
    jobs.WORKFLOW_ID ,
    WORKFLOW_STATUS ,
    PROJECT_NAME ,
    PROJECT_ID ,
    WORKFLOW_JOB_NAME ,
    steps.NODE_ID ,
    steps.NODE_STATUS ,
    steps.NODE_START_TIME ,
    steps.NODE_RUN_TIME ,
    steps.subnode_id ,
    steps.subnode_status
    FROM
    odmr_user_workflow_jobs jobs, odmr_user_workflow_all steps
    WHERE
    jobs.workflow_job_name in (
    select workflow_job_name from (
    select t.*, row_number () over (partition by workflow_id order by job_creation_time desc, workflow_chain_name desc) rid from odmr_user_workflow_jobs t
    ) where rid = 1
    ) AND steps.wf_job_name (+) = jobs.workflow_job_name
    AND (jobs.JOB_CREATION_TIME > (SYSDATE - INTERVAL '24' HOUR)) ORDER BY node_start_time desc
  • 5. Re: Locked workflow
    de45e462-4cf1-4ad2-8f0b-4688375a6400 Newbie
    Currently Being Moderated

    How did you scheduled the oracle data miner workflow in sql developer?

    Appreciate your help..

    -HS

  • 6. Re: Locked workflow
    Mark Kelly Oracle ACE
    Currently Being Moderated

    HI HS,

    Please always add a new discussion rather than add a question to an existing one.

    Thanks, Mark

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points