ASM & RDBMS Interactions: Finding the ASM Instance

I thought it would be worthwhile having a couple of blog posts on how the RDBMS instance interacts with the ASM instance in various situations.

So I guess we should start at the beginning and ask how the RDBMS actually knows where to contact the ASM instance that it is storing its files in at all?

When an ASM instance is started it registers with the Cluster Synchronisation Services (CSS) daemon running as occsd.bin. This is part of Oracle Clusterware, but the occsd.bin runs in a single instance configuration as well.

Whenever an RDBMS instance needs for the first time to access files that reside on ASM storage and the RDBMS has not needed prior access to the ASM instance, the RDBMS needs to find the connection details for the ASM instance.

These connection details are NOT stored anywhere and you do not need to configure them. The RDBMS instance contacts CSS and gets passed the connect string for the ASM instance. The RDBMS then connects as sysdba to the ASM instance using OS authentication.

It’s actually the RDBMS’s ASMB background process that connects to the ASM instance and remains connected while there are ASM files open. Termination of this ASMB background process is fatal for the instance.

ASMLib Troubleshooting

I’ve noticed a few forum questions regarding ASM or indeed the OUI not being able to see devices that are managed via ASMLib. This prompted me to “upgrade” my knowledge of ASMLib and this blog is just a few extra tools for checking on your ASMLib devices.

By the way, anyone out there thinking ASMLib is not getting a whole lot of love from Oracle of late? The latest updates on the ASMLib page seems to be early 2007.

Anyway, first troubleshooting tip is a simple one, but make sure you have all three ASMLib rpms:


# rpm -qa |grep asm
oracleasm-support-2.0.3-1
oracleasmlib-2.0.2-1
oracleasm-2.6.9-22.ELsmp-2.0.3-1


You get odd behaviour without all of ‘em. So what do each of these provide you:


# rpm -ql oracleasm-support
/etc/init.d/oracleasm
/etc/sysconfig/oracleasm
/usr/lib/oracleasm/oracleasm_debug_link
/usr/sbin/asmscan
/usr/sbin/asmtool


So the init.d oracleasm script is really where you configure disks and includes various options, like listing disks and querying. This is actually just a shell script that calls the executables asmscan and asmtool. There is a configuration file in /etc/sysconfig where you can change things like the pattern to scan for devices and you also have the ability to exclude devices using this configuration file. Excluding devices and explicitly setting the scanorder can be useful for multipath devices.

Once you have ran /etc/init.d/oracleasm configure you should see a new device:


# df -ha |grep asm
oracleasmfs 0 0 0 - /dev/oracleasm



# rpm -ql oracleasmlib
/opt/oracle/extapi
/opt/oracle/extapi/64
/opt/oracle/extapi/64/asm
/opt/oracle/extapi/64/asm/orcl
/opt/oracle/extapi/64/asm/orcl/1
/opt/oracle/extapi/64/asm/orcl/1/libasm.so
/usr/sbin/oracleasm-discover


So this rpm provides you with a library and an executable. Running the executable once you have configured devices is kinda nice:


# /usr/sbin/oracleasm-discover
Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.2 (KABI_V2)]
Discovered disk: ORCL:VOL1 [121634784 blocks (62277009408 bytes), maxio 512]
Discovered disk: ORCL:VOL2 [20971488 blocks (10737401856 bytes), maxio 512]
Discovered disk: ORCL:VOL3 [20971488 blocks (10737401856 bytes), maxio 512]
Discovered disk: ORCL:VOL4 [419424957 blocks (214745577984 bytes), maxio 512]


The final rpm is the kernel module:


# rpm -ql oracleasm-2.6.9-22.ELsmp
/lib/modules/2.6.9-22.ELsmp/kernel/drivers/addon/oracleasm
/lib/modules/2.6.9-22.ELsmp/kernel/drivers/addon/oracleasm/oracleasm.ko


You want to ensure that the oracleasm has been loaded by the kernel:


# /sbin/lsmod |grep oracleasm
oracleasm 55176 1


You can find information about the module with modinfo:


# /sbin/modinfo oracleasm
filename: /lib/modules/2.6.9-22.ELsmp/kernel/drivers/addon/oracleasm/oracleasm.ko
description: Kernel driver backing the Generic Linux ASM Library.
author: Joel Becker
version: 2.0.3
license: GPL
depends:
vermagic: 2.6.9-22.ELsmp SMP gcc-3.4


Make sure the devices you are trying to use are known by the kernel you can check in /dev/ or look in /proc/partitions. ASMLib likes to work on partitions, you can create this on a device using fdisk.

A list of devices marked by ASMLib is generated with:


# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
VOL4


You can cross-reference this with what is in the /dev/oracleasm/disks directory:


# ls -l /dev/oracleasm/disks/
total 0
brw-rw—- 1 oracle oinstall 8, 17 Jun 24 09:13 VOL1
brw-rw—- 1 oracle oinstall 8, 49 Jun 24 09:13 VOL2
brw-rw—- 1 oracle oinstall 8, 65 Jun 24 09:13 VOL3
brw-rw—- 1 oracle oinstall 8, 97 Jun 24 09:13 VOL4


You can use querydisk to determine which device a particular ASMLib Volume corresponds to:


# /etc/init.d/oracleasm querydisk VOL1
Disk “VOL1″ is a valid ASM disk on device [8, 17]


You can find out which devices this represents with the following:


# grep “8 17″ /proc/partitions
8 17 60817392 sdb1


Still paranoid that this might not be your device, check the contents of the disk header:


# od -c /dev/sdb1 |head -10
0000000 001 202 001 001 200 036 - W 310
0000020
0000040 O R C L D I S K V O L 1
0000060
0000100 020 \n 001 003 V O L 1
0000120
0000140 D A T A 1
0000160
0000200 V O L 1
0000220


There is also a neat trick with blkid which shows the disk headers:


#./blkid|grep asm
/dev/sdb1: LABEL=”VOL1″ TYPE=”oracleasm”
/dev/sdd1: LABEL=”VOL2″ TYPE=”oracleasm”
/dev/sde1: LABEL=”VOL3″ TYPE=”oracleasm”
/dev/sdg1: LABEL=”VOL4″ TYPE=”oracleasm”
/dev/sdo1: LABEL=”VOL1″ TYPE=”oracleasm”
/dev/sdq1: LABEL=”VOL2″ TYPE=”oracleasm”
/dev/sdr1: LABEL=”VOL3″ TYPE=”oracleasm”
/dev/sdt1: LABEL=”VOL4″ TYPE=”oracleasm”
/dev/emcpowerf1: LABEL=”VOL4″ TYPE=”oracleasm”
/dev/emcpowerp1: LABEL=”VOL3″ TYPE=”oracleasm”
/dev/emcpowero1: LABEL=”VOL2″ TYPE=”oracleasm”
/dev/emcpowern1: LABEL=”VOL1″ TYPE=”oracleasm”


You can see from the above, that I have multiple devices corresponding to the same physical device and I am using EMC Powerpath as the multipathing software.

Note not all versions of blkid (well it’s actually the E2fsprogs version) pick up oracleasm as a type.

AS you can see there are various techniques to check what devices you have configured via ASMLib for using with your ASM instance!

ASM Disk Discovery

There is a bit of confusion around about how you can present devices for ASM to use. ASM disk discovery is essentially the process by which ASM discovers device names that it is allowed access to. The easiest mechanism to get ASM to see devices is to use the device name as discovered by the O/S in conjunction with the ASM_DISKSTRING parameter.

This parameter allows the ASM instance to find all devices within the location that the parameter has been set to, and wildcards are allowed. For ASM to see the device you want, it must be located within the path you have specified in the ASM_DISKSTRING parameter AND the permissions must be such that whatever user your ASM instance runs as can read/write to the device you want ASM to find.

The ASM_diskstring has default values that are operating system dependent. On Linux the default of ASM_DISKSTRING is /dev/raw/*, I’d change this to /dev/sd* assuming your devices follow this nomenclature.

While on Solaris the default is /dev/rsdk/* It is important to note on Solaris you have to use the devices under the rdsk tree NOT the /dev/dsk tree!

Once a new device is added to allow ASM to see it, a select on either the V$ASM_DISK, or V$ASM_DISKGROUP views kicks of a discovery process.

It is the RBAL background process that is responsible for discovering the devices. Running an strace on the rbal background process after adding a new device shows the following:

14472 access("/dev/loop4", R_OK|W_OK)   = 0
14472 stat64("/dev/loop4", {st_mode=S_IFBLK|0660, st_rdev=makedev(7, 4), ...}) = 0
14472 open("/dev/loop4", O_RDONLY|O_LARGEFILE) = 25
14472 ioctl(25, BLKGETSIZE, 0xbfd90a70) = 0
14472 close(25)

In the above I had ASM_DISKSTRING set to /dev/loop* and therefore RBAL discovers all devices, including ones ASM already knows about (this is not shown in the above strace output) beginning with loop within the /dev/ directory. This then presented me (I already had some devices within a diskgroup) with the following in V$ASM_DISK:

sys@+ASM> select group_number, disk_number, mount_status, header_status, path from v$asm_disk; 

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU PATH
------------ ----------- ------- ------------ --------
           0           0 CLOSED  CANDIDATE    /dev/loop4
           1           0 CACHED  MEMBER       /dev/loop0
           1           2 CACHED  MEMBER       /dev/loop2
           1           1 CACHED  MEMBER       /dev/loop1

A mount_status of closed means ASM can see the disk but it is not using it. A header_status of candidate means the disk is not part of a disk group (as opposed to MEMBER which means it is part of a diskgroup) but can be added to one.

It’s not all that scary, but I guess it might be outside some DBAs comfort zones!

ASM Mirroring

ASM can provide RAID like protection for your data. ASM provides redundancy via failure groups, essentially when you create a disk group you assign disks that are members of the disk group to a particular failure group. ASM will then mirror the data between these failure groups. The idea being you keep disks that are dependent on the same hardware in the same failure group, so failure of a particular component does not impact the availability of your disk group.

ASM actually mirrors at the extent level. When an extent is allocated there is the concept of a primary extent and a secondary extent, essentially a primary copy and a mirror copy and these are allocated within different failure groups. The above diagram shows primary extents in purple and secondary extents in red (each square represents an extent).

By default, ASM always reads the primary copy of an extent. At first I thought this was a big limitation and would reduce the I/O bandwidth available, but of course ASM mirroring ensures the primary copies are spread across the failure groups so that the I/O is spread over the maximum number of drives.

Frits Hoogland pointed out to me this may still be less efficient than traditional mirroring in that ASM has no chance of optimising a read based on which underlying physical device has the closest disk head position.

To be fair, Oracle themselves to state that external redundancy is preferred unless you have particular requirements that can only be provided with a software RAID solution. Oh and yeah, preferred_read_failure_groups seem like a real win for extended clusters if that is your bag.

ASM and disk “hot spots”

I’ve seen repeated in various locations that ASM somehow has the ability to move data around in response to how much I/O is ocurring on each of the disks that ASM is managing. The theory goes that by doing this, ASM is able to balance the I/O amongst all the drives, thus giving your RDBMS instance(s) that are using ASM the absolute tip-top I/O performance that could possibly be achieved given your hardware limitations.

Sounds great? Trouble is, it just is not true. This idea has gained a bit of traction in the community, and I’m sure many people think ASM is perhaps more clever than it actually is. Whether this is due to marketing terminological inexactitude, i’ll leave up to the reader to decide.

The only metric ASM uses when determining where data should be located is the capacity of the disks in a disk group. ASM’s goal in placing data is to ensure every drive is filled to the same amount. Therefore if you have a disk group of equal size they will receive the same amount of data. The theory being that by spreading the data evenly across the drives you will achieve good I/O performance as both drives are likely to be serving the same number of I/O requests.

ASM does expose some data on how many requests each disk in a disk group is performing, this is via V$ASM_DISK_STAT:

SQL>select group_number, disk_number, read_time, write_time, bytes_read, bytes_written

from V$ASM_DISK_STAT;

GROUP_NUMBER DISK_NUMBER  READ_TIME WRITE_TIME BYTES_READ BYTES_WRITTEN
------------ ----------- ---------- ---------- ---------- -------------

4                0       14910505.7 4626148.24 4.1821E+13 1.4998E+12
4                1       14965432.3 5324739.98 4.1833E+13 1.6264E+12 

There are two disks in this disk group , which are actually of equal size. They have both read and written a similar quantity of data, though it is not exactly equal. The average write time shows a bigger discrepancy than the read times.

Basically, the point is that for equal sized disks in a disk group the ASM algorithm of distributing data according to capacity works reasonably well.

But consider if you had different sized disks in a disk group. A larger disk gets more data. Is a larger disk actually quicker at returning that data? Well, probably not, and the larger the discrepancy in sizes the larger the skew of I/O there will be.

Maybe one day, ASM will have the ability shift data based on the I/O activity of the underlying drives but until then, make sure all the disks you have in a disk group are of the same size (oh and same performance characteristic). That way you’ll protect yourself from any I/O hot spots that ASM won’t quite save you from yet!

Dropping a disk in an ASM Disk Group

One of the big selling points of ASM is the ability to reconfigure the storage online. Previously I’ve blogged about expanding a disk in a disk group. Another useful feature of ASM is to use it to migrate from one set of disks in a disk group to another, or indeed from one storage array to another.

This is basically achievable because ASM distributes data across all disks in a disk group evenly, and assuming you have enough space, you can happily drop disks in a disk group and ASM will seamlessly migrate the data to the existing disks in the disk group.


SQL> select group_number, name, TOTAL_MB, FREE_MB
from V$asm_disk_stat;

GROUP_NUMBER      NAME          TOTAL_MB    FREE_MB
------------ ---------------- ---------- ----------
	   1      VOL1		61439	     61187
	   2      VOL2		61439	     61164
	   3      VOL3		61439	     61164
	   4      VOL4	       409594	    310962
	   4      VOL5	       153597	     95240

So, we see here that VOL4 and VOL5 are two disks (luns) in disk group 4. Previously I had expanded VOL4 and this now has enough capacity to encompass all the data resident on this disk group. I am now safe to drop VOL5 and this is an online operation:

SQL> alter diskgroup DATA4 drop disk VOL5;

Diskgroup altered.

This alter diskgroup command essentially shuffles extents from the disk you are removing and distributes them to the remaining disks in your disk group. While the operation is continuing you can check V$ASM_OPERATION for the progress you are making:

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE  EST_MINUTES
------------ ----- ---- ----- ----- ------ -------  ---------- ----------
	   4 REBAL RUN     1	 1   100   42234       1007     41 

Most of the columns here are self explanatory, however the SOFAR column tells you the number of Allocation Units (au) that have been moved, the EST_WORK and EST_RATE are also in au and au/minute.

Once the rebalance has moved all the Allocation Units the disk is removed from the disk group:


SQL> select group_number, name, TOTAL_MB, FREE_MB
from V$asm_disk_stat;

GROUP_NUMBER      NAME          TOTAL_MB    FREE_MB
------------ ---------------- ---------- ----------
	   1      VOL1		61439	     61187
	   2      VOL2		61439	     61164
	   3      VOL3		61439	     61164
	   4      VOL4	       409594	    252006

Dropping a disk in a disk group seemed to work as advertised, the real benefit of course, is instead of it being just a disk you were dropping but that it was a lun representing a whole storage array, then this has real potential for allowing you to upgrade storage or even migrate to a different storage platform entirely.

Expanding an ASM disk

One of the major advantages of ASM is the ability to reconfigure the storage online. In theory you should be able to add disks, remove disks, and resize disks all the while your ASM and RDBMS instances just keep humming along.

However, I don’t think it is actually possible to expand a lun without downtime if you are using ASMLib. Part of the problem seems to be that with ASMLib you have to create a partition and certainly on RedHat 4 Update 3, using kernel 2.6.9-34.ELsmp, to change a partition table required that ASM was not using the disk that the partition table was residing on.

Recently I found this out the hard way when I attempted to increase the size of a lun that was being used by ASM. Expanding the lun on the storage was fairly straightforward on the EMC Clariion on which the data was residing.

I’m not really sure if this is the best way of mapping OS device -> ASM disk:

[jason@bdb ~]$ sudo /etc/init.d/oracleasm querydisk VOL4
Disk “VOL4″ is a valid ASM disk on device [8, 1]


I believe this to be the major and minor number of the device, so you can look in /dev to see what device this corresponds to:

[jason@bdb ~]$ ls -l /dev/sda1
brw-rw—- 1 root disk 8, 1 Apr 3 16:29 /dev/sda1


Or indeed thanks to Charles Kim you can run the querydisk the opposite way round:

[jason@bdb ~]$ sudo /etc/init.d/oracleasm querydisk /dev/sda1
Disk “/dev/sda1″ is marked an ASM disk with the label “VOL4″


I cannot see in any V$ASM view where this mapping from asm disk -> OS device is exposed, perhaps ASMLib is getting in the way here. What can say is that ASM disk VOL4 maps to /dev/sda1 which is a partiton on /dev/sda. I then increased the size of the lun that this device was created from.

Then comes the scary part, getting the OS to see the increased lun. This was running on rhel 4 update 3, and after a reboot I could see the following via fdisk:

[jason@bdb ~]$ sudo /sbin/fdisk /dev/sda

Command (m for help): p

Disk /dev/sda: 429.4 GB, 429496729600 bytes
255 heads, 63 sectors/track, 52216 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 1 32635 262140606 83 Linux


So the OS can see that the device /dev/sda now has 52216 cylinders but only 32635 (which was the original lun size) have been allocated to the partition /dev/sda1. Now you can actually just delete the partition and recreate it without losing any data:

Command (m for help): d
Selected partition 1


This has deleted the partition

Command (m for help): p

Disk /dev/sda: 429.4 GB, 429496729600 bytes
255 heads, 63 sectors/track, 52216 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System


Now you have to recreate it:

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-52216, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-52216, default 52216):
Using default value 52216

Now we can see the /dev/sda1 partition is up to the full capacity of the underlying lun:

Command (m for help): p

Disk /dev/sda: 429.4 GB, 429496729600 bytes
255 heads, 63 sectors/track, 52216 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 1 52216 419424988+ 83 Linux

Don’t forget to write the changes out:


Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

I actually had ASM shut down at this point because fdisk had previously stated the device was busy (when trying to write the new partition table) and that the kernel would still use the old partition:


WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.


Once ASM (and obviously the RDBMS instance relying on this ASM instance) was down, I was able to write the partition table. Without changing the partition table, ASM would not recognise that the luns had been increased. After this partition table was written, getting ASM to increase what it thought was the size of disk was quite simple:


SQL> select group_number, name, TOTAL_MB, FREE_MB
from V$asm_disk_stat; 2

GROUP_NUMBER NAME TOTAL_MB FREE_MB
———— —————————— ———- ———-
1 VOL1 61439 61187
2 VOL2 61439 61164
3 VOL3 61439 61164
4 VOL4 255996 157374
4 VOL5 153597 95230

SQL> alter diskgroup DATA4 resize all rebalance power 4;

Diskgroup altered.


SQL> select group_number, name, TOTAL_MB, FREE_MB
from V$asm_disk_stat; 2

GROUP_NUMBER NAME TOTAL_MB FREE_MB
———— —————————— ———- ———-
1 VOL1 61439 61187
2 VOL2 61439 61164
3 VOL3 61439 61164
4 VOL4 409594 310962
4 VOL5 153597 95240


So what am I saying, well it seems that with ASMLib it is hard to resize a disk completely online with ASM, but due to the fact that the Linux partition table cannot be re-written while ASM has open the device.

Perhaps, this is a disadvantage of ASMLib compared to running without out, though for my money ASMLib seems to be the favoured Oracle solution, certainly I think it is pushed in the documentation.

It’s a bug!

Well it seems like the issue whereby my disk group was showing free space on 1 disk in the diskgroup but out of space on the other disk has turned out to be a bug. The details of the problem are available in the last post.

Oracle support are now saying that we are being hit by Bug 4380450, which is to do with:

Unbalanced space usage if diskgroup has only two disks both of different sizes.

This bug is known to affect 10.2.0.3 (the version I was running). and is marked as fixed in 10.2.0.4 and 11.1.0.6. The disk group must have exactly 2 disks and the disks must be of different sizes, then it is possible you will have unbalanced data allocation, which in turn can lead you to filling one of the drives, which means you can no longer allocate disk space from this disk group. If the data was balanced you would still have storage capacity available.

The workarounds on this bug are to perform a manual rebalance or use disks that are the same size - advice which I would thoroughly agree with!

Keep Disks in your Diskgroup the same size

In my production instances, I have only ever used ASM with external redundancy. Hey, I’m paying expensive fees for fancy hardware RAID, I might as well use it. I therefore tend to present to ASM a LUN which is normally a RAID 10 stripe set. As far as ASM is concerned this is one large disk and it does not have to worry about failure groups.

Well, that is the ideal, but as we know we don’t live in a perfect world. On one of the production instances, due to using “hand me down” hardware, I have created 2 LUNS, and these are of differing sizes. Everything was running along happily until, one of the LUNS ran out of space.

I had thought ASM was meant to distribute extents based on the size of the disks in the diskgroup, so for example, if your diskgroup was made up of 2 disks and one was say 60GB and one was 120GB the 120GB would contain twice as many extents (Allocation Units) as the 60GB disk. This would ensure that one disk in the diskgroup was never filled up while the other one still had plenty of space. Well, it seems that this does not necessarily work perfectly in practice.

So I have a diskgroup lets, call it DATA4 and it is made up of two disks VOL4 and VOL5 and when you look at V$ASM_DISKGROUP this diskgroup has lots of lovely free space:

 SQL> select group_number, name, total_mb, free_mb
 from V$ASM_DISKGROUP;

GROUP_NUMBER NAME		 TOTAL_MB	FREE_MB
------------ ------------------ ----------     ----------
4            DATA4		 220391	       24501

So If you just looked at that view you would be hard pushed to explain why you could not allocate space in your diskgroup. However if you diskgroup is made up of multiple disks take a look at the following view:

SQL> select group_number, name, TOTAL_MB, FREE_MB
from V$asm_disk_stat;
GROUP_NUMBER NAME			      TOTAL_MB	  FREE_MB
------------ ------------------------------ ---------- ----------
	   4 VOL4				124660	    24501
	   4 VOL5				 95731	        0

Oh great! All the available space in the diskgroup is on one of the disks in the diskgroup. ASM is not clever enough to just then allocate new extents to this disk in the diskgroup, it will just keep on doing it’s effective round robin distribution of extents, which means you will get an ORA-15041 error saying the diskgroup space is exhausted. And you’ll be convinced that it ain’t so if you just look at V$ASM_DISKGROUP.

Thankfully, there is help at hand to fix this in the rebalance process. I had thought a rebalance was only required when the storage had physically changed, i.e. adding a new disk, but a rebalance basically evened out where the data was stored:

SQL> alter diskgroup DATA4 rebalance;

You can set a variable level of speed to the rebalance using the power syntax. After the rebalance completed and it took 41 minutes at power 1. I saw the following in V$ASM_DISK_STAT:

SQL> select group_number, name, TOTAL_MB, FREE_MB
from V$asm_disk_stat;
GROUP_NUMBER NAME			      TOTAL_MB	  FREE_MB
------------ ------------------------------ ---------- ----------
	   4 VOL4				124660	    13859
	   4 VOL5				 95731	    10642

Bingo! I can now allocate new extents in my diskgroup and I have not increased the storage available by 1 byte.

Definitely, it will save you pain if you keep all disks in your diskgroup the same size.

Managing Datafiles on a Standby using ASM

I encountered a curious failure in a dataguard environment, that seems interesting enough to distribute to a wider audience. The system was running 10.2.0.3 on Linux, with the datafiles stored in ASM. This was recorded recently in the RDBMS instance alert log:

MRP0: Background Media Recovery terminated with error 1237
Sun Mar 23 09:57:19 2008
Errors in file /opt/oracle/product/admin/STANDBY/bdump/standby1_mrp0_27165.trc:
ORA-01237: cannot extend datafile 35
ORA-01110: data file 35: '+DATA4/standby/datafile35.dbf'
ORA-17505: ksfdrsz:1 Failed to resize file to size 1624704 blocks
ORA-15041: diskgroup space exhausted

While in the ASM instance alert log I found the following:

Sun Mar 23 09:57:18 2008
WARNING: allocation failure on disk VOL5 for file 286 xnum 12693

At first sight you might think this is an obvious case of the diskgroup filling up and that more space needs to be allocated to it. However when I checked how much free space was available I saw:

SQL> select name, total_mb, free_mb, usable_file_mb from v$asm_diskgroup;

NAME				 TOTAL_MB    FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- --------------
DATA1				    61439      29766	      29766
DATA2				    10239	3356	       3356
DATA3				    10239	3356	       3356
DATA4				   220391      25077	      25077
FRA				    68197      67959	      67959

So as far as the V$ASM_DISKGROUP view was concerned there really was enough storage space to allocate to this datafile, note this datafile was already a considerable size so the amount it was extending was nothing compared to the 25GB free. Much scratching of heads ensued, and  I started thinking if fragmentation could be responsible, but then I looked at the contents of the affected diskgroup using asmcmd.

I spotted that there was a datafile that had been removed a couple of weeks previously from the primary. At the same time as that datafile, several others had been removed and all these were gone. And then we remembered that the first drop tablespace command had not included the and datafiles clause. We have standby_file_management set to auto and this worked perfectly for files that were automatically removed on the primary. It did not work for the datafile that was removed manually, as you’d probably expect. Running rm within ASMCMD logged the following kind of thing into the alert log of the ASM instance:

SQL> alter diskgroup 'DATA4' drop file '+DATA4/STANDBY/removed_datafile.dbf'

Once this datafile was removed the standby could continue processing happily and whatever caused it to fail to extend the datafile was now not causing it a problem. The real question is why I could not allocate the space when the diskgroup was not really full?

I think what happened was that the RDBMS instance thought the space allocated to the datafile that was being used by the dropped tablesapace was now available for use again and the database instance tried to extend a tablespace into this space but found it was in fact still occupied by the datafile that had not been removed.

Clearly having the database clean up datafiles automatically is a really useful feature, and this becomes doubly so in the case of a dataguard environment. Certainly, I think it is a good idea to drop a tablespace with the including contents and datafiles clause.