Search This Blog

Total Pageviews

Saturday, 9 June 2012

Oracle ASM useful Scripts


Oracle ASM useful Scripts : 

ASM Scripts





             Free GB = Total TB × 1024 × (100 − Usage %) / 100         
*******************************************************************************************




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

rpm -qa --last | grep oracleasm -- No RPM grep: No: No such file or directory grep: RPM: No such file or directory
rpm -qa --last | grep oracleasm

===================
srvctl status asm -v ASM is running on srv1 Detailed state on node srv1: Started
srvctl stop asm


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? 
set linesize 300 pages 300
col PATH for a20
col LIBRARY for a70
col NAME for a15
select inst_id,group_number grp_num,name,state,header_status header,mount_status mount,path, library
from gv$asm_disk
order by inst_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 : &current_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 -----> optional

Check 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-discover


for 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.1167894287
asmcmd lsod -G RECO01
kfod disks=all
--------------------------------------------------------------------------------
 Disk          Size Path                                     User     Group
================================================================================
   1:      40959 MB /dev/oracleasm/disks/DATADISK1           grid     oinstall
   2:      12287 MB /dev/oracleasm/disks/OCRDISK1            grid     oinstall
--------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
================================================================================
      +ASM /u01/app/19.0.0/grid

 which 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
;



with GB!!!
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 : &current_instance                                           |
PROMPT +------------------------------------------------------------------------+

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    220
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 total_gb              FORMAT 999,999.99    HEAD 'Total Size (GB)'
COLUMN used_gb               FORMAT 999,999.99    HEAD 'Used Size (GB)'
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 total_gb used_gb 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(total_mb / 1024, 2)                total_gb
  , ROUND((total_mb - free_mb) / 1024, 2)    used_gb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM  v$asm_diskgroup
WHERE total_mb != 0
ORDER BY name
/



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



====
[grid@srv1 ~]$ /u01/app/19.0.0/grid/bin/kfed read |ls -l /dev/oracleasm/disks/*|wc -l
2





[grid@srv1 ~]$ /u01/app/19.0.0/grid/bin/kfed read |ls -l /dev/oracleasm/disks/*
brw-rw----. 1 grid oinstall 8, 33 Jan  4 10:10 /dev/oracleasm/disks/DATADISK1
brw-rw----. 1 grid oinstall 8, 17 Jan  4 10:10 /dev/oracleasm/disks/OCRDISK1
[grid@srv1 ~]$



 #####################################
      
[grid@srv1 ~]$ /u01/app/19.0.0/grid/bin/kfed read /dev/oracleasm/disks/DATADISK1|egrep -i "kfbh.endian|kfbh.type|kfbh.block.blk"

kfbh.endian:                          1 ; 0x000: 0x01
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.block.blk:                       0 ; 0x004: blk=0  ASM header should be zero. <<<<<<<<<<<<<


Type	Value
kfbh.endian	System Endianness – 0 Big Endian (Unix) 1 Little Endian (Linux & Windows)
kfbh.type	ASM block type, KFBTYP_DISKHEAD  = ASM disk header block
kfbh.block.blk	ASM block number, ASM header should be zero. 

############################################




/u01/app/19.0.0/grid/bin/kfed read /dev/oracleasm/disks/DATADISK1 |egrep -i "kfdhdb.hdrsts|kfdhdb.dskname|kfdhdb.grpname|kfdhdb.fgname|kfdhdb.secsize|blksize|driver.provstr|kfdhdb.ausize"



kfdhdb.driver.provstr:ORCLDISKDATADISK1 ; 0x000: length=17
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:           DATADISK_0000 ; 0x028: length=13
kfdhdb.grpname:                DATADISK ; 0x048: length=8
kfdhdb.fgname:            DATADISK_0000 ; 0x068: length=13
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  4194304 ; 0x0bc: 0x00400000



kfdhdb.driver.provstr	ORCLDISK+[ASM disk name] for ASMLIB and ASMFD disks. ORCLDISK for non-ASMLIB/ASMFD disks.
kfdhdb.grptyp	Oracle ASM group type
kfdhdb.hdrsts	Header status
kfdhdb.dskname	Disk name in the disk group
kfdhdb.grpname	Oracle ASM disk group name
kfdhdb.fgname	Oracle ASM failure group name
kfdhdb.secsize	Sector size (bytes)
kfdhdb.blksize	Block size (bytes)
kfdhdb.ausize	ASM allocation unit size (bytes)
kfdhdb.dsksize	Disk size (bytes)






 /u01/app/19.0.0/grid/bin/kfed read /dev/oracleasm/disks/DATADISK1 |head -30
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                   401598961 ; 0x00c: 0x17efe9f1
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:ORCLDISKDATADISK1 ; 0x000: length=17
kfdhdb.driver.reserved[0]:   1096040772 ; 0x008: 0x41544144
kfdhdb.driver.reserved[1]:   1263749444 ; 0x00c: 0x4b534944
kfdhdb.driver.reserved[2]:           49 ; 0x010: 0x00000031
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                318767104 ; 0x020: 0x13000000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:           DATADISK_0000 ; 0x028: length=13
kfdhdb.grpname:                DATADISK ; 0x048: length=8
kfdhdb.fgname:            DATADISK_0000 ; 0x068: length=13
kfdhdb.siteguid[0]:                   0 ; 0x088: 0x00
kfdhdb.siteguid[1]:                   0 ; 0x089: 0x00
kfdhdb.siteguid[2]:                   0 ; 0x08a: 0x00
kfdhdb.siteguid[3]:                   0 ; 0x08b: 0x00
kfdhdb.siteguid[4]:                   0 ; 0x08c: 0x00

(Doc ID 1088867.1)

kfed repair /dev/oracleasm/disks/DATADISK1  ausz=4194304











blkid | grep asm | sort -rn
/dev/sdc1: LABEL="DATADISK1" TYPE="oracleasm"
/dev/sdb1: LABEL="OCRDISK1" TYPE="oracleasm"





kfed read /dev/oracleasm/disks/OCRDISK1 | egrep 'name|size|type'
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfdhdb.dskname:            OCRDISK_0000 ; 0x028: length=12
kfdhdb.grpname:                 OCRDISK ; 0x048: length=7
kfdhdb.fgname:             OCRDISK_0000 ; 0x068: length=12
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  4194304 ; 0x0bc: 0x00400000
kfdhdb.dsksize:                    3071 ; 0x0c4: 0x00000bff
[grid@srv1 ~]$



[grid@srv1 ~]$ kfod disks=all
--------------------------------------------------------------------------------
 Disk          Size Path                                     User     Group
================================================================================
   1:      40959 MB /dev/oracleasm/disks/DATADISK1           grid     oinstall
   2:      12287 MB /dev/oracleasm/disks/OCRDISK1            grid     oinstall
--------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
================================================================================
      +ASM /u01/app/19.0.0/grid



[grid@srv1 ~]$  kfod op=DISKS label=TRUE disks=ALL name=TRUE
--------------------------------------------------------------------------------
 Disk          Size Path                                    DiskName           Site      Failgroup           Disk-Label User     Group
================================================================================
   1:      40959 MB /dev/oracleasm/disks/DATADISK1           DATADISK_0000         #              #            DATADISK1 grid     oinstall
   2:      12287 MB /dev/oracleasm/disks/OCRDISK1            OCRDISK_0000         #              #             OCRDISK1 grid     oinstall
[grid@srv1 ~]$


[grid@srv1 ~]$ kfod op=groups
--------------------------------------------------------------------------------
Group          Size          Free Redundancy Name
================================================================================
   1:      12284 MB      12184 MB     EXTERN OCRDISK
   2:      40956 MB      31064 MB     EXTERN DATADISK
[grid@srv1 ~]$











asmcmd lsattr -l -G DATADISK

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




# cat /etc/*release
# uname -a
# rpm -qa | grep oracleasm
# lsmod|grep oracleasm
# modinfo oracleasm
# oracleasm status
# oracleasm scandisks
# oracleasm listdisks
# oracleasm listdisks | xargs oracleasm querydisk -d
# oracleasm listdisks | xargs oracleasm querydisk -p
# blkid | grep oracleasm
# cat /etc/sysconfig/oracleasm
# ls -ltr /dev/oracleasm/disks/
# export ORACLE_HOME=<ASM_HOME>
# export LD_LIBRARY_PATH=$ORACLE_HOME/lib
# $ORACLE_HOME/bin/kfod asm_diskstring='ORCL:*' disks=all dscvgroup=TRUE name=TRUE status=true
# $ORACLE_HOME/bin/kfod asm_diskstring='/dev/oracleasm*/*’ disks=all dscvgroup=TRUE name=TRUE status=true


--multipath check
/sbin/blkid | grep oracleasm|grep -i DATA010

 oracleasm querydisk -p DATA001
Disk "DATA001" is a valid ASM disk
/dev/sdi1: LABEL="DATA001" TYPE="oracleasm" PARTLABEL="emcpoweraa1" PARTUUID="ca7d445d-6a13-46e2-9696-43a1f58e984b"
/dev/sdao1: LABEL="DATA001" TYPE="oracleasm" PARTLABEL="emcpoweraa1" PARTUUID="ca7d445d-6a13-46e2-9696-43a1f58e984b"
/dev/sdbb1: LABEL="DATA001" TYPE="oracleasm" PARTLABEL="emcpoweraa1" PARTUUID="ca7d445d-6a13-46e2-9696-43a1f58e984b"
/dev/sdda1: LABEL="DATA001" TYPE="oracleasm" PARTLABEL="emcpoweraa1" PARTUUID="ca7d445d-6a13-46e2-9696-43a1f58e984b"
/dev/emcpoweraa1: LABEL="DATA001" TYPE="oracleasm" PARTLABEL="emcpoweraa1" PARTUUID="ca7d445d-6a13-46e2-9696-43a1f58e984b"






 




[grid@srv1 ~]$ srvctl status asm -v ASM is running on srv1 Detailed state on node srv1: Started
[grid@srv1 ~]$ srvctl stop asm PRCR-1065 : Failed to stop resource ora.asm CRS-2974: unable to act on resource 'ora.asm' on server 'srv1' because that would require stopping or relocating resource 'ora.DATADISK.dg' but the appropriate force flag was not specified
[grid@srv1 ~]$ srvctl stop diskgroup -g DATADISK [grid@srv1 ~]$ srvctl stop asm PRCR-1065 : Failed to stop resource ora.asm CRS-2974: unable to act on resource 'ora.asm' on server 'srv1' because that would require stopping or relocating resource 'ora.OCRDISK.dg' but the appropriate force flag was not specified
[grid@srv1 ~]$ srvctl stop asm^C [grid@srv1 ~]$ asmcmd -p ASMCMD [+] > ASMCMD [+] > ls -s Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name 512 512 4096 4194304 12284 12184 0 12184 0 N OCRDISK/ ASMCMD [+] > exit


[grid@srv1 ~]$ srvctl stop diskgroup -g OCRDISK PRCR-1065 : Failed to stop resource ora.OCRDISK.dg CRS-2675: Stop of 'ora.OCRDISK.dg' on 'srv1' failed
[grid@srv1 ~]$ srvctl stop diskgroup -g OCRDISK -f PRCR-1065 : Failed to stop resource ora.OCRDISK.dg CRS-2675: Stop of 'ora.OCRDISK.dg' on 'srv1' failed
[grid@srv1 ~]$ /u01/app/19.0.0/grid/bin/crsctl stop has -f CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'srv1' CRS-2673: Attempting to stop 'ora.evmd' on 'srv1' CRS-2673: Attempting to stop 'ora.OCRDISK.dg' on 'srv1' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'srv1' CRS-2677: Stop of 'ora.OCRDISK.dg' on 'srv1' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'srv1' CRS-2677: Stop of 'ora.evmd' on 'srv1' succeeded CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'srv1' succeeded CRS-2677: Stop of 'ora.asm' on 'srv1' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'srv1' CRS-2677: Stop of 'ora.cssd' on 'srv1' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'srv1' has completed CRS-4133: Oracle High Availability Services has been stopped.


ps -ef | grep pmon ps -ef | grep d.bin



crsctl status res -t crsctl start has crsctl stop has crsctl start has ps -ef|grep -i d.bin srvctl start asm pwd sqlplus / as sysasm crsctl start res ora.asm -init



/u01/app/19.0.0/grid/bin/crsctl stat resource -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.DATADISK.dg OFFLINE OFFLINE srv1 STABLE ora.LISTENER.lsnr ONLINE OFFLINE srv1 STARTING ora.OCRDISK.dg OFFLINE OFFLINE srv1 STABLE ora.asm ONLINE OFFLINE srv1 Instance Shutdown,ST ABLE ora.ons OFFLINE OFFLINE srv1 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.cssd 1 OFFLINE OFFLINE STABLE ora.diskmon 1 OFFLINE OFFLINE STABLE ora.evmd 1 ONLINE INTERMEDIATE srv1 STABLE ora.oradb.db 1 OFFLINE OFFLINE STABLE ora.oradb.prodb_srvp.svc 1 OFFLINE OFFLINE STABLE -------------------------------------------------------------------------------- [grid@srv1 ~]$ crsctl start resource -all CRS-5702: Resource 'ora.LISTENER.lsnr' is already running on 'srv1' CRS-5702: Resource 'ora.cssd' is already running on 'srv1' CRS-5702: Resource 'ora.evmd' is already running on 'srv1' CRS-2501: Resource 'ora.ons' is disabled CRS-5702: Resource 'ora.asm' is already running on 'srv1' CRS-2672: Attempting to start 'ora.diskmon' on 'srv1' CRS-5702: Resource 'ora.DATADISK.dg' is already running on 'srv1' CRS-2672: Attempting to start 'ora.oradb.db' on 'srv1' CRS-5702: Resource 'ora.OCRDISK.dg' is already running on 'srv1' CRS-5062: Oracle binary file '/u01/app/oracle/product/19.0.0/db_1/bin/oracle' does not exist on node srv1. CRS-2674: Start of 'ora.oradb.db' on 'srv1' failed CRS-2679: Attempting to clean 'ora.oradb.db' on 'srv1' CRS-2681: Clean of 'ora.oradb.db' on 'srv1' succeeded CRS-2672: Attempting to start 'ora.oradb.db' on 'srv1' CRS-5062: Oracle binary file '/u01/app/oracle/product/19.0.0/db_1/bin/oracle' does not exist on node srv1. CRS-2674: Start of 'ora.oradb.db' on 'srv1' failed CRS-2679: Attempting to clean 'ora.oradb.db' on 'srv1' CRS-2681: Clean of 'ora.oradb.db' on 'srv1' succeeded CRS-2676: Start of 'ora.diskmon' on 'srv1' succeeded CRS-4000: Command Start failed, or completed with errors. [grid@srv1 ~]$ [grid@srv1 ~]$ [grid@srv1 ~]$ /u01/app/19.0.0/grid/bin/crsctl stat resource -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.DATADISK.dg ONLINE ONLINE srv1 STABLE ora.LISTENER.lsnr ONLINE ONLINE srv1 STABLE ora.OCRDISK.dg ONLINE ONLINE srv1 STABLE ora.asm ONLINE ONLINE srv1 Started,STABLE ora.ons OFFLINE OFFLINE srv1 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.cssd 1 ONLINE ONLINE srv1 STABLE ora.diskmon 1 OFFLINE OFFLINE STABLE ora.evmd 1 ONLINE ONLINE srv1 STABLE ora.oradb.db 1 ONLINE OFFLINE STABLE ora.oradb.prodb_srvp.svc 1 ONLINE OFFLINE STABLE -------------------------------------------------------------------------------- [grid@srv1 ~]$ asmcmd -p ASMCMD [+] > ASMCMD [+] > lsdg State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 512 4096 4194304 40956 30840 0 30840 0 N DATADISK/ MOUNTED EXTERN N 512 512 4096 4194304 12284 12184 0 12184 0 N OCRDISK/



srvctl stop asm -f



oracleasm listdisks | while read disk
do
oracleasm querydisk -p $disk
done

udevadm info --query=all --name=/dev/sdb | grep ID_WWN
 powermt display dev=all
lsblk -f | grep -B1 "DATA01"
powermt display dev=all | grep -B1 -A17 emcpower

oracleasm querydisk -p DATA01  

oracleasm querydisk -p RECO01



crsctl status res | grep -v "^$" | \ awk -F "=" 'BEGIN {print ""} {printf("%s", NR%4 ? $2 "|" : $2 "\n")}' | \ sed -e 's/ *, /,/g' -e 's/, /,/g' | \ awk -F "|" ' BEGIN { printf "%-40s %-35s %-20s %-50s\n\n", \ "RESOURCE_NAME", "RESOURCE_TYPE", "TARGET", "STATE" } { split($3, trg, ",") split($4, st, ",") } { for (i in trg) { printf "%-40s %-35s %-20s %-50s\n", \ $1, $2, trg[i], st[i] } }'


alias crsres='crsctl status res | grep -v "^$" | \ awk -F "=" '\''BEGIN {print ""} {printf("%s", NR%4 ? $2 "|" : $2 "\n")}'\'' | \ sed -e '\''s/ *, /,/g'\'' -e '\''s/, /,/g'\'' | \ awk -F "|" '\''BEGIN { printf "%-40s %-35s %-20s %-50s\n\n", \ "RESOURCE_NAME", "RESOURCE_TYPE", "TARGET", "STATE" } { split($3, trg, ",") split($4, st, ",") } { for (i in trg) { printf "%-40s %-35s %-20s %-50s\n", \ $1, $2, trg[i], st[i] } }'\''' crsres
===
RESOURCE_NAME RESOURCE_TYPE TARGET STATE ora.DATA.dg ora.diskgroup.type OFFLINE OFFLINE ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE on oragrid ora.asm ora.asm.type ONLINE ONLINE on oragrid ora.cssd ora.cssd.type ONLINE ONLINE on oragrid ora.diskmon ora.diskmon.type OFFLINE OFFLINE ora.evmd ora.evm.type ONLINE ONLINE on oragrid ora.ons ora.ons.type OFFLINE OFFLINE [grid@oragrid grid]$




SQL> ALTER SYSTEM SET asm_diskstring='/dev/sd*' SCOPE=MEMORY;

System altered.


CREATE DISKGROUP DATA EXTERNAL REDUNDANCY disk '/dev/sdb1' ;SQL> SQL>


Diskgroup created.

SQL> SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
[grid@oragrid grid]$ asmcmd -p
ASMCMD [+] > ls -s
Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
   512             512   4096  1048576     15359    15297                0           15297              0             N  DATA/
ASMCMD [+] > exit
[grid@oragrid grid]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 15 15:43:10 2026
Version 19.25.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0

SQL> 

set lines 999;
col diskgroup for a15
col diskname for a15
col path for a35
select a.name DiskGroup,b.name DiskName, b.total_mb, (b.total_mb-b.free_mb) Used_MB, b.free_mb,b.path,b.header_status
from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number
order by b.group_number,b.name;  




DISKGROUP       DISKNAME          TOTAL_MB    USED_MB    FREE_MB PATH                                HEADER_STATU
--------------- --------------- ---------- ---------- ---------- ----------------------------------- ------------
DATA            DATA_0000            15359         62      15297 /dev/sdb1                           MEMBER

SQL> CREATE PFILE='/tmp/asm_init.ora' FROM MEMORY;

File created.

SQL> CREATE SPFILE='+DATA/asmspfile.ora' FROM PFILE='/tmp/asm_init.ora';

File created.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
[grid@oragrid grid]$
[grid@oragrid grid]$ srvctl modify asm -spfile "+DATA/asmspfile.ora"


 srvctl config asm
ASM home: <CRS home>
Password file:
Backup of Password file:
ASM listener: LISTENER
Spfile: +DATA/asmspfile.ora
ASM diskgroup discovery string: /dev/sd*
[grid@oragrid grid]$









./asm_disk_lun_map2.sh | awk 'BEGIN{OFS="\t"} NR==1{print;next} {gsub(/,/,"\n\t"); print}'

[grid@965647-5sorc03 ~]$ cat asm_disk_lun_map2.sh
#!/bin/bash
# asm_disk_lun_map_clean.sh
# Maps ASM disks to OS devices and diskgroup in a clean format

echo -e "ASM_DISK\tDEVICES\tDISKGROUP\tHEADER_STATUS\tMOUNT_STATUS"
echo "---------------------------------------------------------------------"

for disk in $(oracleasm listdisks); do
    # Get all OS devices for this ASM disk
    dev_list=$(oracleasm querydisk -p $disk 2>/dev/null | grep '^/dev' | sed 's/:.*//' | tr '\n' ',' | sed 's/,$//')

    # Skip if no devices found
    [ -z "$dev_list" ] && dev_list="N/A"

    # Query ASM for diskgroup, header_status, mount_status
    read diskgroup header mount <<<$(sqlplus -s / as sysasm <<EOF
SET HEADING OFF FEEDBACK OFF PAGESIZE 0 VERIFY OFF
SELECT dg.name, d.header_status, d.mount_status
FROM v\$asm_disk d
JOIN v\$asm_diskgroup dg ON d.group_number = dg.group_number
WHERE d.name='$disk';
EXIT;
EOF
)
    diskgroup=$(echo $diskgroup | xargs)
    header=$(echo $header | xargs)
    mount=$(echo $mount | xargs)

    # Print clean table row
    echo -e "$disk\t$dev_list\t$diskgroup\t$header\t$mount"
done







size for d in 2026_08_{11..15}; do asmcmd du "+reco/VIHAAN/ARCHIVELOG/$d" done total ------ total=0 for d in 2026_08_{11..15}; do mb=$(asmcmd du "+reco/VIHAAN/ARCHIVELOG/$d" | awk 'NR==2 {print $1}') total=$((total + mb)) done echo "Total Used_MB = $total" echo "Total GB = $(awk "BEGIN {printf \"%.2f\", $total/1024}")"


==============================


for d in /dev/oracleasm/disks/*; do echo "===== $d =====" readlink -f "$d" lsblk -no NAME,TYPE,SIZE,WWN "$(readlink -f "$d")" 2>/dev/null done






Thursday, 7 June 2012


Oracle 10g Active Session  History    


                                                                     
                                                       
                                                                     
                                             



set pagesize 200
col EVENT format a40
SELECT DECODE (session_state, 'WAITING', event, NULL) event, session_state, COUNT(*), 
SUM (time_waited) time_waited 
FROM v$active_session_history 
WHERE sample_time > SYSDATE - 1/24 
GROUP BY DECODE (session_state, 'WAITING', event, NULL), 
session_state ORDER BY time_waited DESC;

EVENT                                    SESSION   COUNT(*) TIME_WAITED
---------------------------------------- ------- ---------- -----------
reliable message                         WAITING         20    18927347
gcs log flush sync                       WAITING          1       29291
control file sequential read             WAITING          5       18952
control file parallel write              WAITING          2       10017
log file sync                            WAITING          1        7060
log file parallel write                  WAITING          1        6561
gc cr block 2-way                        WAITING          2        1340
                                         ON CPU         122           0
null event                               WAITING          1           0

9 rows selected.

Top CPU consuming Session in last 5 minutes? 

Select        session_id,
              count(*)
from          v$active_session_history
where         session_state= 'ON CPU' and
              SAMPLE_TIME > sysdate - (5/(24*60))
group by      session_id
order by     count(*) desc;

 SESSION_ID   COUNT(*)
 ---------- ----------
       2670         12
       2755          1
       2701          1

Top Waiting Session  in last 5 minutes

 Select           session_id,
                  count(*)
 from             v$active_session_history
 where            session_state='WAITING'  and
                  SAMPLE_TIME >  SYSDATE - (5/(24*60))
 group by         session_id
 order by         count(*) desc;

 SESSION_ID   COUNT(*)
 ---------- ----------
       2738          1
       
       

Investigate one session running that module


SQL> select distinct module from v$active_session_history ;

MODULE
------------------------------------------------
sqlplus@********** (TNS V1-V3)
backup incr datafile
backup full datafile
DBMS_SCHEDULER
w4wp.exe
SEVERITY EVALUATION
racgimon@********** (TNS V1-V3)
restore full datafile
backup archivelog
Oracle Enterprise Manager.rollup
rman@********** (TNS V1-V3)
MMON_SLAVE
racgimon@****** (TNS V1-V3)
15 rows selected.
col EVENT format a40
SELECT DECODE (session_state, 'WAITING', event, NULL)   event,  session_state, COUNT(*) counter , 
SUM (time_waited) time_waited 
FROM v$active_session_history 
WHERE 1=1 
-- and module = 'w4wp.exe' 
AND sample_time > SYSDATE - 1/24  
--AND session_id = 276 
GROUP BY DECODE (session_state, 'WAITING', event, NULL), session_state   
ORDER BY time_waited DESC;
 
 EVENT                                    SESSION    COUNTER TIME_WAITED
 ---------------------------------------- ------- ---------- -----------
 reliable message                         WAITING         20    18927347
 gcs log flush sync                       WAITING          1       29291
 control file sequential read             WAITING          5       18952
 control file parallel write              WAITING          2       10017
 log file sync                            WAITING          1        7060
 log file parallel write                  WAITING          1        6561
 gc cr block 2-way                        WAITING          2        1340
                                          ON CPU         124           0
 null event                               WAITING          1           0
 9 rows selected.

Join with v$sqlarea and include sql_stms for that session

col sqls format a50
col EVENT_NAME format a25
SELECT  substr(b.sql_text,1,50) sqls, b.sql_id, DECODE (a.session_state, 'WAITING', a.event, NULL) "EVENT_NAME", a.session_state, COUNT(*) counter , 
SUM (a.time_waited) time_waited 
FROM v$active_session_history a  , V$SQLAREA b 
WHERE a.sample_time > SYSDATE - 1/24  
AND a.SQL_ID = b.SQL_ID 
-- AND a.module = '  ' 
-- AND  a.session_id = 276 
GROUP BY b.sql_text, b.sql_id,
DECODE (a.session_state, 'WAITING', a.event, NULL), a.session_state 
ORDER BY time_waited DESC;

SQLS                                               SQL_ID        EVENT_NAME                SESSION    COUNTER TIME_WAITED
-------------------------------------------------- ------------- ------------------------- ------- ---------- -----------
SELECT COUNT(*) FROM MGMT_METRIC_DEPENDENCY_DETAIL dj1q81rxhr7w3                           ON CPU          15           0
SELECT overtime3.*, LU1.title_txt as ratetitle, LU dp14mt0pcd9qz                           ON CPU           1           0
SELECT DECODE (session_state, :"SYS_B_0", event, N a7srcjcxc5ndx                           ON CPU           2           0
SELECT  substr(b.sql_text,:"SYS_B_0",:"SYS_B_1") s apzsyc42mzacp                           ON CPU           1           0
SELECT  b.sql_text, DECODE (a.session_state, :"SYS dwjwa5969hfjc                           ON CPU           1           0
select decode(nvl(to_char(s.sid),-:"SYS_B_00"),-:" dzhnbx1xydp8x                           ON CPU           5           0
select      ash.session_id,      ash.session_seria fuusjf8tkp10y                           ON CPU           3           0
SELECT CURRENT_STATUS FROM MGMT_CURRENT_AVAILABILI 661tvbvnac891                           ON CPU          10           0
SELECT OP.*******_NUM FROM *******_POSITION OP WHE gxmzbfrhsxt65                           ON CPU          24           0
 Select           session_id,                   co 712dz9xh6rx44                           ON CPU           1           0
SELECT  substr(b.sql_text,:"SYS_B_0",:"SYS_B_1"),  02fw2pw2718yk                           ON CPU           1           0
SELECT DECODE (session_state, :"SYS_B_0", event, N b218g0qq3hwm0                           ON CPU           1           0
select ash.SQL_ID , sum(decode(ash.session_state,: 41asd2tscsmcd                           ON CPU           1           0
 select decode(nvl(to_char(s.sid),-:"SYS_B_00"),-: fps86p3ur4qu2                           ON CPU           3           0
DECLARE job BINARY_INTEGER := :job; next_date DATE 6gvch1xu9ca3g                           ON CPU           7           0
select      ash.session_id,      ash.session_seria 5g2qccc23aun2                           ON CPU           9           0
SELECT DECODE (session_state, :"SYS_B_0", event, N 42cgt5qz22z0a                           ON CPU           1           0
17 rows selected.

What SQL is currently using the most resources? 

col sqls format a50
col USERNAME format a20
SELECT active_session_history.user_id,dba_users.username, substr(sqlarea.sql_text,1,50) sqls , 
-- sqlarea.sql_text, 
active_session_history.SQL_ID,
SUM(active_session_history.wait_time + active_session_history.time_waited) total_wait_time 
FROM v$active_session_history active_session_history, 
v$sqlarea sqlarea, dba_users 
WHERE active_session_history.sample_time BETWEEN SYSDATE - 60/2880 AND SYSDATE 
AND active_session_history.sql_id = sqlarea.sql_id 
AND active_session_history.user_id = dba_users.user_id 
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username,active_session_history.SQL_ID
ORDER BY 4;

  USER_ID USERNAME             SQLS                                               SQL_ID        TOTAL_WAIT_TIME
---------- -------------------- -------------------------------------------------- ------------- ---------------
         0 SYS                  SELECT  substr(b.sql_text,:"SYS_B_0",:"SYS_B_1"),  02fw2pw2718yk               3
        29 SYSMAN               SELECT CURRENT_STATUS FROM MGMT_CURRENT_AVAILABILI 661tvbvnac891             596
        29 SYSMAN               DECLARE job BINARY_INTEGER := :job; next_date DATE 6gvch1xu9ca3g             264
         0 SYS                  SELECT  substr(b.sql_text,:"SYS_B_0",:"SYS_B_1") s apzsyc42mzacp               3
         0 SYS                  SELECT DECODE (session_state, :"SYS_B_0", event, N b218g0qq3hwm0               1
        29 SYSMAN               SELECT COUNT(*) FROM MGMT_METRIC_DEPENDENCY_DETAIL dj1q81rxhr7w3             812
      6548 *****               SELECT overtime3.*, LU1.title_txt as ratetitle, LU dp14mt0pcd9qz              27
         0 SYS                  SELECT  b.sql_text, DECODE (a.session_state, :"SYS dwjwa5969hfjc               4
      6548 CAR                  SELECT OP.*******_NUM FROM *******_POSITION OP WHE gxmzbfrhsxt65            8428
9 rows selected.


What object is currently causing the highest resource waits?


set linesize 150
col OBJECT_NAME format a28
col EVENT format a20
SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event, SUM(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history, dba_objects 
WHERE active_session_history.sample_time BETWEEN SYSDATE - 60/2880 AND SYSDATE 
AND active_session_history.current_obj# = dba_objects.object_id 
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event 
ORDER BY ttl_wait_time DESC;

 OBJECT_NAME                  OBJECT_TYPE         EVENT                TTL_WAIT_TIME
 ---------------------------- ------------------- -------------------- -------------
 SCHEDULER$_GLOBAL_ATTRIBUTE  TABLE               reliable message           7812784
 SCHEDULER$_GLOBAL_ATTRIBUTE  TABLE                                          5878998
 SCHEDULER$_JOB               TABLE                                          4892625
 SCHEDULER$_JOB               TABLE               enq: JS - queue lock          4212
 SCHEDULER$_JOB               TABLE               gc cr block 2-way              729
 I_LINK1                      INDEX                                                6
 I_PROFILE                    INDEX                                                4
 7 rows selected.
 
Top SQL by CPU usage, wait time and IO time

     select
     ash.SQL_ID ,
     sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
     sum(decode(ash.session_state,'WAITING',1,0))  -
     sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0))    "WAIT" ,
     sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0))    "IO" ,
     sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash, v$event_name en
where SQL_ID is not NULL  and en.event#=ash.event#
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1))   desc

 SQL_ID               CPU       WAIT         IO      TOTAL
 ------------- ---------- ---------- ---------- ----------
 4gd6b1r53yt88          0       1284          0       1284
 8szmwam7fysa3          0         16         81         97
 fp6yq2gqh1x55          0         10          0         10
 8p8yrmpfknn3f          0          2          8         10
 apaj3xcy1arks          0          5          0          5
 2wxbajru4agjp          0          5          0          5
 b6usrg82hwsa3          0          5          0          5
 89qyn4bbt03jq          0          0          3          3
 4y1y43113gv8f          0          2          1          3
 4ph7h2jrsquwh          0          2          0          2
 33y4yxzq1ms0d          0          2          0          2

 
Top SESSION by CPU usage, wait time and IO time  !!!!!!!!!!!

col PROGRAM format a30
select
     ash.session_id,
     ash.session_serial#,
     ash.user_id,
     ash.program,
     sum(decode(ash.session_state,'ON CPU',1,0))       "CPU",
     sum(decode(ash.session_state,'WAITING',1,0)) -
     sum(decode(ash.session_state,'WAITING',
        decode(en.wait_class,'User I/O',1, 0 ), 0))    "WAITING" ,
     sum(decode(ash.session_state,'WAITING',
        decode(en.wait_class,'User I/O',1, 0 ), 0))    "IO" ,
     sum(decode(session_state,'ON CPU',1,1))           "TOTAL"
from v$active_session_history ash,v$event_name en
where en.event# = ash.event#
group by session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) desc

 SESSION_ID SESSION_SERIAL#    USER_ID PROGRAM                               CPU    WAITING         IO      TOTAL
 ---------- --------------- ---------- ------------------------------ ---------- ---------- ---------- ----------
       2676           28638          0 rman@********** (TNS V1-V3)             0      85895          0      85895
       2670            8769          0 rman@********** (TNS V1-V3)             0      18599          0      18599
       2738               1          0 oracle@********** (MMON)                0       1284          0       1284
       2676           47346          0 rman@********** (TNS V1-V3)             0        834          0        834
       2752               1          0 oracle@********** (LMON)                0        272          0        272
       2743               1          0 oracle@********** (LGWR)                0        269          0        269
       2742               1          0 oracle@********** (CKPT)                0        123          0        123

Top SQL with Username and connection status Top SQL with Username and connection status

col  NAME      format a20
col  PROGRAM   format a30
col "STATUS"   format a15
select decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED') "STATUS",
topsession.session_id   "SESSION_ID",
u.name                  "NAME",
topsession.program      "PROGRAM",
max(topsession.CPU)     "CPU",
max(topsession.WAITING) "WAITING",
max(topsession.IO)      "IO",
max(topsession.TOTAL)   "TOTAL"
from ( select
ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,
sum(decode(session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash, v$event_name en
where en.event# = ash.event#
group by session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) ) topsession, v$session s, user$ u
where u.user# =topsession.user_id 
and
-- /* outer join to v$session because the session might be disconnected */ 
topsession.session_id = s.sid (+) 
and topsession.session_serial# = s.serial# (+)
and u.name !='SYS'
group by topsession.session_id, topsession.session_serial#, topsession.user_id, topsession.program, s.username,s.sid,s.paddr,u.name
order by max(topsession.TOTAL) desc



 STATUS          SESSION_ID NAME                 PROGRAM                               CPU    WAITING         IO      TOTAL
 --------------- ---------- -------------------- ------------------------------ ---------- ---------- ---------- ----------
 DISCONNECTED          2687 SYSMAN               oracle@********** (J000)                0          0          3          3
 DISCONNECTED          2687 *****                oracle@********** (J000)                0          1          0          1
 DISCONNECTED          2670 SYSMAN               oracle@********** (J000)                0          1          0          1
 DISCONNECTED          2687 *****                oracle@********** (J000)                0          1          0          1
 DISCONNECTED          2703 *****                oracle@********** (J000)                0          1          0          1
 DISCONNECTED          2687 SYSMAN               oracle@********** (J000)                0          1          0          1
 DISCONNECTED          2683 SYSMAN               oracle@********** (J000)                0          0          1          1
7 rows selected.

The Power of ASH (Active Session History)
Oracle 10g, brings many new features through which one can easily tune the bad sqls or also can diagnose the database performance issues.Using database metrics, active session history and time model views.Following query fetchs top sqls spent more on cpu/wait/io. 

select
ash.SQL_ID ,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by ash.SQL_ID

SQL_ID               CPU       WAIT         IO      TOTAL
------------- ---------- ---------- ---------- ----------
gb4qjzp9su4h4          0          1          0          1
18naypzfmabd6          0          0          1          1
5qkswwgj544dx          0          1          0          1
8szmwam7fysa3          0         16         81         97
bj8hx2dgty2pp          0          0          1          1
7xgrmvd2nnjn6          0          1          0          1
1un0npcp105qq          0          1          0          1

Get the sql_text for above sql_id.

SELECT sql_text FROM v$sqlarea WHERE sql_id = '8szmwam7fysa3';
dbms_xplan.display_awr can be used to extract the sql plan for this sql_id.
SELECT * FROM table(dbms_xplan.display_awr('8szmwam7fysa3'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8szmwam7fysa3
--------------------
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,  NULL,
'FALSE', :5, 'FALSE'))
Plan hash value: 1947143079
-------------------------------------------------------------------------
| Id  | Operation                         | Name                | Cost  |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |                     |    24 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| OBJECT_GROWTH_TREND |       |
-------------------------------------------------------------------------
Note
-----
   - cpu costing is off (consider enabling it)
20 rows selected.
  

set pagesize 32000 
set linesize 200 
column sid format a10 
column username format a10 
column terminal format a10 
column sql_text format a90 wrap 
column OPERATION format a30 
column OBJECT format a30 
break on SID  
Select ''''||s.sid||','||s.serial#||'''' SID, 
CASE  
  WHEN (E.OPERATION like '%STATEMENT') then Q.SQL_TEXT 
  else LPAD(' ',E.DEPTH*2,' ')||RTRIM(REPLACE(E.OPERATION||'/'||E.OPTIONS||'/'||E.OBJECT_OWNER||'.'||E.OBJECT_NAME,'//','/')||'/'||to_char(E.PARTITION_START)||'-'||to_char(E.PARTITION_STOP),'/-.') 
END SQL_TEXT 
--,E.OBJECT_NODE 
--,E.OPTIMIZER 
--,E.ID 
--,E.PARENT_ID 
--,E.DEPTH 
--,E.COST 
--,E.CARDINALITY 
--,E.BYTES 
--,E.OTHER_TAG 
--,E.PARTITION_START 
--,E.PARTITION_STOP 
--,E.PARTITION_ID 
--,E.OTHER 
--,E.DISTRIBUTION 
,E.CPU_COST 
,E.IO_COST 
From v$sql_plan E, v$sql Q, V$SESSION S 
Where E.address=Q.address(+) 
and E.hash_value=Q.hash_value(+) 
and E.child_number=Q.child_number(+) 
and E.address=S.sql_address  
order by s.sid,e.ID 
/  

SID                                               SQL COMMAND                                           CPU_COST    IO_COST
---------- ------------------------------------------------------------------------------------------ ---------- ----------
'2701,1097 Select :"SYS_B_00"||s.sid||:"SYS_B_01"||s.serial#||:"SYS_B_02" SID, CASE   WHEN (E.OPERATI
3'         ON like :"SYS_B_03") then Q.SQL_TEXT   else LPAD(:"SYS_B_04",E.DEPTH*:"SYS_B_05",:"SYS_B_0
           6")||RTRIM(REPLACE(E.OPERATION||:"SYS_B_07"||E.OPTIONS||:"SYS_B_08"||E.OBJECT_OWNER||:"SYS
           _B_09"||E.OBJECT_NAME,:"SYS_B_10",:"SYS_B_11")||:"SYS_B_12"||to_char(E.PARTITION_START)||:
           "SYS_B_13"||to_char(E.PARTITION_STOP),:"SYS_B_14") END SQL_TEXT --,E.OBJECT_NODE --,E.OPTI
           MIZER --,E.ID --,E.PARENT_ID --,E.DEPTH --,E.COST --,E.CARDINALITY --,E.BYTES --,E.OTHER_T
           AG --,E.PARTITION_START --,E.PARTITION_STOP --,E.PARTITION_ID --,E.OTHER --,E.DISTRIBUTION
            ,E.CPU_COST ,E.IO_COST From v$sql_plan E, v$sql Q, V$SESSION S Where E.address=Q.address(
           +) and E.hash_value=Q.hash_value(+) and E.child_number=Q.child_number(+) and E.address=S.s
           ql_address order by s.sid,e.ID
             SORT/ORDER BY                                                                              16549005          0
               NESTED LOOPS/OUTER                                                                         391415          0
                 NESTED LOOPS                                                                             387615          0
                   NESTED LOOPS                                                                           383815          0
                     FIXED TABLE/FULL/SYS.X$KSUSE                                                         380315          0
                     FIXED TABLE/FIXED INDEX/SYS.X$KSLED (ind:2)                                            3500          0
                   FIXED TABLE/FIXED INDEX/SYS.X$KQLFXPL (ind:2)                                            3800          0
                 FIXED TABLE/FIXED INDEX/SYS.X$KGLCURSOR_CHILD (ind:1)                                      3800          0


 
 
#CPU Time and OS process
COLUMN sid                     FORMAT 99999          HEADING 'SID'
COLUMN oracle_username         FORMAT a12            HEADING 'DB User'         JUSTIFY right
COLUMN os_username             FORMAT a12            HEADING 'OS User'         JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING 'Session Program' 
COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN session_cpu             FORMAT 9,999,999,999  HEADING 'CPU(ms/s)'
COLUMN sql_text                FORMAT a40            HEADING 'SQL COMMAND'     JUSTIFY center
SET pagesize 200
SELECT   s.sid sid
       , lpad(s.username,12)  oracle_username
       , lpad(s.osuser,9)     os_username
       , s.program            session_program
       , lpad(s.machine,8)    session_machine
       , (select ss.value/1000 from v$sesstat ss, v$statname sn
 where ss.sid = s.sid 
 and   sn.statistic# = ss.statistic# and
       sn.name = 'CPU used by this session') session_cpu,sql_text
FROM   v$sqlarea sqlarea, v$session s
WHERE  s.sql_address    = sqlarea.address
ORDER BY session_cpu desc; 

   SID      DB User      OS User Session Program     Machine      CPU(ms/s)               SQL COMMAND
------ ------------ ------------ ------------------ -------- -------------- ----------------------------------------
  2701          SYS    oracle    sqlplus@***001**db ***001m*             93 SELECT   s.sid sid        , lpad(s.usern
                                  (TNS V1-V3)                               ame,:"SYS_B_0")  oracle_username
                                                                            , lpad(s.osuser,:"SYS_B_1")     os_usern
                                                                            ame        , s.program            sessio
                                                                            n_program        , lpad(s.machine,:"SYS_
                                                                            B_2")    session_machine        , (selec
                                                                            t ss.value/:"SYS_B_3" from v$sesstat ss,
                                                                             v$statname sn  where ss.sid = s.sid  an
                                                                            d   sn.statistic# = ss.statistic# and
                                                                                 sn.name = :"SYS_B_4") session_cpu,s
                                                                            ql_text FROM   v$sqlarea sqlarea, v$sess
                                                                            ion s WHERE  s.sql_address    = sqlarea.
                                                                            address ORDER BY session_cpu desc


set linesize 200 
set pagesize 32000 
column sid format a13 
column username format a10 
column terminal format a10 
column sql_text format a90 wrap 
column OPERATION format a30 
column OBJECT format a30 
break on SID  
Select ''''||s.sid||','||s.serial#||'''' SID, 
CASE  
  WHEN (E.OPERATION like '%STATEMENT') then Q.SQL_TEXT 
  else LPAD(' ',E.DEPTH*2,' ')||RTRIM(REPLACE(E.OPERATION||'/'||E.OPTIONS||'/'||E.OBJECT_OWNER||'.'||E.OBJECT_NAME,'//','/')||'/'||to_char(E.PARTITION_START)||'-'||to_char(E.PARTITION_STOP),'/-.') 
END SQL_TEXT 
,E.CPU_COST 
,E.IO_COST 
From v$sql_plan E, v$sql Q, V$SESSION S 
Where E.address=Q.address(+) 
and E.hash_value=Q.hash_value(+) 
and E.child_number=Q.child_number(+) 
and E.address=S.sql_address  
and sid=:X_SID --------- <<<<<<<<<<<
order by s.sid,e.ID 
/  

SID                                                  SQL COMMAND                                           CPU_COST    IO_COST
------------- ------------------------------------------------------------------------------------------ ---------- ----------
'2701,10973'  Select :"SYS_B_00"||s.sid||:"SYS_B_01"||s.serial#||:"SYS_B_02" SID, CASE   WHEN (E.OPERATI
              ON like :"SYS_B_03") then Q.SQL_TEXT   else LPAD(:"SYS_B_04",E.DEPTH*:"SYS_B_05",:"SYS_B_0
              6")||RTRIM(REPLACE(E.OPERATION||:"SYS_B_07"||E.OPTIONS||:"SYS_B_08"||E.OBJECT_OWNER||:"SYS
              _B_09"||E.OBJECT_NAME,:"SYS_B_10",:"SYS_B_11")||:"SYS_B_12"||to_char(E.PARTITION_START)||:
              "SYS_B_13"||to_char(E.PARTITION_STOP),:"SYS_B_14") END SQL_TEXT ,E.CPU_COST ,E.IO_COST Fro
              m v$sql_plan E, v$sql Q, V$SESSION S Where E.address=Q.address(+) and E.hash_value=Q.hash_
              value(+) and E.child_number=Q.child_number(+) and E.address=S.sql_address and sid=:"SYS_B_
              15" order by s.sid,e.ID
                SORT/ORDER BY                                                                              16172243          0
                  NESTED LOOPS/OUTER                                                                          14653          0
                    NESTED LOOPS                                                                              10853          0
                      NESTED LOOPS                                                                             7053          0
                        FIXED TABLE/FIXED INDEX/SYS.X$KSUSE (ind:1)                                            3553          0
                        FIXED TABLE/FIXED INDEX/SYS.X$KSLED (ind:2)                                            3500          0
                      FIXED TABLE/FIXED INDEX/SYS.X$KQLFXPL (ind:2)                                            3800          0
                    FIXED TABLE/FIXED INDEX/SYS.X$KGLCURSOR_CHILD (ind:1)                                      3800          0
9 rows selected.



For Sql Text ....

SELECT sql_text FROM v$sqlarea WHERE sql_id = '8szmwam7fysa3’'


For explain Plan 

SELECT * FROM table(dbms_xplan.display_awr('8szmwam7fysa3'));



set linesize 200 
set pagesize 32000 
column sid format a10 
column username format a10 
column terminal format a10 
column sql_text format a40 wrap 
Select ''''||s.sid||','||s.serial#||'''' SID 
  ,s.status 
  ,s.username 
  ,s.terminal 
  ,q.SQL_TEXT 
  ,q.command_type 
from  v$session s  
    ,(select distinct  
    address 
   ,sql_text 
   ,command_type  
  from v$sql) q 
  where  q.address=s.sql_address 
  and s.sid=:X_SID 
  order by s.sid 
/ 


Oracle Query sql_text in sql area,session,process,pid,cpu time,users Under Creative Commons License: Attribution 
 
 
set linesize 200
col "Snap Day" format a15
col "Sql" format a40
SELECT * 
FROM (SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "CPU Time" 
+ "Disk Reads" 
+ "Buffer Gets" 
+ "Writes" 
+ "Sorts" 
+ "Parses" DESC) AS "Rank", 
i1.* 
FROM (SELECT TO_CHAR (hs.begin_interval_time,'MM/DD/YY') "Snap Day", 
shs.sql_id                                                "Sql id", 
-- REPLACE (CAST (DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40) ), 
-- CHR (10), '') "Sql", 
SUM (shs.executions_delta) "Execs", 
ROUND ( (SUM (shs.elapsed_time_delta) / 1000000) / SUM (shs.executions_delta),1) "Time Ea Sec", 
ROUND ( (SUM (shs.cpu_time_delta) / 1000000) / SUM (shs.executions_delta), 1)    "CPU Ea Sec", 
-- ROUND ( (SUM (shs.iowait_delta) / 1000000)/ SUM (shs.executions_delta),1)     "IO/Wait Ea Sec", 
SUM (shs.cpu_time_delta)      "CPU Time", 
SUM (shs.disk_reads_delta)    "Disk Reads", 
SUM (shs.buffer_gets_delta)   "Buffer Gets", 
SUM (shs.direct_writes_delta) "Writes", 
SUM (shs.parse_calls_delta)   "Parses", 
SUM (shs.sorts_delta)         "Sorts", 
SUM (shs.elapsed_time_delta)  "Elapsed" 
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht 
ON (sht.sql_id = shs.sql_id) 
INNER JOIN dba_hist_snapshot hs 
ON (shs.snap_id = hs.snap_id) 
HAVING SUM (shs.executions_delta) > 0 
GROUP BY shs.sql_id, 
TO_CHAR (hs.begin_interval_time, 'MM/DD/YY'), 
CAST (DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40) 
) 
ORDER BY "Snap Day" DESC) i1 
ORDER BY "Snap Day" DESC) 
WHERE "Rank" <= 20 
AND "Snap Day" = TO_CHAR (SYSDATE, 'MM/DD/YY'); 
 
      Rank Snap Day        Sql id             Execs Time Ea Sec CPU Ea Sec   CPU Time Disk Reads Buffer Gets     Writes     Parses      Sorts    Elapsed
---------- --------------- ------------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------
         1 05/28/12        6gvch1xu9ca3g       1560         1.5        1.5 2313611092       2928   273553140          0       1560          0 2328252252
         2 05/28/12        dj1q81rxhr7w3   24793080           0          0  901691280          0   223137720          0       1560          0  901691280
         3 05/28/12        brcgd2t9fv9j4        312           2          2  614230556          0     5401968          0        312          0  614230556
         4 05/28/12        gxmzbfrhsxt65        312           2          2  614102356          0     5401968          0        312        624  614102356
         5 05/28/12        661tvbvnac891   24799320           0          0  304841546          0    49598640          0       3120          0  304841546
         6 05/28/12        bunssq950snhf         52         1.4        1.4   74053802          8         578          0         52         52   74110932
         7 05/28/12        d2w5sd5tug7xf         22        10.1        2.5   54123262     110656    11337998        586         22        194  221471924
         8 05/28/12        84qubbrsr0kfn         52          .6         .6   31550544        494        7960          0      
         


10g or higher: Find Sessions with the Highest DB Time
       
       
       set lines 300
       col module format a50
       col event format a40
       col username format a15
       SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, se.event, s.module, se.time_waited
       FROM v$session_event se, v$session s, v$process p
       where s.last_call_et < 1800 -- active within last 1/2 hour
       AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
       AND se.sid = s.sid
       AND s.paddr = p.addr
       ORDER BY se.time_waited desc
        
       
       SID    SERIAL# OS PID       USERNAME        MODULE          DB Time
       (sec) CPU Time (sec)      % CPU
---------- ---------- ------------ --------------- --------------- -------------------- -------------- ----------
      2284      33483 27025        SMRCA           w3wp.exe                        6.04           4.21       69.7
      2117      21985 26847        SYS             sqlplus@***001m                  .03           1.18    3933.33
                                                   tdb (TNS V1-V3)

      2074      33314 26839        SMRCA           w3wp.exe                        6.72           4.22       62.8
      2653      33104 26816        SMRCA                                            .33            .04      12.12
      2132      49296 26812        SMRCA           w3wp.exe                         .01              0          0
      1963        587 13537        SMRCA                                            .68             .4      58.82
      2210      10798 26354        SMRCA                                           7.37           4.05      54.95
      2290      59823 26352        SMRCA           w3wp.exe                         .01              0          0
      1900      22304 26287        SMRCA                                            .31            .22      70.97
      2654      33047 26281        SMRCA            
      
      





set term off feed off echo off tab off

col legacy_db new_value legacy_db noprint
select decode(substr(version,1,instr(version,'.')-1),9,'--',10,'--',11,'--','') legacy_db from v$instance;
set term on feed on



set linesize 400 pagesize 300
col sample_time format a25
col sql_exec_start format a25
col sql_id format a13
col inst_id format 9999 head 'INST|ID'
col con_id format 999 head 'CON|ID'

alter session set nls_timestamp_format = 'dd-mm-yyyy hh24:mi:ss.ff';
alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';

-- this is problematic on a legacy db, as we cannot comment out a sqlplus command with a substitution variable
--&&legacy_db break on sql_id skip 1 on con_id on inst_id on sql_exec_id on seconds
break on sql_id skip 1 on con_id on inst_id on session_id on sql_exec_id on seconds


with data as (
	select
	 	sql_id
		&legacy_db , con_id
		, inst_id
		, session_id
		, sql_exec_id
		, count(*) over (partition by sql_id
			&legacy_db , con_id
			, inst_id, session_id, sql_exec_id  ) seconds
		, row_number() over (partition by sql_id
			&legacy_db , con_id
			, inst_id, session_id, sql_exec_id order by sql_exec_id, sample_id ) row#
		, sample_id
		, sql_exec_start
		, sample_time
		, sql_plan_operation
	from gv$active_session_history
	where sql_id is not null
		and sample_id is not null
		and sql_exec_id is not null
	and sql_id  = 'gxg6739srrmd7'
)
select *
from data
where seconds > 1
order by  sql_id
	&legacy_db , con_id
	, inst_id, sql_exec_id ,sample_id
/

===


set linesize 200 trimspool on  pagesize 100


define 1='fm0dqh3ynwscr'
col u_sql_id new_value u_sql_id noprint
var v_sql_id varchar2(13) 

col event for a0

set term off feed off verify off

select '&1' u_sql_id from dual;

set term on feed on

exec :v_sql_id := '&u_sql_id'

break on session_id on sql_exec_id skip 1

with sql_data as (
	select inst_id, sql_id
		, session_id, session_serial#, sql_exec_id
		, nvl(event,'CPU') event
	from gv$active_session_history h
	where sql_id = :v_sql_id
		and sql_exec_id is not null
	order by
		inst_id
		, sql_id
		, session_id
		, session_serial#
		, sql_exec_id
)
select 
	session_id, sql_exec_id
	, event
	, count(event) event_count
from sql_data
group by 
	session_id, sql_exec_id	, event
order by 
	session_id, sql_exec_id
	, event_count
/


====

define 1='fm0dqh3ynwscr'
DEF   3="TIMESTAMP'2025-05-10 07:15:00'"
DEF   4="TIMESTAMP'2025-05-13 07:15:00'"

define v_event_filter=''

col u_sql_id new_value u_sql_id noprint
var v_sql_id varchar2(13) 

set linesize 200 trimspool on pagesize 100
col sample_time format a28
set term off feed off verify off
--col event for 20
col EVENT for a25
select '&1' u_sql_id from dual;
set term on feed on
exec :v_sql_id := '&u_sql_id'
break on session_id  skip 1

with blocked as (
	select distinct h.instance_number, h.dbid, h.blocking_session, h.blocking_session_serial#,  h.snap_id, h.sample_id, h.sql_id
	from dba_hist_active_sess_history h
	join dba_hist_snapshot s on s.snap_id = h.snap_id
		and s.instance_number = h.instance_number
		and s.dbid = h.dbid
	where h.blocking_session is not null
	and s.begin_interval_time 
between &3 and &4
	--and h.event like '&v_event_filter'
),
blockers as (
	select distinct
		max(blkr.snap_id) over (partition by blkr.session_id, blkr.session_serial#, blkr.event) snap_id
		, max(blkr.sample_id) over (partition by blkr.session_id, blkr.session_serial#, blkr.event) sample_id
		, max(blkr.sample_time) over (partition by blkr.session_id, blkr.session_serial#, blkr.event) sample_time
		, count(blkr.sample_time) over (partition by blkr.session_id, blkr.session_serial#, blkr.event) event_count
		, b.sql_id
		, blkr.session_id
		, blkr.session_serial#
		, blkr.session_state
		-- NULL for top level blockers
		, blkr.blocking_session
		, blkr.event
		--, blkr.time_waited
	from blocked b
	join dba_hist_active_sess_history blkr
		on b.snap_id = blkr.snap_id
		and b.dbid = blkr.dbid
		and b.instance_number = blkr.instance_number
		and b.sample_id = blkr.sample_id
		and b.blocking_session = blkr.session_id
		and b.blocking_session_serial# = blkr.session_serial#
		--and blkr.blocking_session is null
)
select *
from blockers
order by sample_id, event, event_count
/





Oracle DBA

anuj blog Archive