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.