When ASM rebalancing stops...!!!!

Was adding disks to ASM diskgroups few days back as a part of weekly space addition. Added one disk to each of the ASM diskgorup (CDBP and CDBP2). The disk space got reflected in both the diskgroups, but lot of interesting things were waiting for me. I was able to use space the space in CDBP2 diskgroup for tablespaces but CDBP diskgroup space got jammed….

Why?????????? No idea…..

Do I need to drill the logs…Yup go ahead….!!!!!!!!!

SQL> alter diskgroup CDBP add disk '/dev/rrawasm40lv' rebalance power 11;

ASM alert says - Disk added successfully

-----------------------------------------------------------------------------------------------

SQL> alter diskgroup CDBP add disk '/dev/rrawasm40lv' rebalance power 11

Sat Aug 15 04:47:12 2009

NOTE: initializing header on grp 3 disk CDBP_0031

NOTE: cache opening disk 31 of grp 3: CDBP_0031 path:/dev/rrawasm40lv

NOTE: PST update: grp = 3

NOTE: requesting all-instance disk validation for group=3

Sat Aug 15 04:47:12 2009

NOTE: disk validation pending for group 3/0x3bae00fb (CDBP)

SUCCESS: validated disks for 3/0x3bae00fb (CDBP)

-----------------------------------------------------------------------------------------------

Database alert says - Disk added successfully

-----------------------------------------------------------------------------------------------

Sat Aug 15 04:47:17 2009

SUCCESS: disk CDBP_0031 (31.3954110510) added to diskgroup CDBP

-----------------------------------------------------------------------------------------------

Diskgroup status shows the added space

DG# NAME STATE TYPE TOTAL_MB FREE_MB

--- --------------- ----------- ------ ---------- ----------

1 CDBP2 MOUNTED EXTERN 1540096 282161

2 CDBP_FRA MOUNTED EXTERN 255744 255657

3 CDBP MOUNTED EXTERN 7575552 262177

But.......!!!!!!!!!!!!

SQL> alter database datafile '+CDBP/data01/cdb_stg_tbl_tbsp11.dbf' resize 26000M;

Sat Aug 15 09:32:07 2009

ORA-1237 signalled during: alter database datafile '+CDBP/data01/cdb_stg_tbl_tbsp11.dbf' resize 26000M...
ORA-01237: cannot extend datafile 78
ORA-01110: data file 78: '+CDBP/data01/cdb_stg_tbl_tbsp11.dbf'
ORA-17505: ksfdrsz:1 Failed to resize file to size 832000 blocks
ORA-15041: diskgroup space exhausted

Oops….!!!!!!!!....Try again….

SQL>alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp12.dbf' size 25000M autoextend on next 1M maxsize 50000M

Sat Aug 15 04:56:13 2009

alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp12.dbf' size 25000M autoextend on next 1M maxsize 50000M

Sat Aug 15 04:56:13 2009

ORA-1119 signalled during: alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp12.dbf' size 25000M autoextend on next 1M maxsize 50000M...

Sat Aug 15 04:57:09 2009

No Luck!!!!!!!!!

Bounced the database and asm at this point.…….And tried adding space...No Luck!!!!!

Again checked the ASM alert log….

ASM started re-balancing

Sat Aug 15 04:47:24 2009

Starting background process ARB6

ARB5 started with pid=19, OS id=454928

Sat Aug 15 04:47:24 2009

Starting background process ARB7

ARB6 started with pid=20, OS id=454896

Sat Aug 15 04:47:24 2009

Starting background process ARB8

ARB7 started with pid=21, OS id=516370

Sat Aug 15 04:47:24 2009

Starting background process ARB9

ARB8 started with pid=22, OS id=545090

Sat Aug 15 04:47:24 2009

Starting background process ARBA

ARB9 started with pid=23, OS id=541246

Sat Aug 15 04:47:24 2009

NOTE: assigning ARB0 to group 3/0x3bae00fb (CDBP)

NOTE: assigning ARB1 to group 3/0x3bae00fb (CDBP)

NOTE: assigning ARB2 to group 3/0x3bae00fb (CDBP)

NOTE: assigning ARB3 to group 3/0x3bae00fb (CDBP)

ARBA started with pid=24, OS id=303848

Sat Aug 15 04:47:24 2009

NOTE: assigning ARB4 to group 3/0x3bae00fb (CDBP)

NOTE: assigning ARB5 to group 3/0x3bae00fb (CDBP)

NOTE: assigning ARB6 to group 3/0x3bae00fb (CDBP)

NOTE: assigning ARB7 to group 3/0x3bae00fb (CDBP)

NOTE: assigning ARB8 to group 3/0x3bae00fb (CDBP)

NOTE: assigning ARB9 to group 3/0x3bae00fb (CDBP)

NOTE: assigning ARBA to group 3/0x3bae00fb (CDBP)

oops.....!!!!!!!!!!!

Sat Aug 15 04:47:24 2009

WARNING: allocation failure on disk CDBP_0024 for file 260 xnum 1

Sat Aug 15 04:47:24 2009

WARNING: allocation failure on disk CDBP_0021 for file 259 xnum 1

Sat Aug 15 04:47:24 2009

WARNING: allocation failure on disk CDBP_0002 for file 265 xnum 1

WARNING: allocation failure on disk CDBP_0023 for file 265 xnum 4

Sat Aug 15 04:47:24 2009

WARNING: allocation failure on disk CDBP_0000 for file 262 xnum 1

Sat Aug 15 04:47:24 2009

WARNING: allocation failure on disk CDBP_0022 for file 265 xnum 6

Sat Aug 15 04:47:24 2009

WARNING: allocation failure on disk CDBP_0025 for file 264 xnum 1

Sat Aug 15 04:47:24 2009

WARNING: allocation failure on disk CDBP_0025 for file 257 xnum 5

Sat Aug 15 04:47:24 2009

WARNING: allocation failure on disk CDBP_0002 for file 264 xnum 2

Sat Aug 15 04:47:24 2009

WARNING: allocation failure on disk CDBP_0016 for file 265 xnum 18

WARNING: allocation failure on disk CDBP_0020 for file 261 xnum 1

Sat Aug 15 04:47:24 2009

ERROR: ORA-15041 thrown in ARB1 for group number 3

Sat Aug 15 04:47:24 2009

Errors in file /u01/app/oracle/admin/+ASM/bdump/+asm_arb1_553184.trc:

ORA-15041: diskgroup space exhausted

Sat Aug 15 04:47:24 2009

NOTE: stopping process ARB1

Sat Aug 15 04:47:24 2009

ERROR: ORA-1013 thrown in ARB0 for group number 3

Sat Aug 15 04:47:24 2009

Errors in file /u01/app/oracle/admin/+ASM/bdump/+asm_arb0_430158.trc:

ORA-01013: user requested cancel of current operation

Sat Aug 15 04:47:24 2009

NOTE: stopping process ARB0

Sat Aug 15 04:47:24 2009

I am on fire…!!!!!!!!!!!.....Took me about 2 hours when I got Metalink Note 351117.1

asmdebug.sql -- To be executed on the ASM instance

rdbmsdebug.sql -- To be executed on the RDBMS instance

asmdebug.out says…

DG# NAME STATE TYPE TOTAL_MB FREE_MB

--- --------------- ----------- ------ ---------- ----------

1 CDBP2 MOUNTED EXTERN 1540096 282161

2 CDBP_FRA MOUNTED EXTERN 255744 255657

3 CDBP MOUNTED EXTERN 7575552 262177

Number of allocated (V) and free (F) Allocation Units

group# disk# VF COUNT(*)

---------- ---------- -- ----------

3 5 F 1

3 5 V 255807

3 6 F 1

3 6 V 255807

3 7 V 255808

3 8 F 1

3 8 V 255807

3 18 F 1

3 18 V 255807

No free AU’s (allocation units) in disks. Disks jammed and stopped rebalancing.

The debug result is showing many of the disks in diskgroup CDBP are almost 100% full. When any disk in a diskgroup is full, the rebalance job cannot run and the disk operation cannot complete.

Even though there were new disks added to the diskgroup, but the rebalance did not run.Thus, any attempt to use more free space would get the space error.

Solution

1 Clean up some space in this diskgroup by dropping files or shrinking files
after dropping DB objects/records.

2. Once disk usage of ALL disks in the diskgroup drops down to around 95% full or at least 100-200MB free space for each disk, run rebalance manually.

ALTER DISKGROUP REBALANCE POWER 11;

3. Monitor the rebalance job in v$asm_operation view. Once the rebalance is complete, you can resize the datafiles as needed.

Resized some files.….!!!!!!!!!!!

SQL> alter database datafile '+CDBP/system/sysaux01.dbf' resize 1400M

Sat Aug 15 12:10:09 2009

alter database datafile '+CDBP/system/sysaux01.dbf' resize 1400M

Completed: alter database datafile '+CDBP/system/sysaux01.dbf' resize 1400M

Sat Aug 15 12:18:45 2009

Sat Aug 15 12:07:50 2009

alter database datafile '+CDBP/data01/perfstat_01.dbf' resize 200M

Sat Aug 15 12:07:51 2009

Completed: alter database datafile '+CDBP/data01/perfstat_01.dbf' resize 200M

Sat Aug 15 12:08:01 2009

alter database datafile '+CDBP/data01/perfstat_01.dbf' resize 100M

Sat Aug 15 12:08:01 2009

Completed: alter database datafile '+CDBP/data01/perfstat_01.dbf' resize 100M

Sat Aug 15 12:08:49 2009

Asm rebalancing succeeded for CDBP diskgroup

.

Sat Aug 15 14:28:07 2009

NOTE: stopping process ARBA

Sat Aug 15 14:28:07 2009

WARNING: rebalance not completed for group 3/0x2ebf5cac (CDBP)

Sat Aug 15 14:28:07 2009

SUCCESS: rebalance completed for group 3/0x2ebf5cac (CDBP)

Tried adding space to tablespace

Sat Aug 15 12:18:45 2009

alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp12.dbf' size 30000M autoextend on next 1M maxsize 50000M

Sat Aug 15 12:24:11 2009

Completed: alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp12.dbf' size 30000M autoextend on next 1M maxsize 50000M

Sat Aug 15 12:25:06 2009

alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp13.dbf' size 30000M autoextend on next 1M maxsize 50000M

Sat Aug 15 12:30:11 2009

Completed: alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp13.dbf' size 30000M autoextend on next 1M maxsize 50000M

Sat Aug 15 12:33:59 2009

alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp14.dbf' size 30000M autoextend on next 1M maxsize 50000M

Sat Aug 15 12:40:15 2009

Completed: alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp14.dbf' size 30000M autoextend on next 1M maxsize 50000M

Sat Aug 15 12:41:03 2009

alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp15.dbf' size 30000M autoextend on next 1M maxsize 50000M

Sat Aug 15 12:46:30 2009

Completed: alter tablespace CDB_STG_TBL_TBSP add datafile '+CDBP/data01/cdb_stg_tbl_tbsp15.dbf' size 30000M autoextend on next 1M maxsize 50000M

Voila…………:-))))))))..Worked this time....!!!!!

--Rishi