Oracle 23c New Features for DBA'sHere I log, some important ASM scripts which are very useful for me as well as a ready reference for you all.
Before to scripts, here are the important views respective to ASM instances.
| View Name | ASM Instance | DB Instance |
| V$ASM_DISKGROUP | Describes a disk group (number, name, size related info, state, and redundancy type) |
Contains one row for every open ASM disk in the DB instance. |
| V$ASM_CLIENT | Identifies databases using disk groups managed by the ASM instance. |
Contains no rows. |
| V$ASM_DISK | Contains one row for every disk discovered by the ASM instance, including disks that are not part of any disk group. | Contains rows only for disks in the disk groups in use by that DB instance. |
| V$ASM_FILE | Contains one row for every ASM file in every disk group mounted by the ASM instance. | Contains rows only for files that are currently open in the DB instance. |
| V$ASM_TEMPLATE | Contains one row for every template present in every disk group mounted by the ASM instance. | Contains no rows. |
| V$ASM_ALIAS | Contains one row for every alias present in every disk group mounted by the ASM instance. | Contains no rows. |
| v$ASM_OPERATION | Contains one row for every active ASM long running operation executing in the ASM instance., such as rebalance operation | Contains no rows. |
| v$ASM_ATTRIBUTE | Contains one row for every diskgroup pertaining to the disk group attribute, such as compatible asm, rdbms asm etc | Contains no rows. |
| V$ASM_DISK_IOSTAT | about disk I/O statistics for each ASM client | One row if queried from the database instance that is viewed |
| V$ASM_DISK_STAT | displays performance statistics in the same way that V$ASM_DISK does, but without performing discovery of new disks. This results in a less expensive operation | Contains rows only for disks in the disk groups in use by that DB instance. |
Click Below for scripts
Script #1:- displays one row for every disk discovered by the ASM instance, including disks which are not part of any disk group
set pages 50000 lines 130 echo on head on flush on veri on feed on
col PATH form a25
col GB_read for 99999
col GB_written for 9999
select PATH,
mount_status,
state,
TOTAL_MB,
FREE_MB,
mount_date,
bytes_read/1024/1024/1024 GB_read,
BYTES_WRITTEN/1024/1024/1024 GB_written
from v$asm_disk_stat
order by mount_status;
exit
EOF
echo
exit
PATH MOUNT_S STATE TOTAL_MB FREE_MB MOUNT_DAT GB_READ GB_WRITTEN ------------------------- ------- -------- ---------- ---------- --------- ------- ---------- /dev/sda1/ASM4 CACHED NORMAL 118683 70107 11-JAN-12 6371 33 /dev/sda2ASM9 CACHED NORMAL 118683 70106 11-JAN-12 5919 37 /dev/sda3/ASM8 CACHED NORMAL 118683 70112 11-JAN-12 5619 31 /dev/sda4/ASM1 CACHED NORMAL 118683 70107 11-JAN-12 5874 31 /dev/sda5/ASM2 CACHED NORMAL 118683 98958 11-JAN-12 0 28 /dev/sda6/ASM3 CACHED NORMAL 118683 70106 11-JAN-12 5453 30 /dev/sda7/ASM10 CACHED NORMAL 118683 98956 11-JAN-12 0 28 /dev/sda8/ASM5 CACHED NORMAL 118683 70111 11-JAN-12 5566 34 /dev/sdb1/spfile+ASM.ora CLOSED NORMAL 149 0 /dev/sdb2/ocr.dbf CLOSED NORMAL 149 0 /dev/sdb3/votingdisk CLOSED NORMAL 149 0
Script 2#:- Check Clients that connected to database
set pages 50000 lines 100 echo on head on flush on veri on feed on
col INSTANCE_NAME form a20
col SOFTWARE_VERSION form a20
col COMPATIBLE_VERSION form a20
select * from v$asm_client;
GROUP_NUMBER INSTANCE_NAME DB_NAME STATUS SOFTWARE_VERSION COMPATIBLE_VERSION
------------ -------------------- -------- ------------ -------------------- --------------------
1 ractst1 ractst CONNECTED 11.2.0.1.0 11.2.0.1.0
Script 3#:- This script display % free space on each diskgroup. Heading were turned off to use the output to feed alert scripts. displays one row for every disk group discovered by the ASM instance
set head off flush on veri on feed off
select
round(100*FREE_MB/TOTAL_MB) ||','|| NAME
from v$asm_diskgroup
/
Script 4#:- Provide Information about ASM files
col TYPE form a15
col FILE_NUMBER form 9999 head FILE_NUM
col GROUP_NUMBER form 9999 head GR_NUM
col GB for 9999.99
select GROUP_NUMBER ,
FILE_NUMBER ,
BYTES/1024/1024/1024 GB ,
TYPE ,
STRIPED ,
MODIFICATION_DATE
from v$asm_file
where TYPE != 'ARCHIVELOG'
/
GR_NUM FILE_NUM GB TYPE STRIPE MODIFICAT
------ -------- -------- --------------- ------ ---------
1 256 .00 PARAMETERFILE COARSE 22-JAN-12
1 643 .02 CONTROLFILE FINE 22-JAN-12
1 644 .02 CONTROLFILE FINE 22-JAN-12
3 257 3.32 DATAFILE COARSE 21-JAN-12
3 258 29.30 DATAFILE COARSE 22-JAN-12
3 259 6.40 DATAFILE COARSE 21-JAN-07
3 266 29.30 DATAFILE COARSE 22-JAN-07
3 267 .78 DATAFILE COARSE 21-JAN-07
3 268 .02 CHANGETRACKING COARSE 22-JAN-12
3 269 .02 CONTROLFILE FINE 11-JAN-12
3 270 .02 CONTROLFILE FINE 11-JAN-12
3 271 .02 CONTROLFILE FINE 11-JAN-12
3 284 15.00 DATAFILE COARSE 21-JAN-12
3 285 1.95 ONLINELOG FINE 21-JAN-12
3 286 1.95 ONLINELOG FINE 22-JAN-12
3 287 1.95 ONLINELOG FINE 21-JAN-12
3 288 1.95 ONLINELOG FINE 22-JAN-12
3 289 1.95 ONLINELOG FINE 21-JAN-12
3 290 1.95 ONLINELOG FINE 22-JAN-12
3 291 1.95 ONLINELOG FINE 22-JAN-12
3 292 1.95 ONLINELOG FINE 22-JAN-12
Script 5#:- Displays one row for every disk group discovered by the ASM instance
set pages 100 lines 130 echo on head on flush on veri on feed on
col TYPE form a15
col FILE_NUMBER form 9999 head FILE_NUM
col GROUP_NUMBER form 9999 head GR_NUM
col GB for 9999.99
select GROUP_NUMBER ,
FILE_NUMBER ,
COMPOUND_INDEX ,
INCARNATION ,
BLOCK_SIZE ,
BLOCKS ,
BYTES/1024/1024/1024 GB ,
TYPE ,
STRIPED ,
CREATION_DATE ,
MODIFICATION_DATE
from v$asm_file
where TYPE != 'ARCHIVELOG'
/
GR_NUM FILE_NUM COMPOUND_INDEX INCARNATION BLOCK_SIZE BLOCKS GB TYPE STRIPE CREATION_ MODIFICAT
------ -------- -------------- ----------- ---------- ---------- -------- --------------- ------ --------- ---------
1 256 16777472 589938807 512 13 .00 PARAMETERFILE COARSE 08-MAY-06 22-JAN-07
1 643 16777859 612467025 16384 1517 .02 CONTROLFILE FINE 21-JAN-07 22-JAN-07
1 644 16777860 612467037 16384 1517 .02 CONTROLFILE FINE 21-JAN-07 22-JAN-07
3 256 50331904 581368661 16384 1515 .02 CONTROLFILE FINE 02-FEB-06 08-MAY-06
3 257 50331905 602197139 8192 435201 3.32 DATAFILE COARSE 26-SEP-06 21-JAN-07
3 258 50331906 592234419 8192 3840001 29.30 DATAFILE COARSE 04-JUN-06 22-JAN-07
3 259 50331907 581368675 8192 838401 6.40 DATAFILE COARSE 02-FEB-06 21-JAN-07
3 266 50331914 592234263 8192 3840001 29.30 DATAFILE COARSE 04-JUN-06 22-JAN-07
3 267 50331915 590552237 8192 102401 .78 DATAFILE COARSE 16-MAY-06 21-JAN-07
3 268 50331916 581864701 512 43137 .02 CHANGETRACKING COARSE 08-FEB-06 22-JAN-07
3 269 50331917 589938393 16384 1517 .02 CONTROLFILE FINE 08-MAY-06 11-JAN-07
3 270 50331918 589938393 16384 1517 .02 CONTROLFILE FINE 08-MAY-06 11-JAN-07
3 271 50331919 589938393 16384 1517 .02 CONTROLFILE FINE 08-MAY-06 11-JAN-07
3 284 50331932 590541737 8192 1966081 15.00 DATAFILE COARSE 15-MAY-06 21-JAN-07
3 285 50331933 590541871 512 4096001 1.95 ONLINELOG FINE 15-MAY-06 21-JAN-07
3 286 50331934 590541895 512 4096001 1.95 ONLINELOG FINE 15-MAY-06 22-JAN-07
3 287 50331935 590541921 512 4096001 1.95 ONLINELOG FINE 15-MAY-06 21-JAN-07
3 288 50331936 590541943 512 4096001 1.95 ONLINELOG FINE 15-MAY-06 22-JAN-07
3 289 50331937 590541969 512 4096001 1.95 ONLINELOG FINE 15-MAY-06 21-JAN-07
3 290 50331938 590541989 512 4096001 1.95 ONLINELOG FINE 15-MAY-06 22-JAN-07
3 291 50331939 590542011 512 4096001 1.95 ONLINELOG FINE 15-MAY-06 22-JAN-07
3 292 50331940 590542031 512 4096001 1.95 ONLINELOG FINE 15-MAY-06 22-JAN-07
Script 6#:- displays one row for every disk group discovered by the ASM instance
set pages 50000 lines 130 echo on head on flush on veri on feed on
col Disk_Group form a15
col File_Type form a30
col MB form '999,999,999'
select
a.NAME Disk_Group ,
b.TYPE File_Type ,
b.BYTES/1024/1024 MB
from v$asm_diskgroup a,
v$asm_file b
where a.GROUP_NUMBER=b.GROUP_NUMBER
order by 1,2
/
DISK_GROUP FILE_TYPE MB --------------- ------------------------------ ------------ CRMARCHDG ARCHIVELOG 1,996 CRMARCHDG ARCHIVELOG 1,999 CRMARCHDG ARCHIVELOG 0 CRMARCHDG ARCHIVELOG 398 CRMARCHDG ARCHIVELOG 1,999 CRMARCHDG ARCHIVELOG 19 CRMARCHDG ARCHIVELOG 66 CRMARCHDG ARCHIVELOG 0 CRMARCHDG ARCHIVELOG 1,994 CRMARCHDG ARCHIVELOG 1,998 CRMARCHDG CONTROLFILE 24 CRMARCHDG CONTROLFILE 24 CRMARCHDG PARAMETERFILE 0 CRMDATADG CHANGETRACKING 21 CRMDATADG CONTROLFILE 24 CRMDATADG CONTROLFILE 24 CRMDATADG CONTROLFILE 24 CRMDATADG CONTROLFILE 24 CRMDATADG DATAFILE 3,400 CRMDATADG DATAFILE 30,000 CRMDATADG DATAFILE 15,360 CRMDATADG DATAFILE 20,480 CRMDATADG DATAFILE 13,264 CRMDATADG DATAFILE 30,000 CRMDATADG DATAFILE 30,000 CRMDATADG ONLINELOG 2,000 CRMDATADG ONLINELOG 2,000 CRMDATADG ONLINELOG 2,000 CRMDATADG ONLINELOG 2,000 CRMDATADG ONLINELOG 2,000 CRMDATADG ONLINELOG 2,000 CRMDATADG ONLINELOG 2,000 CRMDATADG ONLINELOG 2,000 CRMDATADG TEMPFILE 32,767
Script #7:- Display configuration and statistics of ASM Disk Groups
set pages 50000 lines 130 echo on head on flush on veri on feed on
col NAME for a10
col SEC_SZ for 999
col BLK_SZ for 9999
col STR_MB for 99
col GROUP_NUMBER for 99 head GRP
col COMPATIBILITY for a10
col DB_COMPAT for a10
col STATE for a7
col TYPE for a7
col TOT_GB for 999.9
col USABLE_GB for 999.9
col OFF_DSK for 999
col UNBLC for a5
col DB_COMPAT for a10
select GROUP_NUMBER ,
NAME ,
SECTOR_SIZE SEC_SZ ,
BLOCK_SIZE BLK_SZ ,
ALLOCATION_UNIT_SIZE /1024/1024 STR_MB,
STATE ,
TYPE ,
TOTAL_MB /1024 TOT_GB,
USABLE_FILE_MB /1024 USABLE_GB ,
OFFLINE_DISKS OFF_DSK ,
UNBALANCED UNBLC ,
DATABASE_COMPATIBILITY DB_COMPAT
from v$asm_diskgroup_stat
/
GRP NAME SEC_SZ BLK_SZ STR_MB STATE TYPE TOT_GB USABLE_GB OFF_DSK UNBLC DB_COMPAT --- ---------- ------ ------ ------ ------- ------- ------ --------- ------- ----- ---------- 1 CRMARCHDG 512 4096 1 MOUNTED EXTERN 231.8 189.4 0 N 10.1.0.0.0 3 CRMDATADG 512 4096 1 MOUNTED EXTERN 927.2 547.7 0 N 10.1.0.0.0
Script 9#: All of above scripts.
set wrap off set lines 155 pages 9999 col "Group Name" for a6 Head "Group|Name" col "Disk Name" for a10 col "State" for a10 col "Type" for a10 Head "Diskgroup|Redundancy" col "Total GB" for 9,990 Head "Total|GB" col "Free GB" for 9,990 Head "Free|GB" col "Imbalance" for 99.9 Head "Percent|Imbalance" col "Variance" for 99.9 Head "Percent|Disk Size|Variance" col "MinFree" for 99.9 Head "Minimum|Percent|Free" col "MaxFree" for 99.9 Head "Maximum|Percent|Free" col "DiskCnt" for 9999 Head "Disk|Count" prompt prompt ASM Disk Groups prompt =============== SELECT g.group_number "Group" , g.name "Group Name" , g.state "State" , g.type "Type" , g.total_mb/1024 "Total GB" , g.free_mb/1024 "Free GB" , 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance" , 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance" , 100*(min(d.free_mb/d.total_mb)) "MinFree" , 100*(max(d.free_mb/d.total_mb)) "MaxFree" , count(*) "DiskCnt" FROM v$asm_disk d, v$asm_diskgroup g WHERE d.group_number = g.group_number and d.group_number <> 0 and d.state = 'NORMAL' and d.mount_status = 'CACHED' GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb ORDER BY 1; prompt ASM Disks In Use prompt ================ col "Group" for 999 col "Disk" for 999 col "Header" for a9 col "Mode" for a8 col "State" for a8 col "Created" for a10 Head "Added To|Diskgroup" --col "Redundancy" for a10 --col "Failure Group" for a10 Head "Failure|Group" col "Path" for a19 --col "ReadTime" for 999999990 Head "Read Time|seconds" --col "WriteTime" for 999999990 Head "Write Time|seconds" --col "BytesRead" for 999990.00 Head "GigaBytes|Read" --col "BytesWrite" for 999990.00 Head "GigaBytes|Written" col "SecsPerRead" for 9.000 Head "Seconds|PerRead" col "SecsPerWrite" for 9.000 Head "Seconds|PerWrite" select group_number "Group" , disk_number "Disk" , header_status "Header" , mode_status "Mode" , state "State" , create_date "Created" --, redundancy "Redundancy" , total_mb/1024 "Total GB" , free_mb/1024 "Free GB" , name "Disk Name" --, failgroup "Failure Group" , path "Path" --, read_time "ReadTime" --, write_time "WriteTime" --, bytes_read/1073741824 "BytesRead" --, bytes_written/1073741824 "BytesWrite" , read_time/reads "SecsPerRead" , write_time/writes "SecsPerWrite" from v$asm_disk_stat where header_status not in ('FORMER','CANDIDATE') order by group_number , disk_number / Prompt File Types in Diskgroups Prompt ======================== col "File Type" for a16 col "Block Size" for a5 Head "Block|Size" col "Gb" for 9990.00 col "Files" for 99990 break on "Group Name" skip 1 nodup select g.name "Group Name" , f.TYPE "File Type" , f.BLOCK_SIZE/1024||'k' "Block Size" , f.STRIPED , count(*) "Files" , round(sum(f.BYTES)/(1024*1024*1024),2) "Gb" from v$asm_file f,v$asm_diskgroup g where f.group_number=g.group_number group by g.name,f.TYPE,f.BLOCK_SIZE,f.STRIPED order by 1,2; clear break prompt Instances currently accessing these diskgroups prompt ============================================== col "Instance" form a8 select c.group_number "Group" , g.name "Group Name" , c.instance_name "Instance" from v$asm_client c , v$asm_diskgroup g where g.group_number=c.group_number / prompt Free ASM disks and their paths prompt ============================== col "Disk Size" form a9 select header_status "Header" , mode_status "Mode" , path "Path" , lpad(round(os_mb/1024),7)||'Gb' "Disk Size" from v$asm_disk where header_status in ('FORMER','CANDIDATE') order by path / prompt Current ASM disk operations prompt =========================== select * from v$asm_operation /
The above scripts were collected from different sites and blogs and often proved very useful to me, Many thanks to them
-Thanks
Geek DBA
Follow Me!!!