Single resource deadlock: blocked by granted enqueue, f 0

We are noticing "Single resource deadlock" in alert log of the database.

Error Message
*** 2017-09-14 15:51:27.949
*** SESSION ID:(2218.11151) 2017-09-14 15:51:27.949
*** CLIENT ID:(TESTUSER) 2017-09-14 15:51:27.949
*** SERVICE NAME:(VISEBS) 2017-09-14 15:51:27.949
*** MODULE NAME:(e:PO:bes:oracle.apps.fnd.wf.ds.user.updated) 2017-09-14 15:51:27.949
*** CLIENT DRIVER:() 2017-09-14 15:51:27.949
*** ACTION NAME:(PO/PO_BUYER_CCRP) 2017-09-14 15:51:27.949

Single resource deadlock: blocked by granted enqueue, f 0
Granted global enqueue 0xf215c1260

Reasons:
It can be due to the PRAGMA AUTONOMOUS_TRANSACTION given inside the package or procedure it is calling.
Due to autonomous transaction any insert , update or delete is there on same row in parent and child can create deadlock.

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

Enable trace with bind and wait for EBS user sessions, concurrent request

Enable trace with bind and wait for EBS user session, concurrent request.

Set this  profile option from user level for which you want to sql trace
Profile Option Name : Initialization SQL Statement - Custom
Level : User

Profile Option Value:
BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'TEST_RUN' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;


You can change the level value for below options.
Level Value
0 - No trace.
2 - Normal Sql Trace
4 - Trace with bind
8 - Trace with wait
12 - Trace with bind and wait

SQL to find trace file name for running concurrent request ( if trace enabled )

SELECT DISTINCT user_concurrent_queue_name "Manager",
                phase_code,
                request_id,
                s.inst_id,
                s.sid,
                s.serial#,
                s.status,
                s.sql_id,
                event,
                r.os_process_id,
                NVL (s.process, r.os_process_id) PROCESS,
                NVL (machine, logfile_node_name) MACHINE,
                ROUND (last_call_et / 60, 2) LAST_CALL_ET,
                gp.pid Process_id,
                gp.spid Server_PID,
                gp.tracefile TRACEFILE
  FROM gv$session s,
       gv$process gp,
       fnd_concurrent_requests r,
       fnd_concurrent_processes fcp,
       fnd_concurrent_queues_tl fcq
 WHERE     s.process(+) = r.os_process_id
       AND s.PADDR = gp.addr
       AND s.inst_id = gp.inst_id
       AND request_id = &req_id
       AND r.controlling_manager = fcp.concurrent_process_id
       AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
       AND phase_code = 'R';

   

Create plugable Database

Below is the command to create a plugable database. Another way is to create through dbca.

CREATE PLUGGABLE DATABASE orclpdb ADMIN USER pdbadm IDENTIFIED BY password
  STORAGE (MAXSIZE 2G)
  DEFAULT TABLESPACE testpdb
    DATAFILE '/disk1/oracle/dbs/orclpdb/sales01.dbf' SIZE 250M AUTOEXTEND ON
  PATH_PREFIX = '/disk1/oracle/dbs/orclpdb/'
  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/orclpdb/');

How to drop Plugable database

Below is the command to drop a plugable database

DROP PLUGGABLE DATABASE ORCLPDB INCLUDING DATAFILES;

Move a tablespace from ASM to filesystem

Move a tablespace to ASM from filesystem

Follow the below step to move tablespace to ASM from filesystem
Steps are as below.

1. Offilne the tablespace
2. Copy using RMAN
3. Rename datafile
4. Make it online, recover if required
5. Delete the old datafile

SQL> set line 300
SQL> set pages 300
SQL> col FILE_NAME for a100
SQL> SELECT FILE_NAME , tablespace_name FROM DBA_DATA_FILES;

FILE_NAME                                                                                            TABLESPACE_NAME
---------------------------------------------------------------------------------------------------- ------------------------------
/u01/app/oracle/product/12.1.0.2/orcltst/dbs/ORA_TEST                                                ORA_TEST

SQL>  ALTER TABLESPACE ORA_TEST  offline;

Tablespace altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
localhost:/db/home/oracle > rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jan 25 09:48:59 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLTST (DBID=3881831134)

RMAN> copy datafile '/u01/app/oracle/product/12.1.0.2/orcltst/dbs/ORA_TEST' to  '+ORCLTST';

Starting backup at 25-JAN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00022 name=/u01/app/oracle/product/12.1.0.2/orcltst/dbs/ORA_TEST
output file name=+ORCLTST/ORCLTST/DATAFILE/ora_test.463.934210303 tag=TAG20170125T095142 RECID=4 STAMP=934192303
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JAN-17

Starting Control File and SPFILE Autobackup at 25-JAN-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File and SPFILE Autobackup command on ORA_DISK_1 channel at 01/25/2017 09:51:45
ORA-07217: sltln: environment variable cannot be evaluated.

RMAN>

SQL> alter database rename file '/u01/app/oracle/product/12.1.0.2/orcltst/dbs/ORA_TEST' to '+ORCLTST/ORCLTST/DATAFILE/ora_test.463.934210303';

Database altered.

SQL> col FILE_NAME for a100
SQL> set line 300
SQL> set pages 300
SQL> SELECT FILE_NAME , tablespace_name FROM DBA_DATA_FILES;

FILE_NAME                                                                                            TABLESPACE_NAME
---------------------------------------------------------------------------------------------------- ------------------------------
+ORCLTST/ORCLTST/DATAFILE/ora_test.463.934210303                                                      ORA_TEST

22 rows selected.

SQL> select * from gv$recover_file;

   INST_ID      FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME          CON_ID
---------- ---------- ------- ------- ----------------------------------------------------------------- ---------- --------- ----------
         2         22 OFFLINE OFFLINE OFFLINE NORMAL                                                             0                    0
         1         22 OFFLINE OFFLINE OFFLINE NORMAL                                                             0                    0

8 rows selected.

SQL> alter tablespace ORA_TEST  online;

Tablespace altered.

SQL>  select * from gv$recover_file;

no rows selected

SQL> select count(*) from ;

  COUNT(*)
----------
         1

SQL> alter system switch all logfile;

System altered.

SQL> SELECT FILE_NAME , tablespace_name FROM DBA_DATA_FILES;

FILE_NAME                                                                                            TABLESPACE_NAME
---------------------------------------------------------------------------------------------------- ------------------------------
+ORCLTST/ORCLTST/DATAFILE/ora_test.463.934210303                                                      ORA_TEST

SQL>