Thursday, June 28, 2018

How to remove an Oracle RAC database


-- you can follow the below steps to remove an existing Oracle RAC database manually

-- Oracle binaries are set up with "oracle" OS user so first switch to this user

[root@exatestdbadm01 ~]# su - oracle
[oracle@exatestdbadm01 ~]$ . profile_insistst
[oracle@exatestdbadm01 ~]$ env|grep ORA
ORACLE_SID=INSISTST1
ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/dbhome_1
[oracle@exatestdbadm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 19 13:24:03 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

-- first set the "cluster_database" parameter to "FALSE"

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> create pfile from spfile;       

File created.

SQL> shutdown immediate

Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive restrict pfile='/u01/app/oracle/product/12.1.0.1/dbhome_1/dbs/initINSISTST1.ora';
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2702112 bytes
Variable Size            1035994336 bytes
Database Buffers         1086324736 bytes
Redo Buffers               12865536 bytes
Database mounted.
SQL> select name from v$database;

NAME
---------
INSISTST

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> exit

-- remove the related database files in "dbs" folder

[oracle@exatestdbadm01 dbs]$ pwd
/u01/app/oracle/product/12.1.0.1/dbhome_1/dbs

[oracle@exatestdbadm01 dbs]$ ls -l
total 2477628
-rw-r----- 1 oracle oinstall  22773760 May 10 15:19 c-1909097454-20160510-01
-rw-r----- 1 oracle oinstall  36896768 Jun  2 09:26 c-1909097454-20160602-02
-rw-r----- 1 oracle oinstall  36896768 Jun  2 10:01 c-1909097454-20160602-03
-rw-r----- 1 oracle oinstall  56524800 Jun  7 17:55 c-1909097454-20160607-02
-rw-r----- 1 oracle oinstall 147521536 Jun 16 10:28 c-1909097454-20160616-02
-rw-r----- 1 oracle oinstall 147554304 Jun 17 15:50 c-1909097454-20160617-04
-rw-r----- 1 oracle oinstall 162234368 Jun 27 01:55 c-1909097454-20160627-00
-rw-r----- 1 oracle oinstall 270073856 Jul  1 15:04 c-1909097454-20160701-02
-rw-r----- 1 oracle oinstall 270073856 Jul  8 09:52 c-1909097454-20160708-00
-rw-r----- 1 oracle oinstall 270073856 Jul 12 14:38 c-1909097454-20160712-02
-rw-r----- 1 oracle oinstall 270073856 Jul 12 17:34 c-1909097454-20160712-03
-rw-r----- 1 oracle asmadmin 281608192 Jul 18 12:01 c-1909097454-20160718-02
-rw-r----- 1 oracle asmadmin 281608192 Jul 18 14:22 c-1909097454-20160718-04
-rw-rw---- 1 oracle asmadmin      1544 Jul 15 19:48 hc_fenikstd1.dat
-rw-rw---- 1 oracle asmadmin      1544 Jul 19 13:40 hc_INSISTST1.dat
-rw-rw---- 1 oracle asmadmin      1544 Jul 19 11:16 hc_INSISTST.dat
-rw-rw---- 1 oracle asmadmin    524288 Jul 19 13:45 id_fenikstd1.dat
-rw-rw---- 1 oracle asmadmin    524288 Jul 19 13:30 id_INSISTST1.dat
-rw-rw---- 1 oracle asmadmin    524288 Jul 19 11:16 id_INSISTST.dat
-rw-r----- 1 oracle oinstall      1976 Jun  1 14:13 initfenikstd1.ora
-rw-r----- 1 oracle oinstall       722 Jul 19 13:37 initINSISTST1.ora
-rw-r--r-- 1 oracle oinstall      2992 Feb  3  2012 init.ora
-rw-r----- 1 oracle oinstall 281526272 Jul 19 07:39 snapcf_fenikstd1.f
-rw-r----- 1 oracle asmadmin      2560 Jul 19 13:37 spfileINSISTST1.ora
[oracle@exatestdbadm01 dbs]$ rm *INSISTST*
[oracle@exatestdbadm01 dbs]$ ls -l
total 2476588
-rw-r----- 1 oracle oinstall  22773760 May 10 15:19 c-1909097454-20160510-01
-rw-r----- 1 oracle oinstall  36896768 Jun  2 09:26 c-1909097454-20160602-02
-rw-r----- 1 oracle oinstall  36896768 Jun  2 10:01 c-1909097454-20160602-03
-rw-r----- 1 oracle oinstall  56524800 Jun  7 17:55 c-1909097454-20160607-02
-rw-r----- 1 oracle oinstall 147521536 Jun 16 10:28 c-1909097454-20160616-02
-rw-r----- 1 oracle oinstall 147554304 Jun 17 15:50 c-1909097454-20160617-04
-rw-r----- 1 oracle oinstall 162234368 Jun 27 01:55 c-1909097454-20160627-00
-rw-r----- 1 oracle oinstall 270073856 Jul  1 15:04 c-1909097454-20160701-02
-rw-r----- 1 oracle oinstall 270073856 Jul  8 09:52 c-1909097454-20160708-00
-rw-r----- 1 oracle oinstall 270073856 Jul 12 14:38 c-1909097454-20160712-02
-rw-r----- 1 oracle oinstall 270073856 Jul 12 17:34 c-1909097454-20160712-03
-rw-r----- 1 oracle asmadmin 281608192 Jul 18 12:01 c-1909097454-20160718-02
-rw-r----- 1 oracle asmadmin 281608192 Jul 18 14:22 c-1909097454-20160718-04
-rw-rw---- 1 oracle asmadmin      1544 Jul 15 19:48 hc_fenikstd1.dat
-rw-rw---- 1 oracle asmadmin    524288 Jul 19 13:46 id_fenikstd1.dat
-rw-r----- 1 oracle oinstall      1976 Jun  1 14:13 initfenikstd1.ora
-rw-r--r-- 1 oracle oinstall      2992 Feb  3  2012 init.ora
-rw-r----- 1 oracle oinstall 281526272 Jul 19 07:39 snapcf_fenikstd1.f
[oracle@exatestdbadm01 dbs]$ pwd
/u01/app/oracle/product/12.1.0.1/dbhome_1/dbs

-- remove the folders belonging to this database under "$ORACLE_BASE/admin" folder

[oracle@exatestdbadm01 dbs]$ cd /u01/app/oracle
[oracle@exatestdbadm01 oracle]$ cd admin
[oracle@exatestdbadm01 admin]$ ls
cdc  clcdc  DBFSDB  DRAHEODS  fenikstd  INSISDET  INSISDUV  INSISTST  NECOTEST  scripts
[oracle@exatestdbadm01 admin]$ rm -rf INSISTST
[oracle@exatestdbadm01 admin]$ ls -l
total 36
drwxr-xr-x 3 oracle oinstall 4096 Jul  3  2015 cdc
drwxr-xr-x 3 oracle oinstall 4096 Jul  6  2015 clcdc
drwxr-x--- 6 oracle oinstall 4096 May 14  2015 DBFSDB
drwxr-xr-x 3 oracle oinstall 4096 Dec 24  2015 DRAHEODS
drwxr-xr-x 7 oracle asmadmin 4096 Mar 11 11:30 fenikstd
drwxr-x--- 4 oracle oinstall 4096 Jun 15  2015 INSISDET
drwxr-xr-x 3 oracle asmadmin 4096 Jun 12  2015 INSISDUV
drwxr-x--- 7 oracle oinstall 4096 Jun 15  2015 NECOTEST
drwxr-xr-x 4 oracle oinstall 4096 May 30 17:40 scripts

-- remove the audit files beloging to this database "INSISTST"

[oracle@exatestdbadm01] /home/oracle > cd $ORACLE_HOME/rdbms/audit
[oracle@exatestdbadm01] /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/audit > ls -l *INSISTST* | wc -l
483
[oracle@exatestdbadm01] /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/audit > ls -l *INSISTST* | head -10
-rw-r----- 1 oracle asmadmin 876 Jun 22  2017 INSISTST1_ora_100036_20170622130128627210143795.aud
-rw-r----- 1 oracle asmadmin 876 Aug 10  2016 INSISTST1_ora_100184_20160810195249674576143795.aud
-rw-r----- 1 oracle asmadmin 867 Jul 21  2016 INSISTST1_ora_101085_20160721180141680820143795.aud
-rw-r----- 1 oracle asmadmin 876 Nov 17  2016 INSISTST1_ora_103568_20161117195645299824143795.aud
-rw-r----- 1 oracle asmadmin 876 Nov  3  2016 INSISTST1_ora_104181_20161103125604823090143795.aud
-rw-r----- 1 oracle asmadmin 876 May 12  2017 INSISTST1_ora_104912_20170512150603904131143795.aud
-rw-r----- 1 oracle asmadmin 868 Dec  1  2017 INSISTST1_ora_105013_20171201234204022596143795.aud
-rw-r----- 1 oracle asmadmin 876 Feb 19  2017 INSISTST1_ora_105642_20170219020355095097143795.aud
-rw-r----- 1 oracle asmadmin 876 Dec  1  2016 INSISTST1_ora_105707_20161201200625717435143795.aud
-rw-r----- 1 oracle asmadmin 876 Jan 31  2017 INSISTST1_ora_106908_20170131000531231798143795.aud
[oracle@exatestdbadm01] /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/audit > rm -f INSISTST*

-- remove the database service

[grid@exatestdbadm01 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATAC1.dg
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.DBFS_DG.dg
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.LISTENER_BAN.lsnr
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.LISTENER_EBS.lsnr
               ONLINE  INTERMEDIATE exatestdbadm01           Not All Endpoints Re
                                                             gistered,STABLE
               ONLINE  INTERMEDIATE exatestdbadm02           Not All Endpoints Re
                                                             gistered,STABLE
ora.RECOC1.CLOUDFS.advm
               ONLINE  ONLINE       exatestdbadm01           Volume device /dev/a
                                                             sm/cloudfs-444 is on
                                                             line,STABLE
               ONLINE  ONLINE       exatestdbadm02           Volume device /dev/a
                                                             sm/cloudfs-444 is on
                                                             line,STABLE
ora.RECOC1.dg
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.asm
               ONLINE  ONLINE       exatestdbadm01           Started,STABLE
               ONLINE  ONLINE       exatestdbadm02           Started,STABLE
ora.net1.network
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.net2.network
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.ons
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.recoc1.cloudfs.acfs
               ONLINE  ONLINE       exatestdbadm01           mounted on /cloudfs,
                                                             STABLE
               ONLINE  ONLINE       exatestdbadm02           mounted on /cloudfs,
                                                             STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       exatestdbadm01           STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.MGMTLSNR
      1        ONLINE  OFFLINE                               169.254.8.18,STABLE
ora.cvu
      1        ONLINE  OFFLINE                               STABLE
ora.dbfsdb.db
      1        ONLINE  ONLINE       exatestdbadm01           Open,STABLE
      2        ONLINE  ONLINE       exatestdbadm02           Open,STABLE
ora.exatestdbadm01.vip
      1        ONLINE  ONLINE       exatestdbadm01           STABLE
ora.exatestdbadm01_2.vip
      1        ONLINE  ONLINE       exatestdbadm01           STABLE
ora.exatestdbadm02.vip
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.exatestdbadm02_2.vip
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.fenikstd.db
      1        ONLINE  ONLINE       exatestdbadm01           Open,STABLE
      2        ONLINE  ONLINE       exatestdbadm02           Open,STABLE
ora.insistst.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.mgmtdb
      1        ONLINE  ONLINE       exatestdbadm02           Open,STABLE
ora.msdp.db
      1        ONLINE  ONLINE       exatestdbadm01           Open,STABLE
      2        ONLINE  ONLINE       exatestdbadm02           Open,STABLE
ora.necotest.db
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.oc4j
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       exatestdbadm01           STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.upgmsdp.db
      1        ONLINE  ONLINE       exatestdbadm01           Open,STABLE
      2        ONLINE  ONLINE       exatestdbadm02           Open,STABLE
--------------------------------------------------------------------------------

-- GI setup is owner by the "grid" OS user

[grid@exatestdbadm01 ~]$ srvctl remove database -d INSISTST
PRCD-1229 : An attempt to access configuration of database INSISTST was rejected because its version 12.1.0.1.0 differs from the program version 12.1.0.2.0. Instead run the program from /u01/app/oracle/product/12.1.0.1/dbhome_1.

-- we need to switch to database owner OS user that is "oracle"

[grid@exatestdbadm01 ~]$ exit
logout
[root@exatestdbadm01 ~]# su - oracle
[oracle@exatestdbadm01 ~]$ . profile_insistst
[oracle@exatestdbadm01 ~]$ srvctl remove database -d INSISTST
Remove the database INSISTST? (y/[n]) y
[oracle@exatestdbadm01 ~]$ exit
logout

--check the srvctl and see that the "INSISTST" database has gone

[root@exatestdbadm01 ~]# su - grid
[grid@exatestdbadm01 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATAC1.dg
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.DBFS_DG.dg
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.LISTENER_BAN.lsnr
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.LISTENER_EBS.lsnr
               ONLINE  INTERMEDIATE exatestdbadm01           Not All Endpoints Re
                                                             gistered,STABLE
               ONLINE  INTERMEDIATE exatestdbadm02           Not All Endpoints Re
                                                             gistered,STABLE
ora.RECOC1.CLOUDFS.advm
               ONLINE  ONLINE       exatestdbadm01           Volume device /dev/a
                                                             sm/cloudfs-444 is on
                                                             line,STABLE
               ONLINE  ONLINE       exatestdbadm02           Volume device /dev/a
                                                             sm/cloudfs-444 is on
                                                             line,STABLE
ora.RECOC1.dg
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.asm
               ONLINE  ONLINE       exatestdbadm01           Started,STABLE
               ONLINE  ONLINE       exatestdbadm02           Started,STABLE
ora.net1.network
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.net2.network
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.ons
               ONLINE  ONLINE       exatestdbadm01           STABLE
               ONLINE  ONLINE       exatestdbadm02           STABLE
ora.recoc1.cloudfs.acfs
               ONLINE  ONLINE       exatestdbadm01           mounted on /cloudfs,
                                                             STABLE
               ONLINE  ONLINE       exatestdbadm02           mounted on /cloudfs,
                                                             STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       exatestdbadm01           STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.MGMTLSNR
      1        ONLINE  OFFLINE                               169.254.8.18,STABLE
ora.cvu
      1        ONLINE  OFFLINE                               STABLE
ora.dbfsdb.db
      1        ONLINE  ONLINE       exatestdbadm01           Open,STABLE
      2        ONLINE  ONLINE       exatestdbadm02           Open,STABLE
ora.exatestdbadm01.vip
      1        ONLINE  ONLINE       exatestdbadm01           STABLE
ora.exatestdbadm01_2.vip
      1        ONLINE  ONLINE       exatestdbadm01           STABLE
ora.exatestdbadm02.vip
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.exatestdbadm02_2.vip
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.fenikstd.db
      1        ONLINE  ONLINE       exatestdbadm01           Open,STABLE
      2        ONLINE  ONLINE       exatestdbadm02           Open,STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       exatestdbadm02           Open,STABLE
ora.msdp.db
      1        ONLINE  ONLINE       exatestdbadm01           Open,STABLE
      2        ONLINE  ONLINE       exatestdbadm02           Open,STABLE
ora.necotest.db
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.oc4j
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       exatestdbadm01           STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       exatestdbadm02           STABLE
ora.upgmsdp.db
      1        ONLINE  ONLINE       exatestdbadm01           Open,STABLE
      2        ONLINE  ONLINE       exatestdbadm02           Open,STABLE
--------------------------------------------------------------------------------





Monday, June 25, 2018

"enq: JS - queue lock" - session stucks when you try to run a scheduler job


-- In one of our test databases sessions started to stuck on waiting for "enq: JS - queue lock" when we tried to run a scheduler job

-- when we query the running job our job is not there

select * from dba_scheduler_running_jobs order by job_name, job_subname nulls first;
--nrr

-- we checked the session trying to run the job and it was waiting on "enq: JS - queue lock"

-- we tried to find what is causing this wait event,which session is blocking our session

SELECT (SELECT inst_id||' - '||username
          FROM gv$session
         WHERE SID = a.SID AND inst_id = a.inst_id) blocker, a.SID,
       ' is blocking ', (SELECT inst_id||' - '||username
                           FROM gv$session
                          WHERE SID = b.SID AND inst_id = b.inst_id) blockee,
       b.SID,b.type,b.lmode,b.request
  FROM gv$lock a, gv$lock b
 WHERE a.BLOCK > 0
   AND b.request > 0
   AND a.id1 = b.id1
   AND a.id2 = b.id2
   AND a.TYPE = b.TYPE
   AND a.inst_id = b.inst_id;
/*
BLOCKER,SID,'ISBLOCKING',BLOCKEE,SID_1,TYPE,LMODE,REQUEST
2 - ,457, is blocking ,INSIS_SCHED,2607,JS,0,6
*/ 

-- in above output session sid 457 runninh on 2.node of RAC is blocking our session running by user INSIS_SCHED

-- lets find out what is this blocking session

select * from gv$session where inst_id = 2 and sid = 457;
/*
INST_ID,SADDR,SID,SERIAL#,AUDSID,PADDR,USER#,USERNAME,COMMAND,OWNERID,TADDR,LOCKWAIT,STATUS,SERVER,SCHEMA#,SCHEMANAME,OSUSER,PROCESS,MACHINE,PORT,TERMINAL,PROGRAM,TYPE,SQL_ADDRESS,SQL_HASH_VALUE,SQL_ID,SQL_CHILD_NUMBER,SQL_EXEC_START,SQL_EXEC_ID,PREV_SQL_ADDR,PREV_HASH_VALUE,PREV_SQL_ID,PREV_CHILD_NUMBER,PREV_EXEC_START,PREV_EXEC_ID,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID,PLSQL_OBJECT_ID,PLSQL_SUBPROGRAM_ID,MODULE,MODULE_HASH,ACTION,ACTION_HASH,CLIENT_INFO,FIXED_TABLE_SEQUENCE,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,TOP_LEVEL_CALL#,LOGON_TIME,LAST_CALL_ET,PDML_ENABLED,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,RESOURCE_CONSUMER_GROUP,PDML_STATUS,PDDL_STATUS,PQ_STATUS,CURRENT_QUEUE_DURATION,CLIENT_IDENTIFIER,BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION,FINAL_BLOCKING_SESSION_STATUS,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION,SEQ#,EVENT#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW,P3TEXT,P3,P3RAW,WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE,WAIT_TIME_MICRO,TIME_REMAINING_MICRO,TIME_SINCE_LAST_WAIT_MICRO,SERVICE_NAME,SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS,SQL_TRACE_PLAN_STATS,SESSION_EDITION_ID,CREATOR_ADDR,CREATOR_SERIAL#,ECID,SQL_TRANSLATION_PROFILE_ID,PGA_TUNABLE_MEM,CON_ID,EXTERNAL_NAME
2,000000014495D2E0,457,51019,0,0000000144037BC0,0,,3,2147483644,0000000153ECAFA8,,ACTIVE,DEDICATED,0,SYS,oracle,339197,exatestdbadm02.anadoluhayat.com.tr,0,UNKNOWN,oracle@exatestdbadm02.anadoluhayat.com.tr (CJQ0),BACKGROUND,000000019DE335F8,907909844,bb94zasv1v6qn,0,30.05.2018 17:06:48,33554434,0000000078CF5448,3679126745,5ms6rbzdnq16t,0,30.05.2018 17:06:40,33629857,,,,,,0,,0,,3044543448,7049,1,15225,0,59,4.05.2018 12:32:37,4487401,NO,NONE,NONE,NO,_ORACLE_BACKGROUND_GROUP_,DISABLED,ENABLED,ENABLED,0,,UNKNOWN,,,UNKNOWN,,,14941,353,PX Deq: Join ACK,sleeptime/senderid,268533758,0000000010017FFE,passes,852812,00000000000D034C,,2017746056,0000000078445C88,2723168908,6,Idle,0,2,WAITING,1520815,-1,0,SYS$BACKGROUND,DISABLED,FALSE,FALSE,FIRST EXEC,0,0000000144037BC0,1,,0,65536,0,

*/

-- as you can see from the above output the blocking session is a background session called CJQ0 and it is waiting on wait event "PX Deq: Join Ack"

-- This situation can be caused by insufficient SGA in the database

-- the solution can be killing the spid of this session

-- first set the "job_queue_processes" parameter to the value "0"

alter system set job_queue_processes = 0 scope = both;

-- you can find the paddr value from above query

select spid from gv$process where inst_id = 2 and addr = '0000000144037BC0';
--339197

-- now ssh to the related RAC node (inst_id = 2) and kill the process 339197

[oracle@exatestdbadm02] /u01/app/oracle/diag/rdbms/insistst/INSISTST2/trace > ps -ef| grep 339197
oracle   165460 156355  0 11:12 pts/1    00:00:00 grep 339197
oracle   339197      1  0 May04 ?        00:10:21 ora_cjq0_INSISTST2
[oracle@exatestdbadm02] /u01/app/oracle/diag/rdbms/insistst/INSISTST2/trace > kill -9 339197

-- when we kill the old process which has problem, the database automatically creates a new cjq0 process

[oracle@exatestdbadm02] /u01/app/oracle/diag/rdbms/insistst/INSISTST2/trace > ps -ef| grep "_cjq"
grid      94449      1  0 May03 ?        01:34:21 mdb_cjq0_-MGMTDB
oracle    96512      1  0 May04 ?        00:14:59 ora_cjq0_AHETST2
orauat   141841      1  0 May04 ?        00:14:45 ora_cjq0_AHEUAT2
oracle   170008      1  0 11:13 ?        00:00:00 ora_cjq0_INSISTST2
oracle   171119 156355  0 11:13 pts/1    00:00:00 grep _cjq
oracle   176484      1  0 Jun20 ?        00:01:24 ora_cjq0_AHEDWH2
orauat   338101      1  0 May04 ?        00:25:10 ora_cjq0_INSISUAT2
[oracle@exatestdbadm02] /u01/app/oracle/diag/rdbms/insistst/INSISTST2/trace >
*/

-- set the "job_queue_processes" parameter back to the original value
alter system set job_queue_processes = 1000 scope = both; 

-- below is the output from alert log of the database while we perform the above jobs

-- alert log from 1.node
/*
Mon Jun 25 11:11:00 2018
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;
Mon Jun 25 11:11:00 2018
Stopping background process CJQ0
Mon Jun 25 11:15:03 2018
Starting background process CJQ0
Mon Jun 25 11:15:03 2018
ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH;
Mon Jun 25 11:15:03 2018
CJQ0 started with pid=70, OS id=103621 , bound to OS numa node 1
*/

-- alert log from 2.node
/*
Mon Jun 25 11:13:16 2018
Restarting dead background process CJQ0
Starting background process CJQ0
Mon Jun 25 11:13:16 2018
CJQ0 started with pid=130, OS id=170008 , bound to OS numa node 0
*/

-- lets check if there is a blocking lock still

SELECT (
SELECT username
FROM gv$session
WHERE sid=a.sid) blocker,
a.sid, ' is blocking ', (
SELECT username
FROM gv$session
WHERE sid=b.sid) blockee,
b.sid
FROM gv$lock a, gv$lock b
WHERE a.block = 1
AND b.request > 0
AND a.id1 = b.id1
AND a.id2 = b.id2;

SELECT (SELECT inst_id||' - '||username
          FROM gv$session
         WHERE SID = a.SID AND inst_id = a.inst_id) blocker, a.SID,
       ' is blocking ', (SELECT inst_id||' - '||username
                           FROM gv$session
                          WHERE SID = b.SID AND inst_id = b.inst_id) blockee,
       b.SID,b.type,b.lmode,b.request
  FROM gv$lock a, gv$lock b
 WHERE a.BLOCK > 0
   AND b.request > 0
   AND a.id1 = b.id1
   AND a.id2 = b.id2
   AND a.TYPE = b.TYPE
   AND a.inst_id = b.inst_id;
--nrr

-- there is no blocking lock

-- lets check the running scheduler jobs and see that our job started to run successfully

select * from dba_scheduler_running_jobs order by job_name, job_subname nulls first;
/*
OWNER,JOB_NAME,JOB_SUBNAME,JOB_STYLE,DETACHED,SESSION_ID,SLAVE_PROCESS_ID,SLAVE_OS_PROCESS_ID,RUNNING_INSTANCE,RESOURCE_CONSUMER_GROUP,ELAPSED_TIME,CPU_USED,DESTINATION_OWNER,DESTINATION,CREDENTIAL_OWNER,CREDENTIAL_NAME,LOG_ID
INSIS_SCHEDULER,CloseDay_PriceDependent,,REGULAR,FALSE,,,,,,+00 00:00:13.685387,,,,,,4294967296
INSIS_SCHEDULER,CloseDay_PriceDependent,CD_BFM,REGULAR,FALSE,648,182,175412,2,BATCH_GROUP,+00 00:00:07.961555,+00 00:00:04.230000,,,,,1559466,00036309
*/