Query on v$asm_diskgroup Hangs

Due to underneath SAN LUN issue, after manually dismounting problem disk group, the queries on v$asm_* views hang forever.

1)  Check blocking sessions, and find blocking session is SID=426, and the final blocking session SID=392.

SQL> select SID,LAST_CALL_ET,EVENT,BLOCKING_SESSION,FINAL_BLOCKING_SESSION 
from v$session where BLOCKING_SESSION is not null;

SID LAST_CALL_ET  EVENT         BLOCKING_SESSION FINAL_BLOCKING_SESSION
 12  2313        enq: DD - contention       426        392
 17  9526        enq: DD - contention       426        392
 23  7726        enq: DD - contention       426        392
 25  527598      enq: DD - contention       426        392
 40  384765      enq: DD - contention       426        392
 418 132675      enq: DD - contention       426        392
 425 3397        enq: DD - contention       426        392
 426 530330      rdbms ipc reply            392        392
 444 15982       enq: DD - contention       426        392

...
..
.

2) Check what is session SID=392, which is ASM RBAL background process.

SQL> select s.sid,p.SPID,p.PNAME,p.BACKGROUND 
       from v$process p, v$session s 
      where s.PADDR=p.ADDR and s.sid in ( 426, 392);

SID        SPID                     PNAME B
---------- ------------------------ ----- -
 392       11670                    RBAL  1
 426       24638

$ ps -eaf | grep 11670
grid 11670 1 0 Mar22 ? 00:11:26 asm_rbal_+ASM1

3) Check which processes are still holding dismounted disk group.

$ lsof | grep ASMD_TEST
oracleasm 1246 grid 4r BLK 253,10 0t0 18446612242469980264 /dev/oracleasm/disks/ASMD_TEST1
oracle_20 2087 grid 14r BLK 253,10 0t0 18446612242469980264 /dev/oracleasm/disks/ASMD_TEST1
oracle_33 3371 grid 14r BLK 253,10 0t0 18446612242469980264 /dev/oracleasm/disks/ASMD_TEST1
oracle_47 4754 grid 17r BLK 253,10 0t0 18446612242469980264 /dev/oracleasm/disks/ASMD_TEST1
...
..
.

$ fuser /dev/oracleasm/disks/ASMD_TEST1
/dev/oracleasm/disks/ASMD_TEST1: 1246 2087 3371 4754 ... ... ..

In this case, ASM instance bounce is required for resolving this issue.

Step by Step Installing Oracle 12c GI and RAC on Linux 7 Using VirtualBox

Great start to build your own RAC with detailed instructions and explanations. For building 18c/19c/20c GI/RAC, this post is still helpful. After completing this lab, you should be in better position as an Oracle RAC DBA.

Contents

Introduction

This article describes the detailed step by step installation of Oracle Database 12c  (12.1.0.2 64-bit) GI and RAC on Linux (Oracle Linux 7.1 64-bit) using VirtualBox (5.0.10). It applies to Oracle 18c and 19c GI and RAC as well.

Subscribe to get access

Read more of this content when you subscribe today.