ORA-15063: ASM discovered an insufficient number of disks for diskgroup

Error:

ERROR: diskgroup DG02 was not mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DG02" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DG02"
ORA-15080: synchronous I/O operation to a disk failed

ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 4096
WARNING: Read Failed. group:0 disk:0 AU:0 offset:0 size:4096
ERROR: no read quorum in group: required 2, found 0 disks

ERROR: -9(Error 27061, OS Error (Linux-x86_64 Error: 5: Input/output error


When:

Storage has been disconnected from server or power cycled without stopping ASM or database instances

Analysis & Resolution:

Step1: ASM Alert log has been reviewed to get more details about the error messages & disk involved


Step2:  Queried disk views in running ASM instance to get the mount & header status of the disk , which was closed & candidate respectively suspecting metadata corruption in disk

SQL>select NAME, TOTAL_MB,FREE_MB from v$asm_diskgroup;

no rows selected


SQL>set line 900
SQL>set pages 900
SQL>col PATH for a40
SQL>select GROUP_NUMBER, DISK_NUMBER,INCARNATION,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,OS_MB,FREE_MB,NAME,path from v$asm_disk order by 2;


GROUP_NUMBER DISK_NUMBER INCARNATION MOUNT_S HEADER_STATU MODE_ST STATE         OS_MB    FREE_MB NAME                           PATH
------------ ----------- ----------- ------- ------------ ------- -------- ---------- ---------- ------------------------------ ----------------------------------------
           0           0  3912595095 CLOSED  CANDIDATE    ONLINE  NORMAL       512000          0                                /dev/mapper/mpathe




Step3: Restarted the ASM instance to check if disk group gets mounted & queries on v$ views for corrupted disk path . No luck



SQL> shut immediate;
ORA-15100: invalid or missing diskgroup name
ASM instance shutdown


SQL> startup
ASM instance started
Total System Global Area  283930624 bytes
Fixed Size                  2227664 bytes
Variable Size             256537136 bytes
ASM Cache                  25165824 bytes
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DG02" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DG02"


SQL> select * from v$asm_disk where PATH='/dev/mapper/mpathe';
GROUP_NUMBER DISK_NUMBER COMPOUND_INDEX INCARNATION MOUNT_S HEADER_STATU MODE_ST STATE    REDUNDA LIBRARY                                                              OS_MB    TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB NAME                           FAILGROUP                      LABEL                           PATH                    UDID                                                      PRODUCT                          CREATE_DA MOUNT_DAT REPAIR_TIMER      READS     WRITES  READ_ERRS WRITE_ERRS  READ_TIME WRITE_TIME BYTES_READ BYTES_WRITTEN P HASH_VALUE  HOT_READS HOT_WRITES HOT_BYTES_READ HOT_BYTES_WRITTEN COLD_READS COLD_WRITES COLD_BYTES_READ COLD_BYTES_WRITTEN V SECTOR_SIZE FAILGRO
------------ ----------- -------------- ----------- ------- ------------ ------- -------- ------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------- ------------ ------------------------------ ------------------------------ ------------------------------- ---------------------------------------- 

           0           0              0  3909105280 CLOSED  CANDIDATE    ONLINE  NORMAL   UNKNOWN System                                                               512000          0          0           0            0                                                                                               /dev/mapper/mpathe       0                                                        


Step4: Asked system & storage admin to check from OS & storage end for any misconfiguration or corruption along with OS restart. Still No Luck !



Step5: Performed disk integrity check using grid kfed command. content found to be invalid as highlighted below







$ kfed read /dev/mapper/mpathe
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
7F0B46922400 00000000 00000000 00000000 00000000  [................]
        Repeat 26 times
7F0B469225B0 00000000 00000000 00000000 00009A1D  [................]
7F0B469225C0 FEEE0001 0001FFFF FFFF0000 00003E7F  [.............>..]
7F0B469225D0 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
7F0B469225F0 00000000 00000000 00000000 AA550000  [..............U.]
7F0B46922600 20494645 54524150 00010000 0000005C  [EFI PART....\...]
7F0B46922610 61BAE647 00000000 00000001 00000000  [G..a............]
7F0B46922620 3E7FFFFF 00000000 00000022 00000000  [...>....".......]
7F0B46922630 3E7FFFDE 00000000 234EED11 4FB5F1FC  [...>......N#...O]
7F0B46922640 E49A8493 8FF0260A 00000002 00000000  [.....&..........]
7F0B46922650 00000080 00000080 AB54D286 00000000  [..........T.....]
7F0B46922660 00000000 00000000 00000000 00000000  [................]
  Repeat 217 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]



Step6: Disk status re-validated before running repair disk





$ kfod status=TRUE asm_diskstring='/dev/mapper/*' disk=all dscvgroup=TRUE
--------------------------------------------------------------------------------------------
 Disk          Size Header    Path                                     Disk Group   User     Group
========================================================================
 1:     512000 Mb CANDIDATE /dev/mapper/mpathe                       #            grid     oinstall
---------------------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
========================================================================
      +ASM /u01/app/grid/product/11.2.0/grid



Step7: Disk repaired using kfed repair which has restored/repaired the disk header to get metadata back on the disk at desired location


$ kfed repair /dev/mapper/mpathe
 

$ kfed read /dev/mapper/mpathe
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  3848140538 ; 0x00c: 0xe55df6fa
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:               DG02_0000 ; 0x028: length=9
kfdhdb.grpname:                    DG02 ; 0x048: length=4
kfdhdb.fgname:                DG02_0000 ; 0x068: length=9
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32993836 ; 0x0a8: HOUR=0xc DAYS=0x11 MNTH=0xc YEAR=0x7dd
kfdhdb.crestmp.lo:            632394752 ; 0x0ac: USEC=0x0 MSEC=0x65 SECS=0x1b MINS=0x9
kfdhdb.mntstmp.hi:             32993843 ; 0x0b0: HOUR=0x13 DAYS=0x11 MNTH=0xc YEAR=0x7dd
kfdhdb.mntstmp.lo:           1118950400 ; 0x0b4: USEC=0x0 MSEC=0x75 SECS=0x2b MINS=0x10
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                  512000 ; 0x0c4: 0x0007d000
kfdhdb.pmcnt:                         6 ; 0x0c8: 0x00000006
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn:                      2 ; 0x0d4: 0x00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32993836 ; 0x0e4: HOUR=0xc DAYS=0x11 MNTH=0xc YEAR=0x7dd
kfdhdb.grpstmp.lo:            632058880 ; 0x0e8: USEC=0x0 MSEC=0x31d SECS=0x1a MINS=0x9
kfdhdb.vfstart:                       0 ; 0x0ec: 0x00000000
kfdhdb.vfend:                         0 ; 0x0f0: 0x00000000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000
kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0x00000000
kfdhdb.ub4spare[1]:                   0 ; 0x100: 0x00000000
kfdhdb.ub4spare[2]:                   0 ; 0x104: 0x00000000
kfdhdb.ub4spare[3]:                   0 ; 0x108: 0x00000000
kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0x00000000
kfdhdb.ub4spare[17]:                  0 ; 0x140: 0x00000000
.

.
.
fdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0x00000000
kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0x00000000
kfdhdb.acdb.ents:                     0 ; 0x1dc: 0x0000
kfdhdb.acdb.ub2spare:                 0 ; 0x1de: 0x0000
 


Step8:  ASM instance started , DG got mounted & DB Started as well
 
$ sqlplus "/ as sysasm"
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 12 19:47:16 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area  283930624 bytes
Fixed Size                  2227664 bytes
Variable Size             256537136 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
 

SQL> select NAME, TOTAL_MB,FREE_MB from v$asm_diskgroup;
NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
DG02                               512000       9629

SQL>DISK_NUMBER,INCARNATION,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,OS_MB,FREE_MB,NAME,path from v$asm_disk order by 2;GROUP_NUMBER DISK_NUMBER INCARNATION MOUNT_S HEADER_STATU MODE_ST STATE         OS_MB    FREE_MB NAME                           PATH
------------ ----------- ----------- ------- ------------ ------- -------- ---------- ---------- ------------------------------ ----------------------------------------
           1           0  3909403310 CACHED  MEMBER       ONLINE  NORMAL       512000       9629 DG02_0000                      /dev/mapper/mpathe
      


$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 12 19:48:11 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size                  2229064 bytes
Variable Size            1224740024 bytes
Database Buffers          335544320 bytes
Redo Buffers              157814784 bytes
Database mounted.
Database opened.



ORA-01031: insufficient privileges

Error:

ORA-01031: insufficient privileges

When: 

While compiling a procedure/package/function

Solution:

Even after granting privileges like CREATE PROCEDURE / CREATE ANY PROCEDURE or EXECUTE / EXECUTE ANY PROCEDURE , ORA-01031 is faced. 

Such issue is common whenever accessing cross owned objects for modification. 

Here, CSSSAP is accessing CSS owned objects for modification without appropriate privileges

Granting ALTER ANY PROCEDURE helps to over come the insufficient privilege error as below :

SQL>  alter procedure CSS.PROC_ACCESSD compile;

 alter procedure
CSS.PROC_ACCESSD compile
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> grant ALTER ANY PROCEDURE to csssap;

Grant succeeded.

SQL> SQL> conn csssap/********

Connected.
SQL> alter procedure CSS.PROC_ACCESSD compile;

Procedure altered.

SQL>

 


My Popular Posts