ORA-1237 signalled during: ALTER DATABASE DATAFILE

When I tried to resize the datafile, I was getting the below errors even though we have plenty of space available in ASM.


Mon Oct 31 13:12:52 2011
ALTER DATABASE DATAFILE '+DBNAME_DATA_01/DBNAME2/datafile/bipsched.280.751030175' RESIZE 120M
ORA-1237 signalled during: ALTER DATABASE DATAFILE '+DBNAME_DATA_01/DBNAME2/datafile/bipsched.280.751030175' RESIZE 120M...
ORA-17505: ksfdrsz:1 Failed to resize file to size 3168 blocks
ORA-15061: ASM operation not supported [41]
~
~



I can create a new tablespace as below but cannot resize any datafile.

SQL> create tablespace test;

Tablespace created.

SQL> drop tablespace test;

Tablespace dropped.


ASM Diskgroup information ..


DiskGroup Name %Used TOTAL_MB FREE_MB
-------------------- ----- ------------ ------------
DBNAME_DATA_01 58 1,048,000 439,612


SQL> select group_number, disk_number, total_mb, free_mb from v$asm_disk order by 1;

GROUP_NUMBER DISK_NUMBER TOTAL_MB FREE_MB
------------ ----------- ---------- ----------
1 11 131000 81944
1 12 131000 55811
1 10 131000 35444
1 9 131000 16276
1 8 131000 42609
1 7 131000 62822


29 rows selected.


ASM log:
======================

NOTE: ASM client DBNAME:DBNAME disconnected unexpectedly.
NOTE: check client alert log.
NOTE: Process state recorded in trace file /ora01/u0001/oracle/diag/asm/+asm/+ASM/trace/+ASM_ora_5197.trc
~
~


*** 2011-10-31 13:06:42.127
kggpnpSIHAGetItem 2 = /dev/oracle/*
KGGPNP_SIHA: attribute value for 'ASM_DISKSTRING' is '/dev/oracle/*'

*** 2011-10-31 13:11:07.151
kggpnpSIHAGetItem 2 = /dev/oracle/*

*** 2011-10-31 13:11:11.711
KGGPNP_SIHA: attribute value for 'ASM_DISKSTRING' is '/dev/oracle/*'
kggpnpSIHAGetItem 2 = /dev/oracle/*

*** 2011-10-31 13:11:46.464
KGGPNP_SIHA: attribute value for 'ASM_DISKSTRING' is '/dev/oracle/*'
kggpnpSIHAGetItem 2 = /dev/oracle/*

No much useful info from the ASM logs:


Tried rebalance as suggested by some on the oracle forums:
============================================================

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> alter diskgroup DBNAME_DATA_01 rebalance power 11;

Diskgroup altered.

SQL> select sysdate, OPERATION,STATE, POWER, ACTUAL, SOFAR, EST_WORK,EST_RATE, EST_MINUTES
from v$asm_operation;
2
SYSDATE OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
--------- ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES
-----------
31-OCT-11 REBAL RUN 11 11 192 103187 3952
26


SQL> /

SYSDATE OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
--------- ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES
-----------
31-OCT-11 REBAL RUN 11 11 1272 103592 4058
25


SQL> set linesize 1000
SQL> /

SYSDATE OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
--------- ----- ---- ---------- ---------- ---------- ---------- ---------- -----------
31-OCT-11 REBAL RUN 11 11 3192 107444 6249 16




In MOS:

Resolution:

ORA-15061 reported while doing a file operation with 11.1 or 11.2 ASM after PSU applied in database home [ID 1070880.1]

and Indeed our ASM and DB homes are different and we recently applied PSU patch to our DBHOME but not to the ASM home.
So as per the above note it is requirement to apply the PSU on both the homes.

Applying the patch:

SQL> shutdown immediate;


unixhost(+ASM) /ora_backup/u0001/patches/PSU/9952216
>/ora_backup/u0001/patches/OPatch/OPatch/opatch napply -skip_subset -skip_duplicate
Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /ora01/u0001/oracle/11.2.0/grid
Central Inventory : /ora01/u0001/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 11.2.0.1.6
OUI version : 11.2.0.1.0
Log file location : /ora01/u0001/oracle/11.2.0/grid/cfgtoollogs/opatch/opatch2011-11-01_17-33-11PM.log

Verifying environment and performing prerequisite checks...
Checking skip_duplicate
Checking skip_subset
OPatch continues with these patches: 9952216

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Is the local system ready for patching? [y|n]
y

User Responded with: Y
Backing up files...
Applying interim patch '9952216' to OH '/ora01/u0001/oracle/11.2.0/grid'
ApplySession: Optional component(s) [ oracle.oraolap, 11.2.0.1.0 ] , [ oracle.precomp.common, 11.2.0.1.0 ] not present in the Oracle Home or a higher version is found.



Patches 9952216 successfully applied.

After aplying the patch.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DBNAME2


SQL> alter database datafile '+DBNAME_DATA_01/DBNAME2/datafile/undotbs1.272.750861081' resize 850M;

Database altered.