Disk Array Migration for ASM Databases

This blog identifies the DBA steps required to migrate databases from one disk array to another.

Identify the ASM diskgroups and their disk (LUN) sizes

Login to the ASM instance and identify the LUNs allocated to server.

SQL> SET PAGES 999

SQL> col name format a15

For 11g ASM:

SQL> select d.group_number, g.name, d.os_mb

from v$asm_disk d left outer join v$asm_diskgroup g

on (d.group_number = g.group_number)

order by g.group_number, d.os_mb;

For 10g ASM:

SQL> select d.group_number, g.name, d.total_mb

from v$asm_disk d left outer join v$asm_diskgroup g

on (d.group_number = g.group_number)

order by g.group_number, d.total_mb;

You may discard LUNs with group number 0, as they are not part of any diskgroup. Request Sysadmin team to provision the new LUNs to the container, same LUN size or total of multiple LUNs in the group is equal or more to what is allocated to the group. 

 To find out what is allocated and free in each diskgroup, you may use:

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

Once LUNs are provisioned by Unix team, use OEM or SQL to add the LUNs to respective diskgroups [existing diskgroup]. NO outage required for this step. Remember to login to 11g ASM using the SYSASM privilege, for 10g login using SYSDBA privilege.

SQL> ALTER DISKGROUP ADD DISK ‘/full_path_of_device’;

To find out which LUNs are available to add, you may use SQL:

SQL> col path format a40

SQL> select path, os_mb from v$asm_disk where group_number = 0;

{for 10g ASM, please substitute os_mb with total_mb}

Then, go ahead and drop the old LUNs. No need to wait for the add operation to complete.

SQL> ALTER DISKGROUP DROP DISK name_of_old_LUN;

The name of the LUN can be found by querying the V$ASM_DISK...

SQL> select path,name from v$asm_disk where group_number=3;

Keep the rebalance power low, so that the migration activity does not impact database performance.

You may check the progress of rebalance periodically using V$ASM_OPERATION, once no rows are in this view, you may advice SYSADMIN team to pull out the old LUNs from the server. V$ASM_OPERATION also shows the work so far completed and how long it will take to complete the work.

SYSAUX Tablespace Growth - Review and Fix

Received an alert from one of the databases that SYSAUX tablespace is nearing its allocated 24GB space. 24GB is too much for SYSAUX, especially on a development database... so need to make sure the contents are reviewed before adding more space...

Oracle10g+ versions have a view V$SYSAUX_OCCUPANTS, showing what components are in SYSAUX tablespace and how much space is used. It also shows the procedure to use if you decide to move this component from SYSAUX to another tablespace.



SQL> col OCCUPANT_NAME format a20

SQL> select occupant_name, space_usage_kbytes

     from v$sysaux_occupants
     where space_usage_kbytes > 10240
     order by space_usage_kbytes desc;

OCCUPANT_NAME        SPACE_USAGE_KBYTES
-------------------- ------------------
SM/AWR                          2875776
LOGMNR                           573184
SM/ADVISOR                       448384
XDB                              177216
SDO                               79936
STREAMS                           76928
AO                                49536
XSOQHIST                          49536
XSAMD                             31872
SM/OTHER                          17984
TEXT                              14720

11 rows selected.

SQL>

So, it really does not add up anywhere near 24GB, the largest component is AWR and is only occupying less than 3GB... What else is in SYSAUX, that does no belong to the standard Oracle components?


SQL> col SEGMENT_NAME format a25
SQL> col segment_type format a10
SQL> col bytes format "999,999,999,999"
SQL> select segment_name, bytes 
     from dba_segments 
     where tablespace_name = 'SYSAUX'
     and bytes > 1048576000 order by bytes;

SEGMENT_NAME                         BYTES
------------------------- ----------------
SYS_LOB0002516712C00008$$   20,408,434,688

1 row selected.

SQL>

It's a LOB segment, let us find out which table...

SQL> select table_name, column_name

  2  from dba_lobs

  3  where segment_name = 'SYS_LOB0002516712C00008$$';

TABLE_NAME

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

COLUMN_NAME

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

STREAMS$_APPLY_SPILL_MSGS_PART

MESSAGE

Aaaha! We recently enabled Change Data Capture feature on this database, and it is using STREAMS... Now, need to see what's causing it to grow and how to fix...!

Also, shows that V$SYSAUX_OCCUPANTS do not consider the LOB segments, when calculating space occupied  :-(