After modifying Workflow Package getting below error ORA-00054: resource busy and acquire with NOWAIT

After modifying Workflow Package getting below error ORA-00054: resource busy and acquire with NOWAIT

After modifying any workflow package, while approving or acting on the notification getting below error.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 

ORA-06512: at "APPS.WF_NOTIFICATION", line 5137 ORA-06512: at line 1

It is due to the old state of the existing code creating this error.

Resolution:

1. Clear the old session locking up the old objects.

a. Search the session


select s.* 

from gv$access a, gv$session s 

where object IN  ('AP_WORKFLOW_PKG')

and a.inst_id=s.inst_id 

and a.sid=s.sid 


order by logon_time;



b. Kill the session ( Restart workflow and OPMN, will clear most of them )



2. Bounce the database

Check which file rsync is accessing

Check current file rsync is accessing

It is quiet troublesome to find which folder or file rsync is currently scanning. Sometimes it is required check it when it is running for a long time and you are not able to find the directory where it is consuming more time.

There are several approach to find this.

1. lsof command to find the current file it is accessing.

2. Doing the rsync in vervose mode and printing it to a file

I prefer the first one as it is easier to do.

$ lsof -ad3-999 -c rsync

COMMAND   PID    USER   FD   TYPE             DEVICE SIZE/OFF      NODE NAME

rsync   15070 root    3r   DIR               0,20 20235264   2829984 /app/testing/documents (loaclhost:/root_vdm_1/app)

rsync   15070 root    4u  unix 0xffff880c260033c0      0t0 827481535 socket

rsync   15070 root    5u  unix 0xffff88074fb9f080      0t0 827481536 socket

rsync   15071 root    3u  unix 0xffff880b011e9480      0t0 827481546 socket

rsync   15072 root    4u  unix 0xffff880b011e9100      0t0 827481547 socket

If you want to watch it periodically(every 5 sec), then use the watch command to do it.

$ watch -n 5 "lsof -ad3-999 -c rsync"

Error at line 536: ORA-04021: timeout occurred while waiting to lock object while executing datapatch for PSU

Error at line 536: ORA-04021: timeout occurred while waiting to lock object while executing datapatch for PSU

Getting below error while executing below error for database PSU ( datapatch )

 Error at line 536: ORA-04021: timeout occurred while waiting to lock object

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

./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 Production on Tue Aug 29 14:55:58 2017

Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: ......

Connecting to database...OK

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Patch 20204035 ():

  Installed in the binary registry and the SQL registry

Patch 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)):

  Not installed in the binary or the SQL registry

Patch 24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)):

  Installed in the binary registry and the SQL registry

Bundle series PSU:

  ID 170718 in the binary registry and ID 161018 in the SQL registry

Adding patches to installation queue and performing prereq checks...

Installation queue:

  Nothing to roll back

  The following patches will be applied:

    25755742 (DATABASE PATCH SET UPDATE 12.1.0.2.170718)

Installing patches...

Patch installation complete.  Total patches installed: 1

Validating logfiles...

Patch 25755742 apply: WITH ERRORS

  logfile: ....._....log (errors)

    Error at line 536: ORA-04021: timeout occurred while waiting to lock object

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

Log file details 

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

Session altered.

Library created.

CREATE OR REPLACE PACKAGE BODY dbms_audit_mgmt wrapped

*

ERROR at line 1:

ORA-04021: timeout occurred while waiting to lock objectPackage body created.

Grant succeeded.

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

Resolution:

 Some session might be locking this object. Below are the solutions for this problem.

1. Search the session blocking the object and kill it if possible.

2. Shutdown the database and do a startup restrict