Sunday, 17 June 2012

Oracle 10g physical standby log gap check via shell script

#!/usr/bin/ksh 
. /u01/app/ora/.profile 
export ORAENV_ASK=NO 
export PATH=${PATH}:/usr/local/bin 
export ORACLE_HOME=/u01/app/ora/10g 
export ORACLE_BASE=/u01/app/ora 
export ORACLE_SID=spscfm1 
export ORACLE_HOSTNAME=SMI 
export TNS_ADMIN=${ORACLE_HOME}/network/admin 
export PATH=$PATH:$ORACLE_HOME/bin 
sqlplus -S "/ as sysdba" <<! 
set heading off 
set feedb off 
spool /u01/app/ora/admin/spscfm/logs/diff.log 
select (select name from V\$DATABASE),(select max(sequence#) from v\$archived_logwhere dest_id=1) Current_primary_seq,( select max(sequence#) from v\$archived_logwhere to_date(next_time,'dd-mm-yyyy') > sysdate-1 
and dest_id=2 ) max_stby,(select nvl((select max(sequence#) - min(sequence#) fromv\$archived_log 
where to_date(next_time,'dd-mm-yyyy') > sysdate-1 
and dest_id=2 and applied='NO'),0) from dual) "To be applied",((select max(sequence#) from v\$archived_log 
where dest_id=1) - (select max(sequence#) from v\$archived_log 
where dest_id=2)) "To be Shipped" 
from dual 
/ 
spool off 
! 
diff=`awk '{print $4}' /u01/app/ora/admin/spscfm/logs/diff.log` 
#echo $diff 
if [[ $diff -gt 0 ]]; 
then 
echo "
The figures show whether the logs have been successfully applied to the Standby. If the number in the subject is > 0 then this many logs haven't been applied to the Standby
Primary:***0001*** 
Standby:***0001***">>/u01/app/ora/admin/spscfm/logs/diff.log 
cat /u01/app/ora/admin/spscfm/logs/diff.log | /usr/bin/mailx -s " ###SY01POY - Standby Log difference `printf $diff` " "support.@.com"
 fi

Sunday, 10 June 2012

Oracle restore control file on standby for ASM

Control file restore

on primary

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/oradb01.standbycontrolfilename.ctl' REUSE;



on standby

rman>restore controlfile to '+DATA1/ORADB01/controlfile/Current.142.664657123' from /tmp/oradb1.standbycontrolfilename.ctl ;

Saturday, 9 June 2012

Oracle ASM useful Scripts

Oracle ASM useful Scripts :

ASM Scripts

http://anuj-singh.blogspot.com/2011/09/oracle-asm-disk-report.html for diskgroup info

cat /etc/sysconfig/oracleasm
rpm -qa | grep -i oracleasm 
ls -l /dev/oracleasm/disks

 ls -la /dev/oracleasm/disks
cat /proc/partitions

/usr/sbin/oracleasm listdisks

systemctl status oracleasm

 /sbin/blkid | grep oracleasm

 cat -n /etc/systemd/system/oracle-ohasd.service

oracleasm-discover

modinfo oracleasm
lsmod | grep oracleasm



SET LINESIZE  150
SET PAGESIZE  9999
SET VERIFY    off
COLUMN full_alias_path        FORMAT a63                  HEAD 'File Name'
COLUMN system_created         FORMAT a8                   HEAD 'System|Created?'
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes'
COLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space'
COLUMN type                   FORMAT a18                  HEAD 'File Type'
COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'
COLUMN striped                FORMAT a8                   HEAD 'Striped'
COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'
COLUMN disk_group_name        noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label ""              of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report
SELECT
    CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
  , bytes
  , space
  , NVL(LPAD(type, 18), '<DIRECTORY>')  type
  , creation_date
  , disk_group_name
  , LPAD(system_created, 4) system_created
FROM
    ( SELECT
          g.name               disk_group_name
        , a.parent_index       pindex
        , a.name               alias_name
        , a.reference_index    rindex
        , a.system_created     system_created
        , f.bytes              bytes
        , f.space              space
        , f.type               type
        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date
FROM  v$asm_file f ,v$asm_alias a ,v$asm_diskgroup g
where  (f.group_number(+) = a.group_number
and     f.file_number(+)  = a.file_number )
        and g.group_number(+) =f.group_number
      )
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
/

                                                                                                                                              System
File Name                                                                    Bytes              Space File Type          Creation Date        Created?
--------------------------------------------------------------- ------------------ ------------------ ------------------ -------------------- --------
+DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.731105699                            1,536          2,097,152   ASMPARAMETERFILE 30-SEP-2010 20:54:58    Y
+DATA/ORCL/DATAFILE/UNKNOWN.266.731227433                              104,865,792        214,958,080           DATAFILE 01-OCT-2010 06:43:53    Y
+DATA/ORCL/DATAFILE/SYSTEM.256.731226373                               723,525,632      1,452,277,760           DATAFILE 01-OCT-2010 06:25:59    Y
+DATA/ORCL/DATAFILE/SYSAUX.257.731226431                               576,724,992      1,158,676,480           DATAFILE 01-OCT-2010 06:27:11    Y
+DATA/ORCL/DATAFILE/UNDOTBS1.258.731226451                              99,622,912        204,472,320           DATAFILE 01-OCT-2010 06:27:31    Y
+DATA/ORCL/DATAFILE/USERS.259.731226455                                  5,251,072         12,582,912           DATAFILE 01-OCT-2010 06:27:34    Y
+DATA/ORCL/CONTROLFILE/Current.261.731227231                             9,748,480         50,331,648        CONTROLFILE 01-OCT-2010 06:40:30    Y
+DATA/ORCL/CONTROLFILE/Current.260.731227229                             9,748,480         50,331,648        CONTROLFILE 01-OCT-2010 06:40:29    Y
+DATA/ORCL/ONLINELOG/group_2.263.731227315                              52,429,312        110,100,480          ONLINELOG 01-OCT-2010 06:41:55    Y
+DATA/ORCL/ONLINELOG/group_3.264.731227335                              52,429,312        110,100,480          ONLINELOG 01-OCT-2010 06:42:15    Y
+DATA/ORCL/ONLINELOG/group_1.262.731227291                              52,429,312        110,100,480          ONLINELOG 01-OCT-2010 06:41:30    Y
+DATA/ORCL/TEMPFILE/TEMP.265.731227415                                  30,416,896         62,914,560           TEMPFILE 01-OCT-2010 06:43:35    Y
+DATA/ORCL/PARAMETERFILE/spfile.267.731228339                                2,560          2,097,152      PARAMETERFILE 01-OCT-2010 06:58:58    Y
+DATA/ORCL/system01.dbf                                                723,525,632      1,452,277,760           DATAFILE 01-OCT-2010 06:25:59    N
+DATA/ORCL/users01.dbf                                                   5,251,072         12,582,912           DATAFILE 01-OCT-2010 06:27:34    N
+DATA/ORCL/control01.ctl                                                 9,748,480         50,331,648        CONTROLFILE 01-OCT-2010 06:40:29    N
+DATA/ORCL/control02.ctl                                                 9,748,480         50,331,648        CONTROLFILE 01-OCT-2010 06:40:30    N
+DATA/ORCL/redo01.log                                                   52,429,312        110,100,480          ONLINELOG 01-OCT-2010 06:41:30    N
+DATA/ORCL/redo02.log                                                   52,429,312        110,100,480          ONLINELOG 01-OCT-2010 06:41:55    N
+DATA/ORCL/redo03.log                                                   52,429,312        110,100,480          ONLINELOG 01-OCT-2010 06:42:15    N
+DATA/ORCL/temp01.dbf                                                   30,416,896         62,914,560           TEMPFILE 01-OCT-2010 06:43:35    N
+DATA/ORCL/undotbs01.dbf                                                99,622,912        204,472,320           DATAFILE 01-OCT-2010 06:27:31    N
+DATA/ORCL/example01.dbf                                               104,865,792        214,958,080           DATAFILE 01-OCT-2010 06:43:53    N
+DATA/ORCL/sysaux01.dbf                                                576,724,992      1,158,676,480           DATAFILE 01-OCT-2010 06:27:11    N
+DATA/ORCL/spfileorcl.ora                                                    2,560          2,097,152      PARAMETERFILE 01-OCT-2010 06:58:58    N
                                                                ------------------ ------------------
                                                                     3,434,391,040      7,079,985,152

+FRA/ORCL/ONLINELOG/group_2.258.741267209                               52,429,312         53,477,376          ONLINELOG 24-JAN-2011 11:33:28    Y
+FRA/ORCL/ONLINELOG/group_1.256.741266959                               52,429,312         53,477,376          ONLINELOG 24-JAN-2011 11:29:18    Y
+FRA/ORCL/ONLINELOG/group_3.257.741266861                               52,429,312         53,477,376          ONLINELOG 24-JAN-2011 11:27:40    Y
+FRA/ORCL/redo01b.log                                                   52,429,312         53,477,376          ONLINELOG 24-JAN-2011 11:29:18    N
+FRA/ORCL/redo03b.log                                                   52,429,312         53,477,376          ONLINELOG 24-JAN-2011 11:27:40    N
+FRA/ORCL/redo02b.log                                                   52,429,312         53,477,376          ONLINELOG 24-JAN-2011 11:33:28    N
                                                                ------------------ ------------------
                                                                       314,575,872        320,864,256

                                                                ------------------ ------------------
Grand Total:                                                         3,748,966,912      7,400,849,408

31 rows selected.





SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN disk_group_name        FORMAT a16         HEAD 'Disk Group Name'
COLUMN alias_name             FORMAT a30         HEAD 'Alias Name'
COLUMN file_number                               HEAD 'File|Number'
COLUMN file_incarnation                          HEAD 'File|Incarnation'
COLUMN alias_index                               HEAD 'Alias|Index'
COLUMN alias_incarnation                         HEAD 'Alias|Incarnation'
COLUMN parent_index                              HEAD 'Parent|Index'
COLUMN reference_index                           HEAD 'Reference|Index'
COLUMN alias_directory        FORMAT a10         HEAD 'Alias|Directory?'
COLUMN system_created         FORMAT a8          HEAD 'System|Created?'
break on report on disk_group_name skip 1
SELECT
    g.name               disk_group_name
  , a.name               alias_name
  , a.file_number        file_number
  , a.file_incarnation   file_incarnation
  , a.alias_index        alias_index
  , a.alias_incarnation  alias_incarnation
  , a.parent_index       parent_index
  , a.reference_index    reference_index
  , a.alias_directory    alias_directory
  , a.system_created     system_created
FROM  v$asm_alias a ,v$asm_diskgroup g 
where a.group_number=g.group_number
ORDER BY g.name , a.file_number
/


                                     File        File      Alias       Alias     Parent  Reference Alias      System
Alias Name                         Number Incarnation      Index Incarnation      Index      Index Directory? Created?
------------------------------ ---------- ----------- ---------- ----------- ---------- ---------- ---------- --------
REGISTRY.253.731105699                253   731105699        106           1   16777322   33554431 N          Y
SYSTEM.256.731226373                  256   731226373        212           1   16777428   33554431 N          Y
system01.dbf                          256   731226373        160           1   16777375   33554431 N          N
sysaux01.dbf                          257   731226431        161           1   16777375   33554431 N          N
SYSAUX.257.731226431                  257   731226431        213           1   16777428   33554431 N          Y
undotbs01.dbf                         258   731226451        162           1   16777375   33554431 N          N
UNDOTBS1.258.731226451                258   731226451        214           1   16777428   33554431 N          Y
USERS.259.731226455                   259   731226455        215           1   16777428   33554431 N          Y
users01.dbf                           259   731226455        163           1   16777375   33554431 N          N
control01.ctl                         260   731227229        165           1   16777375   33554431 N          N
Current.260.731227229                 260   731227229        265           1   16777481   33554431 N          Y
control02.ctl                         261   731227231        166           1   16777375   33554431 N          N
Current.261.731227231                 261   731227231        266           1   16777481   33554431 N          Y
group_1.262.731227291                 262   731227291        318           1   16777534   33554431 N          Y
redo01.log                            262   731227291        168           1   16777375   33554431 N          N
group_2.263.731227315                 263   731227315        319           1   16777534   33554431 N          Y
redo02.log                            263   731227315        169           1   16777375   33554431 N          N
redo03.log                            264   731227335        170           1   16777375   33554431 N          N
group_3.264.731227335                 264   731227335        320           1   16777534   33554431 N          Y
temp01.dbf                            265   731227415        172           1   16777375   33554431 N          N
TEMP.265.731227415                    265   731227415        371           1   16777587   33554431 N          Y
example01.dbf                         266   731227433        173           1   16777375   33554431 N          N
UNKNOWN.266.731227433                 266   731227433        216           1   16777428   33554431 N          Y
spfile.267.731228339                  267   731228339        424           1   16777640   33554431 N          Y
spfileorcl.ora                        267   731228339        175           1   16777375   33554431 N          N
ONLINELOG                      4294967295  4294967295        167           1   16777375   16777534 Y          Y
CONTROLFILE                    4294967295  4294967295        164           1   16777375   16777481 Y          Y
DATAFILE                       4294967295  4294967295        159           1   16777375   16777428 Y          Y
ORCL                           4294967295  4294967295          1           1   16777216   16777375 Y          Y
TEMPFILE                       4294967295  4294967295        171           1   16777375   16777587 Y          Y
PARAMETERFILE                  4294967295  4294967295        174           1   16777375   16777640 Y          Y
ASM                            4294967295  4294967295          0           1   16777216   16777269 Y          Y
ASMPARAMETERFILE               4294967295  4294967295         53           1   16777269   16777322 Y          Y

group_1.256.741266959                 256   741266959        106           3   33554538   50331647 N          Y
redo01b.log                           256   741266959         54           3   33554485   50331647 N          N
group_3.257.741266861                 257   741266861        107           1   33554538   50331647 N          Y
redo03b.log                           257   741266861         55           1   33554485   50331647 N          N
group_2.258.741267209                 258   741267209        108           1   33554538   50331647 N          Y
redo02b.log                           258   741267209         56           1   33554485   50331647 N          N
ONLINELOG                      4294967295  4294967295         53           1   33554485   33554538 Y          Y
ORCL                           4294967295  4294967295          0           1   33554432   33554485 Y          Y


41 rows selected.




SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN full_path              FORMAT a75                  HEAD 'ASM File Name / Volume Name / Device Name'
COLUMN system_created         FORMAT a8                   HEAD 'System|Created?'
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes'
COLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space'
COLUMN type                   FORMAT a18                  HEAD 'File Type'
COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'
COLUMN striped                FORMAT a8                   HEAD 'Striped'
COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'
COLUMN disk_group_name        noprint

BREAK ON report ON disk_group_name SKIP 1

COMPUTE sum LABEL ""              OF bytes space ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF bytes space ON report

SELECT
    CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path
  , db_files.bytes
  , db_files.space
  , NVL(LPAD(db_files.type, 18), '<DIRECTORY>')  type
  , db_files.creation_date
  , db_files.disk_group_name
  , LPAD(db_files.system_created, 4) system_created
FROM
    ( SELECT
          g.name               disk_group_name
        , a.parent_index       pindex
        , a.name               alias_name
        , a.reference_index    rindex
        , a.system_created     system_created
        , f.bytes              bytes
        , f.space              space
        , f.type               type
        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date
      FROM
          v$asm_file f RIGHT OUTER JOIN v$asm_alias     a USING (group_number, file_number)
                                   JOIN v$asm_diskgroup g USING (group_number)
    ) db_files
WHERE db_files.type IS NOT NULL
START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0
    CONNECT BY PRIOR db_files.rindex = db_files.pindex
UNION
SELECT
    '+' || volume_files.disk_group_name ||  ' [' || volume_files.volume_name || '] ' ||  volume_files.volume_device full_path
  , volume_files.bytes
  , volume_files.space
  , NVL(LPAD(volume_files.type, 18), '<DIRECTORY>')  type
  , volume_files.creation_date
  , volume_files.disk_group_name
  , null
FROM
    ( SELECT
          g.name               disk_group_name
        , v.volume_name        volume_name
        , v.volume_device       volume_device
        , f.bytes              bytes
        , f.space              space
        , f.type               type
        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date
      FROM
          v$asm_file f RIGHT OUTER JOIN v$asm_volume    v USING (group_number, file_number)
                                   JOIN v$asm_diskgroup g USING (group_number)
    ) volume_files
WHERE volume_files.type IS NOT NULL
/



asmcmd lsattr -G DATA -l
 
Name                        Value
access_control.enabled      FALSE
access_control.umask        066
appliance._partnering_type  GENERIC
ate_conversion_done         true
au_size                     4194304   <<
cell.smart_scan_capable     FALSE
cell.sparse_dg              allnonsparse
compatible.asm              19.0.0.0.0
compatible.rdbms            10.1.0.0.0
content.check               FALSE
content.type                data
content_hardcheck.enabled   FALSE
disk_repair_time            12.0h
failgroup_repair_time       24.0h
idp.boundary                auto
idp.type                    dynamic
logical_sector_size         512
phys_meta_replicated        true
preferred_read.enabled      FALSE
scrub_async_limit           1
scrub_metadata.enabled      TRUE
sector_size                 512
thin_provisioned            FALSE
vam_migration_done          false



set linesize 300 pagesize 300
col compatibility 	for a20
col DATABASE_COMPATIBILITY for a25
select allocation_unit_size,allocation_unit_size/1024/1024 allocation_unit_size_MB,compatibility,database_compatibility from v$asm_diskgroup ;


to search spfile !!!!

 
 SET LINESIZE  150
SET PAGESIZE  9999
SET VERIFY    off
COLUMN full_alias_path        FORMAT a63                  HEAD 'File Name'
COLUMN system_created         FORMAT a8                   HEAD 'System|Created?'
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes'
COLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space'
COLUMN type                   FORMAT a18                  HEAD 'File Type'
COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'
COLUMN striped                FORMAT a8                   HEAD 'Striped'
COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'
COLUMN disk_group_name        noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label ""              of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report

select * from (SELECT
    CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
  , bytes
  , space
  , NVL(LPAD(type, 18), '<DIRECTORY>')  type
  , creation_date
  , disk_group_name
  , LPAD(system_created, 4) system_created
FROM
    ( SELECT
          g.name               disk_group_name
        , a.parent_index       pindex
        , a.name               alias_name
        , a.reference_index    rindex
        , a.system_created     system_created
        , f.bytes              bytes
        , f.space              space
        , f.type               type
        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date
FROM  v$asm_file f ,v$asm_alias a ,v$asm_diskgroup g
where  (f.group_number(+) = a.group_number
and     f.file_number(+)  = a.file_number )
        and g.group_number(+) =f.group_number
      )
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex)
where 1=1
-- and full_alias_path like '%spfile%'
--and full_alias_path like '%control%'
and type like '%PARAMETERFILE%'
/

SET PAGESIZE  9999
SET VERIFY    off

COLUMN disk_group_name    FORMAT a15               			HEAD 'Disk Group Name'
COLUMN disk_path          FORMAT a25               			HEAD 'Disk Path'
COLUMN reads              FORMAT 999,999,999,999,999,999,999       	HEAD 'Reads'
COLUMN writes             FORMAT 999,999,999,999,999,999,999      	HEAD 'Writes'
COLUMN read_errs          FORMAT 999,999           			HEAD 'Read|Errors'
COLUMN write_errs         FORMAT 999,999           			HEAD 'Write|Errors'
COLUMN read_time          FORMAT 999,999,999       			HEAD 'Read|Time'
COLUMN write_time         FORMAT 999,999,999       			HEAD 'Write|Time'
COLUMN bytes_read         FORMAT 999,999,999,999,999,999,999   		HEAD 'Bytes|Read'
COLUMN bytes_written      FORMAT 999,999,999,999,999,999,999   		HEAD 'Bytes|Written'

break on report on disk_group_name skip 2

compute sum label ""              of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on disk_group_name
compute sum label "Grand Total: " of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on report

SELECT
    a.name                disk_group_name
  , b.path                disk_path
  , b.reads               reads
  , b.writes              writes
  , b.read_errs           read_errs 
  , b.write_errs          write_errs
  , b.read_time           read_time
  , b.write_time          write_time
  , b.bytes_read          bytes_read
  , b.bytes_written       bytes_written
FROM v$asm_diskgroup a ,v$asm_disk b 
where a.group_number=b.group_number
ORDER BY a.name
/

Disk Path                   Reads       Writes   Errors   Errors         Time         Time             Read          Written
-------------------- ------------ ------------ -------- -------- ------------ ------------ ---------------- ----------------
ORCL:ASMDISK01                 64        1,720        0        0            1            4        1,196,032        7,045,120
ORCL:ASMDISK02                 38        1,719        0        0            0            5          155,648        7,041,024
ORCL:ASMDISK04                 53            8        0        0            1            0        1,200,128           32,768
ORCL:ASMDISK03                 33        1,718        0        0            0            2          139,264        7,036,928
                     ------------ ------------ -------- -------- ------------ ------------ ---------------- ----------------
                              188        5,165        0        0            2           12        2,691,072       21,155,840


ORCL:ASMDISK05                 68        1,713        0        0            2            3        1,216,512        7,016,448
ORCL:ASMDISK07                 47            4        0        0            1            0        1,175,552           16,384
ORCL:ASMDISK06                 28            2        0        0            0            0          114,688            8,192
ORCL:ASMDISK08                 35            6        0        0            1            0          143,360           24,576
                     ------------ ------------ -------- -------- ------------ ------------ ---------------- ----------------
                              178        1,725        0        0            4            3        2,650,112        7,065,600


                     ------------ ------------ -------- -------- ------------ ------------ ---------------- ----------------
                              366        6,890        0        0            5           15        5,341,184       28,221,440

8 rows selected.

Disk info With date !!

SET LINESIZE  300  PAGESIZE  9999  VERIFY    off

COLUMN disk_group_name    FORMAT a15               		HEAD 'Disk Group Name'
COLUMN disk_path          FORMAT a25               		HEAD 'Disk Path'
COLUMN reads              FORMAT 999,999,999,9999       	HEAD 'Reads'
COLUMN writes             FORMAT 999,999,999,9999        	HEAD 'Writes'
COLUMN read_errs          FORMAT 999,999,999,9999           	HEAD 'Read|Errors'
COLUMN write_errs         FORMAT 999,999           		HEAD 'Write|Errors'
COLUMN read_time          FORMAT 999,999,999       		HEAD 'Read|Time'
COLUMN write_time         FORMAT 999,999,999       		HEAD 'Write|Time'
COLUMN bytes_read         FORMAT 999,999,999,999,999,999   	HEAD 'Bytes|Read'
COLUMN bytes_written      FORMAT 999,999,999,999,999   		HEAD 'Bytes|Written'
col CREATE_DATE for a20
SELECT
    a.name                disk_group_name
  , b.path                disk_path
  , b.bytes_read          bytes_read
  , b.bytes_written       bytes_written
,b.total_mb 
, CREATE_DATE
FROM v$asm_diskgroup a , v$asm_disk b 
where a.group_number=b.group_number
ORDER BY CREATE_DATE desc
/

SET LINESIZE  300  PAGESIZE  9999  VERIFY    off

COLUMN disk_group_name    FORMAT a15               		HEAD 'Disk Group Name'
COLUMN disk_path          FORMAT a25               		HEAD 'Disk Path'
COLUMN reads              FORMAT 999,999,999,9999       	HEAD 'Reads'
COLUMN writes             FORMAT 999,999,999,9999        	HEAD 'Writes'
COLUMN read_errs          FORMAT 999,999,999,9999           	HEAD 'Read|Errors'
COLUMN write_errs         FORMAT 999,999           		HEAD 'Write|Errors'
COLUMN read_time          FORMAT 999,999,999       		HEAD 'Read|Time'
COLUMN write_time         FORMAT 999,999,999       		HEAD 'Write|Time'
COLUMN bytes_read         FORMAT 999,999,999,999,999,999   	HEAD 'Bytes|Read'
COLUMN bytes_written      FORMAT 999,999,999,999,999   		HEAD 'Bytes|Written'
col CREATE_DATE for a20
break on report on disk_group_name skip 2
compute sum label ""              of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on disk_group_name
compute sum label "Grand Total: " of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on report
SELECT
    a.name                disk_group_name
  , b.path                disk_path
  , b.reads               reads
  , b.writes              writes
  , b.read_errs           read_errs 
  , b.write_errs          write_errs
  , b.read_time           read_time
  , b.write_time          write_time
  , b.bytes_read          bytes_read
  , b.bytes_written       bytes_written
, CREATE_DATE
FROM v$asm_diskgroup a , v$asm_disk b 
where a.group_number=b.group_number
ORDER BY CREATE_DATE desc
/

asm_alias.sql
-- PURPOSE  : Provide a summary report of all alias definitions contained within all ASM disk groups.                                     


SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off

COLUMN disk_group_name        FORMAT a16         HEAD 'Disk Group Name'
COLUMN alias_name             FORMAT a30         HEAD 'Alias Name'
COLUMN file_number                               HEAD 'File|Number'
COLUMN file_incarnation                          HEAD 'File|Incarnation'
COLUMN alias_index                               HEAD 'Alias|Index'
COLUMN alias_incarnation                         HEAD 'Alias|Incarnation'
COLUMN parent_index                              HEAD 'Parent|Index'
COLUMN reference_index                           HEAD 'Reference|Index'
COLUMN alias_directory        FORMAT a10         HEAD 'Alias|Directory?'
COLUMN system_created         FORMAT a8          HEAD 'System|Created?'
break on report on disk_group_name skip 1
SELECT
    g.name               disk_group_name
  , a.name               alias_name
  , a.file_number        file_number
  , a.file_incarnation   file_incarnation
  , a.alias_index        alias_index
  , a.alias_incarnation  alias_incarnation
  , a.parent_index       parent_index
  , a.reference_index    reference_index
  , a.alias_directory    alias_directory
  , a.system_created     system_created
FROM     v$asm_alias a ,v$asm_diskgroup g 
where a.group_number=g.group_number
ORDER BY g.name , a.file_number
/


                                     File        File      Alias       Alias     Parent  Reference Alias      System
Alias Name                         Number Incarnation      Index Incarnation      Index      Index Directory? Created?
------------------------------ ---------- ----------- ---------- ----------- ---------- ---------- ---------- --------
REGISTRY.253.731105699                253   731105699        106           1   16777322   33554431 N          Y
SYSTEM.256.731226373                  256   731226373        212           1   16777428   33554431 N          Y
system01.dbf                          256   731226373        160           1   16777375   33554431 N          N
sysaux01.dbf                          257   731226431        161           1   16777375   33554431 N          N
SYSAUX.257.731226431                  257   731226431        213           1   16777428   33554431 N          Y
undotbs01.dbf                         258   731226451        162           1   16777375   33554431 N          N
UNDOTBS1.258.731226451                258   731226451        214           1   16777428   33554431 N          Y
USERS.259.731226455                   259   731226455        215           1   16777428   33554431 N          Y
users01.dbf                           259   731226455        163           1   16777375   33554431 N          N
control01.ctl                         260   731227229        165           1   16777375   33554431 N          N
Current.260.731227229                 260   731227229        265           1   16777481   33554431 N          Y
control02.ctl                         261   731227231        166           1   16777375   33554431 N          N
Current.261.731227231                 261   731227231        266           1   16777481   33554431 N          Y
group_1.262.731227291                 262   731227291        318           1   16777534   33554431 N          Y
redo01.log                            262   731227291        168           1   16777375   33554431 N          N
group_2.263.731227315                 263   731227315        319           1   16777534   33554431 N          Y
redo02.log                            263   731227315        169           1   16777375   33554431 N          N
redo03.log                            264   731227335        170           1   16777375   33554431 N          N
group_3.264.731227335                 264   731227335        320           1   16777534   33554431 N          Y
temp01.dbf                            265   731227415        172           1   16777375   33554431 N          N
TEMP.265.731227415                    265   731227415        371           1   16777587   33554431 N          Y
example01.dbf                         266   731227433        173           1   16777375   33554431 N          N
UNKNOWN.266.731227433                 266   731227433        216           1   16777428   33554431 N          Y
spfile.267.731228339                  267   731228339        424           1   16777640   33554431 N          Y
spfileorcl.ora                        267   731228339        175           1   16777375   33554431 N          N
ONLINELOG                      4294967295  4294967295        167           1   16777375   16777534 Y          Y
CONTROLFILE                    4294967295  4294967295        164           1   16777375   16777481 Y          Y
DATAFILE                       4294967295  4294967295        159           1   16777375   16777428 Y          Y
ORCL                           4294967295  4294967295          1           1   16777216   16777375 Y          Y
TEMPFILE                       4294967295  4294967295        171           1   16777375   16777587 Y          Y
PARAMETERFILE                  4294967295  4294967295        174           1   16777375   16777640 Y          Y
ASM                            4294967295  4294967295          0           1   16777216   16777269 Y          Y
ASMPARAMETERFILE               4294967295  4294967295         53           1   16777269   16777322 Y          Y

group_1.256.741266959                 256   741266959        106           3   33554538   50331647 N          Y
redo01b.log                           256   741266959         54           3   33554485   50331647 N          N
group_3.257.741266861                 257   741266861        107           1   33554538   50331647 N          Y
redo03b.log                           257   741266861         55           1   33554485   50331647 N          N
group_2.258.741267209                 258   741267209        108           1   33554538   50331647 N          Y
redo02b.log                           258   741267209         56           1   33554485   50331647 N          N
ONLINELOG                      4294967295  4294967295         53           1   33554485   33554538 Y          Y
ORCL                           4294967295  4294967295          0           1   33554432   33554485 Y          Y


41 rows selected.





asm_disks_perf.sql
--  PURPOSE  : Provide a summary report of all disks contained within all ASM disk groups along with their performance metrics.                


SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off

COLUMN disk_group_name    FORMAT a15               HEAD 'Disk Group Name'
COLUMN disk_path          FORMAT a20               HEAD 'Disk Path'
COLUMN reads              FORMAT 999,999,999       HEAD 'Reads'
COLUMN writes             FORMAT 999,999,999       HEAD 'Writes'
COLUMN read_errs          FORMAT 999,999           HEAD 'Read|Errors'
COLUMN write_errs         FORMAT 999,999           HEAD 'Write|Errors'
COLUMN read_time          FORMAT 999,999,999       HEAD 'Read|Time'
COLUMN write_time         FORMAT 999,999,999       HEAD 'Write|Time'
COLUMN bytes_read         FORMAT 999,999,999,999   HEAD 'Bytes|Read'
COLUMN bytes_written      FORMAT 999,999,999,999   HEAD 'Bytes|Written'

break on report on disk_group_name skip 2

compute sum label ""              of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on disk_group_name
compute sum label "Grand Total: " of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on report

SELECT
    a.name                disk_group_name
  , b.path                disk_path
  , b.reads               reads
  , b.writes              writes
  , b.read_errs           read_errs 
  , b.write_errs          write_errs
  , b.read_time           read_time
  , b.write_time          write_time
  , b.bytes_read          bytes_read
  , b.bytes_written       bytes_written
FROM v$asm_diskgroup a ,v$asm_disk b 
where a.group_number=b.group_number
ORDER BY a.name
/


                                                   Read    Write         Read        Write            Bytes            Bytes
Disk Path                   Reads       Writes   Errors   Errors         Time         Time             Read          Written
-------------------- ------------ ------------ -------- -------- ------------ ------------ ---------------- ----------------
ORCL:ASMDISK01                 58        1,439        0        0            1            3        1,171,456        5,894,144
ORCL:ASMDISK02                 32        1,438        0        0            0            5          131,072        5,890,048
ORCL:ASMDISK04                 47            8        0        0            1            0        1,175,552           32,768
ORCL:ASMDISK03                 27        1,437        0        0            0            2          114,688        5,885,952
                     ------------ ------------ -------- -------- ------------ ------------ ---------------- ----------------
                              164        4,322        0        0            2           10        2,592,768       17,702,912


ORCL:ASMDISK05                 62        1,432        0        0            2            3        1,191,936        5,865,472
ORCL:ASMDISK07                 41            4        0        0            1            0        1,150,976           16,384
ORCL:ASMDISK06                 22            2        0        0            0            0           90,112            8,192
ORCL:ASMDISK08                 29            6        0        0            1            0          118,784           24,576
                     ------------ ------------ -------- -------- ------------ ------------ ---------------- ----------------
                              154        1,444        0        0            4            3        2,551,808        5,914,624


                     ------------ ------------ -------- -------- ------------ ------------ ---------------- ----------------
                              318        5,766        0        0            5           13        5,144,576       23,617,536

8 rows selected.




asm_disks.sql
--  PURPOSE  : Provide a summary report of all disks contained within all disk 
--             groups. This script is also responsible for queriing all        
--             candidate disks - those that are not assigned to any disk       
--             group.                                                          

SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off

COLUMN disk_group_name        FORMAT a20           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a17           HEAD 'Path'
COLUMN disk_file_name         FORMAT a20           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

break on report on disk_group_name skip 1

compute sum label ""              of total_mb used_mb on disk_group_name
compute sum label "Grand Total: " of total_mb used_mb on report

SELECT
    NVL(a.name, '[CANDIDATE]')                       disk_group_name
  , b.path                                           disk_file_path
  , b.name                                           disk_file_name
  , b.failgroup                                      disk_file_fail_group
  , b.total_mb                                       total_mb
  , (b.total_mb - b.free_mb)                         used_mb
  , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
FROM     v$asm_diskgroup a , v$asm_disk b 
where a.group_number=b.group_number(+)
ORDER BY  a.name
/


Path              File Name            Fail Group           File Size (MB) Used Size (MB) Pct. Used
----------------- -------------------- -------------------- -------------- -------------- ---------
ORCL:ASMDISK01    ASMDISK01            ASMDISK01                     2,304            879     38.15
ORCL:ASMDISK02    ASMDISK02            ASMDISK02                     2,304            896     38.89
ORCL:ASMDISK04    ASMDISK04            ASMDISK04                     2,304            895     38.85
ORCL:ASMDISK03    ASMDISK03            ASMDISK03                     2,304            889     38.59
                                                            -------------- --------------
                                                                     9,216          3,559

ORCL:ASMDISK05    ASMDISK05            ASMDISK05                     2,304             54      2.34
ORCL:ASMDISK07    ASMDISK07            ASMDISK07                     2,304             55      2.39
ORCL:ASMDISK06    ASMDISK06            ASMDISK06                     2,304             53      2.30
ORCL:ASMDISK08    ASMDISK08            ASMDISK08                     2,304             56      2.43
                                                            -------------- --------------
                                                                     9,216            218

                                                            -------------- --------------
                                                                    18,432          3,777

8 rows selected.

---to check asmlib or afd?

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/*`

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

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

 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
;

df -ha|grep -i asm oracleasmfs 0 0 0 - /dev/oracleasm

 /etc/init.d/oracleasm status
/etc/init.d/oracleasm is deprecated. Use 'oracleasm status'


[oracle]$ oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

log]# cat /etc/sysconfig/oracleasm
#
# This is a configuration file for automatic loading of the Oracle
# Automatic Storage Management library kernel driver.  It is generated
# By running /etc/init.d/oracleasm configure.  Please use that method
# to modify this file
#

# ORACLEASM_ENABLED: 'true' means to load the driver on boot.
ORACLEASM_ENABLED=true

# ORACLEASM_UID: Default user owning the /dev/oracleasm mount point.
ORACLEASM_UID=grid

# ORACLEASM_GID: Default group owning the /dev/oracleasm mount point.
ORACLEASM_GID=oinstall

# ORACLEASM_SCANBOOT: 'true' means scan for ASM disks on boot.
ORACLEASM_SCANBOOT=true

# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER="dm"

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE="sd"

# ORACLEASM_SCAN_DIRECTORIES: Scan disks under these directories
ORACLEASM_SCAN_DIRECTORIES=""

# ORACLEASM_USE_LOGICAL_BLOCK_SIZE: 'true' means use the logical block size
# reported by the underlying disk instead of the physical. The default
# is 'false'
ORACLEASM_USE_LOGICAL_BLOCK_SIZE=false

======


 cat asm_mapping.sh

-rwxrwxrwx 1 root    root           389 Feb  8 04:23 asm_mapping.sh

 #!/bin/sh -e
for disk in `/usr/sbin/oracleasm listdisks`
do
oracleasm querydisk -d $disk
#ls -l /dev/*|grep -E `oracleasm querydisk -d $disk|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'|awk -F, '{print $1 ",.*" $2}'`
# Alternate option to remove []
ls -l /dev/*|grep -E `oracleasm querydisk -d $disk|awk '{print $NF}'|sed 's/[][]//g'|awk -F, '{print $1 ",.*" $2}'`|grep -i Disk
echo
done

Disk "FLSH_DATA3" is a valid ASM disk on device [249,8]
brw-rw----  1 root disk     249,   0 Jan  8 07:57 /dev/dm-0
brw-rw----  1 root disk     249,   1 Jan  8 07:57 /dev/dm-1


powermt display dev=all

for multipath

cat /etc/multipath.conf
blacklist {
        device {
                vendor "*"
                product "*"
        }
}

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


asmcmd lsof -G DATA | grep -i undotbs01.dbf



set linesize 500 pagesize 300
col ALIAS_NAME for a30 
col ALIAS_PATH  for a30 
col FILE_PATH  for a70


WITH qry1 AS
(
SELECT aa.name,
       ag.name group_name,
       af.type,
       aa.file_number,
       aa.file_incarnation,
       aa.parent_index parent_index
FROM v$asm_alias aa,
     v$asm_diskgroup ag,
     v$asm_file af
WHERE aa.file_number = af.file_number
AND aa.group_number = ag.group_number
AND ag.group_number = af.group_number
AND aa.file_incarnation = af.incarnation
AND aa.system_created = 'N'
AND ag.group_number = 1
)
SELECT alias_name,
       '+'||group_name||'/'||LISTAGG(CASE WHEN alias_name = root_name THEN name END,'/') WITHIN GROUP(ORDER BY lvl DESC) alias_path,
       '+'||group_name||'/'||LISTAGG(CASE WHEN alias_name != root_name THEN name END,'/') WITHIN GROUP(ORDER BY lvl DESC) file_path
FROM
(
SELECT aa.name,
       q1.group_name,
       q1.file_number,
       q1.name alias_name,
       LEVEL lvl,
       CONNECT_BY_ROOT aa.name root_name
FROM v$asm_alias aa,
     qry1 q1
WHERE aa.group_number = 1
START WITH (aa.name = q1.name OR (aa.name != q1.name AND aa.file_number = q1.file_number))
CONNECT BY PRIOR aa.parent_index = aa.reference_index
AND q1.name = PRIOR q1.name
)
GROUP BY group_name,file_number,alias_name

*****


asmcmd lsof -G DATA | grep -i undotbs01.dbf

===

file info


set linesize 500 pagesize 300
col ALIAS_NAME for a30 
col ALIAS_PATH  for a30 
col FILE_PATH  for a70


WITH qry1 AS
(
SELECT aa.name,
       ag.name group_name,
       af.type,
       aa.file_number,
       aa.file_incarnation,
       aa.parent_index parent_index
FROM v$asm_alias aa,
     v$asm_diskgroup ag,
     v$asm_file af
WHERE aa.file_number = af.file_number
AND aa.group_number = ag.group_number
AND ag.group_number = af.group_number
AND aa.file_incarnation = af.incarnation
AND aa.system_created = 'N'
AND ag.group_number = 1
)
SELECT alias_name,
       '+'||group_name||'/'||LISTAGG(CASE WHEN alias_name = root_name THEN name END,'/') WITHIN GROUP(ORDER BY lvl DESC) alias_path,
       '+'||group_name||'/'||LISTAGG(CASE WHEN alias_name != root_name THEN name END,'/') WITHIN GROUP(ORDER BY lvl DESC) file_path
FROM
(
SELECT aa.name,
       q1.group_name,
       q1.file_number,
       q1.name alias_name,
       LEVEL lvl,
       CONNECT_BY_ROOT aa.name root_name
FROM v$asm_alias aa,
     qry1 q1
WHERE aa.group_number = 1
START WITH (aa.name = q1.name OR (aa.name != q1.name AND aa.file_number = q1.file_number))
CONNECT BY PRIOR aa.parent_index = aa.reference_index
AND q1.name = PRIOR q1.name
)
GROUP BY group_name,file_number,alias_name

===


ALIAS_NAME                     ALIAS_PATH                     FILE_PATH
------------------------------ ------------------------------ ----------------------------------------------------------------------
spfileIVIHA.ora                +DATA/IVIHA/spfileIVIHA.ora    +DATA/
test_data01.dbf                +DATA/IVIHA/IVIHA/IVIHA/IVIHA/ +DATA/IVIHA/IVIHA/IVIHA/IVIHA/IVIHA/IVIHA/IVIHA/IVIHA/DATAFILE/DATAFIL
                               test_data01.dbf/test_data01.db E/DATAFILE/DATAFILE/TEST_DATA.3754.1146037979/TEST_DATA.3754.114603797



set pages 1000 linesize 300
set term off
col mp new_value pname
col dp new_value dname
col gp new_value gname
select 'a'||max(length(path)) mp, 'a'||max(length(name)+1) dp from v$asm_disk;
select 'a'||max(length(name)+1) gp from v$asm_diskgroup;
 
set term on
 
col path                for &pname heading "Path"
col diskname 		for &dname heading "Disk Name"
col groupname 		for &gname heading "Group Name"
col sector_size 	for 99999 heading "Sector|Size"
col block_size 		for 9999 heading "Block|Size"
col total_gig 		for 9,999,999 heading "Group|Total|GB"
col free_gig 		for 9,999,999 heading "Group|Free|GB"
col dtotal_gig 		for 9,999,999 heading "Disk|Total|GB"
col dfree_gig 		for 9,999,999 heading "Disk|Free|GB"
col au 			for 999 heading "AU"
col failgroup 		for a15 heading 'Fail Group'
 
set lines 150
 
break on groupname on total_gig on free_gig skip 1
 
SELECT g.name groupname,
 d.path,
 d.name diskname,
 g.block_size,
 g.allocation_unit_size/1024/1024 au,
 g.state
FROM v$asm_diskgroup g, v$asm_disk d
WHERE d.group_number = g.group_number
ORDER BY g.name, d.disk_number;
 
clear breaks

                                              Block
Group N Path                   Disk Name       Size   AU STATE
------- ---------------------- -------------- ----- ---- -----------
DATA01  /dev/ora/ORA-DATA01p2  DATA01_0000     4096    4 MOUNTED


 
 SET LINESIZE  150  PAGESIZE  9999
SET VERIFY    off
COLUMN full_alias_path        FORMAT a63                  HEAD 'File Name'
COLUMN system_created         FORMAT a8                   HEAD 'System|Created?'
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes'
COLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space'
COLUMN type                   FORMAT a18                  HEAD 'File Type'
COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'
COLUMN striped                FORMAT a8                   HEAD 'Striped'
COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'
COLUMN disk_group_name        noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label ""              of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report

select * from (SELECT
    CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
  , bytes
  , space
  , NVL(LPAD(type, 18), '<DIRECTORY>')  type
  , creation_date
  , disk_group_name
  , LPAD(system_created, 4) system_created
FROM
    ( SELECT
          g.name               disk_group_name
        , a.parent_index       pindex
        , a.name               alias_name
        , a.reference_index    rindex
        , a.system_created     system_created
        , f.bytes              bytes
        , f.space              space
        , f.type               type
        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date
FROM  v$asm_file f ,v$asm_alias a ,v$asm_diskgroup g
where  (f.group_number(+) = a.group_number
and     f.file_number(+)  = a.file_number )
        and g.group_number(+) =f.group_number
      )
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex)
where 1=1
--and full_alias_path like '%spfile%'
--and full_alias_path like '%control%' or full_alias_path like '%CONTROL%' 
and type like '%PARAMETERFILE%' or type like '%spfile%'
/
 
 
 
                                                                                                                                             System
File Name                                                                    Bytes              Space File Type          Creation Date       Created?
--------------------------------------------------------------- ------------------ ------------------ ------------------ -------------------- --------
+DATA01/XXXXX/PARAMETERFILE/spfile.2271.1173159311                       25,088          1,048,576      PARAMETERFILE 01-JUL-2024 05:35:11    Y
                                                                ------------------ ------------------
                                                                            25,088          1,048,576

                                                                ------------------ ------------------
Grand Total:                                                                25,088          1,048,576

======

 cat asm_device_mapping.sh


#!/bin/bash
## ASMLIB_DISK  -- disk name in ASMLIB
## ASM_DISK -- disk name in ASM
## DEVICE -- physical disk name
GRID_HOME=`cat /etc/oratab  | grep ^+ASM | awk -F":" '{print $2}'`
for ASMLIB_DISK in `ls /dev/oracleasm/disks/*`
  do
    ASM_DISK=`$GRID_HOME/bin/kfed read $ASMLIB_DISK | grep dskname | tr -s ' '| cut -f2 -d' '`
    majorminor=`ls -l $ASMLIB_DISK | tr -s ' ' | cut -f5,6 -d' '`
    device=`ls -l /dev/ | tr -s ' ' | grep -w "$majorminor" | cut -f10 -d' '`
    echo "ASMLIB disk name : $ASMLIB_DISK"
    echo "ASM_DISK name : $ASM_DISK"
    echo "Physical disk device : /dev/$device"
done

or

 cat asmdisk.sh
#!/bin/bash
for asmlibdisk in `ls /dev/oracleasm/disks/*`
do
  echo "ASMLIB disk name: $asmlibdisk"
  asmdisk=`/u01/app/19.0.0/grid/bin/kfed read $asmlibdisk | grep dskname | tr -s ' '| cut -f2 -d' '`
  echo "ASM disk name: $asmdisk"
  majorminor=`ls -l $asmlibdisk | tr -s ' ' | cut -f5,6 -d' '`
  device=`ls -l /dev | tr -s ' ' | grep "$majorminor" | cut -f10 -d' '`
  echo "Device path: /dev/$device"
done

output

 ./asmdisk.sh
ASMLIB disk name: /dev/oracleasm/disks/VOTING
ASM disk name:
Device path: /dev/sdb1

====

/u01/app/19.0.0/grid/bin/kfed read |ls /dev/oracleasm/disks/*
/dev/oracleasm/disks/VOTING

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Sunday, 17 June 2012

Oracle 10g physical standby log gap check via shell script

Sunday, 10 June 2012

Oracle restore control file on standby for ASM

Saturday, 9 June 2012

Oracle ASM useful Scripts

Oracle ASM useful Scripts :

Check status again:

Oracle DBA

anuj blog Archive