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.

Cleanup Windows winsxs folder...

Recently noticed my Windows 7 C: Drive is almost getting full and found that the folder WINSXS under C:\Windows\ is more than 10GB !!! Its Microsoft Service Pack data that is not being removed after installation is completed. Well, for a reason of-course to un-install later if you decided... In my case, I dont think i will be uninstalling service packs. So, need to clean it up.

Run the following in DOS window it should get you back at least 5GB!!!

DISM /online /Cleanup-Image /SpSuperseded