ASM: Queries Hanging (v$asm_disk)

Solved Problem When ASM Queries on v$asm_disk Hang / Block 

Wenn trying to display ASM information, Oracle Cloud Control (Enterprise Manager) displays nothing.

Also when trying to perform some queries on ASM, they "hang" and no answer is returned.

(For example: SELECT * FROM v$asm_disk; hangs)

Commands with the ASM tool asmcmdh still work:

oracle@<Host>:~/ [+ASM] asmcmdh

ASMCMD> lsdsk -p

Group_Num  Disk_Num      Incarn  Mount_Stat  Header_Stat  Mode_Stat  State   Path

        1         0  2526484498  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/grid

        2         6  2526484516  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u01_07

        2         7  2526484517  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u01_08

        2         8  2526484518  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u01_09

        2         9  2526484519  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u01_10

        3         0  2526484503  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u02_01

        3         1  2526484505  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u02_02

        3         2  2526484504  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u02_03

        3         3  2526484507  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u02_04

        3         4  2526484506  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u02_05

The Metalink Doc ID 8786114.8 suggests to verify / correct the ASM discovery string in order to exclude "invalid devices"

like /dev/kmem or other devices that should not be scanned by the ASM disk discovery.

For example it suggests not to use /dev/* as an ASM disk discovery string.

Querying the DB for the ASM disk discovery string, returns:

SQL> show parameter asm_diskstring
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring                       string      /dev/ASM

Listing the mount points using the result from above, shows that still some mount points exits (highlighted)

that are not valid any more. (i.e. no storage is mounted on these mount points any more)

(These are not listed be the asmcmdh command from above.)

(This is not directly apparent from the result below. But I knew, that nothing was mounted at these mount points any more.)

oracle@<Host>:~/ [+ASM] ll /dev/ASM*

total 0
drwxr-xr-x  2 root root  360 Jan 24 10:29 .
drwxr-xr-x 12 root root 3660 Mar 26 15:47 ..
lrwxrwxrwx  1 root root    7 Apr 18  2013 grid -> ../xvdf
lrwxrwxrwx  1 root root    7 Apr 18  2013 u01_01 -> ../xvdb
lrwxrwxrwx  1 root root    7 Apr 18  2013 u01_02 -> ../xvdc
lrwxrwxrwx  1 root root    7 Apr 18  2013 u01_03 -> ../xvdd
lrwxrwxrwx  1 root root    7 Apr 18  2013 u01_04 -> ../xvde
lrwxrwxrwx  1 root root    7 Oct  4  2013 u01_05 -> ../xvdllrwxrwxrwx  1 root root    7 Oct  4  2013 u01_06 -> ../xvdm
lrwxrwxrwx  1 root root    7 Jan 24 10:29 u01_07 -> ../xvdn
lrwxrwxrwx  1 root root    7 Jan 24 10:29 u01_08 -> ../xvdo
lrwxrwxrwx  1 root root    7 Jan 24 10:29 u01_09 -> ../xvdp
lrwxrwxrwx  1 root root    7 Jan 24 10:29 u01_10 -> ../xvdq
lrwxrwxrwx  1 root root    7 Apr 18  2013 u02_01 -> ../xvdg
lrwxrwxrwx  1 root root    7 Apr 18  2013 u02_02 -> ../xvdh
lrwxrwxrwx  1 root root    7 Apr 18  2013 u02_03 -> ../xvdi
lrwxrwxrwx  1 root root    7 Apr 18  2013 u02_04 -> ../xvdj
lrwxrwxrwx  1 root root    7 Apr 18  2013 u02_05 -> ../xvdk

Problem Cause

As described in the Metalink-Article from above, the SQL statement and Cloud Control hang,

because the statement tried to query inexistent LUNs with a very long timeout.

Solution

In my case, the solution was to remove the old (invalid) mount points.

I did not need to change the ASM disk discovery string.

Tags: Oracle, ASM, Problems&Solutions, LUN, Hang, blockiert, Publish
May 06, 2014 at 09:21AM

ORA: How To Increase the Redolog Size Under ASM

Only for information (is not ready neeeded here) - how to list the redo log members:

SQL> select group#, status, type, substr(member,1,50) "member" from v$logfile;

    GROUP# STATUS  TYPE    member

---------- ------- ------- --------------------------------------------------

         1         ONLINE  +U01/a1028t_site1/onlinelog/group_1.257.771684831

         1         ONLINE  +U02/a1028t_site1/onlinelog/group_1.257.771684833

         2         ONLINE  +U01/a1028t_site1/onlinelog/group_2.258.771684833

         2         ONLINE  +U02/a1028t_site1/onlinelog/group_2.258.771684833

         3         ONLINE  +U01/a1028t_site1/onlinelog/group_3.259.771684835

         3         ONLINE  +U02/a1028t_site1/onlinelog/group_3.259.771684835

List the redo log groups and their status:

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS

---------- ---------- ---------- ----------------

         1          1        250 CURRENT

         2          1        250 ACTIVE

         3          1        250 ACTIVE

Create a new temporary group (here with number 10).

(I think, that this is not realy needed. I think that we only need to perform several "alter system switch logfile;" until the groups that are now ACTIVE become INACTIVE.)

SQL> ALTER DATABASE ADD LOGFILE GROUP 10 SIZE 250m;

Database altered.

Here the new temporary group "10":

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS

---------- ---------- ---------- ----------------

         1          1        250 ACTIVE

         2          1        250 CURRENT

         3          1        250 ACTIVE

        10          1        250 UNUSED

Now, switch the logfiles until the now "ACTIVE" groups get the status "INACTIVE". . .

SQL> alter system switch logfile;

System altered.

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS

---------- ---------- ---------- ----------------

         1          1        250 ACTIVE

         2          1        250 ACTIVE

         3          1        250 ACTIVE

        10          1        250 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

. . .

Here we see, that our temporary group "10" is the only active one:

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS

---------- ---------- ---------- ----------------

         1          1        250 INACTIVE

         2          1        250 INACTIVE

         3          1        250 INACTIVE

        10          1        250 CURRENT

Once we have "INACTIVE" groups, we can delete each of these "INACTIVE" groups and recreate them with the new size.

Deleting "INACTIVE" group "1":

SQL> ALTER DATABASE drop LOGFILE GROUP 1;

Database altered.

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS

---------- ---------- ---------- ----------------

         2          1        250 INACTIVE

         3          1        250 INACTIVE

        10          1        250 CURRENT

And recreating it with the new size:

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 512m;

Database altered.

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS

---------- ---------- ---------- ----------------

         1          1        512 UNUSED

         2          1        250 INACTIVE

         3          1        250 INACTIVE

        10          1        250 CURRENT

Also deleting "INACTIVE" group "2" and recreating it with the new site of 512 MB:

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 SIZE 512m;

Database altered.

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS

---------- ---------- ---------- ----------------

         1          1        512 UNUSED

         2          1        512 UNUSED

         3          1        250 INACTIVE

        10          1        250 CURRENT

I switched the logfile in order to prove that my new group "1" is OK:

SQL> alter system switch logfile;

System altered.

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS

---------- ---------- ---------- ----------------

         1          1        512 CURRENT

         2          1        512 UNUSED

         3          1        250 INACTIVE

        10          1        250 ACTIVE

And so on for all groups.

Switch the logfile, until the temporary group "10" becomes "INACTIVE":

. . .

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

. . .

Now that the temporary group "10" is "INACTIVE", it can be deleted:

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS

---------- ---------- ---------- ----------------

         1          1        512 CURRENT

         2          1        512 INACTIVE

         3          1        512 INACTIVE

        10          1        250 INACTIVE

SQL> ALTER DATABASE DROP LOGFILE GROUP 10;

Database altered.

Here is the final result:

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS

---------- ---------- ---------- ----------------

         1          1        512 CURRENT

         2          1        512 INACTIVE

         3          1        512 INACTIVE

REMARK: As you can see below, two files have automatically been created for each group:

SQL> select group#, status, type, substr(member,1,50) "member" from v$logfile;

    GROUP# STATUS  TYPE    member

---------- ------- ------- --------------------------------------------------

         1         ONLINE  +U01/a1028t_site1/onlinelog/group_1.257.771762599

         1         ONLINE  +U02/a1028t_site1/onlinelog/group_1.257.771762601

         2         ONLINE  +U01/a1028t_site1/onlinelog/group_2.258.771762649

         2         ONLINE  +U02/a1028t_site1/onlinelog/group_2.258.771762649

         3         ONLINE  +U01/a1028t_site1/onlinelog/group_3.259.771762723

         3         ONLINE  +U02/a1028t_site1/onlinelog/group_3.259.771762725

6 rows selected.

I think that the files were automatically created, because two log destinations have been configured. (I need to verify this):

SQL> show parameter db_create_online_log_dest_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_create_online_log_dest_1          string      +U01

db_create_online_log_dest_2          string      +U02

db_create_online_log_dest_3          string

db_create_online_log_dest_4          string

db_create_online_log_dest_5          string

Tags: Oracle, ASM, Redolog, Publish
January 05, 2012 at 10:09AM