ORA-00060-DEADLOCK DETECTED

Thu Dec 01 14:53:28 2016
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/db_name/db_name/trace/db_name_ora_120412.trc.
Thu Dec 01 14:53:40 2016
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/db_name/db_name/trace/db_name_
ora_107905.trc.
[oracle@ trace]$



*** 2016-12-01 14:53:37.906
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:


Deadlock condition : generally this situations can be seen at OS perspective at a common pace , but when it occurs at the DB level then surely it involves developers and DBA to look kindle at the code and infrastructure manipulation perspective ..

Usual cause of deadlocks is due to poor coding skills and improper management of DB resources 

DBA need to work with the Apps teams closely to resolve this issue. 

Let me give an example here : 

If a query is accessing the block,which is accessed by another query might cause contention & result in the conditions of dead lock ..
The same condition can be ruled out at OS perspective using Mutual exclusion and Semaphores.
Where as with DB perspective we need to change the parameter for a particular table which causing dead lock 

There are 2 parameter with respect to concurrent access of blocks 

ini_trans
max_trans  which states mini and max amount of users can access the DB in a given single point of time

ini_trans is 1 by default and max_trans 255

To avoid issues with deadloack ini_trans can be tune as per the requirement of apps and load on the DB for a particular block

Issue while patching the database post to 12c upgrade

Connecting to database...OK
catcon: ALL catcon-related output will be written to /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2556730_2016_11_25_10_54_39/sqlpatch_catcon__catcon_2556730.lst
catcon: See /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2556730_2016_11_25_10_54_39/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2556730_2016_11_25_10_54_39/sqlpatch_catcon__*.lst files for spool files, if any
Bootstrapping registry and package to current versions...done
  Error in bootstrap log /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2556730_2016_11_25_10_54_39/bootstrap_db_name.log:
    Error at line 26: ORA-00406: COMPATIBLE parameter needs to be 12.0.0.0.0 or greater
    Error at line 76: Warning: Package Body created with compilation errors.
    Error at line 82: 78/5     PL/SQL: SQL Statement ignored
    Error at line 83: 78/12    PL/SQL: ORA-00942: table or view does not exist
    Error at line 84: 80/5     PL/SQL: SQL Statement ignored
    Error at86767 line 85: 80/17    PL/SQL: ORA-00942: table or view does not exist
    Error at line 86: 117/5    PL/SQL: SQL Statement ignored
    Error at line 87: 132/10   PL/SQL: ORA-00942: table or view does not exist
    Error at line 88: 151/5    PL/SQL: SQL Statement ignored
    Error at line 92: 151/12   PL/SQL: ORA-00942: table or view does not exist
Prereq check failed, exiting without installing any patches.


Now,The patching has failed with the above errors 

There are 3 solutions for the issues
1.Change compatible parameter in the pfile or spfile
  --It has been observed the changing the compatible to 12.1.0 will create performance issues and may cause problems with final deliveries to the client.
--And once the Compatible parameter has been changed to 12c , then the downgrade is not possible unless you're restoring it from 11g pre-upgrade backup

2. As per MOS it's bug ; Bug no :24386767 and MOS doc id 24386767.8
According to which Bugfix is developed to resolve this issues

3. The same solution is provided in 2016 Q4 PSU patch release 

The feasible solution will be to apply PSU patch and rule-out the error

Enjoy working ...!!!