RAC: How data blocks are loaded into the buffer cache

In this scenario, we will demonstrate how buffer cache is populated by data blocks in a RAC environment.
The following information was copied by Oracle9i Real Application Clusters Concepts Guide and describes the states of a block, when loaded into the buffer cache. It's strange, this information was removed from 10g/11g documentation:

Block Access Modes and Buffer States
An additional concurrency control concept is the buffer state which is the state of a buffer in the local cache of an instance.
The buffer state of a block relates to the access mode of the block.
For example, if a buffer state is exclusive current (XCUR), an instance owns the resource in exclusive mode.
To see a buffer’s state, query the STATUS column of the V$BH dynamic performance view.
The V$BH view provides information about the block access mode and their buffer state names as follows:

* With a block access mode of NULL the buffer state name is CR:
An instance can perform a consistent read of the block.
That is, if the instance holds an older version of the data.
* With a block access mode of S the buffer state name is SCUR:
An instance has shared access to the block and can only perform reads.
* With a block access mode of X the buffer state name is XCUR:
An instance has exclusive access to the block and can modify it.
* With a block access mode of NULL the buffer state name is PI:
An instance has made changes to the block but retains copies of it as past images to record its state before changes.

Only the SCUR and PI buffer states are Real Application Clusters-specific.
There can be only one copy of any one block buffered in the XCUR state in the cluster database at any time.
To perform modifications on a block, a process must assign an XCUR buffer state to the buffer containing the data block.
For example, if another instance requests read access to the most current version of the same block,
then Oracle changes the access mode from exclusive to shared, sends a current read version of the block to the requesting instance
and keeps a PI buffer if the buffer contained a dirty block.
At this point, the first instance has the current block and the requesting instance also has the current block in shared mode.
Therefore, the role of the resource becomes global.
There can be multiple shared current (SCUR) versions of this block cached throughout the cluster database at any time.

First, we create a new LMT/ASSM tablespace:

SQL> CREATE TABLESPACE HELPDESK2 DATAFILE 
  '/oradb/helpdesk2_01.dbf' SIZE 5M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Tablespace created.

SQL> ALTER USER HELPDESK QUOTA UNLIMITED ON HELPDESK2;
User altered.

We connect to Instance 1 and create a new table:

1> CREATE TABLE HELPDESK.TESTTBL (ID NUMBER, TEXT VARCHAR2(100)) TABLESPACE HELPDESK2;
Table created.

We query DBA_EXTENTS:

SELECT *
FROM DBA_EXTENTS
WHERE SEGMENT_NAME='TESTTBL'
AND OWNER='HELPDESK';

   

OWNER	SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	TABLESPACE_NAME	EXTENT_ID	FILE_ID	BLOCK_ID	BYTES	BLOCKS	RELATIVE_FNO
HELPDESK	TESTTBL		TABLE	HELPDESK2	0	2483	9	65536	8	437

LMT/ASSM tablespaces have a minimum extent size of 64K, that's why table's initial size is 64K.
Since tablespace's block size is 8K, table's initial extent occupies 8 blocks and since the starting block's ID is 9, it occupies blocks 9 to 16.
Are all 8 blocks free?
Let's find out:

SQL> SET SERVEROUTPUT ON

DECLARE
        V_OWNER  DBA_SEGMENTS.OWNER%TYPE;
        V_NAME   DBA_SEGMENTS.SEGMENT_NAME%TYPE;
        V_TYPE   DBA_SEGMENTS.SEGMENT_TYPE%TYPE;
        V_TBL    NUMBER;
        V_TBB    NUMBER;
        V_UBL    NUMBER;
        V_UBB    NUMBER;
        V_LUFID  NUMBER;
        V_LUBID  NUMBER;
        V_LUB    NUMBER;
BEGIN
        V_OWNER := 'HELPDESK';
        V_NAME := 'TESTTBL';
        V_TYPE := 'TABLE';
        DBMS_SPACE.UNUSED_SPACE (V_OWNER, V_NAME, V_TYPE, V_TBL, V_TBB, V_UBL, V_UBB, V_LUFID, V_LUBID, V_LUB);
        DBMS_OUTPUT.PUT_LINE ('OWNER: ' || V_OWNER);
        DBMS_OUTPUT.PUT_LINE ('SEGMENT NAME: ' || V_NAME);
        DBMS_OUTPUT.PUT_LINE ('SEGMENT TYPE: ' || V_TYPE);
        DBMS_OUTPUT.PUT_LINE ('TOTAL BLOCKS: ' || V_TBL);
        DBMS_OUTPUT.PUT_LINE ('TOTAL BYTES: ' || V_TBB);
        DBMS_OUTPUT.PUT_LINE ('UNUSED BLOCKS: ' || V_UBL);
        DBMS_OUTPUT.PUT_LINE ('UNUSED BYTES: ' || V_UBB);
        DBMS_OUTPUT.PUT_LINE ('LAST USED EXTENT FILE ID: ' || V_LUFID);
        DBMS_OUTPUT.PUT_LINE ('LAST USED EXTENT BLOCK ID: ' || V_LUBID);
        DBMS_OUTPUT.PUT_LINE ('LAST USED BLOCK: ' || V_LUB);
END;
/
 
OWNER: HELPDESK
SEGMENT NAME: TESTTBL
SEGMENT TYPE: TABLE
TOTAL BLOCKS: 8
TOTAL BYTES: 65536
UNUSED BLOCKS: 5
UNUSED BYTES: 40960
LAST USED EXTENT FILE ID: 2483
LAST USED EXTENT BLOCK ID: 9
LAST USED BLOCK: 3

PL/SQL procedure successfully completed.

No, only 5 blocks are unused.
Now, let's see what was loaded into the buffer caches, when we created the table:

SELECT A.INST_ID,
         C.TABLESPACE_NAME,
         C.FILE_NAME,
         A.BLOCK#,
         B.OWNER,
         B.OBJECT_NAME,
         B.SUBOBJECT_NAME,
         B.OBJECT_TYPE,
         DECODE (A.CLASS#,
                    1, 'data block',
                    2, 'sort block',
                    3, 'save undo block',
                    4, 'segment header',
                    5, 'save undo header',
                    6, 'free list',
                    7, 'extent map',
                    8, '1st level bmb',
                    9, '2nd level bmb',
                    10, '3rd level bmb',
                    11, 'bitmap block',
                    12, 'bitmap index block',
                    13, 'file header block',
                    14, 'unused',
                    15, 'system undo header',
                    16, 'system undo block',
                    17, 'undo header',
                    18, 'undo block',
                    A.CLASS#)
             CLASS,
         DECODE (A.STATUS,
                    'free', 'Not currently in use',
                    'xcur', 'Exclusive',
                    'scur', 'Shared current',
                    'cr', 'Consistent read',
                    'read', 'Being read from disk',
                    'mrec', 'In media recovery mode',
                    'irec', 'In instance recovery mode',
                    A.STATUS)
             STATUS
  FROM GV$BH A, DBA_OBJECTS B, DBA_DATA_FILES C
 WHERE A.OBJD = B.DATA_OBJECT_ID
AND A.FILE# = C.FILE_ID
AND B.OWNER='HELPDESK'
AND B.OBJECT_NAME='TESTTBL'
ORDER BY 4, 1;

   

INST_ID	TABLESPACE_NAME	FILE_NAME	BLOCK#	OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	CLASS	STATUS
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Exclusive

So, TESTTBL's blocks 9 (1st level bitmap block), 10 (2nd level bitmap block) and 11(segment header) were loaded into Instance 1's buffer cache.
These are the 3 used blocks. Let's take a segment header's (11) dump to verify it:

SQL> EXEC DBMS_SPACE_ADMIN.SEGMENT_DUMP('HELPDESK2','437','11');

PL/SQL procedure successfully completed.

We read in the generated trace file:

Segment Dump: segment dba = 644 : 0x6d40000b <--Block 11
Segment Type - Pagetable Segment
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8
                  last map  0x00000000  #maps: 0      offset: 2720
      Highwater::  0x6d40000c  ext#: 0      blk#: 3      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x6d40000c <-- Highwater Mark at block 12   ext#: 0      blk#: 3      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
  Level 1 BMB for High HWM block: 0x6d400009
  Level 1 BMB for Low HWM block: 0x6d400009
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001438
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x6d40000a
  Last Level 1 BMB:  0x6d400009 <-- Block 9
  Last Level II BMB:  0x6d40000a <-- Block 10
  Last Level III BMB:  0x00000000 <-- There is no 3rd level bitmap block
     Map Header:: next  0x00000000  #extents: 1    obj#: 1667683 flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x6d400009  length: 8

  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x6d400009 Data dba:  0x6d40000c
  --------------------------------------------------------

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x6d40000a

Dump of Second Level Bitmap Block
   number: 1       nfree: 1       ffree: 0      pdba:     0x6d40000b
   Inc #: 0 Objd: 1667683 <-- TESTTBL's data_object_id
  opcode:0
 xid:
  L1 Ranges :
  --------------------------------------------------------
   0x6d400009  Free: 5 Inst: 1

  --------------------------------------------------------
Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 1         parent dba:  0x6d40000a   poffset: 0
   unformatted: 5       total: 8         first useful block: 3 <-- 8 blocks total, 5 blocks unused
   owning instance : 1 <-- Instance 1 has the ownership
   instance ownership changed at
   Last successful Search
   Freeness Status:  nf1 0      nf2 0      nf3 0      nf4 0

   Extent Map Block Offset: 4294967295
   First free datablock : 3
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Inc #: 0 Objd: 1667683
  HWM Flag: HWM Set
      Highwater::  0x6d40000c  ext#: 0      blk#: 3      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x6d400009  Length: 8      Offset: 0

   0:Metadata   1:Metadata   2:Metadata   3:unformatted
   4:unformatted   5:unformatted   6:unformatted   7:unformatted
  --------------------------------------------------------

We insert 1 row from Instance 1:

1> INSERT INTO HELPDESK.TESTTBL VALUES(1, 'TEXT1');

1 row created.

All TESTTBL's blocks were loaded into Instance 1's buffer cache:   

INST_ID	TABLESPACE_NAME	FILE_NAME	BLOCK#	OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	CLASS	STATUS
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Consistent read
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	13	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	14	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	15	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	16	HELPDESK	TESTTBL		TABLE	data block	Exclusive

We commit:

1> COMMIT;
Commit complete.

Let's find out the block in which this row was stored:

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(A.ROWID) BLOCK#, A.*
FROM HELPDESK.TESTTBL A;

   

BLOCK#	ID	TEXT
16	1	TEXT1

The block where the row was stored (16) remained in exclusive (xcur) state and all other data blocks changed their state to shared current (scur):   

INST_ID	TABLESPACE_NAME	FILE_NAME	BLOCK#	OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	CLASS	STATUS
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Consistent read
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	13	HELPDESK	TESTTBL		TABLE	data block	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	14	HELPDESK	TESTTBL		TABLE	data block	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	15	HELPDESK	TESTTBL		TABLE	data block	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	16	HELPDESK	TESTTBL		TABLE	data block	Exclusive

We insert one more row from Intance 2, this time, and we commit:

2> INSERT INTO HELPDESK.TESTTBL VALUES(2, 'TEXT2');
1 row created.

2> commit;
Commit complete.

This row was placed in block 12:

BLOCK#	ID	TEXT
12	2	TEXT2
16	1	TEXT1

Instance 1 kept an image of 1st level bmb (pi), Instance 2 got exclusive access (xcur) to 1st level bmb and 2nd level bmb, read (cr) the segment header and got exclusive access (xcur) to block 12, where the new row was stored:   

INST_ID	TABLESPACE_NAME	FILE_NAME	BLOCK#	OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	CLASS	STATUS
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	pi
2	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Exclusive
2	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Consistent read
2	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Consistent read
2	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Shared current
2	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Consistent read
2	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Shared current
2	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	16	HELPDESK	TESTTBL		TABLE	data block	Exclusive

We, now, delete the 2nd row from Instance 1:

1> DELETE FROM HELPDESK.TESTTBL WHERE ID=2;
1 row deleted.

1> COMMIT;
Commit complete.

Instance 1 got block 12 in exclusive (xcur) state:   

INST_ID	TABLESPACE_NAME	FILE_NAME	BLOCK#	OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	CLASS	STATUS
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	pi
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Exclusive
2	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	pi
1	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	pi
2	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Consistent read
2	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Shared current
2	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Consistent read
1	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Consistent read
1	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Consistent read
2	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	pi
1	HELPDESK2	/oradb/helpdesk2_01.dbf	16	HELPDESK	TESTTBL		TABLE	data block	Exclusive

RAC: Log switch frequency script

The following script reports how much time passed between log switches in a time period.
Although it's written to provide information per instance in RACs, it will also work in Single Instances.

SELECT C.INSTANCE,
         C.THREAD#,
         B.SEQUENCE# "START SEQUENCE",
         TO_CHAR (B.FIRST_TIME, 'DD-MM-YYYY HH24:MI:SS') "START TIME",
         A.SEQUENCE# "END SEQUENCE",
         TO_CHAR (A.FIRST_TIME, 'DD-MM-YYYY HH24:MI:SS') "END TIME",
         TO_CHAR (
            TRUNC (SYSDATE)
            + NUMTODSINTERVAL ( (A.FIRST_TIME - B.FIRST_TIME) * 86400,
                               'SECOND'),
            'HH24:MI:SS')
            DURATION
    FROM V$LOG_HISTORY A, V$LOG_HISTORY B, V$THREAD C
   WHERE     A.SEQUENCE# = B.SEQUENCE# + 1
         AND A.THREAD# = C.THREAD#
         AND B.THREAD# = C.THREAD#
         AND A.FIRST_TIME BETWEEN TO_DATE ('23-07-2012 00:00:00',
                                           'DD-MM-YYYY HH24:MI:SS')
                              AND TO_DATE ('24-07-2012 00:00:00',
                                           'DD-MM-YYYY HH24:MI:SS')
ORDER BY 4;

Let's try it for a day, e.g. 23/07/12:   

INSTANCE	THREAD#	START SEQUENCE	START TIME	END SEQUENCE	END TIME	DURATION
OTE1	1	436571	22-07-12 23:59:44	436572	23-07-12 00:08:12	00:08:27
OTE2	2	402951	22-07-12 23:59:46	402952	23-07-12 00:15:31	00:15:45
OTE1	1	436572	23-07-12 00:08:12	436573	23-07-12 00:11:48	00:03:36
...	...	...	...	...	...	...
OTE2	2	403315	23-07-12 23:57:51	403316	23-07-12 23:59:42	00:01:51
OTE1	1	436873	23-07-12 23:58:15	436874	23-07-12 23:59:10	00:00:54
OTE1	1	436874	23-07-12 23:59:10	436875	23-07-12 23:59:40	00:00:29

Oracle Enterprise Manager 11g (11.1.0.1.0) agent installation using response file

Download and extract agent's binaries in a directory in your server, e.g. /var/opt/oragrid/binaries.
Open file /var/opt/oragrid/binaries/hpi/response/additional_agent.rsp and do the following changes:

SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
DECLINE_SECURITY_UPDATES=TRUE
#MYORACLESUPPORT_USERNAME=
#MYORACLESUPPORT_PASSWORD=
#COLLECTOR_SUPPORTHUB_URL=
ORACLE_AGENT_HOME_LOCATION=[directory where agent software will be installed, e.g. /var/opt/oragrid]
OMS_HOST=[server's name where OMS is installed]
OMS_PORT=[server's port, default 4889]
AGENT_REGISTRATION_PASSWORD=[sysman's password]

If you are installing an agent in a RAC, change also:

CLUSTER_NODES="[node1's name],[node2's name],..."

If you don't want to mess with server's oracle central inventory, use a custom inventory, by creating an oraInst.loc file, e.g. /var/opt/oragrid/oraInst.loc.
Open it and add:

inventory_loc=[path where this inventory will be placed, e.g. /var/opt/oragrid/oraInventory]
inst_group=[agent's user group, dba or oinstall]

Go to /var/opt/oragrid/binaries/hpi/agent/install and run:

runInstaller -invPtrLoc /var/opt/oragrid/oraInst.loc -silent -responseFile /var/opt/oragrid/binaries/hpi/response/additional_agent.rsp

If you are installing in a RAC, find cluster name:

cd $CRS_HOME/bin
./cemutlo -n

Now, run:

runInstaller -invPtrLoc /var/opt/oragrid/oraInst.loc -silent -responseFile /var/opt/oragrid/binaries/hpi/response/additional_agent.rsp -CLUSTER_NAME=[cluster name] CLUSTER_NODES="{[node1's name],[node2's name],...}"

There is a bug that fills emagent.trc pretty fast.
So, at the end, install patch 9756966.

Finally, run [agent's home]/root.sh as root.

In the RAC installation, all onoff patches that were supposed to be installed after agent's installation, failed, because although I had specified my custom oraInst.loc in the runInstaller command, OPatch was using oraInst.loc under agent's home directory.
Probably, you will not face this issue, if you don't specify a custom inventory.
If that happens also to you, go to [agent's home]/install/oneoffs/111010 and install them manually.

If you try to monitor a 9i database not in 9.2.0.8.0 patchset, you will probably get many ORA-07445 in the alert log and core dumps in your database's cdump directory.
To fix this, edit [agent's home]/sysman/config/emd.properties and add the parameter AvoidOCIPing=1.
Then, restart the agent.

RAC: Library Cache locks and Pins

Scripts presented here and here are not very helpful for RAC environments, because x$ tables are local to instances and if the blocker is in a different instance than the blocked, you will get no results.

SELECT INST_ID,
       HANDLE,
       GRANT_LEVEL,
       REQUEST_LEVEL,
       RESOURCE_NAME1,
       RESOURCE_NAME2,
       PID,
       TRANSACTION_ID0,
       TRANSACTION_ID1,
       OWNER_NODE,
       BLOCKED,
       BLOCKER,
       STATE
  FROM GV$GES_BLOCKING_ENQUEUE;

Querying GV$GES_BLOCKING_ENQUEUE you will see in RESOURCE_NAME1 column, in a library cache lock entries like "[0x1b370d][0x81b0000],[LA]" and in library cache pins "[0x1b370d][0x81b0000],[NA]".
The range for library cache locks is LA to LZ and for library cache pins NA to NZ.

The blocker is the session with value "1" in the BLOCKER column.

Move huge database's datafiles to new filesystems

Our eBS database is 14+ TB/2100+ datafiles big and we have to move all its datafiles to new filesystems, taking into account their physical reads, so no more than 1 heavily read datafile would be per filesystem.
We will be given 100 200GB new filesystems and we will leave 10GB empty in each, so only 190GB per filesystem will be used.
The old naming convention is "/oracleXXX/oradata/" and the new will be "/oraerpXXX/oradata".

A manual approach of this case is almost impossible to be implemented for a database of this size, even if we do not consider the "physical reads" clause.

To order our datafiles by physical reads (or writes) we can use the following script, which uses AWR snapshots (10g+):

SELECT A.FILENAME,
         D.TABLESPACE_NAME,
         D.CONTENTS,
         E.BYTES,
         NVL (SUM (A.PHYRDS), 0) READS,
         NVL (SUM (A.PHYWRTS), 0) WRITES
    FROM DBA_HIST_FILESTATXS A,
         V$TABLESPACE C,
         DBA_TABLESPACES D,
         DBA_DATA_FILES E
   WHERE     A.SNAP_ID = 15400   --LAST SNAPSHOT
         AND A.TS# = C.TS#
         AND C.NAME = D.TABLESPACE_NAME
         AND A.FILENAME = E.FILE_NAME
GROUP BY A.FILENAME,
         D.TABLESPACE_NAME,
         D.CONTENTS,
         E.BYTES
UNION
  SELECT A.FILENAME,
         D.TABLESPACE_NAME,
         D.CONTENTS,
         E.BYTES,
         NVL (SUM (A.PHYRDS), 0) READS,
         NVL (SUM (A.PHYWRTS), 0) WRITES
    FROM DBA_HIST_TEMPSTATXS  A,
         V$TABLESPACE C,
         DBA_TABLESPACES D,
         DBA_TEMP_FILES E
   WHERE     A.SNAP_ID = 15400   --LAST SNAPSHOT
         AND A.TS# = C.TS#
         AND C.NAME = D.TABLESPACE_NAME
         AND A.FILENAME = E.FILE_NAME
GROUP BY A.FILENAME,
         D.TABLESPACE_NAME,
         D.CONTENTS,
         E.BYTES
ORDER BY 5 DESC;

Or we may use a script reading GV$FILESTAT, GV$TEMPSTAT, which keep statistics since instance startup:

SELECT B.FILE_NAME,
         C.TABLESPACE_NAME,
         C.CONTENTS,
         B.BYTES,
         NVL (SUM (A.PHYRDS), 0) READS,
         NVL (SUM (A.PHYWRTS), 0) WRITES
    FROM GV$FILESTAT A, DBA_DATA_FILES B, DBA_TABLESPACES C
   WHERE A.FILE# = B.FILE_ID AND B.TABLESPACE_NAME = C.TABLESPACE_NAME
GROUP BY B.FILE_NAME,
         C.TABLESPACE_NAME,
         C.CONTENTS,
         B.BYTES
UNION
  SELECT B.FILE_NAME,
         C.TABLESPACE_NAME,
         C.CONTENTS,
         B.BYTES,
         NVL (SUM (A.PHYRDS), 0) READS,
         NVL (SUM (A.PHYWRTS), 0) WRITES
    FROM GV$TEMPSTAT A, DBA_TEMP_FILES B, DBA_TABLESPACES C
   WHERE A.FILE# = B.FILE_ID AND B.TABLESPACE_NAME = C.TABLESPACE_NAME
GROUP BY B.FILE_NAME,
         C.TABLESPACE_NAME,
         C.CONTENTS,
         B.BYTES
ORDER BY 5 DESC;                 --6 for WRITES

First, we create these tables:

CREATE TABLE HELPDESK.DISKS
(
   DISK_ID     NUMBER,
   SIZE_MAX    NUMBER,
   SIZE_USED   NUMBER,
   PATH_NAME   VARCHAR2 (513 BYTE)
)
TABLESPACE HELPDESK
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 128 K
         NEXT 128 K
         MINEXTENTS 1
         MAXEXTENTS UNLIMITED
         PCTINCREASE 0
         BUFFER_POOL DEFAULT)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE TABLE HELPDESK.DFILES
(
   DISK_ID           NUMBER,
   NAME              VARCHAR2 (513 BYTE),
   NAME_NEW          VARCHAR2 (513 BYTE),
   BYTES             NUMBER,
   TABLESPACE_NAME   VARCHAR2 (30 BYTE),
   CONTENTS          VARCHAR2 (9 BYTE),
   READS             NUMBER,
   WRITES            NUMBER,
   BACKUP_COM        VARCHAR2 (2000 BYTE),
   ALTER_COM         VARCHAR2 (2000 BYTE)
)
TABLESPACE HELPDESK
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 128 K
         NEXT 128 K
         MINEXTENTS 1
         MAXEXTENTS UNLIMITED
         PCTINCREASE 0
         BUFFER_POOL DEFAULT)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

GRANT ALL ON HELPDESK.DISKS TO SYSTEM;

GRANT ALL ON HELPDESK.DFILES TO SYSTEM;

In DISKS table, we will keep record of how much size of each new filesystem will be used (SIZE_USED).
In DFILES, we will get the new datafiles' location (NAME_NEW) and we will, automatically, create the commands needed for NetBackup to restore them to their new locations (BACKUP_COM) and the "ALTER DATABASE RENAME FILE [old datafile location] TO [new datafile location]" commands (ALTER_COM).

We create and run the following procedure:

CREATE OR REPLACE PROCEDURE SYSTEM.DISK_GROUPING
IS
   --ORDERING IN THESE CURSORS IS NEEDED, IF YOU WANT TO TAKE INTO ACCOUNT THE PHYSICAL READS
   CURSOR C1
   IS
        SELECT NAME, BYTES, TABLESPACE_NAME
          FROM HELPDESK.DFILES
      ORDER BY READS DESC;

   CURSOR C2
   IS
        SELECT DISK_ID,
               SIZE_MAX,
               SIZE_USED,
               PATH_NAME
          FROM HELPDESK.DISKS
      ORDER BY SIZE_USED;
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE HELPDESK.DISKS';

   FOR I IN 1 .. 100
   LOOP        --CREATE 100 DISKS WITH 190GB (204010946560 bytes) USABLE SPACE
      INSERT INTO HELPDESK.DISKS (DISK_ID,
                                  SIZE_MAX,
                                  SIZE_USED,
                                  PATH_NAME)
           VALUES (I,
                   204010946560,
                   0,
                   '/oraerp' || I || '/oradata/');
   END LOOP;

   EXECUTE IMMEDIATE 'TRUNCATE TABLE HELPDESK.DFILES';

   INSERT INTO HELPDESK.DFILES (NAME,
                                TABLESPACE_NAME,
                                CONTENTS,
                                BYTES,
                                READS,
                                WRITES)        --PUT DATAFILES IN DFILES TABLE
        SELECT B.FILE_NAME,
               C.TABLESPACE_NAME,
               C.CONTENTS,
               B.BYTES,
               NVL (SUM (A.PHYRDS), 0) READS,
               NVL (SUM (A.PHYWRTS), 0) WRITES
          FROM GV$FILESTAT A, DBA_DATA_FILES B, DBA_TABLESPACES C
         WHERE A.FILE# = B.FILE_ID AND B.TABLESPACE_NAME = C.TABLESPACE_NAME
      GROUP BY B.FILE_NAME,
               C.TABLESPACE_NAME,
               C.CONTENTS,
               B.BYTES;

   INSERT INTO HELPDESK.DFILES (NAME,
                                TABLESPACE_NAME,
                                CONTENTS,
                                BYTES,
                                READS,
                                WRITES)        --PUT TEMPFILES IN DFILES TABLE
        SELECT B.FILE_NAME,
               C.TABLESPACE_NAME,
               C.CONTENTS,
               B.BYTES,
               NVL (SUM (A.PHYRDS), 0) READS,
               NVL (SUM (A.PHYWRTS), 0) WRITES
          FROM GV$TEMPSTAT A, DBA_TEMP_FILES B, DBA_TABLESPACES C
         WHERE A.FILE# = B.FILE_ID AND B.TABLESPACE_NAME = C.TABLESPACE_NAME
      GROUP BY B.FILE_NAME,
               C.TABLESPACE_NAME,
               C.CONTENTS,
               B.BYTES;

   COMMIT;

   FOR DFILE IN C1
   LOOP
      FOR DISK IN C2
      LOOP
         IF DFILE.BYTES < DISK.SIZE_MAX - DISK.SIZE_USED
         THEN
            UPDATE HELPDESK.DFILES
               SET DISK_ID = DISK.DISK_ID,
                   NAME_NEW =
                      DISK.PATH_NAME
                      || SUBSTR (DFILE.NAME,
                                 INSTR (DFILE.NAME,
                                        '/',
                                        3,
                                        3)
                                 + 1)
             WHERE NAME = DFILE.NAME;

            UPDATE HELPDESK.DFILES
               SET BACKUP_COM = 'change ' || NAME || ' to ' || NAME_NEW,
                   ALTER_COM =
                         'ALTER DATABASE RENAME FILE '''
                      || NAME
                      || ''' TO '''
                      || NAME_NEW
                      || ''';'
             WHERE NAME = DFILE.NAME;

            UPDATE HELPDESK.DISKS
               SET SIZE_USED = SIZE_USED + DFILE.BYTES
             WHERE DISK_ID = DISK.DISK_ID;

            COMMIT;
            EXIT;
         END IF;
      END LOOP;
   END LOOP;
END DISK_GROUPING;
/

Now, querying DISKS, we see:

DISK_ID	SIZE_MAX	SIZE_USED	PATH_NAME
1	204,010,946,560	155,508,015,104	/oraerp1/oradata/
2	204,010,946,560	153,190,662,144	/oraerp2/oradata/
3	204,010,946,560	151,382,917,120	/oraerp3/oradata/
4	204,010,946,560	164,322,344,960	/oraerp4/oradata/
...	...	...	...
98	204,010,946,560	151,424,925,696	/oraerp98/oradata/
99	204,010,946,560	151,666,032,640	/oraerp99/oradata/
100	204,010,946,560	152,240,717,824	/oraerp100/oradata/

So, for instance, in the "/oraerp3/oradata/" 141GB of total 200GB will be used.

Querying DFILES (SELECT DISK_ID, NAME, NAME_NEW, READS FROM HELPDESK.DFILES ORDER BY DISK_ID, READS DESC;), we see:

DISK_ID	NAME	NAME_NEW	READS
1	/oracle89/oradata/ar_payment_schedules_all_011.dbf	/oraerp1/oradata/ar_payment_schedules_all_011.dbf	2,235,050,034
1	/oracle36/oradata/xxra_interfacex05.dbf	/oraerp1/oradata/xxra_interfacex05.dbf	26,396,759
1	/oracle8/oradata/user_data18.dbf	/oraerp1/oradata/user_data18.dbf	20,936,923
1	/oracle87/oradata/user_index81.dbf	/oraerp1/oradata/user_index81.dbf	19,653,078
1	/oracle78/oradata/ar_receivable_applications_all_007.dbf	/oraerp1/oradata/ar_receivable_applications_all_007.dbf	9,722,306
1	/oracle92/oradata/ax_sle_lines_078.dbf	/oraerp1/oradata/ax_sle_lines_078.dbf	7,687,651
1	/oracle78/oradata/czx26.dbf	/oraerp1/oradata/czx26.dbf	2.474.694
...	...	...	...

Observe how datafiles are spread in each filesystem, according to physical reads.

CAUTION: If you have, for instance, 2 or more datafiles, e.g. named "user_data18.dbf" in different old filesystems, you must check these datafiles will not be placed in the same new filesystem.
This procedure does not guarantee no overlaps will occur.

Blocking Sessions

Summary
The script following finds the blocking sessions in the database:

SELECT                                                           /*+ RULE */
        LPAD ('--->', DECODE (A.REQUEST, 0, 0, 5)) || A.SID SID,
         B.SERIAL#,
         DECODE (TRUNC (SYSDATE - LOGON_TIME),
                 0, NULL,
                 TRUNC (SYSDATE - LOGON_TIME) || ' Days' || ' + ')
         || TO_CHAR (
               TO_DATE (TRUNC (MOD (SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'),
               'HH24:MI:SS')
            LOGON,
         B.STATUS,
         B.SCHEMANAME SCHEMA,
         DECODE (O.OWNER || '.' || O.OBJECT_NAME,
                 '.', NULL,
                 O.OWNER || '.' || O.OBJECT_NAME)
            OBJECT,
         O.OBJECT_TYPE,
         B.OSUSER,
         B.MACHINE,
         B.MODULE,
         B.PROGRAM,
         DECODE (BLOCK, 0, NULL, 'BLOCKER')
         || DECODE (REQUEST, 0, NULL, '-->WAITER')
            BLOCKER,
         DECODE (A.LMODE,
                 0, 'None',
                 1, 'Null',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive',
                 A.LMODE)
            LMODE,
         DECODE (
            TRUNC (CTIME / 86400),
            0, TO_CHAR (TO_DATE (CTIME, 'SSSSS'), 'HH24:MI:SS'),
            TRUNC (CTIME / 86400) || ' Days + '
            || TO_CHAR (
                  TO_DATE (CTIME - (TRUNC (CTIME / 86400)) * 86400, 'SSSSS'),
                  'HH24:MI:SS'))
            TIME,
         A.TYPE,
            'alter system kill session '
         || ''''
         || A.SID
         || ', '
         || B.SERIAL#
         || ''''
         || ' immediate;'
            KILL_SESSION,
         DECODE (
            OBJECT_TYPE,
            NULL, NULL,
               'Dbms_Rowid.rowid_create(1, '
            || ROW_WAIT_OBJ#
            || ', '
            || ROW_WAIT_FILE#
            || ',
'
            || ROW_WAIT_BLOCK#
            || ', '
            || ROW_WAIT_ROW#
            || ')')
            ROW_ID
    FROM V$LOCK A, V$SESSION B, DBA_OBJECTS O
   WHERE A.SID = B.SID AND (LMODE = 0 OR BLOCK = 1)
         AND O.OBJECT_ID(+) =
                DECODE (B.ROW_WAIT_OBJ#, -1, NULL, B.ROW_WAIT_OBJ#)
ORDER BY A.ID1, A.REQUEST;

For RAC databases the above script will not discover the blockings between sessions in different nodes.
Try this one:

  SELECT B.INST_ID,
         LPAD ('--->', DECODE (A.REQUEST, 0, 0, 5)) || A.SID SID,
         A.ID1,
         A.ID2,
         A.LMODE,
         A.BLOCK,
         A.REQUEST,
         DECODE (A.TYPE,
                 'MR', 'Media Recovery',
                 'RT', 'Redo Thread',
                 'UN', 'User Name',
                 'TX', 'Transaction',
                 'TM', 'DML',
                 'UL', 'PL/SQL User Lock',
                 'DX', 'Distributed Xaction',
                 'CF', 'Control File',
                 'IS', 'Instance State',
                 'FS', 'File Set',
                 'IR', 'Instance Recovery',
                 'ST', 'Disk Space Transaction',
                 'TS', 'Temp Segment',
                 'IV', 'Library Cache Invalidation',
                 'LS', 'Log Start or Switch',
                 'RW', 'Row Wait',
                 'SQ', 'Sequence Number',
                 'TE', 'Extend Table',
                 'TT', 'Temp Table',
                 A.TYPE)
            LOCK_TYPE,
         B.PROGRAM,
         B.OSUSER,
         B.USERNAME,
         B.STATUS,
         B.MODULE,
         B.ACTION,
         B.LOGON_TIME,
         B.LAST_CALL_ET,
            'alter system kill session '
         || ''''
         || A.SID
         || ', '
         || B.SERIAL#
         || ''''
         || ' immediate;'
            KILL_SESSION,
         DECODE (
            OBJECT_TYPE,
            NULL, NULL,
               'Dbms_Rowid.rowid_create(1, '
            || ROW_WAIT_OBJ#
            || ', '
            || ROW_WAIT_FILE#
            || ', '
            || ROW_WAIT_BLOCK#
            || ', '
            || ROW_WAIT_ROW#
            || ')')
            ROW_ID
    FROM GV$LOCK A, GV$SESSION B, DBA_OBJECTS O
   WHERE     (A.ID1, A.ID2) IN (SELECT ID1, ID2
                                  FROM GV$LOCK
                                 WHERE LMODE = 0)
         AND A.INST_ID = B.INST_ID
         AND A.SID = B.SID
         AND O.OBJECT_ID(+) =
                DECODE (B.ROW_WAIT_OBJ#, -1, NULL, B.ROW_WAIT_OBJ#)
ORDER BY A.ID1, A.ID2, A.REQUEST;

Tip: If the column BLOCK=2, 'Global' then this lock is global, it comes from the other instance.
Tip: The last column of the query for blocked sessions is ROWID of the record!
To find the exact locked row execute:

SELECT *
  FROM OBJECT
 WHERE ROWID = DBMS_ROWID.ROWID_CREATE (1,
                                        48804,
                                        31,
                                        114809,
                                        210);

Identify waiting sessions

Summary
The first and most important script about Oracle Wait Interface (OWI), is where current sessions waiting:

  SELECT A.INST_ID,
         A.SID,
         B.MODULE,
         A.SEQ#,
         A.EVENT,
         A.P1TEXT,
         A.P1,
         A.P1RAW,
         A.P2TEXT,
         A.P2,
         A.P2RAW,
         A.P3TEXT,
         A.P3,
         A.P3RAW,
         A.WAIT_TIME,
         A.SECONDS_IN_WAIT,
         A.STATE,
         B.SERIAL#,
         B.USERNAME,
         B.OSUSER,
         B.PADDR,
         B.LOGON_TIME,
         B.PROCESS,
         B.SQL_HASH_VALUE,
         B.SADDR,
         B.ROW_WAIT_OBJ#,
         B.ROW_WAIT_FILE#,
         B.ROW_WAIT_BLOCK#,
         B.ROW_WAIT_ROW#,
            'alter system kill session '
         || ''''
         || B.SID
         || ', '
         || B.SERIAL#
         || ''''
         || ' immediate;'
            KILL_SESSION
    FROM GV$SESSION_WAIT A, GV$SESSION B
   WHERE     A.INST_ID = B.INST_ID
         AND A.SID = B.SID
         AND B.USERNAME IS NOT NULL
         AND B.TYPE <> 'BACKGROUND'
         AND A.EVENT IN
                ('db file sequential read',
                 'db file scattered read',
                 'latch free',
                 'direct path read',
                 'direct path write',
                 'enqueue',
                 'library cache pin',
                 'library load lock',
                 'buffer busy waits',
                 'latch: cache buffers chains',
                 'SQL*Net message from dblink',
                 'free buffer waits',
                 'cache buffers chains',
                 'library cache lock',
                 'global cache open x',
                 'global cache cr request',
                 'queue messages')
ORDER BY A.SECONDS_IN_WAIT DESC;

Some useful explanations
sid, serial#, status, logon_time, osuser, username, module, action, program: come from gv$session information
kill_session_sql: is the sql statement for killing the session
spid: is the unix process id on the database server, in case you want to $>kill -9 spid
sql_hash_value: is the SQL_ADDRESS to identify the SQL statement that is currently being executed. You'll need it for explain plans, etc.
event, latch_name, sec: wait events and how much time is waiting!
p1, p2, p3: arguments to find the object related to waiting

Tip: The query filters only the most important wait events!

IO wait events
------------------------------
db file sequential read
db file scattered read
direct path read
direct path write

Memory wait events
-------------------------------
Buffer Cache wait events
latch free
buffer busy waits
cache buffers chains
free buffer waits

Library Cache wait events
library cache lock
library cache pin
library load lock

Transaction wait events
----------------------------
enqueues

RAC wait events
---------------------------
global cache open x
global cache cr request
queue messages

Temporary tablespace SS Contention In RAC

Space Sharing
As instances allocate temp space, they soft reserve the space in their private SGA. When a process runs out of any new allocatable space in the temp tablespace, it requests the other instances in the cluster to free up any unused space. This is done through a Cross Instance Call. the CIC is protected by the SS enqueue. The SS enqueue is taken on the tablespace (p1) and instance (p2). The request for free space is done by:

1. Taking SS enqueue on the tablespace / instance. this means any process trying to steal space on behalf of this tablespace will block on this enqueue.
2. CIC. The space-release-CIC serializes on the CIC type. Ie., On the entire node any process trying to steal space on behalf of ANY tablespace will have to wait for the CIC to complete. Note that even if you create 10 temp tablespaces, only one CIC can be outstanding at any time.
a. Under the CIC, the SMONs in the other instances release the free space if any and acknowledge the completion of the CIC.
b. When all SMONs acknowledge, the CIC completes.

The above process may hung resulting to temp space allocation imbalance.
A big batch process running on Node 2 could consume a big portion of the temporary tablespace.
Sessions running on Node 1 of need for temp/sort segments, cannot allocate them, since there is not any available for this node,
so they request them from Node 2 [SS enqueue], which cannot give them back since the batch process is still running.
Querying GV$SORT_SEGMENT shows FREE_BLOCKS=0.
The enqueue is shown at GV$SESSION_WAIT:

  SELECT A.INST_ID,
         A.SID,
         B.MODULE,
         A.SEQ#,
         A.EVENT,
         A.P1TEXT,
         A.P1,
         A.P1RAW,
         A.P2TEXT,
         A.P2,
         A.P2RAW,
         A.P3TEXT,
         A.P3,
         A.P3RAW,
         A.WAIT_TIME,
         A.SECONDS_IN_WAIT,
         A.STATE,
         B.SERIAL#,
         B.USERNAME,
         B.OSUSER,
         B.PADDR,
         B.LOGON_TIME,
         B.PROCESS,
         B.SQL_HASH_VALUE,
         B.SADDR,
         B.ROW_WAIT_OBJ#,
         B.ROW_WAIT_FILE#,
         B.ROW_WAIT_BLOCK#,
         B.ROW_WAIT_ROW#
    FROM GV$SESSION_WAIT A, GV$SESSION B
   WHERE     A.INST_ID = B.INST_ID
         AND A.SID = B.SID
         AND B.USERNAME IS NOT NULL
         AND B.TYPE <> 'BACKGROUND'
         AND A.EVENT IN
                ('db file sequential read',
                 'db file scattered read',
                 'latch free',
                 'direct path read',
                 'direct path write',
                 'enqueue',
                 'library cache pin',
                 'library cache load lock',
                 'buffer busy waits',
                 'free buffer waits',
                 'library cache lock',
                 'latch: cache buffers chains')
ORDER BY A.SECONDS_IN_WAIT DESC;

  

INST_ID	SID	MODULE	SEQ#	EVENT	P1TEXT	P1	P1RAW	P2TEXT	P2	P2RAW	P3TEXT	P3	P3RAW	WAIT_TIME	SECONDS_IN_WAIT	STATE	SERIAL#	USERNAME
1	81	? @ecapps1 (TNS V1-V3)	13883	enqueue	name|mode	1397948422	53530006	id1	2	2	id2	2	2	0	2124	WAITING	18872	APPS
1	94	JDBC Thin Client	13883	enqueue	name|mode	1397948422	53530006	id1	2	2	id2	2	2	0	2124	WAITING	13167	APPS
2	168	INVTTMTX	8230	enqueue	name|mode	1397948422	53530006	id1	2	2	id2	2	2	0	2124	WAITING	9122	APPS
2	221	CSXSRISR	7243	enqueue	name|mode	1397948422	53530006	id1	2	2	id2	2	2	0	2124	WAITING	6548	APPS

P1 can be decoded and show us the enqueue type:

SELECT CHR (BITAND (1397948422,                                           --P1
                               -16777216) / 16777215)
       || CHR (BITAND (1397948422,                                        --P1
                                  16711680) / 65535)
          LOCK_TYPE,
       MOD (1397948422,                                                   --P1
                       16) LOCK_MODE
  FROM DUAL;

LOCK_TYPE	LOCK_MODE
SS	6

Even after the batch process has finished at Node 2 and the blocks in the temporary tablespace are freed, there is no reallocation of them to Node 1, so that the blocked sessions will be served.
These sessions, and every new requesting space in the temporary tablespace at Node 1, will remain blocked.
Bouncing the database will not resolve the issue, since RAC “remembers” how the temporary tablespace was shared between the nodes at the point of the shutdown, so in a few minutes you will be facing the same problem.
You should create a new temporary tablespace (e.g. TEMP2) and set it as “default temporary” for the database:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

After that you should kill all the sessions blocked by a SS enqueue.

If your database is 10gR2, you may have at your disposal another way to solve this:
From 10.2.0.3 onwards, one off patch is available for 10.1.0.5 as part of Bug 4882834 a manual method is provided to free up temp space from local instance's cache. Each invocation of the command will free up a maximum of (100 extents, 0.1% of cached free space).
The syntax is:

ALTER SESSION SET EVENTS 'immediate trace name drop_segments level tablespace_number+1';

If tablespace_number+1 is of tablespace group, then space will be freed from all the tablespaces in the group. The command impacts the local instance's cache.