Tag Archives: ORA-59000

Rebalance operation in Disk group Oracle ASM

Rebalance operation after add or remove disk in Disk group ASM

Rebalance operation depend upon the ASM_POWER_LIMIT parameter which defines the speed of re-balance operation for Automatic re-balancing. You can do it manually with ALTER DISKGROUP command with defining POWER clause. Higher value means more speed. If you use 0 then rebalance operation stop. You need to start the rebalance operation by setting the power greater than 0 value.

Note:
Rebalance operation always run on single node if either you are using RAC environment.
Rebalance operation required PGA memory for extent relocation.

Following phase options during rebalance operation:
1. RESTORE(default): has 3 option RESYNC(sync the stale extend on the disk online), RESILVER(Exadata mirror data to stale), RESTORE(restore redundancy of forcing disk)
2. BALANCE : restores redundancy of all the disks in the disk group and also balances extents on Oracle ASM disks
3. PREPARE : completes the work corresponding to the prepare SQL operation
4. COMPACT : de-fragments and compacts extents across Oracle ASM disks.

Note: PASS column of V$ASM_OPERATION is updated for resync, rebalance & compact operations.

Check the phase of rebalance operation

SQL> SELECT GROUP_NUMBER, PASS, STATE FROM V$ASM_OPERATION;
GROUP_NUMBER PASS STAT
------------ --------- ----
2 RESYNC WAIT
2 REBALANCE WAIT
2 COMPACT WAIT

Example of adding and rebalancing disk in the diskgroup

Added the disk DATA1 in diskgroup with POWER 5
WAIT clause means query wait to complete until rebalance operation is done successfully.
NOWAIT (Default) clause means query will return you need to monitor operation from v$ASM_OPERATION view.

ALTER DISKGROUP data1 ADD DISK '/devices/diskd10' REBALANCE POWER 5 WAIT;

ALTER DISKGROUP data2 REBALANCE RESTORE POWER 5 WAIT;

Increase power of Rebalance operation with power 10 with modify keyword

ALTER DISKGROUP diskgroup2 REBALANCE MODIFY POWER 10;

Note: Query not wait for complete the operation(default nowait).

use of compact phase

ALTER DISKGROUP data2 REBALANCE WITH BALANCE COMPACT;

Query the V$ASM_OPERATION view to monitor the status of this operation.

select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION where GROUP_NUMBER=1;

INST_ID OPERA STAT POWER SOFAR EST_WORK EST_RATE EST_MINUTES
------- ----- ---- ----- ----- -------- -------- -----------
3       REBAL WAIT  1
2       REBAL RUN   1    516   53736       2012  26
4       REBAL WAIT  1


Note: ORA-59000 error at the end of the rebalance if all rebalance is not done. Result show in V$ASM_OPERATION

Advertisements