How to Add/Drop Disk in ASM

Its not that quite often your SA asks you to release one of the disks that you been using it for a while for your database but if they do then you need to know how to remove the disk and add a new disk from your ASM.

Here are the steps to Add and Drop a Disk from ASM Diskgroup. Note that asm_power_limit parameter once set is permanent as it writes to both spfile as well (check alert log when you do alter session set asm_power_limit = 10 command to see that it converts it into scope=both).

What's asm_power_limit parameter? 

In simple words, Think that you are increasing the work force to finish the job (re-balancing the disk(s)) quicker. You can read more in Oracle Docs.

OK, Coming to simple steps to Add a Disk:

1. Assign the disk to ORACLEASM
1. /etc/init.d/oracleasm createdisk TIER2_DATA03 /dev/mapper/mpath14p1
2. ScanDisk in ALL NODES
1. /etc/init.d/oracleasm scandisks
3. Check the header_status in v$asm_disks to make sure that the disk can be added (check below for each status description). 
1. Login as sqlplus / as sysasm – note SYSASM here… (for 11g)
2. set lines 200
3. select group_number, substr(path,1,20) path, substr(name,1,20) disk_name, total_mb, free_mb, state, header_status, mode_status from v$asm_disk;
4. Now Add the Disk to diskgroup.
1. alter diskgroup TIER2_DATA add disk 'ORCL:TIER2_DATA03'; --At this point the new disk is added to the group and Re-Balancing is occurring in the background (basically spreading the data across all the disks along with the new one in that diskgroup)
5. Check the status in v$asm_operations for rebalancing action.
1. select * from v$asm_operation;
6. You are ready to start using the new disk.
7. Drop the old Disk
1. alter diskgroup TIER2_DATA drop disk TIER2_DATA01; --At this point, all the data on disk that you are dropping is being distributed to other disks within that diskgroup.
2. select *from v$asm_operation; --to check the status on rebalancing while dropping the disk
8. Once dropped from ASM, delete from oracleasm library:
1. [root@ndhdbd1 disks]# /etc/init.d/oracleasm deletedisk TIER2_DATA01
9. Removing ASM disk "TIER2_DATA01":                          [  OK  ]
10. ScanDisk in ALL NODES
1. /etc/init.d/oracleasm scandisks

V$ASM_DISK Header_Status Info:

• UNKNOWN - Automatic Storage Management disk header has not been read
• CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
• INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.
• PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
• MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
• FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
• CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
• FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.

 

How to Find Mapping of ASM Disk to Physical Device in Linux

If your ASM is using RAW devices then its pretty easy to find out by just looking into your /etc/sysconfig/rawdevices (hope your DBA had made some comments as to which RAW devices is for which).

If you are using ASMLib then its kinda hard to find out the exact mapping of your ORACLEASM stamped device to the Linux Physical Device.

To make it simple, here are the steps to find the Physical Device that is mapped to your ASMLib disk:
Run the following as a root:
1. /etc/init.d/oracleasm listdisks -- lists all the disks that are in your ASM
2. /etc/init.d/oracleasm querydisk -d disk_name_that_you_wan_to_find_physical_device (this name is from the above result)
3. Note the values in braces [ n1, n2] from the above command result
4. do ls -al /dev/ |grep n1 |grep n2 -- n1 and n2 are the values from the above step you noted

That gives you the device number its mapped to.

Eg.,

#/etc/init.d/oracleasm querydisk -d TIER2_DATA01
Disk "DG1_DISK1" is a valid ASM disk on device [253, 26]


#ls -l /dev |grep 253 |grep 26
brw-rw----  1 root root 253,    26 Nov  3 12:03 dm-26

From the above dm-26 is the physical device that DG1_DISK1 ASM Disk is mapped to.

Here is the shell script that can be used to find mapping for all ASM Disks in one shot:
=====Start

#SC Created this file to map back ASM disks to physical disks
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk  '{print $2}'`
#echo $v_asmdisk
v_start=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $1}'`
v_end=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $2}'`
v_device=`ls -la /dev | grep $v_start | grep $v_end | awk '{print $10}'`
v_mapper=`ls -la /dev/mapper | grep $v_start | grep $v_end | awk '{print $10}'`
echo "ASM disk $v_asmdisk is mapped to device on /dev/$v_device based on [$v_start $v_end]"
echo "ASM disk $v_asmdisk is mapped to device mapper on /dev/mapper/$v_mapper based on [$v_start $v_end]"
done
==EOF==

Oracle Case Insensitive search

Example:

create table san_ci (name varchar2(20));

insert into san_ci values ('John smith');
insert into san_ci values ('John Smith');
insert into san_ci values ('John SMITH');
insert into san_ci values ('John SMITH');
insert into san_ci values ('JOHN smITH');
commit;


select name
  from san_ci
 where name like '%mit%';


NAME
John smith
John Smith

Using Regular Expression Function:

select name
  from san_ci
 where regexp_like (name,'mit','i');
NAME
John smith
John Smith
John SMITH
John SMITH
JOHN smITH

Using 10g NLS parameters:


alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;

select name
  from san_ci
 where name like '%mit%'; --notice the results for the same query above without setting these NLS parameters.
NAME

John smith
John Smith
John SMITH
John SMITH
JOHN smITH

You can create an NLS_SORT functional index on the column if the table that is being used in search criteria is huge:

create index san_ci_idx1 on san_ci (NLSSORT (name, 'NLS_SORT=BINARY_CI') );

Creating the above index alone will not help the query to search the data in case insensitive. You have to set the session variables for case insensitive searches.