select group_number DG#, name, state, type, round(total_mb/1024) TOT_GB, round(free_mb/1024) Free_GB ,ROUND((1- (free_mb / total_mb))*100, 2) pct_used from v$asm_diskgroup;
col path for a40
select group_number,disk_number,header_status,name,substr(path,1,20) "path" ,round(total_mb/1024) TOT_GB,round(free_mb/1024) Free_GB,state,mode_status,mount_status from v$asm_disk;
select name,redundancy,failgroup,path from v$asm_disk where group_number=2;
select instance_name,db_name,status from v$asm_client;
select group_number,file_number,bytes,space,type,redundancy,striped from v$asm_file;
break on group_number skip 1
select group_number,name,redundancy,failgroup,path from v$asm_disk group by group_number,name,redundancy,failgroup,path order by group_number;
select * from (
SELECT
CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path
, db_files.bytes
, db_files.space
, NVL(db_files.type, '<DIRECTORY>') type
--, db_files.creation_date
, db_files.disk_group_name
, LPAD(db_files.system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) db_files
WHERE db_files.type IS NOT NULL
START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR db_files.rindex = db_files.pindex ) x
where x.type = '&ftype'
and x.full_path like '%&dbname%';
With oracleasm , find which disk is mapped to which device
Method 1 :
$ oracleasm listdisks
ASMDISK01
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
$ oracleasm querydisk -d ASMDISK01
Disk "ASMDISK01" Is a valid ASM disk On device[8,17]
$ ls -l /dev/* | grep 8, | grep 17
brw-rw---- 1 root disk 8, 17 Apr 16 15:27
ASMDISK01 is mapped to /dev/sdb1
Method 2 :
ls -ls /dev/oracleasm/disks/ASMDISK*
0 brw-rw—- 1 grid asmadmin 8, 17 Apr 12 18:43 /dev/oracleasm/disks/ASMDISK01
0 brw-rw—- 1 grid asmadmin 8, 18 Apr 12 18:43 /dev/oracleasm/disks/ASMDISK02
# cat /proc/partitions
and check device major with 8, and minor 17
Method 3 :
for i in `ls -l /dev/oracleasm/disks | grep oracle | awk '{print $NF","substr($5,1,length($5)-1)","$6}'`
do
ASM_DEVICE_NAME=`echo $i|awk '{print $1}' FS=","`
MAJOR_NUMBER=`echo $i|awk '{print $2}' FS=","`
MINOR_NUMBER=`echo $i|awk '{print $3}' FS=","`
HOST_DEVICE=`ls -l /dev/ | grep " "${MAJOR_NUMBER}", *"${MINOR_NUMBER}" " | awk '{print $NF}'`
echo $HOSTNAME"|"$ASM_DEVICE_NAME"|/dev/"$HOST_DEVICE
done
col path for a40
select group_number,disk_number,header_status,name,substr(path,1,20) "path" ,round(total_mb/1024) TOT_GB,round(free_mb/1024) Free_GB,state,mode_status,mount_status from v$asm_disk;
select name,redundancy,failgroup,path from v$asm_disk where group_number=2;
select instance_name,db_name,status from v$asm_client;
select group_number,file_number,bytes,space,type,redundancy,striped from v$asm_file;
break on group_number skip 1
select group_number,name,redundancy,failgroup,path from v$asm_disk group by group_number,name,redundancy,failgroup,path order by group_number;
select group_number,disk_number
name,redundancy,failgroup,path,header_status,state,mode_status,mount_status
from v$asm_disk group by group_number,disk_number,name,redundancy,failgroup,path,header_status,state,mode_status,mount_status
order by group_number;
select * from (
SELECT
CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path
, db_files.bytes
, db_files.space
, NVL(db_files.type, '<DIRECTORY>') type
--, db_files.creation_date
, db_files.disk_group_name
, LPAD(db_files.system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) db_files
WHERE db_files.type IS NOT NULL
START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR db_files.rindex = db_files.pindex ) x
where x.type = '&ftype'
and x.full_path like '%&dbname%';
With oracleasm , find which disk is mapped to which device
Method 1 :
$ oracleasm listdisks
ASMDISK01
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
$ oracleasm querydisk -d ASMDISK01
Disk "ASMDISK01" Is a valid ASM disk On device[8,17]
$ ls -l /dev/* | grep 8, | grep 17
brw-rw---- 1 root disk 8, 17 Apr 16 15:27
ASMDISK01 is mapped to /dev/sdb1
Method 2 :
ls -ls /dev/oracleasm/disks/ASMDISK*
0 brw-rw—- 1 grid asmadmin 8, 17 Apr 12 18:43 /dev/oracleasm/disks/ASMDISK01
0 brw-rw—- 1 grid asmadmin 8, 18 Apr 12 18:43 /dev/oracleasm/disks/ASMDISK02
# cat /proc/partitions
and check device major with 8, and minor 17
Method 3 :
for i in `ls -l /dev/oracleasm/disks | grep oracle | awk '{print $NF","substr($5,1,length($5)-1)","$6}'`
do
ASM_DEVICE_NAME=`echo $i|awk '{print $1}' FS=","`
MAJOR_NUMBER=`echo $i|awk '{print $2}' FS=","`
MINOR_NUMBER=`echo $i|awk '{print $3}' FS=","`
HOST_DEVICE=`ls -l /dev/ | grep " "${MAJOR_NUMBER}", *"${MINOR_NUMBER}" " | awk '{print $NF}'`
echo $HOSTNAME"|"$ASM_DEVICE_NAME"|/dev/"$HOST_DEVICE
done
