Oracle ASM useful Scripts :
ASM Scripts
http://anuj-singh.blogspot.
cat /etc/sysconfig/oracleasm rpm -qa | grep -i oracleasm ls -l /dev/oracleasm/disks
ls -la /dev/oracleasm/disks
cat /proc/partitions
/usr/sbin/oracleasm listdisks
systemctl status oracleasm
/sbin/blkid | grep oracleasm
cat -n /etc/systemd/system/oracle-ohasd.service
oracleasm-discover
modinfo oracleasm lsmod | grep oracleasm
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN full_alias_path FORMAT a63 HEAD 'File Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label "" of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report
SELECT
CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
, bytes
, space
, NVL(LPAD(type, 18), '<DIRECTORY>') type
, creation_date
, disk_group_name
, LPAD(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 ,v$asm_alias a ,v$asm_diskgroup g
where (f.group_number(+) = a.group_number
and f.file_number(+) = a.file_number )
and g.group_number(+) =f.group_number
)
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
/
System
File Name Bytes Space File Type Creation Date Created?
--------------------------------------------------------------- ------------------ ------------------ ------------------ -------------------- --------
+DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.731105699 1,536 2,097,152 ASMPARAMETERFILE 30-SEP-2010 20:54:58 Y
+DATA/ORCL/DATAFILE/UNKNOWN.266.731227433 104,865,792 214,958,080 DATAFILE 01-OCT-2010 06:43:53 Y
+DATA/ORCL/DATAFILE/SYSTEM.256.731226373 723,525,632 1,452,277,760 DATAFILE 01-OCT-2010 06:25:59 Y
+DATA/ORCL/DATAFILE/SYSAUX.257.731226431 576,724,992 1,158,676,480 DATAFILE 01-OCT-2010 06:27:11 Y
+DATA/ORCL/DATAFILE/UNDOTBS1.258.731226451 99,622,912 204,472,320 DATAFILE 01-OCT-2010 06:27:31 Y
+DATA/ORCL/DATAFILE/USERS.259.731226455 5,251,072 12,582,912 DATAFILE 01-OCT-2010 06:27:34 Y
+DATA/ORCL/CONTROLFILE/Current.261.731227231 9,748,480 50,331,648 CONTROLFILE 01-OCT-2010 06:40:30 Y
+DATA/ORCL/CONTROLFILE/Current.260.731227229 9,748,480 50,331,648 CONTROLFILE 01-OCT-2010 06:40:29 Y
+DATA/ORCL/ONLINELOG/group_2.263.731227315 52,429,312 110,100,480 ONLINELOG 01-OCT-2010 06:41:55 Y
+DATA/ORCL/ONLINELOG/group_3.264.731227335 52,429,312 110,100,480 ONLINELOG 01-OCT-2010 06:42:15 Y
+DATA/ORCL/ONLINELOG/group_1.262.731227291 52,429,312 110,100,480 ONLINELOG 01-OCT-2010 06:41:30 Y
+DATA/ORCL/TEMPFILE/TEMP.265.731227415 30,416,896 62,914,560 TEMPFILE 01-OCT-2010 06:43:35 Y
+DATA/ORCL/PARAMETERFILE/spfile.267.731228339 2,560 2,097,152 PARAMETERFILE 01-OCT-2010 06:58:58 Y
+DATA/ORCL/system01.dbf 723,525,632 1,452,277,760 DATAFILE 01-OCT-2010 06:25:59 N
+DATA/ORCL/users01.dbf 5,251,072 12,582,912 DATAFILE 01-OCT-2010 06:27:34 N
+DATA/ORCL/control01.ctl 9,748,480 50,331,648 CONTROLFILE 01-OCT-2010 06:40:29 N
+DATA/ORCL/control02.ctl 9,748,480 50,331,648 CONTROLFILE 01-OCT-2010 06:40:30 N
+DATA/ORCL/redo01.log 52,429,312 110,100,480 ONLINELOG 01-OCT-2010 06:41:30 N
+DATA/ORCL/redo02.log 52,429,312 110,100,480 ONLINELOG 01-OCT-2010 06:41:55 N
+DATA/ORCL/redo03.log 52,429,312 110,100,480 ONLINELOG 01-OCT-2010 06:42:15 N
+DATA/ORCL/temp01.dbf 30,416,896 62,914,560 TEMPFILE 01-OCT-2010 06:43:35 N
+DATA/ORCL/undotbs01.dbf 99,622,912 204,472,320 DATAFILE 01-OCT-2010 06:27:31 N
+DATA/ORCL/example01.dbf 104,865,792 214,958,080 DATAFILE 01-OCT-2010 06:43:53 N
+DATA/ORCL/sysaux01.dbf 576,724,992 1,158,676,480 DATAFILE 01-OCT-2010 06:27:11 N
+DATA/ORCL/spfileorcl.ora 2,560 2,097,152 PARAMETERFILE 01-OCT-2010 06:58:58 N
------------------ ------------------
3,434,391,040 7,079,985,152
+FRA/ORCL/ONLINELOG/group_2.258.741267209 52,429,312 53,477,376 ONLINELOG 24-JAN-2011 11:33:28 Y
+FRA/ORCL/ONLINELOG/group_1.256.741266959 52,429,312 53,477,376 ONLINELOG 24-JAN-2011 11:29:18 Y
+FRA/ORCL/ONLINELOG/group_3.257.741266861 52,429,312 53,477,376 ONLINELOG 24-JAN-2011 11:27:40 Y
+FRA/ORCL/redo01b.log 52,429,312 53,477,376 ONLINELOG 24-JAN-2011 11:29:18 N
+FRA/ORCL/redo03b.log 52,429,312 53,477,376 ONLINELOG 24-JAN-2011 11:27:40 N
+FRA/ORCL/redo02b.log 52,429,312 53,477,376 ONLINELOG 24-JAN-2011 11:33:28 N
------------------ ------------------
314,575,872 320,864,256
------------------ ------------------
Grand Total: 3,748,966,912 7,400,849,408
31 rows selected.
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a16 HEAD 'Disk Group Name'
COLUMN alias_name FORMAT a30 HEAD 'Alias Name'
COLUMN file_number HEAD 'File|Number'
COLUMN file_incarnation HEAD 'File|Incarnation'
COLUMN alias_index HEAD 'Alias|Index'
COLUMN alias_incarnation HEAD 'Alias|Incarnation'
COLUMN parent_index HEAD 'Parent|Index'
COLUMN reference_index HEAD 'Reference|Index'
COLUMN alias_directory FORMAT a10 HEAD 'Alias|Directory?'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
break on report on disk_group_name skip 1
SELECT
g.name disk_group_name
, a.name alias_name
, a.file_number file_number
, a.file_incarnation file_incarnation
, a.alias_index alias_index
, a.alias_incarnation alias_incarnation
, a.parent_index parent_index
, a.reference_index reference_index
, a.alias_directory alias_directory
, a.system_created system_created
FROM v$asm_alias a ,v$asm_diskgroup g
where a.group_number=g.group_number
ORDER BY g.name , a.file_number
/
File File Alias Alias Parent Reference Alias System
Alias Name Number Incarnation Index Incarnation Index Index Directory? Created?
------------------------------ ---------- ----------- ---------- ----------- ---------- ---------- ---------- --------
REGISTRY.253.731105699 253 731105699 106 1 16777322 33554431 N Y
SYSTEM.256.731226373 256 731226373 212 1 16777428 33554431 N Y
system01.dbf 256 731226373 160 1 16777375 33554431 N N
sysaux01.dbf 257 731226431 161 1 16777375 33554431 N N
SYSAUX.257.731226431 257 731226431 213 1 16777428 33554431 N Y
undotbs01.dbf 258 731226451 162 1 16777375 33554431 N N
UNDOTBS1.258.731226451 258 731226451 214 1 16777428 33554431 N Y
USERS.259.731226455 259 731226455 215 1 16777428 33554431 N Y
users01.dbf 259 731226455 163 1 16777375 33554431 N N
control01.ctl 260 731227229 165 1 16777375 33554431 N N
Current.260.731227229 260 731227229 265 1 16777481 33554431 N Y
control02.ctl 261 731227231 166 1 16777375 33554431 N N
Current.261.731227231 261 731227231 266 1 16777481 33554431 N Y
group_1.262.731227291 262 731227291 318 1 16777534 33554431 N Y
redo01.log 262 731227291 168 1 16777375 33554431 N N
group_2.263.731227315 263 731227315 319 1 16777534 33554431 N Y
redo02.log 263 731227315 169 1 16777375 33554431 N N
redo03.log 264 731227335 170 1 16777375 33554431 N N
group_3.264.731227335 264 731227335 320 1 16777534 33554431 N Y
temp01.dbf 265 731227415 172 1 16777375 33554431 N N
TEMP.265.731227415 265 731227415 371 1 16777587 33554431 N Y
example01.dbf 266 731227433 173 1 16777375 33554431 N N
UNKNOWN.266.731227433 266 731227433 216 1 16777428 33554431 N Y
spfile.267.731228339 267 731228339 424 1 16777640 33554431 N Y
spfileorcl.ora 267 731228339 175 1 16777375 33554431 N N
ONLINELOG 4294967295 4294967295 167 1 16777375 16777534 Y Y
CONTROLFILE 4294967295 4294967295 164 1 16777375 16777481 Y Y
DATAFILE 4294967295 4294967295 159 1 16777375 16777428 Y Y
ORCL 4294967295 4294967295 1 1 16777216 16777375 Y Y
TEMPFILE 4294967295 4294967295 171 1 16777375 16777587 Y Y
PARAMETERFILE 4294967295 4294967295 174 1 16777375 16777640 Y Y
ASM 4294967295 4294967295 0 1 16777216 16777269 Y Y
ASMPARAMETERFILE 4294967295 4294967295 53 1 16777269 16777322 Y Y
group_1.256.741266959 256 741266959 106 3 33554538 50331647 N Y
redo01b.log 256 741266959 54 3 33554485 50331647 N N
group_3.257.741266861 257 741266861 107 1 33554538 50331647 N Y
redo03b.log 257 741266861 55 1 33554485 50331647 N N
group_2.258.741267209 258 741267209 108 1 33554538 50331647 N Y
redo02b.log 258 741267209 56 1 33554485 50331647 N N
ONLINELOG 4294967295 4294967295 53 1 33554485 33554538 Y Y
ORCL 4294967295 4294967295 0 1 33554432 33554485 Y Y
41 rows selected.SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN full_path FORMAT a75 HEAD 'ASM File Name / Volume Name / Device Name' COLUMN system_created FORMAT a8 HEAD 'System|Created?' COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes' COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space' COLUMN type FORMAT a18 HEAD 'File Type' COLUMN redundancy FORMAT a12 HEAD 'Redundancy' COLUMN striped FORMAT a8 HEAD 'Striped' COLUMN creation_date FORMAT a20 HEAD 'Creation Date' COLUMN disk_group_name noprint BREAK ON report ON disk_group_name SKIP 1 COMPUTE sum LABEL "" OF bytes space ON disk_group_name COMPUTE sum LABEL "Grand Total: " OF bytes space ON report SELECT CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path , db_files.bytes , db_files.space , NVL(LPAD(db_files.type, 18), '<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 UNION SELECT '+' || volume_files.disk_group_name || ' [' || volume_files.volume_name || '] ' || volume_files.volume_device full_path , volume_files.bytes , volume_files.space , NVL(LPAD(volume_files.type, 18), '<DIRECTORY>') type , volume_files.creation_date , volume_files.disk_group_name , null FROM ( SELECT g.name disk_group_name , v.volume_name volume_name , v.volume_device volume_device , 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_volume v USING (group_number, file_number) JOIN v$asm_diskgroup g USING (group_number) ) volume_files WHERE volume_files.type IS NOT NULL /asmcmd lsattr -G DATA -l Name Value access_control.enabled FALSE access_control.umask 066 appliance._partnering_type GENERIC ate_conversion_done true au_size 4194304 << cell.smart_scan_capable FALSE cell.sparse_dg allnonsparse compatible.asm 19.0.0.0.0 compatible.rdbms 10.1.0.0.0 content.check FALSE content.type data content_hardcheck.enabled FALSE disk_repair_time 12.0h failgroup_repair_time 24.0h idp.boundary auto idp.type dynamic logical_sector_size 512 phys_meta_replicated true preferred_read.enabled FALSE scrub_async_limit 1 scrub_metadata.enabled TRUE sector_size 512 thin_provisioned FALSE vam_migration_done false set linesize 300 pagesize 300 col compatibility for a20 col DATABASE_COMPATIBILITY for a25 select allocation_unit_size,allocation_unit_size/1024/1024 allocation_unit_size_MB,compatibility,database_compatibility from v$asm_diskgroup ;to search spfile !!!!SET LINESIZE 150 SET PAGESIZE 9999 SET VERIFY off COLUMN full_alias_path FORMAT a63 HEAD 'File Name' COLUMN system_created FORMAT a8 HEAD 'System|Created?' COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes' COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space' COLUMN type FORMAT a18 HEAD 'File Type' COLUMN redundancy FORMAT a12 HEAD 'Redundancy' COLUMN striped FORMAT a8 HEAD 'Striped' COLUMN creation_date FORMAT a20 HEAD 'Creation Date' COLUMN disk_group_name noprint BREAK ON report ON disk_group_name SKIP 1 compute sum label "" of bytes space on disk_group_name compute sum label "Grand Total: " of bytes space on report select * from (SELECT CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path , bytes , space , NVL(LPAD(type, 18), '<DIRECTORY>') type , creation_date , disk_group_name , LPAD(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 ,v$asm_alias a ,v$asm_diskgroup g where (f.group_number(+) = a.group_number and f.file_number(+) = a.file_number ) and g.group_number(+) =f.group_number ) WHERE type IS NOT NULL START WITH (MOD(pindex, POWER(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) where 1=1 -- and full_alias_path like '%spfile%' --and full_alias_path like '%control%' and type like '%PARAMETERFILE%' /
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_path FORMAT a25 HEAD 'Disk Path'
COLUMN reads FORMAT 999,999,999,999,999,999,999 HEAD 'Reads'
COLUMN writes FORMAT 999,999,999,999,999,999,999 HEAD 'Writes'
COLUMN read_errs FORMAT 999,999 HEAD 'Read|Errors'
COLUMN write_errs FORMAT 999,999 HEAD 'Write|Errors'
COLUMN read_time FORMAT 999,999,999 HEAD 'Read|Time'
COLUMN write_time FORMAT 999,999,999 HEAD 'Write|Time'
COLUMN bytes_read FORMAT 999,999,999,999,999,999,999 HEAD 'Bytes|Read'
COLUMN bytes_written FORMAT 999,999,999,999,999,999,999 HEAD 'Bytes|Written'
break on report on disk_group_name skip 2
compute sum label "" of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on disk_group_name
compute sum label "Grand Total: " of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on report
SELECT
a.name disk_group_name
, b.path disk_path
, b.reads reads
, b.writes writes
, b.read_errs read_errs
, b.write_errs write_errs
, b.read_time read_time
, b.write_time write_time
, b.bytes_read bytes_read
, b.bytes_written bytes_written
FROM v$asm_diskgroup a ,v$asm_disk b
where a.group_number=b.group_number
ORDER BY a.name
/
Disk Path Reads Writes Errors Errors Time Time Read Written -------------------- ------------ ------------ -------- -------- ------------ ------------ ---------------- ---------------- ORCL:ASMDISK01 64 1,720 0 0 1 4 1,196,032 7,045,120 ORCL:ASMDISK02 38 1,719 0 0 0 5 155,648 7,041,024 ORCL:ASMDISK04 53 8 0 0 1 0 1,200,128 32,768 ORCL:ASMDISK03 33 1,718 0 0 0 2 139,264 7,036,928 ------------ ------------ -------- -------- ------------ ------------ ---------------- ---------------- 188 5,165 0 0 2 12 2,691,072 21,155,840 ORCL:ASMDISK05 68 1,713 0 0 2 3 1,216,512 7,016,448 ORCL:ASMDISK07 47 4 0 0 1 0 1,175,552 16,384 ORCL:ASMDISK06 28 2 0 0 0 0 114,688 8,192 ORCL:ASMDISK08 35 6 0 0 1 0 143,360 24,576 ------------ ------------ -------- -------- ------------ ------------ ---------------- ---------------- 178 1,725 0 0 4 3 2,650,112 7,065,600 ------------ ------------ -------- -------- ------------ ------------ ---------------- ---------------- 366 6,890 0 0 5 15 5,341,184 28,221,440 8 rows selected.
Disk info With date !!
SET LINESIZE 300 PAGESIZE 9999 VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_path FORMAT a25 HEAD 'Disk Path'
COLUMN reads FORMAT 999,999,999,9999 HEAD 'Reads'
COLUMN writes FORMAT 999,999,999,9999 HEAD 'Writes'
COLUMN read_errs FORMAT 999,999,999,9999 HEAD 'Read|Errors'
COLUMN write_errs FORMAT 999,999 HEAD 'Write|Errors'
COLUMN read_time FORMAT 999,999,999 HEAD 'Read|Time'
COLUMN write_time FORMAT 999,999,999 HEAD 'Write|Time'
COLUMN bytes_read FORMAT 999,999,999,999,999,999 HEAD 'Bytes|Read'
COLUMN bytes_written FORMAT 999,999,999,999,999 HEAD 'Bytes|Written'
col CREATE_DATE for a20
SELECT
a.name disk_group_name
, b.path disk_path
, b.bytes_read bytes_read
, b.bytes_written bytes_written
,b.total_mb
, CREATE_DATE
FROM v$asm_diskgroup a , v$asm_disk b
where a.group_number=b.group_number
ORDER BY CREATE_DATE desc
/
?
SET LINESIZE 300 PAGESIZE 9999 VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_path FORMAT a25 HEAD 'Disk Path'
COLUMN reads FORMAT 999,999,999,9999 HEAD 'Reads'
COLUMN writes FORMAT 999,999,999,9999 HEAD 'Writes'
COLUMN read_errs FORMAT 999,999,999,9999 HEAD 'Read|Errors'
COLUMN write_errs FORMAT 999,999 HEAD 'Write|Errors'
COLUMN read_time FORMAT 999,999,999 HEAD 'Read|Time'
COLUMN write_time FORMAT 999,999,999 HEAD 'Write|Time'
COLUMN bytes_read FORMAT 999,999,999,999,999,999 HEAD 'Bytes|Read'
COLUMN bytes_written FORMAT 999,999,999,999,999 HEAD 'Bytes|Written'
col CREATE_DATE for a20
break on report on disk_group_name skip 2
compute sum label "" of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on disk_group_name
compute sum label "Grand Total: " of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on report
SELECT
a.name disk_group_name
, b.path disk_path
, b.reads reads
, b.writes writes
, b.read_errs read_errs
, b.write_errs write_errs
, b.read_time read_time
, b.write_time write_time
, b.bytes_read bytes_read
, b.bytes_written bytes_written
, CREATE_DATE
FROM v$asm_diskgroup a , v$asm_disk b
where a.group_number=b.group_number
ORDER BY CREATE_DATE desc
/
asm_alias.sql -- PURPOSE : Provide a summary report of all alias definitions contained within all ASM disk groups. SET LINESIZE 145 SET PAGESIZE 9999 SET VERIFY off COLUMN disk_group_name FORMAT a16 HEAD 'Disk Group Name' COLUMN alias_name FORMAT a30 HEAD 'Alias Name' COLUMN file_number HEAD 'File|Number' COLUMN file_incarnation HEAD 'File|Incarnation' COLUMN alias_index HEAD 'Alias|Index' COLUMN alias_incarnation HEAD 'Alias|Incarnation' COLUMN parent_index HEAD 'Parent|Index' COLUMN reference_index HEAD 'Reference|Index' COLUMN alias_directory FORMAT a10 HEAD 'Alias|Directory?' COLUMN system_created FORMAT a8 HEAD 'System|Created?' break on report on disk_group_name skip 1 SELECT g.name disk_group_name , a.name alias_name , a.file_number file_number , a.file_incarnation file_incarnation , a.alias_index alias_index , a.alias_incarnation alias_incarnation , a.parent_index parent_index , a.reference_index reference_index , a.alias_directory alias_directory , a.system_created system_created FROM v$asm_alias a ,v$asm_diskgroup g where a.group_number=g.group_number ORDER BY g.name , a.file_number / File File Alias Alias Parent Reference Alias System Alias Name Number Incarnation Index Incarnation Index Index Directory? Created? ------------------------------ ---------- ----------- ---------- ----------- ---------- ---------- ---------- -------- REGISTRY.253.731105699 253 731105699 106 1 16777322 33554431 N Y SYSTEM.256.731226373 256 731226373 212 1 16777428 33554431 N Y system01.dbf 256 731226373 160 1 16777375 33554431 N N sysaux01.dbf 257 731226431 161 1 16777375 33554431 N N SYSAUX.257.731226431 257 731226431 213 1 16777428 33554431 N Y undotbs01.dbf 258 731226451 162 1 16777375 33554431 N N UNDOTBS1.258.731226451 258 731226451 214 1 16777428 33554431 N Y USERS.259.731226455 259 731226455 215 1 16777428 33554431 N Y users01.dbf 259 731226455 163 1 16777375 33554431 N N control01.ctl 260 731227229 165 1 16777375 33554431 N N Current.260.731227229 260 731227229 265 1 16777481 33554431 N Y control02.ctl 261 731227231 166 1 16777375 33554431 N N Current.261.731227231 261 731227231 266 1 16777481 33554431 N Y group_1.262.731227291 262 731227291 318 1 16777534 33554431 N Y redo01.log 262 731227291 168 1 16777375 33554431 N N group_2.263.731227315 263 731227315 319 1 16777534 33554431 N Y redo02.log 263 731227315 169 1 16777375 33554431 N N redo03.log 264 731227335 170 1 16777375 33554431 N N group_3.264.731227335 264 731227335 320 1 16777534 33554431 N Y temp01.dbf 265 731227415 172 1 16777375 33554431 N N TEMP.265.731227415 265 731227415 371 1 16777587 33554431 N Y example01.dbf 266 731227433 173 1 16777375 33554431 N N UNKNOWN.266.731227433 266 731227433 216 1 16777428 33554431 N Y spfile.267.731228339 267 731228339 424 1 16777640 33554431 N Y spfileorcl.ora 267 731228339 175 1 16777375 33554431 N N ONLINELOG 4294967295 4294967295 167 1 16777375 16777534 Y Y CONTROLFILE 4294967295 4294967295 164 1 16777375 16777481 Y Y DATAFILE 4294967295 4294967295 159 1 16777375 16777428 Y Y ORCL 4294967295 4294967295 1 1 16777216 16777375 Y Y TEMPFILE 4294967295 4294967295 171 1 16777375 16777587 Y Y PARAMETERFILE 4294967295 4294967295 174 1 16777375 16777640 Y Y ASM 4294967295 4294967295 0 1 16777216 16777269 Y Y ASMPARAMETERFILE 4294967295 4294967295 53 1 16777269 16777322 Y Y group_1.256.741266959 256 741266959 106 3 33554538 50331647 N Y redo01b.log 256 741266959 54 3 33554485 50331647 N N group_3.257.741266861 257 741266861 107 1 33554538 50331647 N Y redo03b.log 257 741266861 55 1 33554485 50331647 N N group_2.258.741267209 258 741267209 108 1 33554538 50331647 N Y redo02b.log 258 741267209 56 1 33554485 50331647 N N ONLINELOG 4294967295 4294967295 53 1 33554485 33554538 Y Y ORCL 4294967295 4294967295 0 1 33554432 33554485 Y Y 41 rows selected. asm_disks_perf.sql -- PURPOSE : Provide a summary report of all disks contained within all ASM disk groups along with their performance metrics. SET LINESIZE 145 SET PAGESIZE 9999 SET VERIFY off COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name' COLUMN disk_path FORMAT a20 HEAD 'Disk Path' COLUMN reads FORMAT 999,999,999 HEAD 'Reads' COLUMN writes FORMAT 999,999,999 HEAD 'Writes' COLUMN read_errs FORMAT 999,999 HEAD 'Read|Errors' COLUMN write_errs FORMAT 999,999 HEAD 'Write|Errors' COLUMN read_time FORMAT 999,999,999 HEAD 'Read|Time' COLUMN write_time FORMAT 999,999,999 HEAD 'Write|Time' COLUMN bytes_read FORMAT 999,999,999,999 HEAD 'Bytes|Read' COLUMN bytes_written FORMAT 999,999,999,999 HEAD 'Bytes|Written' break on report on disk_group_name skip 2 compute sum label "" of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on disk_group_name compute sum label "Grand Total: " of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on report SELECT a.name disk_group_name , b.path disk_path , b.reads reads , b.writes writes , b.read_errs read_errs , b.write_errs write_errs , b.read_time read_time , b.write_time write_time , b.bytes_read bytes_read , b.bytes_written bytes_written FROM v$asm_diskgroup a ,v$asm_disk b where a.group_number=b.group_number ORDER BY a.name / Read Write Read Write Bytes Bytes Disk Path Reads Writes Errors Errors Time Time Read Written -------------------- ------------ ------------ -------- -------- ------------ ------------ ---------------- ---------------- ORCL:ASMDISK01 58 1,439 0 0 1 3 1,171,456 5,894,144 ORCL:ASMDISK02 32 1,438 0 0 0 5 131,072 5,890,048 ORCL:ASMDISK04 47 8 0 0 1 0 1,175,552 32,768 ORCL:ASMDISK03 27 1,437 0 0 0 2 114,688 5,885,952 ------------ ------------ -------- -------- ------------ ------------ ---------------- ---------------- 164 4,322 0 0 2 10 2,592,768 17,702,912 ORCL:ASMDISK05 62 1,432 0 0 2 3 1,191,936 5,865,472 ORCL:ASMDISK07 41 4 0 0 1 0 1,150,976 16,384 ORCL:ASMDISK06 22 2 0 0 0 0 90,112 8,192 ORCL:ASMDISK08 29 6 0 0 1 0 118,784 24,576 ------------ ------------ -------- -------- ------------ ------------ ---------------- ---------------- 154 1,444 0 0 4 3 2,551,808 5,914,624 ------------ ------------ -------- -------- ------------ ------------ ---------------- ---------------- 318 5,766 0 0 5 13 5,144,576 23,617,536 8 rows selected. asm_disks.sql -- PURPOSE : Provide a summary report of all disks contained within all disk -- groups. This script is also responsible for queriing all -- candidate disks - those that are not assigned to any disk -- group. SET LINESIZE 145 SET PAGESIZE 9999 SET VERIFY off COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name' COLUMN disk_file_path FORMAT a17 HEAD 'Path' COLUMN disk_file_name FORMAT a20 HEAD 'File Name' COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group' COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)' COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)' COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used' break on report on disk_group_name skip 1 compute sum label "" of total_mb used_mb on disk_group_name compute sum label "Grand Total: " of total_mb used_mb on report SELECT NVL(a.name, '[CANDIDATE]') disk_group_name , b.path disk_file_path , b.name disk_file_name , b.failgroup disk_file_fail_group , b.total_mb total_mb , (b.total_mb - b.free_mb) used_mb , ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used FROM v$asm_diskgroup a , v$asm_disk b where a.group_number=b.group_number(+) ORDER BY a.name / Path File Name Fail Group File Size (MB) Used Size (MB) Pct. Used ----------------- -------------------- -------------------- -------------- -------------- --------- ORCL:ASMDISK01 ASMDISK01 ASMDISK01 2,304 879 38.15 ORCL:ASMDISK02 ASMDISK02 ASMDISK02 2,304 896 38.89 ORCL:ASMDISK04 ASMDISK04 ASMDISK04 2,304 895 38.85 ORCL:ASMDISK03 ASMDISK03 ASMDISK03 2,304 889 38.59 -------------- -------------- 9,216 3,559 ORCL:ASMDISK05 ASMDISK05 ASMDISK05 2,304 54 2.34 ORCL:ASMDISK07 ASMDISK07 ASMDISK07 2,304 55 2.39 ORCL:ASMDISK06 ASMDISK06 ASMDISK06 2,304 53 2.30 ORCL:ASMDISK08 ASMDISK08 ASMDISK08 2,304 56 2.43 -------------- -------------- 9,216 218 -------------- -------------- 18,432 3,777 8 rows selected.
---to check asmlib or afd? setlinesize 300pages 300col PATHfora20col LIBRARYfora70colNAMEfora15selectinst_id,group_number grp_num,name,state,header_status header,mount_status mount,path, libraryfromgv$asm_diskorderbyinst_id,group_number,name;
INST_ID GRP_NUM NAME STATE HEADER MOUNT PATH LIBRARY
---------- ---------- --------------- -------- ------------ ------- -------------------- ----------------------------------------------------------------------
1 1 DATA0 NORMAL MEMBER CACHED ORCL:DATA0 ASM Library - Generic Linux, version 2.0.4 (KABI_V2)
1 1 DATA1 NORMAL MEMBER CACHED ORCL:DATA1 ASM Library - Generic Linux, version 2.0.4 (KABI_V2)
1 1 DATA10 NORMAL MEMBER CACHED ORCL:DATA10 ASM Library - Generic Linux, version 2.0.4 (KABI_V2)
1 1 DATA11 NORMAL MEMBER CACHED ORCL:DATA11 ASM Library - Generic Linux, version 2.0.4 (KABI_V2)
1 1 DATA12 NORMAL MEMBER CACHED ORCL:DATA12 ASM Library - Generic Linux, version 2.0.4 (KABI_V2)
=== SET LINESIZE 300 SET PAGESIZE 9999 SET VERIFY off COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name' COLUMN disk_path FORMAT a20 HEAD 'Disk Path' COLUMN reads FORMAT 999,999,999,999 HEAD 'Reads' COLUMN writes FORMAT 999,999,999 HEAD 'Writes' COLUMN read_errs FORMAT 999,999 HEAD 'Read|Errors' COLUMN write_errs FORMAT 999,999 HEAD 'Write|Errors' COLUMN read_time FORMAT 999,999,999 HEAD 'Read|Time' COLUMN write_time FORMAT 999,999,999 HEAD 'Write|Time' COLUMN bytes_read FORMAT 999,999,999,999 HEAD 'Bytes|Read' COLUMN bytes_written FORMAT 999,999,999,999 HEAD 'Bytes|Written' break on report on disk_group_name skip 2 compute sum label "" of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on disk_group_name compute sum label "Grand Total: " of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on report SELECT a.name disk_group_name ,DISK_NUMBER ,b.name , b.path disk_path ,LIBRARY /* , b.reads reads , b.writes writes , b.read_errs read_errs , b.write_errs write_errs , b.read_time read_time , b.write_time write_time , b.bytes_read bytes_read , b.bytes_written bytes_written */ FROM v$asm_diskgroup a ,v$asm_disk b where a.group_number=b.group_number and a.name='DATA' ORDER BY a.name /
Disk Group Name DISK_NUMBER NAME Disk Path LIBRARY
--------------- ----------- ------------------------------ -------------------- ----------------------------------------------------------------
DATA 0 FLSH_DATA0 ORCL:FLSH_DATA0 ASM Library - Generic Linux, version 2.0.12 (KABI_V2)
1 FLSH_DATA1 ORCL:FLSH_DATA1 ASM Library - Generic Linux, version 2.0.12 (KABI_V2)
2 FLSH_DATA2 ORCL:FLSH_DATA2 ASM Library - Generic Linux, version 2.0.12 (KABI_V2)
3 FLSH_DATA3 ORCL:FLSH_DATA3 ASM Library - Generic Linux, version 2.0.12 (KABI_V2)
asm_files2.sql
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a25 HEAD 'Disk Group Name'
COLUMN file_name FORMAT a50 HEAD 'File Name'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF bytes space ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF bytes space ON report
SELECT
g.name disk_group_name
, a.name file_name
, 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 JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
WHERE
system_created = 'Y'
ORDER BY
g.name
, file_number
/
Disk Group Name File Name Bytes Space File Type Creation Date
------------------------- -------------------------------------------------- ------------------ ------------------ ------------------ --------------------
DATA REGISTRY.253.731105699 1,536 2,097,152 ASMPARAMETERFILE 30-SEP-2010 20:54:58
SYSTEM.256.731226373 723,525,632 1,452,277,760 DATAFILE 01-OCT-2010 06:25:59
SYSAUX.257.731226431 576,724,992 1,158,676,480 DATAFILE 01-OCT-2010 06:27:11
UNDOTBS1.258.731226451 99,622,912 204,472,320 DATAFILE 01-OCT-2010 06:27:31
USERS.259.731226455 5,251,072 12,582,912 DATAFILE 01-OCT-2010 06:27:34
Current.260.731227229 9,748,480 50,331,648 CONTROLFILE 01-OCT-2010 06:40:29
Current.261.731227231 9,748,480 50,331,648 CONTROLFILE 01-OCT-2010 06:40:30
group_1.262.731227291 52,429,312 110,100,480 ONLINELOG 01-OCT-2010 06:41:30
group_2.263.731227315 52,429,312 110,100,480 ONLINELOG 01-OCT-2010 06:41:55
group_3.264.731227335 52,429,312 110,100,480 ONLINELOG 01-OCT-2010 06:42:15
TEMP.265.731227415 30,416,896 62,914,560 TEMPFILE 01-OCT-2010 06:43:35
UNKNOWN.266.731227433 104,865,792 214,958,080 DATAFILE 01-OCT-2010 06:43:53
spfile.267.731228339 2,560 2,097,152 PARAMETERFILE 01-OCT-2010 06:58:58
************************* ------------------ ------------------
1,717,196,288 3,541,041,152
FRA group_1.256.741266959 52,429,312 53,477,376 ONLINELOG 24-JAN-2011 11:29:18
group_3.257.741266861 52,429,312 53,477,376 ONLINELOG 24-JAN-2011 11:27:40
group_2.258.741267209 52,429,312 53,477,376 ONLINELOG 24-JAN-2011 11:33:28
************************* ------------------ ------------------
157,287,936 160,432,128
------------------ ------------------
Grand Total: 1,874,484,224 3,701,473,280
asm_templates.sql
-- | PURPOSE : Provide a summary report of all template information for all ASM disk groups.
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a16 HEAD 'Disk Group Name'
COLUMN entry_number FORMAT 999 HEAD 'Entry Number'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN stripe FORMAT a8 HEAD 'Stripe'
COLUMN system FORMAT a6 HEAD 'System'
COLUMN template_name FORMAT a30 HEAD 'Template Name'
break on report on disk_group_name skip 1
SELECT
b.name disk_group_name
, a.entry_number entry_number
, a.redundancy redundancy
, a.stripe stripe
, a.system system
, a.name template_name
FROM
v$asm_template a , v$asm_diskgroup b
where a.group_number=b.group_number
ORDER BY
b.name
, a.entry_number
/
Entry Number Redundancy Stripe System Template Name
------------ ------------ -------- ------ ------------------------------
60 MIRROR COARSE Y PARAMETERFILE
61 MIRROR COARSE Y ASMPARAMETERFILE
62 MIRROR COARSE Y ASMPARAMETERBAKFILE
63 MIRROR COARSE Y DUMPSET
64 HIGH FINE Y CONTROLFILE
65 MIRROR COARSE Y FLASHFILE
66 MIRROR COARSE Y ARCHIVELOG
67 MIRROR COARSE Y ONLINELOG
68 MIRROR COARSE Y DATAFILE
69 MIRROR COARSE Y TEMPFILE
170 MIRROR COARSE Y BACKUPSET
171 MIRROR COARSE Y AUTOBACKUP
172 MIRROR COARSE Y XTRANSPORT
173 MIRROR COARSE Y CHANGETRACKING
174 MIRROR COARSE Y FLASHBACK
175 MIRROR COARSE Y DATAGUARDCONFIG
176 MIRROR COARSE Y OCRFILE
177 MIRROR COARSE Y OCRBACKUP
178 HIGH COARSE Y ASM_STALE
60 UNPROT COARSE Y PARAMETERFILE
61 UNPROT COARSE Y ASMPARAMETERFILE
62 UNPROT COARSE Y ASMPARAMETERBAKFILE
63 UNPROT COARSE Y DUMPSET
64 UNPROT FINE Y CONTROLFILE
65 UNPROT COARSE Y FLASHFILE
66 UNPROT COARSE Y ARCHIVELOG
67 UNPROT COARSE Y ONLINELOG
68 UNPROT COARSE Y DATAFILE
69 UNPROT COARSE Y TEMPFILE
170 UNPROT COARSE Y BACKUPSET
171 UNPROT COARSE Y AUTOBACKUP
172 UNPROT COARSE Y XTRANSPORT
173 UNPROT COARSE Y CHANGETRACKING
174 UNPROT COARSE Y FLASHBACK
175 UNPROT COARSE Y DATAGUARDCONFIG
176 UNPROT COARSE Y OCRFILE
177 UNPROT COARSE Y OCRBACKUP
178 UNPROT COARSE Y ASM_STALE
38 rows selected.
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance FROM dual;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : ASM Disk Groups |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN group_name FORMAT a25 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM v$asm_diskgroup
WHERE total_mb != 0
ORDER BY name
/
Disk Group Sector Block Allocation
Name Size Size Unit Size State Type Total Size (MB) Used Size (MB) Pct. Used
------------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
DATA 512 4,096 1,048,576 MOUNTED NORMAL 9,216 3,559 38.62
FRA 512 4,096 1,048,576 MOUNTED EXTERN 9,216 218 2.37
--------------- --------------
Grand Total: 18,432 3,777
set linesize 300 pagesize 500
col NAME for a15
col CREATE_DATE for a15
col failgroup for a14
select g.STATE
, g.type
, g.name
, g.BLOCK_SIZE
, g.ALLOCATION_UNIT_SIZE
, g.REQUIRED_MIRROR_FREE_MB
, sum(d.total_mb)
, sum(d.free_mb)
,d.failgroup
,max(d.VOTING_FILE)
, d.FAILGROUP_TYPE
, g.offline_disks
, min(decode(d.REPAIR_TIMER, 0, 8640000, d.REPAIR_TIMER)) REPAIR_TIMER
, count(*)
,d.CREATE_DATE
FROM v$asm_diskgroup g
LEFT OUTER JOIN v$asm_disk d on d.group_number = g.group_number
and d.group_number = g.group_number
and d.group_number <> 0
GROUP BY g.name
, g.state
, g.type
, d.failgroup
, d.VOTING_FILE
, g.BLOCK_SIZE
, g.ALLOCATION_UNIT_SIZE
, g.REQUIRED_MIRROR_FREE_MB
, g.offline_disks
, d.FAILGROUP_TYPE
, d.REPAIR_TIMER
,d.CREATE_DATE
-- ORDER BY g.name, d.failgroup
ORDER BY CREATE_DATE desc
;
for DiskGroup Name ..
set pages 0 feedback off
select listagg (name, ' ') within group (order by name) from v$asm_diskgroup;
set echo off feedback off heading off linesize 256 pagesize 20000 termout on timing off trimout on trimspool on verify off
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN full_alias_path FORMAT a255 HEAD 'File Name'
COLUMN disk_group_name NOPRINT
SELECT
'ALTER DISKGROUP ' ||
disk_group_name ||
' DROP FILE ''' || CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) || ''';' full_alias_path
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, f.type type
,f.creation_date 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)
-- where f.creation_date < SYSDATE - 1
)
WHERE type IS NOT NULL
and creation_date < SYSDATE - 1 ------- <<<<<<<<<<<<<<<<<<<<<<<------
AND TYPE = 'ARCHIVELOG'
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
/
ASM tools used by Support : KFOD, KFED, AMDU (Doc ID 1485597.1)
AIX /dev/rhdisk*
HP-UX /dev/rdsk/*
Linux /dev/raw/*
Mac OS X /dev/rdisk*s*s1
Solaris /dev/rdsk/*
Tru64UNIX /dev/rdisk/*
set pages 10 feedback off
select listagg ('+'||name, ' ') within group (order by name) "DiskGroupNamefrom" from v$asm_diskgroup;SQL>
DiskGroupNamefrom
--------------------------------------------------------------------------------
+DATA +LOGS
show parameter asm
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups string DATA, LOGS, REDOA, REDOB, DUMPS
asm_diskstring string ORCL:*
Version 19.3.0.0.0
[grid@ibrac01 ~]$ kfod status=TRUE asm_diskstring='ORCL:*' disks=all dscvgroup=TRUE
--------------------------------------------------------------------------------
Disk Size Header Path Disk Group User Group
================================================================================
1: 102401 MB MEMBER ORCL:FLSH_DATA0 DATA
2: 102401 MB MEMBER ORCL:FLSH_DATA1 DATA
3: 102401 MB MEMBER ORCL:FLSH_DATA2 DATA
4: 102401 MB MEMBER ORCL:FLSH_DATA3 DATA
5: 512000 MB MEMBER ORCL:FLSH_DUMPS0 DUMPS
6: 40961 MB MEMBER ORCL:FLSH_GRID0 GRID
7: 102401 MB MEMBER ORCL:FLSH_LOGS0 LOGS
8: 102401 MB MEMBER ORCL:FLSH_MGMT0 MGMT
9: 102401 MB MEMBER ORCL:FLSH_RECO0 RECO
--------------------------------------------------------------------------------
set linesize 200 trimspool on
col inst_id head 'I#' format 99
col path format a80
col diskgroup_name format a10
col disk_name format a15
col disk_number head 'D#' format 999
select
io.inst_id
, dg.name diskgroup_name
, io.disk_number
, d.name disk_name
--, d.label
, d.read_errs
, d.write_errs
, d.path
from gv$ASM_DISK_IOSTAT io
join gv$asm_diskgroup dg on dg.inst_id = io.inst_id
and dg.group_number = io.group_number
join gv$asm_disk d on d.inst_id = io.inst_id
and d.group_number = io.group_number
where d.read_errs + d.write_errs > 0 -- use the where clause if too many rows
/
#!/bin/ksh
for asmlibdisk in `ls /dev/oracleasm/disks/*`
do
print "ASMLIB disk name: $asmlibdisk : \c"
asmdisk=`kfed read $asmlibdisk | grep dskname | tr -s ' '| cut -f2 -d' '`
print "ASM disk name: $asmdisk : \c"
majorminor=`ls -l $asmlibdisk | tr -s ' ' | cut -f5,6 -d' '`
device=`ls -l /dev | tr -s ' ' | grep "$majorminor" | cut -f10 -d' '`
print "Device path: /dev/$device"
done
to check disk path
./asm.sh
ASMLIB disk name: /dev/oracleasm/disks/FLSH_DATA0
ASM disk name: FLSH_DATA0
Device path: /dev/dm-11
ASMLIB disk name: /dev/oracleasm/disks/FLSH_DATA1
ASM disk name: FLSH_DATA1
Device path: /dev/dm-9
ASMLIB disk name: /dev/oracleasm/disks/FLSH_DATA2
ASM disk name: FLSH_DATA2
Device path: /dev/dm-14
ASMLIB disk name: /dev/oracleasm/disks/FLSH_DATA3
ASM disk name: FLSH_DATA3
Device path: /dev/dm-18
ASMLIB disk name: /dev/oracleasm/disks/FLSH_DUMPS0
ASM disk name: FLSH_DUMPS0
Device path: /dev/dm-20
ASMLIB disk name: /dev/oracleasm/disks/FLSH_GRID0
ASM disk name: FLSH_GRID0
Device path: /dev/dm-7
ASMLIB disk name: /dev/oracleasm/disks/FLSH_LOGS0
ASM disk name: FLSH_LOGS0
Device path: /dev/dm-13
ASMLIB disk name: /dev/oracleasm/disks/FLSH_MGMT0
ASM disk name: FLSH_MGMT0
Device path: /dev/dm-17
ASMLIB disk name: /dev/oracleasm/disks/FLSH_RECO0
ASM disk name: FLSH_RECO0
Device path: /dev/dm-16
kfed read /dev/oracleasm/disks/FLSH_DATA0
kfed read /dev/oracleasm/disks/FLSH_DATA0
kfbh.endian: 1 ; 0x000: 0x01
kfbh.hard: 130 ; 0x001: 0x82
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt: 2 ; 0x003: 0x02
kfbh.block.blk: 0 ; 0x004: blk=0
amdu -diskstring 'ORCL:FLSH_DATA0' -dump 'DATA'
amdu -diskstring '/dev/oracleasm/disks/FLSH_DATA0' -dump 'DATA'
amdu_2022_10_27_04_46_28/
AMDU-00204: Disk N0001 is in currently mounted diskgroup DATA.
AMDU-00201: Disk N0001: '/dev/oracleasm/disks/FLSH_DATA0'
amdu -diskstring 'ORCL:FLSH_DATA0' -dump 'DATA'
amdu_2022_10_27_04_45_03/
AMDU-00204: Disk N0001 is in currently mounted diskgroup DATA.
AMDU-00201: Disk N0001: 'ORCL:FLSH_DATA0'
====
SPOOL ASM_FIRST.HTML
SET MARKUP HTML ON
set echo onset pagesize 200
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select 'THIS ASM REPORT WAS GENERATED AT: ==)>; ' , sysdate " " from dual;
select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
select * from v$asm_diskgroup;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT * FROM V$ASM_CLIENT;
select * from V$ASM_ATTRIBUTE;
select * from v$asm_operation;
select * from gv$asm_operation;
select * from v$version;
show parameter asm
show parameter cluster
show parameter instance_type
show parameter instance_name
show parameter spfile
show sga
spool off
exit
cat asmdir.sh
#!/bin/bash
#
# du of each subdirectory in a directory for ASM
#
D=$1
if [[ -z $D ]]
then
echo "Please provide a directory !"
exit 1
fi
(for DIR in `asmcmd ls ${D}`
do
echo ${DIR} `asmcmd du ${D}/${DIR} | tail -1`
done) | awk -v D="$D" ' BEGIN { printf("\n\t\t%40s\n\n", D " subdirectories size") ;
printf("%25s%16s%16s\n", "Subdir", "Used MB", "Mirror MB") ;
printf("%25s%16s%16s\n", "------", "-------", "---------") ;}
{
printf("%25s%16s%16s\n", $1, $2, $3) ;
use += $2 ;
mir += $3 ;
}
END { printf("\n\n%25s%16s%16s\n", "------", "-------", "---------");
printf("%25s%16s%16s\n\n", "Total", use, mir) ;} '
as grid !!!
set env
./asmdir.sh DATA
DATA subdirectories size Subdir Used MB Mirror MB ------ ------- --------- ASM/ 102 102 DB_UNKNOWN/ 3 3 ORADB/ 1521731 1521731
rpm -qa | grep oracleasm
i. Output if installed:
oracleasm-2.6.18-164.el5PAE-2.0.5-1.el5 -----> optional
oracleasm-2.6.18-164.el5debug-2.0.5-1.el5 -----> optional
oracleasm-2.6.18-164.el5-2.0.5-1.el5
oracleasmlib-2.0.4-1.el5
oracleasm-support-2.1.3-1.el5
oracleasm-2.6.18-164.el5xen-2.0.5-1.el5 -----> optionalCheck status again:
# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
/sbin/blkid | grep oracleasm /usr/sbin/oracleasm-discoverfor disk info [root@]# /sbin/blkid | grep oracleasm /dev/emcpowerp1: LABEL="DATA01" TYPE="oracleasm" PARTLABEL="emcpowerp1" PARTUUID="29e9688a-d4be-4c51-9be1-2d4c881850a4"oracleasm listdisks oracleasm status oracleasm listdisks | xargs oracleasm querydisk -p oracleasm querydisk -d -v -p DATA_01 ls -l /dev/oracleasm/disks
asmcmd lsof -G RECO01 | grep -i thread_1_seq_504430.3843.1167894287asmcmd lsod -G RECO01which kfod kfod disks=all ps -ef |grep d.bin cd /u01/app/19.3.0/grid/bin/ ./kfod disks=all oracleasm listdisks oracleasm querydisk -p DATA01 ls -ltr /dev/oracleasm/disks oracleasm querydisk -p ACFS oracleasm querydisk -p DATA02 $GRID_HOME/bin/kfod op=DISKS label=TRUE disks=ALL name=TRUE kfod asm_diskstring=ORCL:* disks=all kfod asm_diskstring=/dev/oracleasm/disks* disks=all $GRID_HOME/bin/kfed /dev/asm/data1-36
/usr/sbin/oracleasm listdisks | xargs oracleasm querydisk -p |grep /dev/emcpowerc
-bash-4.2$ sudo -u grid /u01/app/19.0.0/grid/bin/asmcmd --nocp lsdg --suppressheader data MOUNTED EXTERN N 512 512 4096 1048576 409604 269517 0 269517 0 N DATA/
cat asmdu.sh
#!/bin/bash
# Fred Denis -- Jun 2016 -- http://unknowndba.blogspot.com -- fred.denis3@gmail.com
#
# This scripts shows a clear and colored status of the ASM used and free space
# Please have a look at the usage function or $0 -h for the available options and their description
# More information and screenshots : https://unknowndba.blogspot.ca/2018/03/asmdush-far-better-du-for-asmcmd.html
#
# A note on the --nocp option
# Note that the --nocp asmcmd option (it disables the connection pooling) has been originaly implemented
# as a workaround of a bug that appeared with the April 2016 PSU
# It resolves error messages like this one :
# sh: -c: line 0: unexpected EOF while looking for matching `''
# sh: -c: line 1: syntax error: unexpected end of file
#
#
# The current version of the script is 20190906
#
# 20190906 - Fred Denis - A new -V option to show the version of the script
# 20190219 - Fred Denis - Some had issues with the instance list, I then moved from sed to cut to fix it -- Thanks Jakub !
# 20181218 - Fred Denis - A new -n option to print with no color -- DEFAULT_NOCOLOR can be used to modify the default behavior
# Fixed the regexp to list the instances running
# 20180827 - Fred Denis - A better regexp to list the instances running
# 20180503 - Fred Denis - GI 12c introduces a "Logical_Sector" column, took this into account (Thanks Leon !)
# 20180327 - Fred Denis - "Raw Used " label for the subdirectories "Mirror_used_MB" column, adjustments in the help
# 20180318 - Fred Denis - Shows only mirrored sizes by default and the total non mirrored size only shown with the -v option
# 20180211 - Fred Denis - Many improvements :
# - -d options to list the subdirectories of a directory
# - -v option to show the Raw Free and Reserverd size
# - -m -g and -t to choose the Unit you want the report to be in
# - Default values and verbosity can be changed using the DEFAULT_UNIT and the DEFAULT_VERBOSE variables
# - A nice usage function
# 20170719 - Fred Denis - Remove the --nocp option as default
#
#
# Default values (when no option is specified in the command line)
# The last uncommented value wins
#
DEFAULT_UNIT="MB" # asmcmd default
DEFAULT_UNIT="GB"
DEFAULT_UNIT="TB"
DEFAULT_VERBOSE="Yes"
DEFAULT_VERBOSE="No"
DEFAULT_NOCOLOR="Yes" # Print with no color
DEFAULT_NOCOLOR="No" # Print with colors
#
# Colored thresholds (Red, Yellow, Green)
#
CRITICAL=90
WARNING=75
#
# A color for a nice header
#
WHITE="\033[1;37m"
END_COLOR="\033[m"
#
# Show the version of the script (-V)
#
show_version()
{
VERSION=`awk '{if ($0 ~ /^# 20[0-9][0-9][0-1][0-9]/) {print $2; exit}}' $0`
printf "\n\t\033[1;36m%s\033[m\n" "The current version of "`basename $0`" is "$VERSION"." ;
}
#
# An usage function
#
usage()
{
printf "\n\033[1;37m%-8s\033[m\n" "NAME" ;
cat << END
asmdu.sh - Shows a nice summary of the ASM diskgroups sizes
END
printf "\n\033[1;37m%-8s\033[m\n" "SYNOPSIS" ;
cat << END
$0 [-d] [-m -g -t] [-n] [-v] [-V] [-h]
END
printf "\n\033[1;37m%-8s\033[m\n" "DESCRIPTION" ;
cat << END
$0 needs to be executed as the GI owner user to be able to use asmcmd
With no option $0 will be showing what instances are running and a size summary for each DiskGroup
END
printf "\n\033[1;37m%-8s\033[m\n" "OPTIONS" ;
cat << END
-d The directory you want the size details
-v Verbose -- show the "Total Raw", "Raw Free" and "Reserved" size
You can change the default behavior with the DEFAULT_VERBOSE variable
-m Shows the output in MB
-g Shows the output in GB
-t Shows the output in TB
-m -g -t The default Unit can be specified using the DEFAULT_UNIT variable
If more than one of these options is specified, the last one wins
-n Shows the output with no color (handy to send it by email)
-V Shows the version of the script
-h Shows this help
END
exit 123
}
#
# Parameters management
#
PARAM_UNIT=""
PARAM_VERBOSE=""
while getopts "d:mgtnvhV" OPT; do
case ${OPT} in
d) D=${OPTARG} ;;
m) PARAM_UNIT="MB" ;;
g) PARAM_UNIT="GB" ;;
t) PARAM_UNIT="TB" ;;
n) PARAM_NOCOLOR="Yes" ;;
v) PARAM_VERBOSE="Yes" ;;
V) show_version; exit 567 ;;
h) usage ;;
\?) echo "Invalid option: -$OPTARG" >&2; usage ;;
esac
done
if [[ -z ${PARAM_UNIT} ]]
then # No parameter specified, we use the default
UNIT=${DEFAULT_UNIT}
else
UNIT=${PARAM_UNIT}
fi
if [[ -z ${PARAM_VERBOSE} ]]
then # No parameter specified, we use the default
VERBOSE=${DEFAULT_VERBOSE}
else
VERBOSE=${PARAM_VERBOSE}
fi
if [[ -z ${PARAM_NOCOLOR} ]]
then # No parameter specified, we use the default
NOCOLOR=${DEFAULT_NOCOLOR}
else
NOCOLOR=${PARAM_NOCOLOR}
fi
if [[ ${NOCOLOR} == "Yes" ]]
then
WHITE=""
END_COLOR=""
fi
#
# Set the ASM env
#
OLD_SID=${ORACLE_SID}
ORACLE_SID=`ps -ef | grep pmon | grep asm | awk '{print $NF}' | sed s'/asm_pmon_//' | egrep "^[+]"`
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1
#
# A quick list of the instances that are running on the server
#
ps -ef | grep pmon | grep -v grep | awk '{print $NF}' | cut -d_ -f3,4 | sort | awk -v H="`hostname -s`" 'BEGIN {printf("\n%s", "Instances running on " H " : ")} { printf("%s, ", $0)} END{printf("\n")}' | sed s'/, $//'
#
# Manage parameters
#
if [[ -z $D ]]
then # No directory provided, will check all the DG
DG=`asmcmd lsdg | grep -v State | awk '{print $NF}' | sed s'/\///'`
SUBDIR="No" # Do not show the subdirectories details if no directory is specified
else
DG=`echo $D | sed s'/\/.*$//g'`
fi
#
# A header
#
printf "\n%25s%16s${WHITE}%16s${END_COLOR}" "DiskGroup" "Redundancy" "Total ${UNIT}" # "Raw Free ${UNIT}" "Reserved ${UNIT}" "Usable ${UNIT}" "% Free"
if [[ ${VERBOSE} == "Yes" ]]
then
printf "%16s%16s%16s" "Raw Total ${UNIT}" "Raw Free ${UNIT}" "Reserved ${UNIT}"
fi
printf "${WHITE}%16s%14s${END_COLOR}\n" "Usable ${UNIT}" "% Free"
printf "%25s%16s${WHITE}%16s${END_COLOR}" "---------" "-----------" "--------"
if [[ ${VERBOSE} == "Yes" ]]
then
printf "%16s%16s%16s" "------------" "-----------" "-----------"
fi
printf "${WHITE}%16s${END_COLOR}%14s\n" "---------" "------"
#
# Show DG info
#
for X in ${DG}
do
asmcmd lsdg ${X} | tail -1 |\
awk -v DG="$X" -v W="$WARNING" -v C="$CRITICAL" -v UNIT="$UNIT" -v VERBOSE="$VERBOSE" -v NOCOLOR="$NOCOLOR" '\
BEGIN \
{
if (NOCOLOR == "Yes")
{
COLOR_BEGIN = "" ;
COLOR_END = "" ;
RED = "" ;
GREEN = "" ;
YELLOW = "" ;
WHITE = "" ;
COLOR = "" ;
} else {
COLOR_BEGIN = "\033[1;" ;
COLOR_END = "\033[m" ;
RED = COLOR_BEGIN"31m" ;
GREEN = COLOR_BEGIN"32m" ;
YELLOW = COLOR_BEGIN"33m" ;
WHITE = COLOR_BEGIN"37m" ;
COLOR = GREEN ;
}
DIVIDER = 1 ; # Unit divider
RED_DIV = 1 ; # Redundancy divider
if (UNIT == "GB") { DIVIDER="1024"} ;
if (UNIT == "TB") { DIVIDER="1048576"} ; # 1024 * 1024
}
{ if ($2 == "HIGH") {RED_DIV=3 ;} # Redundancy divider
if ($2 == "NORMAL") {RED_DIV=2 ;} # Redundancy divider
TOTAL = sprintf("%16.2f", $(NF-6)/DIVIDER/RED_DIV) ; # Total mirrored in Unit
USABLE = sprintf("%16.2f", $(NF-3)/DIVIDER) ; # Usable space in Unit
FREE = sprintf("%12d" , USABLE/TOTAL*100) ; # % Free calculated using the Usable size
if ((100-FREE) > W) { COLOR=YELLOW ;} # Colored %Free thresholds
if ((100-FREE) > C) { COLOR=RED ;} # Colored %Free thresholds
printf("%25s%16s%16s", DG, $2, WHITE TOTAL COLOR_END) ; # DG Redundancy and Total
if (VERBOSE == "Yes")
{
printf("%16.2f%16.2f%16.2f", $(NF-6)/DIVIDER, $(NF-5)/DIVIDER, $(NF-4)/DIVIDER); # Total Raw, Raw Free and reserved if Verbose
}
printf("%16s%14s\n", WHITE USABLE COLOR_END, COLOR FREE COLOR_END) ; # Usable and Free %
}'
done
printf "\n"
#
# Subdirs info
#
if [ -z ${SUBDIR} ]
then
(for DIR in `asmcmd ls ${D}`
do
echo ${DIR} `asmcmd --nocp du ${D}/${DIR} | tail -1` # Please look at the "About the --nocp option" notes in the header for more information
# echo ${DIR} `asmcmd du ${D}/${DIR} | tail -1`
done) | awk -v D="$D" -v UNIT="$UNIT"\
' BEGIN { printf("\n\t\t%40s\n\n", D " subdirectories size") ;
printf("%25s%16s%16s\n", "Subdir", "Used " UNIT, "Raw Used " UNIT) ;
printf("%25s%16s%16s\n", "------", "-------", "-----------") ;
DIVIDER=1 ;
if (UNIT == "GB") { DIVIDER="1024" } ;
if (UNIT == "TB") { DIVIDER="1048576" } ; # 1024 * 1024
}
{
use=sprintf("%16.2f", $2/DIVIDER) ;
mir=sprintf("%16.2f", $3/DIVIDER) ;
printf("%25s%16s%16s\n", $1, use, mir) ;
total_use += $2 ;
total_mir += $3 ;
}
END { total_use = sprintf("%16.2f", total_use/DIVIDER) ;
total_mir = sprintf("%16.2f", total_mir/DIVIDER) ;
printf("\n\n%25s%16s%16s\n", "------", "-------", "---------") ;
printf("%25s%16s%16s\n\n", "Total", total_use, total_mir) ;
} '
fi
#
# For information
#
if [[ ${VERBOSE} == "Yes" ]]
then
printf "\t\t%40s\n\n" "Note : Usable = (Raw Free - Reserved)/Redundancy" ;
fi
#****************************************************************************************#
#* E N D O F S O U R C E *#
#****************************************************************************************#
~]$ ./asmdu.sh Instances running on ibrac02 : +APX2, +ASM2, ibrac2, -MGMTDB, vihcdbd8, vihcdbd9 DiskGroup Redundancy Total TB Usable TB % Free --------- ----------- -------- --------- ------ DATA EXTERN 0.39 0.12 30 DUMPS EXTERN 0.49 0.01 2
set linesize 150
select sysdate, GROUP_NUMBER, OPERATION,STATE, POWER, ACTUAL, SOFAR, EST_WORK,EST_RATE, EST_MINUTES from v$asm_operation;
set lines 1000 pages 1000 timing on time on
col path format a30
select group_number,disk_number,name,total_mb,free_mb,path,mount_Status from v$asm_disk order by 1,2;
select * from gv$asm_operation;
select name, total_mb, free_mb from v$asm_diskgroup order by name;
**************************
ASM tools used by Support : KFOD, KFED, AMDU (Doc ID 1485597.1)
SQL> show parameter string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring string ORCL:*
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[grid@rac01 ~]$ kfod status=TRUE asm_diskstring='ORCL:*' disks=all dscvgroup=TRUE OP=all
--------------------------------------------------------------------------------
Disk Size Header Path Disk Group User Group
================================================================================
1: 102401 MB MEMBER ORCL:FLSH_DATA0 DATA
2: 102401 MB MEMBER ORCL:FLSH_DATA1 DATA
3: 102401 MB MEMBER ORCL:FLSH_DATA2 DATA
4: 102401 MB MEMBER ORCL:FLSH_DATA3 DATA
5: 512000 MB MEMBER ORCL:FLSH_DUMPS0 DUMPS
6: 40961 MB MEMBER ORCL:FLSH_GRID0 GRID
7: 102401 MB MEMBER ORCL:FLSH_LOGS0 LOGS
8: 102401 MB MEMBER ORCL:FLSH_MGMT0 MGMT
9: 102401 MB MEMBER ORCL:FLSH_RECO0 RECO
--------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
================================================================================
strace -f -o /home/grid/kfod.txt kfod status=TRUE asm_diskstring='ORCL:*' disks=all dscvgroup=TRUE OP=all
--------------------------------------------------------------------------------
Disk Size Header Path Disk Group User Group
================================================================================
1: 102401 MB MEMBER ORCL:FLSH_DATA0 DATA
2: 102401 MB MEMBER ORCL:FLSH_DATA1 DATA
3: 102401 MB MEMBER ORCL:FLSH_DATA2 DATA
4: 102401 MB MEMBER ORCL:FLSH_DATA3 DATA
5: 512000 MB MEMBER ORCL:FLSH_DUMPS0 DUMPS
6: 40961 MB MEMBER ORCL:FLSH_GRID0 GRID
7: 102401 MB MEMBER ORCL:FLSH_LOGS0 LOGS
8: 102401 MB MEMBER ORCL:FLSH_MGMT0 MGMT
9: 102401 MB MEMBER ORCL:FLSH_RECO0 RECO
--------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
================================================================================
]$ cat /home/grid/kfod.txt|more
27302 execve("/u01/app/19.0.0/grid/bin/kfod", ["kfod", "status=TRUE", "asm_diskstring=ORCL:*", "disks=all", "dscvgroup=TRUE", "OP=all"], 0x7ffdfe51f630 /* 27 vars */) = 0
27302 brk(NULL) = 0x1b6c000
27302 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8791791000
27302 access("/etc/ld.so.preload", R_OK) = -1 ENOENT (No such file or directory)
27302 open("/etc/ld.so.cache", O_RDONLY|O_CLOEXEC) = 3
27302 fstat(3, {st_mode=S_IFREG|0644, st_size=56262, ...}) = 0
27302 mmap(NULL, 56262, PROT_READ, MAP_PRIVATE, 3, 0) = 0x7f8791783000
27302 close(3) = 0
=====
amdu -diskstring 'ORCL:*' -dump 'DATA'
amdu -diskstring 'ORCL:*' -dump 'DATA'
amdu_2024_02_05_04_30_00/
AMDU-00204: Disk N0001 is in currently mounted diskgroup DATA.
AMDU-00201: Disk N0001: 'ORCL:FLSH_DATA0'
[grid@rac01 ~]$ cd amdu_2024_02_05_04_30_00/
[grid@rac01 amdu_2024_02_05_04_30_00]$ ls -ltr
total 135888
-rw-r--r-- 1 grid oinstall 136160 Feb 5 04:30 DATA.map
-rw-r--r-- 1 grid oinstall 13100 Feb 5 04:30 report.txt
-rw-r--r-- 1 grid oinstall 138993664 Feb 5 04:30 DATA_0001.img
[grid@rac01 amdu_2024_02_05_04_30_00]$ cat report.txt
-*-amdu-*-
******************************* AMDU Settings ********************************
ORACLE_HOME: /u01/app/19.0.0/grid
System name: Linux
Node name: rac01.int.smq.datapipe.net
Release: 4.1.12-124.48.2.el7uek.x86_64
Version: #2 SMP Tue Feb 9 14:03:44 PST 2021
Machine: x86_64
amdu run: 05-FEB-24 04:30:00
Endianess: 1
--------------------------------- Operations ---------------------------------
-dump DATA
------------------------------- Disk Selection -------------------------------
-diskstring 'ORCL:*'
------------------------------ Reading Control -------------------------------
------------------------------- Output Control -------------------------------
********************************* DISCOVERY **********************************
----------------------------- DISK REPORT N0001 ------------------------------
Disk Path: ORCL:FLSH_DATA0
Unique Disk ID:
Disk Label: FLSH_DATA0
Physical Sector Size: 512 bytes
set feedback off pagesize 1000 head off underline _ linesize 200 head on col total_gb format 999,999,999 heading 'total gb ' col mb_avail format 999,999,999 heading 'mb avail' col used format 999.99 heading '% used' col volume_name format a10 col volume_device format a25 col mountpath format a15 col attention format a20 col name format a15 col Redundancy format a15 col DISK_SIZE format 9999999999999999 select g.group_number , g.name , g.state , g.total_mb/1024 total_gb ,max(TYPE) Redundancy , trunc((g.free_mb/1024),2) free_gb , usable_file_mb usable_file_mb , count(DISK_NUMBER) Total_Disks , trunc(max(d.TOTAL_MB/1024),2) disk_size from v$asm_diskgroup g,v$asm_disk d where 1=1 and d.group_number = g.group_number group by g.group_number, g.name,g.state, g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb ;
df -ha|grep -i asm oracleasmfs 0 0 0 - /dev/oracleasm
/etc/init.d/oracleasm status
/etc/init.d/oracleasm is deprecated. Use 'oracleasm status'
[oracle]$ oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
log]# cat /etc/sysconfig/oracleasm
#
# This is a configuration file for automatic loading of the Oracle
# Automatic Storage Management library kernel driver. It is generated
# By running /etc/init.d/oracleasm configure. Please use that method
# to modify this file
#
# ORACLEASM_ENABLED: 'true' means to load the driver on boot.
ORACLEASM_ENABLED=true
# ORACLEASM_UID: Default user owning the /dev/oracleasm mount point.
ORACLEASM_UID=grid
# ORACLEASM_GID: Default group owning the /dev/oracleasm mount point.
ORACLEASM_GID=oinstall
# ORACLEASM_SCANBOOT: 'true' means scan for ASM disks on boot.
ORACLEASM_SCANBOOT=true
# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER="dm"
# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE="sd"
# ORACLEASM_SCAN_DIRECTORIES: Scan disks under these directories
ORACLEASM_SCAN_DIRECTORIES=""
# ORACLEASM_USE_LOGICAL_BLOCK_SIZE: 'true' means use the logical block size
# reported by the underlying disk instead of the physical. The default
# is 'false'
ORACLEASM_USE_LOGICAL_BLOCK_SIZE=false
======
cat asm_mapping.sh
-rwxrwxrwx 1 root root 389 Feb 8 04:23 asm_mapping.sh
#!/bin/sh -e
for disk in `/usr/sbin/oracleasm listdisks`
do
oracleasm querydisk -d $disk
#ls -l /dev/*|grep -E `oracleasm querydisk -d $disk|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'|awk -F, '{print $1 ",.*" $2}'`
# Alternate option to remove []
ls -l /dev/*|grep -E `oracleasm querydisk -d $disk|awk '{print $NF}'|sed 's/[][]//g'|awk -F, '{print $1 ",.*" $2}'`|grep -i Disk
echo
done
Disk "FLSH_DATA3" is a valid ASM disk on device [249,8]
brw-rw---- 1 root disk 249, 0 Jan 8 07:57 /dev/dm-0
brw-rw---- 1 root disk 249, 1 Jan 8 07:57 /dev/dm-1
powermt display dev=all
for multipath
cat /etc/multipath.conf
blacklist {
device {
vendor "*"
product "*"
}
}
====================================================
asmcmd lsof -G DATA | grep -i undotbs01.dbf
set linesize 500 pagesize 300
col ALIAS_NAME for a30
col ALIAS_PATH for a30
col FILE_PATH for a70
WITH qry1 AS
(
SELECT aa.name,
ag.name group_name,
af.type,
aa.file_number,
aa.file_incarnation,
aa.parent_index parent_index
FROM v$asm_alias aa,
v$asm_diskgroup ag,
v$asm_file af
WHERE aa.file_number = af.file_number
AND aa.group_number = ag.group_number
AND ag.group_number = af.group_number
AND aa.file_incarnation = af.incarnation
AND aa.system_created = 'N'
AND ag.group_number = 1
)
SELECT alias_name,
'+'||group_name||'/'||LISTAGG(CASE WHEN alias_name = root_name THEN name END,'/') WITHIN GROUP(ORDER BY lvl DESC) alias_path,
'+'||group_name||'/'||LISTAGG(CASE WHEN alias_name != root_name THEN name END,'/') WITHIN GROUP(ORDER BY lvl DESC) file_path
FROM
(
SELECT aa.name,
q1.group_name,
q1.file_number,
q1.name alias_name,
LEVEL lvl,
CONNECT_BY_ROOT aa.name root_name
FROM v$asm_alias aa,
qry1 q1
WHERE aa.group_number = 1
START WITH (aa.name = q1.name OR (aa.name != q1.name AND aa.file_number = q1.file_number))
CONNECT BY PRIOR aa.parent_index = aa.reference_index
AND q1.name = PRIOR q1.name
)
GROUP BY group_name,file_number,alias_name
*****
asmcmd lsof -G DATA | grep -i undotbs01.dbf
===
file info
set linesize 500 pagesize 300
col ALIAS_NAME for a30
col ALIAS_PATH for a30
col FILE_PATH for a70
WITH qry1 AS
(
SELECT aa.name,
ag.name group_name,
af.type,
aa.file_number,
aa.file_incarnation,
aa.parent_index parent_index
FROM v$asm_alias aa,
v$asm_diskgroup ag,
v$asm_file af
WHERE aa.file_number = af.file_number
AND aa.group_number = ag.group_number
AND ag.group_number = af.group_number
AND aa.file_incarnation = af.incarnation
AND aa.system_created = 'N'
AND ag.group_number = 1
)
SELECT alias_name,
'+'||group_name||'/'||LISTAGG(CASE WHEN alias_name = root_name THEN name END,'/') WITHIN GROUP(ORDER BY lvl DESC) alias_path,
'+'||group_name||'/'||LISTAGG(CASE WHEN alias_name != root_name THEN name END,'/') WITHIN GROUP(ORDER BY lvl DESC) file_path
FROM
(
SELECT aa.name,
q1.group_name,
q1.file_number,
q1.name alias_name,
LEVEL lvl,
CONNECT_BY_ROOT aa.name root_name
FROM v$asm_alias aa,
qry1 q1
WHERE aa.group_number = 1
START WITH (aa.name = q1.name OR (aa.name != q1.name AND aa.file_number = q1.file_number))
CONNECT BY PRIOR aa.parent_index = aa.reference_index
AND q1.name = PRIOR q1.name
)
GROUP BY group_name,file_number,alias_name
===
ALIAS_NAME ALIAS_PATH FILE_PATH
------------------------------ ------------------------------ ----------------------------------------------------------------------
spfileIVIHA.ora +DATA/IVIHA/spfileIVIHA.ora +DATA/
test_data01.dbf +DATA/IVIHA/IVIHA/IVIHA/IVIHA/ +DATA/IVIHA/IVIHA/IVIHA/IVIHA/IVIHA/IVIHA/IVIHA/IVIHA/DATAFILE/DATAFIL
test_data01.dbf/test_data01.db E/DATAFILE/DATAFILE/TEST_DATA.3754.1146037979/TEST_DATA.3754.114603797
set pages 1000 linesize 300
set term off
col mp new_value pname
col dp new_value dname
col gp new_value gname
select 'a'||max(length(path)) mp, 'a'||max(length(name)+1) dp from v$asm_disk;
select 'a'||max(length(name)+1) gp from v$asm_diskgroup;
set term on
col path for &pname heading "Path"
col diskname for &dname heading "Disk Name"
col groupname for &gname heading "Group Name"
col sector_size for 99999 heading "Sector|Size"
col block_size for 9999 heading "Block|Size"
col total_gig for 9,999,999 heading "Group|Total|GB"
col free_gig for 9,999,999 heading "Group|Free|GB"
col dtotal_gig for 9,999,999 heading "Disk|Total|GB"
col dfree_gig for 9,999,999 heading "Disk|Free|GB"
col au for 999 heading "AU"
col failgroup for a15 heading 'Fail Group'
set lines 150
break on groupname on total_gig on free_gig skip 1
SELECT g.name groupname,
d.path,
d.name diskname,
g.block_size,
g.allocation_unit_size/1024/1024 au,
g.state
FROM v$asm_diskgroup g, v$asm_disk d
WHERE d.group_number = g.group_number
ORDER BY g.name, d.disk_number;
clear breaks
Block
Group N Path Disk Name Size AU STATE
------- ---------------------- -------------- ----- ---- -----------
DATA01 /dev/ora/ORA-DATA01p2 DATA01_0000 4096 4 MOUNTED
SET LINESIZE 150 PAGESIZE 9999
SET VERIFY off
COLUMN full_alias_path FORMAT a63 HEAD 'File Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label "" of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report
select * from (SELECT
CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
, bytes
, space
, NVL(LPAD(type, 18), '<DIRECTORY>') type
, creation_date
, disk_group_name
, LPAD(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 ,v$asm_alias a ,v$asm_diskgroup g
where (f.group_number(+) = a.group_number
and f.file_number(+) = a.file_number )
and g.group_number(+) =f.group_number
)
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex)
where 1=1
--and full_alias_path like '%spfile%'
--and full_alias_path like '%control%' or full_alias_path like '%CONTROL%'
and type like '%PARAMETERFILE%' or type like '%spfile%'
/
System
File Name Bytes Space File Type Creation Date Created?
--------------------------------------------------------------- ------------------ ------------------ ------------------ -------------------- --------
+DATA01/XXXXX/PARAMETERFILE/spfile.2271.1173159311 25,088 1,048,576 PARAMETERFILE 01-JUL-2024 05:35:11 Y
------------------ ------------------
25,088 1,048,576
------------------ ------------------
Grand Total: 25,088 1,048,576
======
cat asm_device_mapping.sh#!/bin/bash ## ASMLIB_DISK -- disk name in ASMLIB ## ASM_DISK -- disk name in ASM ## DEVICE -- physical disk name GRID_HOME=`cat /etc/oratab | grep ^+ASM | awk -F":" '{print $2}'` for ASMLIB_DISK in `ls /dev/oracleasm/disks/*` do ASM_DISK=`$GRID_HOME/bin/kfed read $ASMLIB_DISK | grep dskname | tr -s ' '| cut -f2 -d' '` majorminor=`ls -l $ASMLIB_DISK | tr -s ' ' | cut -f5,6 -d' '` device=`ls -l /dev/ | tr -s ' ' | grep -w "$majorminor" | cut -f10 -d' '` echo "ASMLIB disk name : $ASMLIB_DISK" echo "ASM_DISK name : $ASM_DISK" echo "Physical disk device : /dev/$device" done
or
cat asmdisk.sh #!/bin/bash for asmlibdisk in `ls /dev/oracleasm/disks/*` do echo "ASMLIB disk name: $asmlibdisk" asmdisk=`/u01/app/19.0.0/grid/bin/kfed read $asmlibdisk | grep dskname | tr -s ' '| cut -f2 -d' '` echo "ASM disk name: $asmdisk" majorminor=`ls -l $asmlibdisk | tr -s ' ' | cut -f5,6 -d' '` device=`ls -l /dev | tr -s ' ' | grep "$majorminor" | cut -f10 -d' '` echo "Device path: /dev/$device" done
output
./asmdisk.sh ASMLIB disk name: /dev/oracleasm/disks/VOTING ASM disk name: Device path: /dev/sdb1
====
/u01/app/19.0.0/grid/bin/kfed read |ls /dev/oracleasm/disks/* /dev/oracleasm/disks/VOTING
