ASM Disk Groups / Redundancy at Diskgroup Level vs. Redundancy at Template Level. Are there differences?

Mathias Zarick's Blog

Mathias is blogging here about Oracle and High Availability.

ASM Disk Groups / Redundancy at Diskgroup Level vs. Redundancy at Template Level. Are there differences?

Rate This
  • Comments 7

 Hi folks,

in last days I was confrontated with some ASM specific questions and so this should be the topic I start my blogging "career" with. Smile

I tested the following with VMWare Workstation. My "DB Server" was a Solaris box.
uname -a output: SunOS lnxzam12 5.10 Generic_137138-09 i86pc i386 i86pc.
Oracle was 10.2.0.4.0.
I tested the following three scenarios, in which each time one of the two asm disks
broke. I had iscsi and local disks, the iscsi disks broke by suspending the VM.
In all three scenarios I broke the disk when running some read write load.

Scenarios.
1. A DB on a mirrored diskgroup
2. A DB on an unprotected diskgroup
3. A DB on a mirrored diskgroup with datafiles based on unprotected asm templates


Scenario 1.
a normal mirrored diskgroup.

My Diskgroup was named DG1 and created on asm disks /asm_disks/local.disk1 and /asm_disks/iscsi.lun1
which are symbolic links:
ls -la /asm_disks/local.disk1 /asm_disks/iscsi.lun1
lrwxrwxrwx   1 root     root          18 May  4 11:33 /asm_disks/iscsi.lun1 -> /dev/rdsk/c4t7d0s0
lrwxrwxrwx   1 root     root          18 May  1 14:49 /asm_disks/local.disk1 -> /dev/rdsk/c2t0d0s0

create diskgroup dg1 disk '/asm_disks/local.disk1','/asm_disks/iscsi.lun1';
Diskgroup created.

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME  PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038       1987
DG1_0001         DG1         /asm_disks/iscsi.lun1         2038       1987

asmcmd lsdg Output:

ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  NORMAL  N      N         512   4096  1048576      4076     3974                0            1987              0  DG1/


No asm templates were manipulated or added. So everything default:

select dg.name group_name, t.name template_name, dg.type, t.redundancy, t.stripe
from v$asm_diskgroup dg, v$asm_template t
where dg.group_number = t.group_number and dg.name='DG1';

GROUP_NAME  TEMPLATE_NAME   TYPE   REDUND STRIPE
----------- --------------- ------ ------ ------
DG1         PARAMETERFILE   NORMAL MIRROR COARSE
DG1         DUMPSET         NORMAL MIRROR COARSE
DG1         CONTROLFILE     NORMAL HIGH   FINE
DG1         ARCHIVELOG      NORMAL MIRROR COARSE
DG1         ONLINELOG       NORMAL MIRROR FINE
DG1         DATAFILE        NORMAL MIRROR COARSE
DG1         DATAGUARDCONFIG NORMAL MIRROR COARSE
DG1         BACKUPSET       NORMAL MIRROR COARSE
DG1         AUTOBACKUP      NORMAL MIRROR COARSE
DG1         XTRANSPORT      NORMAL MIRROR COARSE
DG1         CHANGETRACKING  NORMAL MIRROR COARSE
DG1         FLASHBACK       NORMAL MIRROR FINE
DG1         TEMPFILE        NORMAL MIRROR COARSE

13 rows selected.


My DB named SATURN resided on DG1, all controlfile, datafiles, tempfiles and online redo logs. To produce a little read and write load on the database, I invalidated all packages with utlirp.sql and and started a recompile with utlrp.sql for them. In parallel another session was busy with some nonsense DML on a table. Then i crashed the iscsi LUN, and watched messages file as well as the ASM and SATURN alert logs.

entries in /var/adm/messages:
May  4 15:49:15 lnxzam12 iscsi: [ID 286457 kern.notice] NOTICE: iscsi connection(12) unable to connect to target iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 (errno:145)
May  4 15:52:07 lnxzam12 last message repeated 6 times
May  4 15:52:07 lnxzam12 iscsi: [ID 328943 kern.notice] NOTICE: iscsi session(11) iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 offline
May  4 15:52:07 lnxzam12 scsi: [ID 107833 kern.warning] WARNING: /iscsi/disk@0000iqn.2009-05.com.trivadis%3Astorage.oel5.lnxzam04FFFF,1 (sd7):
May  4 15:52:07 lnxzam12        transport rejected fatal error
May  4 15:52:08 lnxzam12 scsi: [ID 107833 kern.warning] WARNING: /iscsi/disk@0000iqn.2009-05.com.trivadis%3Astorage.oel5.lnxzam04FFFF,1 (sd7):
May  4 15:52:08 lnxzam12        drive offline


the highlights from the alert log of the database instance:

Mon May  4 15:52:07 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_j000_5402.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 478944
Additional information: -1
Mon May  4 15:52:07 2009
WARNING: offlining disk 1.4043124392 (DG1_0001) with mask 0x3

alert log of asm instance:
Mon May  4 15:52:07 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 4088
Additional information: -1
Mon May  4 15:52:08 2009
NOTE: PST update: grp = 1
Mon May  4 15:52:08 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May  4 15:52:08 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May  4 15:52:08 2009
WARNING: offlining disk 1.4043124392 (DG1_0001) with mask 0x3
Mon May  4 15:52:08 2009
WARNING: offlining disk 1.4043124392 (DG1_0001) with mask 0x3
Mon May  4 15:52:08 2009
WARNING: offlining disk 1.4043124392 (DG1_0001) with mask 0x3
Mon May  4 15:52:18 2009
WARNING: kfk failed to open a disk[/asm_disks/iscsi.lun1]
Mon May  4 15:52:18 2009
WARNING: kfk failed to open a disk[/asm_disks/iscsi.lun1]
Mon May  4 15:52:18 2009
WARNING: kfk failed to open a disk[/asm_disks/iscsi.lun1]
Mon May  4 15:52:18 2009
Errors in file /u00/app/oracle/admin/+ASM/udump/+asm_ora_5415.trc:
ORA-15025: could not open disk '/asm_disks/iscsi.lun1'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 3
Mon May  4 15:52:18 2009
Errors in file /u00/app/oracle/admin/+ASM/udump/+asm_ora_5416.trc:
ORA-15025: could not open disk '/asm_disks/iscsi.lun1'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 3
Mon May  4 15:52:18 2009
Errors in file /u00/app/oracle/admin/+ASM/udump/+asm_ora_5412.trc:
ORA-15025: could not open disk '/asm_disks/iscsi.lun1'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 3
Mon May  4 15:52:18 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
Mon May  4 15:52:18 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
Mon May  4 15:52:18 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May  4 15:52:18 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
Mon May  4 15:52:18 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May  4 15:52:18 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
Mon May  4 15:52:18 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
NOTE: cache closing disk 1 of grp 1: DG1_0001
NOTE: cache closing disk 1 of grp 1: DG1_0001
NOTE: cache closing disk 1 of grp 1: DG1_0001
Mon May  4 15:52:21 2009
SUCCESS: refreshed membership for 1/0x8b6ddef8 (DG1)
Mon May  4 15:52:23 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2048
Additional information: -1
Mon May  4 15:52:23 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May  4 15:53:14 2009
WARNING: PST-initiated drop disk 1(2339233528).1(4043124392) (DG1_0001)
Mon May  4 15:53:14 2009
NOTE: PST update: grp = 1
Mon May  4 15:53:14 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May  4 15:53:14 2009
NOTE: requesting all-instance membership refresh for group=1
Mon May  4 15:53:14 2009
NOTE: membership refresh pending for group 1/0x8b6ddef8 (DG1)
SUCCESS: refreshed membership for 1/0x8b6ddef8 (DG1)
Mon May  4 15:53:16 2009
SUCCESS: PST-initiated disk drop completed
Mon May  4 15:53:17 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2048
Additional information: -1
Mon May  4 15:53:17 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May  4 15:53:19 2009
NOTE: starting rebalance of group 1/0x8b6ddef8 (DG1) at power 1
Starting background process ARB0
ARB0 started with pid=19, OS id=5420
Mon May  4 15:53:23 2009
NOTE: assigning ARB0 to group 1/0x8b6ddef8 (DG1)
Mon May  4 15:53:34 2009
NOTE: stopping process ARB0
Mon May  4 15:53:35 2009
SUCCESS: rebalance completed for group 1/0x8b6ddef8 (DG1)
Mon May  4 15:53:35 2009
SUCCESS: rebalance completed for group 1/0x8b6ddef8 (DG1)
NOTE: PST update: grp = 1
Mon May  4 15:53:36 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May  4 15:53:36 2009
WARNING: offline disk number 1 has references (2009 AUs)
NOTE: PST update: grp = 1
Mon May  4 15:53:36 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)

The recompile and dml sessions experienced no problems. The database instance stayed up.

here again the query for the asm diskgroup, which hangs for a while while trying to read from the broken disk:

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME  PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038         27
DG1_0001         DG1                                       2038         27

What is DG1_0001's state?

select state, mode_status, mount_status from v$asm_disk where name='DG1_0001';

STATE    MODE_STATUS   MOUNT_STATUS
-------- ------------- -------------
HUNG     OFFLINE       MISSING

asmcmd lsdg Output:
ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  NORMAL  N      N         512   4096  1048576      2038       27                0              13              1  DG1/

This means ASM has offlined the disk and tried to drop it. Obviously it could not drop as it was last disk in its failure group. Afterwards I resumed the iscsi target server and let /asm_disks/iscsi.lun1 come back.

/var/adm/messages:
May  4 16:17:11 lnxzam12 iscsi: [ID 240218 kern.notice] NOTICE: iscsi session(11) iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 online

So now I need to repair the diskgroup, but how?
A naive adding back gave an error:

SQL> ALTER DISKGROUP DG1 ADD DISK '/asm_disks/iscsi.lun1';
ALTER DISKGROUP DG1 ADD DISK '/asm_disks/iscsi.lun1'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15033: disk '/asm_disks/iscsi.lun1' belongs to diskgroup "DG1"

so I cleared the disk before I was able to add it back
dd if=/dev/zero of=/asm_disks/iscsi.lun1 bs=8192 count=100

ALTER DISKGROUP DG1 ADD DISK '/asm_disks/iscsi.lun1';
Diskgroup altered.

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME  PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0002         DG1         /asm_disks/iscsi.lun1         2038       1922
DG1_0000         DG1         /asm_disks/local.disk1        2038         27
DG1_0001         DG1                                       2038        140

rebalancing actually mirroring starts as also shown in asm instances alert log

Mon May  4 16:21:24 2009
NOTE: starting rebalance of group 1/0x8b6ddef8 (DG1) at power 1
Starting background process ARB0
ARB0 started with pid=16, OS id=5497

after a while both disks are in sync again as we can see in alert log and by the free_mb column:

Mon May  4 16:26:14 2009
SUCCESS: rebalance completed for group 1/0x8b6ddef8 (DG1)

DISK_NAME        GROUP_NAME  PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0002         DG1         /asm_disks/iscsi.lun1         2038         27
DG1_0000         DG1         /asm_disks/local.disk1        2038         27
DG1_0001         DG1                                       2038       2033

Notice: With 11g there is a feature called fast mirror resync, which makes it possible to incrementally update the failed disk with all needed and changed data to bring it in sync again.

Scenario 2.
an unprotected diskgroup.

For this test, the diskgroup was dismounted, asm disks cleared and diskgroup recreated.

create diskgroup dg1 external redundancy disk '/asm_disks/local.disk1','/asm_disks/iscsi.lun1';

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME  PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038       2011
DG1_0001         DG1         /asm_disks/iscsi.lun1         2038       2013

asmcmd lsdg Output:

ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N      N         512   4096  1048576      4076     4024                0            4024              0  DG1/

Notice: We have twice the storage capacity as in first scenario, as we do not mirror any more.

No asm templates were manipulated or added.
Notice: Anyway it is not possible to "upgrade" a template in a unprotected diskgroup to mirrored.

ALTER DISKGROUP DG1 ALTER TEMPLATE DATAFILE ATTRIBUTES (MIRROR);
gives ORA-15067: command or option incompatible with diskgroup redundancy

So everything default:

select dg.name group_name, t.name template_name, dg.type, t.redundancy, t.stripe
from v$asm_diskgroup dg, v$asm_template t
where dg.group_number = t.group_number and dg.name='DG1';

GROUP_NAME  TEMPLATE_NAME   TYPE   REDUND STRIPE
----------- --------------- ------ ------ ------
DG1         PARAMETERFILE   EXTERN UNPROT COARSE
DG1         DUMPSET         EXTERN UNPROT COARSE
DG1         CONTROLFILE     EXTERN UNPROT FINE
DG1         ARCHIVELOG      EXTERN UNPROT COARSE
DG1         ONLINELOG       EXTERN UNPROT FINE
DG1         DATAFILE        EXTERN UNPROT COARSE
DG1         DATAGUARDCONFIG EXTERN UNPROT COARSE
DG1         BACKUPSET       EXTERN UNPROT COARSE
DG1         AUTOBACKUP      EXTERN UNPROT COARSE
DG1         XTRANSPORT      EXTERN UNPROT COARSE
DG1         CHANGETRACKING  EXTERN UNPROT COARSE
DG1         FLASHBACK       EXTERN UNPROT FINE
DG1         TEMPFILE        EXTERN UNPROT COARSE

13 rows selected.

My DB named SATURN again resided on DG1. Same load: invalidation with utlirp.sql and utlrp.sql as well as the dml session. Then I crashed the iscsi LUN by suspending the VM.

of course we get similar entries in /var/adm/messages as in first scenario:
May  4 17:14:25 lnxzam12 iscsi: [ID 286457 kern.notice] NOTICE: iscsi connection(12) unable to connect to target iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 (errno:145)
May  4 17:17:17 lnxzam12 last message repeated 6 times
May  4 17:17:17 lnxzam12 iscsi: [ID 328943 kern.notice] NOTICE: iscsi session(11) iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 offline
May  4 17:17:17 lnxzam12 scsi: [ID 107833 kern.warning] WARNING: /iscsi/disk@0000iqn.2009-05.com.trivadis%3Astorage.oel5.lnxzam04FFFF,1 (sd7):
May  4 17:17:17 lnxzam12        transport rejected fatal error
May  4 17:17:40 lnxzam12 iscsi: [ID 286457 kern.notice] NOTICE: iscsi connection(12) unable to connect to target iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 (errno:145)
May  4 17:19:04 lnxzam12 last message repeated 1 time


database instance alert log:
Mon May  4 17:17:17 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_ckpt_6089.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 58496
Additional information: -1
Mon May  4 17:17:17 2009
KCF: write/open error block=0x1073 online=1
     file=4 +DG1/saturn_site1/datafile/users.265.685990577
     error=27063 txt: ''
Mon May  4 17:17:17 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_ckpt_6089.trc:
ORA-00202: control file: '+DG1/saturn_site1/controlfile/current.256.685990541'
ORA-15080: synchronous I/O operation to a disk failed
Mon May  4 17:17:17 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_lgwr_6087.trc:
ORA-00345: redo log write error block 17973 count 1854
ORA-00312: online log 3 thread 1: '+DG1/saturn_site1/onlinelog/group_3.260.685990545'
ORA-27063: number of bytes read/written is incorrect
Mon May  4 17:17:17 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_lgwr_6087.trc:
ORA-00340: IO error processing online log 3 of thread 1
ORA-00345: redo log write error block 17973 count 1854
ORA-00312: online log 3 thread 1: '+DG1/saturn_site1/onlinelog/group_3.260.685990545'
ORA-27063: number of bytes read/written is incorrect
Mon May  4 17:17:17 2009
LGWR: terminating instance due to error 340
Mon May  4 17:17:18 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_ckpt_6089.trc:
ORA-00204: error in reading (block 316, # blocks 1) of control file
ORA-00202: control file: '+DG1/saturn_site1/controlfile/current.256.685990541'
ORA-15080: synchronous I/O operation to a disk failed
Mon May  4 17:17:18 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_rbal_6106.trc:
ORA-00340: IO error processing online log  of thread
Instance terminated by LGWR, pid = 6087

ASM alert log:
nothing special ... hmm
Mon May  4 17:14:33 2009
NOTE: ASMB process exiting due to lack of ASM file activity


The recompile and dml sessions crashed. The database instance crashed.

Here again the query for the asm diskgroup, which hangs for a while while trying to read from the broken disk:

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME  PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038       1030
DG1_0001         DG1         /asm_disks/iscsi.lun1         2038       1035

What is DG1_0001's state?

select state, mode_status, mount_status from v$asm_disk where name='DG1_0001';

STATE    MODE_STATUS   MOUNT_STATUS
-------- ------------- -------------
NORMAL   ONLINE        CACHED

asmcmd lsdg Output:
ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N      N         512   4096  1048576      2038     1030                0            1030              0  DG1/

This means ASM stays stubborn and still relies on external redundancy as we said when creating the diskgroup. This also means reading files in ASM leads to I/O errors which means database instances crash and are not startable as longs as those disks are not readable again.

SQL> startup
ORA-01078: failure in processing system parameters
ORA-27091: unable to queue I/O
ORA-15081: failed to submit an I/O operation to a disk
ORA-06512: at line 4

After this test I resumed the iscsi target server and let /asm_disks/iscsi.lun1 come back.
/var/adm/messages showed
May  4 17:31:38 lnxzam12 iscsi: [ID 240218 kern.notice] NOTICE: iscsi session(11) iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 online

queries from above show same output, we have a difference when using asmcmd lsdg,
there the whole storage is visible again:
ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N      N         512   4096  1048576      4076     2065                0            2065              0  DG1/

Now I could startup the database and everything was repaired again.

Scenario 3.
a mirrored diskgroup with datafiles based on unprotected asm templates

For this test, the diskgroup was dismounted, asm disks cleared and diskgroup recreated.

create diskgroup dg1 disk '/asm_disks/local.disk1','/asm_disks/iscsi.lun1';

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME  PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038       1987
DG1_0001         DG1         /asm_disks/iscsi.lun1         2038       1987

asmcmd lsdg Output:

ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  NORMAL  N      N         512   4096  1048576      4076     3974                0            1987              0  DG1/

I manipulated the default templates to UNPROTECTED, which saves space:

ALTER DISKGROUP DG1 ALTER TEMPLATE DATAFILE ATTRIBUTES (UNPROTECTED);
ALTER DISKGROUP DG1 ALTER TEMPLATE TEMPFILE ATTRIBUTES (UNPROTECTED);

select dg.name group_name, t.name template_name, dg.type, t.redundancy, t.stripe
from v$asm_diskgroup dg, v$asm_template t
where dg.group_number = t.group_number and dg.name='DG1';

GROUP_NAME  TEMPLATE_NAME   TYPE   REDUND STRIPE
----------- --------------- ------ ------ ------
DG1         PARAMETERFILE   NORMAL MIRROR COARSE
DG1         DUMPSET         NORMAL MIRROR COARSE
DG1         CONTROLFILE     NORMAL HIGH   FINE
DG1         ARCHIVELOG      NORMAL MIRROR COARSE
DG1         ONLINELOG       NORMAL MIRROR FINE
DG1         DATAFILE        NORMAL UNPROT COARSE
DG1         DATAGUARDCONFIG NORMAL MIRROR COARSE
DG1         BACKUPSET       NORMAL MIRROR COARSE
DG1         AUTOBACKUP      NORMAL MIRROR COARSE
DG1         XTRANSPORT      NORMAL MIRROR COARSE
DG1         CHANGETRACKING  NORMAL MIRROR COARSE
DG1         FLASHBACK       NORMAL MIRROR FINE
DG1         TEMPFILE        NORMAL UNPROT COARSE

13 rows selected.

Same procedure as in last both scenarios:
My DB named SATURN I created again on DG1. Same load: invalidation with utlirp.sql and utlrp.sql as well as the dml session. Then I crashed the iscsi LUN by suspending the VM.

similar entries in /var/adm/messages

alert log of the database instance:

Mon May  4 20:38:46 2009
WARNING: offlining disk 1.4041040168 (DG1_0001) with mask 0x3
Mon May  4 20:38:46 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_lgwr_7177.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 110688
Additional information: -1
Mon May  4 20:38:46 2009
WARNING: offlining disk 1.4041040168 (DG1_0001) with mask 0x3
Mon May  4 20:38:56 2009
Thread 1 advanced to log sequence 58 (LGWR switch)
  Current log# 1 seq# 58 mem# 0: +DG1/saturn_site1/onlinelog/group_1.258.685993517
Mon May  4 20:38:57 2009
KCF: write/open error block=0x6a24 online=1
     file=1 +DG1/saturn_site1/datafile/system.261.685993521
     error=15062 txt: ''
Mon May  4 20:38:58 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_dbw0_7175.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01114: IO error writing block to file 1 (block # 27172)
ORA-01110: data file 1: '+DG1/saturn_site1/datafile/system.261.685993521'
ORA-15062: ASM disk is globally closed
Mon May  4 20:38:58 2009
Thread 1 cannot allocate new log, sequence 59
Checkpoint not complete
  Current log# 1 seq# 58 mem# 0: +DG1/saturn_site1/onlinelog/group_1.258.685993517
Mon May  4 20:38:58 2009
DBW0: terminating instance due to error 1242
Mon May  4 20:38:58 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_smon_7181.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
Mon May  4 20:38:58 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_mman_7173.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
Instance terminated by DBW0, pid = 7175

alert log of asm instance
Mon May  4 20:38:46 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 4088
Additional information: -1
NOTE: PST update: grp = 1
Mon May  4 20:38:46 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May  4 20:38:46 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May  4 20:38:46 2009
WARNING: offlining disk 1.4041040168 (DG1_0001) with mask 0x3
Mon May  4 20:38:46 2009
WARNING: offlining disk 1.4041040168 (DG1_0001) with mask 0x3
Mon May  4 20:38:56 2009
WARNING: kfk failed to open a disk[/asm_disks/iscsi.lun1]
Mon May  4 20:38:56 2009
WARNING: kfk failed to open a disk[/asm_disks/iscsi.lun1]
Mon May  4 20:38:56 2009
Errors in file /u00/app/oracle/admin/+ASM/udump/+asm_ora_7420.trc:
ORA-15025: could not open disk '/asm_disks/iscsi.lun1'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 3
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
Mon May  4 20:38:56 2009
Errors in file /u00/app/oracle/admin/+ASM/udump/+asm_ora_7418.trc:
ORA-15025: could not open disk '/asm_disks/iscsi.lun1'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 3
Mon May  4 20:38:56 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
Mon May  4 20:38:56 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May  4 20:38:56 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
Mon May  4 20:38:56 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May  4 20:38:56 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
NOTE: cache closing disk 1 of grp 1: DG1_0001
NOTE: cache closing disk 1 of grp 1: DG1_0001
Mon May  4 20:38:57 2009
SUCCESS: refreshed membership for 1/0x232d91d7 (DG1)
Mon May  4 20:38:58 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2048
Additional information: -1
Mon May  4 20:38:58 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May  4 20:40:14 2009
WARNING: PST-initiated drop disk 1(590189015).1(4041040168) (DG1_0001)
NOTE: PST update: grp = 1
Mon May  4 20:40:14 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May  4 20:40:14 2009
NOTE: requesting all-instance membership refresh for group=1
Mon May  4 20:40:14 2009
NOTE: membership refresh pending for group 1/0x232d91d7 (DG1)
SUCCESS: refreshed membership for 1/0x232d91d7 (DG1)
Mon May  4 20:40:16 2009
SUCCESS: PST-initiated disk drop completed
Mon May  4 20:40:17 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2048
Additional information: -1
Mon May  4 20:40:17 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May  4 20:40:19 2009
NOTE: starting rebalance of group 1/0x232d91d7 (DG1) at power 1
Starting background process ARB0
ARB0 started with pid=13, OS id=7605
Mon May  4 20:40:19 2009
NOTE: assigning ARB0 to group 1/0x232d91d7 (DG1)
Mon May  4 20:40:20 2009
......
!!!!!950 LINES WITH CONTENTS LIKE!!!!!!!
ERROR: group 1, file 265, extent 1013: filling extent with BADFDATA
......
Mon May  4 20:41:04 2009
NOTE: stopping process ARB0
Mon May  4 20:41:04 2009
SUCCESS: rebalance completed for group 1/0x232d91d7 (DG1)
Mon May  4 20:41:04 2009
SUCCESS: rebalance completed for group 1/0x232d91d7 (DG1)
NOTE: PST update: grp = 1
Mon May  4 20:41:04 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May  4 20:41:04 2009
WARNING: offline disk number 1 has references (119 AUs)
NOTE: PST update: grp = 1
Mon May  4 20:41:04 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)

The recompile and dml sessions crashed. The database instance crashed.

Here again the query for the asm diskgroup, which again hangs for a while:

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME  PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038         28
DG1_0001         DG1                                       2038       1917

What is DG1_0001's state?

select state, mode_status, mount_status from v$asm_disk where name='DG1_0001';

STATE    MODE_STATUS   MOUNT_STATUS
-------- ------------- -------------
HUNG     OFFLINE       MISSING

asmcmd lsdg Output:
ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  NORMAL  N      N         512   4096  1048576      2038       28                0              14              1  DG1/

So what is going on here? ASM Diskgroup stayed mounted and marked some asm file extents with BADFDATA. This is obviously somewhat of a corruption in the asm file. And of course certain I/O failed which led to the instance crash. So how can we repair this situation? Lets first resume the iscsi target server and so the /asm_disks/iscsi.lun1 device. And now the iscsi disk must be synced, but there were files which we did not mirror. I do not want to just clear and add back the disk. No chance for the data on the disk? I restarted asm instance and hoped for its intelligence. Nope that did not help! Same situation! So I added another disk to the diskgroup and afterwards I should at least be able to drop the failed disk.

Mon May  4 21:03:09 2009
SQL> alter diskgroup dg1 add disk '/asm_disks/local.disk2'
Mon May  4 21:03:09 2009
WARNING: ignoring disk /asm_disks/iscsi.lun1 in deep discovery
NOTE: initializing header on grp 1 disk DG1_0002
WARNING: ignoring disk /asm_disks/iscsi.lun1 in deep discovery
NOTE: cache opening disk 2 of grp 1: DG1_0002 path:/asm_disks/local.disk2
NOTE: PST update: grp = 1
Mon May  4 21:03:10 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
NOTE: group DG1: relocated PST to: disk 0002 (PST copy 1)
Mon May  4 21:03:10 2009
NOTE: requesting all-instance disk validation for group=1
Mon May  4 21:03:10 2009
NOTE: disk validation pending for group 1/0x30d05295 (DG1)
SUCCESS: validated disks for 1/0x30d05295 (DG1)
Mon May  4 21:03:11 2009
NOTE: PST update: grp = 1
NOTE: requesting all-instance membership refresh for group=1
Mon May  4 21:03:11 2009
NOTE: membership refresh pending for group 1/0x30d05295 (DG1)
SUCCESS: refreshed membership for 1/0x30d05295 (DG1)
Mon May  4 21:03:14 2009
NOTE: requesting all-instance membership refresh for group=1
Mon May  4 21:03:14 2009
NOTE: membership refresh pending for group 1/0x30d05295 (DG1)
SUCCESS: refreshed membership for 1/0x30d05295 (DG1)
Mon May  4 21:03:20 2009
NOTE: starting rebalance of group 1/0x30d05295 (DG1) at power 1
Starting background process ARB0
ARB0 started with pid=12, OS id=7723
Mon May  4 21:03:20 2009
NOTE: assigning ARB0 to group 1/0x30d05295 (DG1)
Mon May  4 21:03:20 2009
NOTE: F1X0 copy 2 relocating from 1:2 to 2:2
NOTE: F1X0 copy 3 relocating from 65534:4294967294 to 65534:4294967294
Mon May  4 21:05:45 2009
NOTE: stopping process ARB0
Mon May  4 21:05:48 2009
SUCCESS: rebalance completed for group 1/0x30d05295 (DG1)
Mon May  4 21:05:48 2009
SUCCESS: rebalance completed for group 1/0x30d05295 (DG1)
NOTE: PST update: grp = 1
WARNING: offline disk number 1 has references (3 AUs)
NOTE: PST update: grp = 1


Yes but now I realized that contents of disk /asm_disks/iscsi.lun1 are lost forever. No problem for all files with mirrored asm templates (redo logs, controlfiles, spfile in my case). But a big problem for datafiles and tempfiles. The latter I could manage, the first not. I have asm file extents in there with the BADFDATA marks.

I started the database instance and I saw that my bad apprehension was so so true:

SQL> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1286488 bytes
Variable Size              71306920 bytes
Database Buffers          134217728 bytes
Redo Buffers                2904064 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '+DG1/saturn_site1/datafile/system.261.685993521'
ORA-01251: Unknown File Header Version read for file number 1

So in production this would mean full database restore.

Summary:

I have tested 3 scenarios:
1. A DB on a mirrored diskgroup
2. A DB on an unprotected diskgroup
3. A DB on a mirrored diskgroup with datafiles based on unprotected asm templates

I brought load on the database and simulated a crash of one out of two asm disks, which can be compared with a partial LUN failure in production systems.

In first scenario, database instance stayed available as expected. Redundancy could be restored later on. In second scenario, the database instance crashed, but could be started up again without problems after "repairing" the failed LUN. In the third scenario, ASM marked file extents with a magic "BADFDATA" entry and corrupted the files that were not mirrored. All data was lost.

So what can we learn for productive systems:
UNPROTECTED asm templates in diskgroups with normal mirrored redundancy can lead to data loss. It is not a good idea to implement diskgroups like this. If you cannot afford mirroring the datafiles use external redundancy and mirror controlfiles, redo logs by multiplexing on the upper layer. Controlfiles can be mirrored as usual with multiple entries in init.ora parameter control_files. Redo log files are mirrored by defining multiple members for each group.

Have fun and take care Mathias

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Post
  • Hi Mathias,

    it's great to see you blogging. On more great resource of proved information here!

    Just 2 comments:

    *) if you want to save the time for wiping the disk, you can also use

    Alter Diskgroup DG1 Add Disk '/asm_disks/iscsi.lun1' FORCE;

    This might not be a big deal with 1 LUN, maybe with a lot of.

    *) As Oracle is really silent about BADFDATA, the function kfdCopy seems to write the corresponding lines into the alert.log.  Unfortunately there is not much more Information about kfdCopy, but at least kfd.c seems to hold a lot of functions for dealing with disks used by ASM. (it's not even mentioned in Note:175982.1 yet)

    keep blogging,

    Martin

  • greate post. Thanks

  • Hello mathias,

    very nice tests ;)  I wonder whether there will be any difference in tests if you let only a specific LUNs fail

    and not simulate a crash of the whole iSCSI filer (by suspending the VM) ;)

    Best Regards. Milen

  • very nice article, it is very helpful to DBAs.

    cheers

  • Hi Milen,

    I would propose the there would be no difference.

    In my test I used only one LUN from iscsi, so an I/O failure on this LUN would have the same effect as no connection iscsi filer at all.

    Hope to answer your question.

    Mathias

  • Hello Martin, Baki, Milen, Mehmood,

    thanks for your feedback!

    Cheers Mathias

  • Awesome article with real Scenario..........

    keep it up :)

Page 1 of 1 (7 items)
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Post