X

Alejandro Vargas' Blog

  • January 22, 2007

ASM sample scripts

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

This is a set of simple scripts to get information out of the ASM instance




1. chk_asm_disk             :  Status and usage of ASM disks
2. chk_asm_client            : Clients connected to the ASM Instance
3. chk_asm_diskgroup     : This script display % free space on each

diskgroup.
4. chk_asm_file                : Provide Information about ASM files
5. chk_asm_file2              : Same as chk_asm_file but provide more information
6. chk_asm_file3              : List summary of files and total used space
7. chk_asm_grp_stat        : Display configuration and statistics of ASM Disk Groups



These scripts are run from tcsh and use the following aliases to setup the environment variables.
These alias are set on .cshrc

----- from .cshrc ------
setenv ASM_HOME /oradisk/app01/oracle/product/10gASM
setenv BASE_PATH /oradisk/app01/oracle/scripts/general:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin:/oradisk/app01/oracle/scripts:/usr/local/maint/oracle:/crmdb/app01/oracle/product/db_scripts/RAC:/crmdb/app01/oracle/product/db_scripts
alias 10asm   'setenv ORACLE_HOME  $ASM_HOME; setenv PATH ${ORACLE_HOME}/bin:${BASE_PATH}'
-----------------------

1. chk_asm_disk: Status and usage of ASM disks

----- script start below this line ----------
#!/bin/csh
# chk_asm_disk
# displays one row for every disk discovered by the ASM instance, including disks which are not part of any disk group
# source ~/.cshrc
10asm
clear
echo
echo
set asm_sid=`ps -ef |grep pmon|grep +ASM|awk '{print$8}'| cut -d"_" -f3`


setenv ORACLE_SID $asm_sid
sqlplus -s '/ as sysdba' << EOF
set pages 50000 lines 130 echo on head on flush on veri on feed on

col PATH form a25
col GB_read for 99999
col GB_written for 9999
select PATH,
        mount_status,
        state,
        TOTAL_MB,
        FREE_MB,
        mount_date,
        bytes_read/1024/1024/1024 GB_read,
        BYTES_WRITTEN/1024/1024/1024 GB_written
from v$asm_disk_stat
order by mount_status;
exit
EOF
echo
exit
# eof chk_asm_disk
----- script finish before this line ----------

Output:

PATH                      MOUNT_S STATE      TOTAL_MB    FREE_MB MOUNT_DAT GB_READ GB_WRITTEN
------------------------- ------- -------- ---------- ---------- --------- ------- ----------
/dev/raw/ASM4             CACHED  NORMAL       118683      70107 11-JAN-07    6371         33
/dev/raw/ASM9             CACHED  NORMAL       118683      70106 11-JAN-07    5919         37
/dev/raw/ASM8             CACHED  NORMAL       118683      70112 11-JAN-07    5619         31
/dev/raw/ASM1             CACHED  NORMAL       118683      70107 11-JAN-07    5874         31
/dev/raw/ASM2             CACHED  NORMAL       118683      98958 11-JAN-07       0         28
/dev/raw/ASM3             CACHED  NORMAL       118683      70106 11-JAN-07    5453         30
/dev/raw/ASM10            CACHED  NORMAL       118683      98956 11-JAN-07       0         28
/dev/raw/ASM5             CACHED  NORMAL       118683      70111 11-JAN-07    5566         34
/dev/raw/ASM6             CACHED  NORMAL       118683      70112 11-JAN-07    6298         30
/dev/raw/ASM7             CACHED  NORMAL       118683      70109 11-JAN-07    5908         31
/dev/raw/spfile+ASM.ora   CLOSED  NORMAL          149          0
/dev/raw/ocr.dbf          CLOSED  NORMAL          149          0
/dev/raw/votingdisk       CLOSED  NORMAL          149          0

13 rows selected.
================================================================

2. chk_asm_client: Clients connected to the ASM Instance

----- script start below this line ----------
#!/bin/csh
# chk_asm_client
# Check disk groups assigned devices
# source ~/.cshrc
10asm
clear
echo
echo
set asm_sid=`ps -ef |grep pmon|grep +ASM|awk '{print$8}'| cut -d"_" -f3`
setenv ORACLE_SID $asm_sid
sqlplus -s '/ as sysdba' << EOF
set pages 50000 lines 100 echo on head on flush on veri on feed on

col INSTANCE_NAME form a20
col SOFTWARE_VERSION form a20
col COMPATIBLE_VERSION form a20
select * from v$asm_client;
exit
EOF
echo
echo

exit
# eof chk_asm_client
----- script finish before this line ---------

Output:

GROUP_NUMBER INSTANCE_NAME        DB_NAME  STATUS       SOFTWARE_VERSION     COMPATIBLE_VERSION
------------ -------------------- -------- ------------ -------------------- --------------------
           1 racdbtst1            racdbtst CONNECTED    10.2.0.1.0           10.2.0.1.0

1 row selected.
================================================================



3. chk_asm_diskgroup : This script display % free space on each diskgroup. Heading were turned off to use the output to feed alert scripts.

----- script start below this line -----------
#!/bin/csh
# chk_asm_diskgroup
# displays one row for every disk group discovered by the ASM instance
# source ~/.cshrc
10asm
clear
echo
set asm_sid=`ps -ef |grep pmon|grep +ASM|awk '{print$8}'| cut -d"_" -f3`
setenv ORACLE_SID $asm_sid
sqlplus -s '/ as sysdba' << EOF
set head off flush on veri on feed off

select
 round(100*FREE_MB/TOTAL_MB) ||','|| NAME
from v$asm_diskgroup
/
exit
EOF
echo
echo

exit
# eof chk_asm_diskgroup
----- script finish before this line ----------

Output:

83,CRMARCHDG
59,CRMDATADG
================================================================
4. chk_asm_file : Provide Information about ASM files

----- script start below this line ----------
#!/bin/csh
# chk_asm_file
# displays one row for every disk group discovered by the ASM instance
# source ~/.cshrc
10asm
clear
echo
set asm_sid=`ps -ef |grep pmon|grep +ASM|awk '{print$8}'| cut -d"_" -f3`
setenv ORACLE_SID $asm_sid

sqlplus -s '/ as sysdba' << EOF
set pages 100 lines 130 echo on head on flush on veri on feed on

col TYPE form a15
col FILE_NUMBER form 9999 head FILE_NUM
col GROUP_NUMBER form 9999 head GR_NUM
col GB for 9999.99

select GROUP_NUMBER   ,
 FILE_NUMBER          ,
 BYTES/1024/1024/1024 GB ,
 TYPE                 ,
 STRIPED              ,
 MODIFICATION_DATE
from v$asm_file
where TYPE != 'ARCHIVELOG'
/
exit
EOF
echo

exit
# eof chk_asm_file
----- script finish before this line ----------

Output:

GR_NUM FILE_NUM       GB TYPE            STRIPE MODIFICAT
------ -------- -------- --------------- ------ ---------
     1      256      .00 PARAMETERFILE   COARSE 22-JAN-07
     1      643      .02 CONTROLFILE     FINE   22-JAN-07
     1      644      .02 CONTROLFILE     FINE   22-JAN-07
     3      256      .02 CONTROLFILE     FINE   08-MAY-06
     3      257     3.32 DATAFILE        COARSE 21-JAN-07
     3      258    29.30 DATAFILE        COARSE 22-JAN-07
     3      259     6.40 DATAFILE        COARSE 21-JAN-07
     3      260    29.30 DATAFILE        COARSE 22-JAN-07
     3      261     6.73 DATAFILE        COARSE 22-JAN-07
     3      262    32.00 TEMPFILE        COARSE 11-JAN-07
     3      263    20.00 DATAFILE        COARSE 22-JAN-07
     3      264    12.95 DATAFILE        COARSE 21-JAN-07
     3      265    29.30 DATAFILE        COARSE 22-JAN-07
     3      266    29.30 DATAFILE        COARSE 22-JAN-07
     3      267      .78 DATAFILE        COARSE 21-JAN-07
     3      268      .02 CHANGETRACKING  COARSE 22-JAN-07
     3      269      .02 CONTROLFILE     FINE   11-JAN-07
     3      270      .02 CONTROLFILE     FINE   11-JAN-07
     3      271      .02 CONTROLFILE     FINE   11-JAN-07
     3      272      .10 DATAFILE        COARSE 21-JAN-07
     3      273     9.77 DATAFILE        COARSE 21-JAN-07
     3      274     9.77 DATAFILE        COARSE 21-JAN-07
     3      275     9.77 DATAFILE        COARSE 21-JAN-07
     3      276    10.77 DATAFILE        COARSE 21-JAN-07
     3      277     2.00 DATAFILE        COARSE 21-JAN-07
     3      278     1.00 DATAFILE        COARSE 21-JAN-07
     3      279    27.00 DATAFILE        COARSE 22-JAN-07
     3      280    26.00 DATAFILE        COARSE 22-JAN-07
     3      281    26.00 DATAFILE        COARSE 22-JAN-07
     3      282    13.00 DATAFILE        COARSE 21-JAN-07
     3      283    14.00 DATAFILE        COARSE 21-JAN-07
     3      284    15.00 DATAFILE        COARSE 21-JAN-07
     3      285     1.95 ONLINELOG       FINE   21-JAN-07
     3      286     1.95 ONLINELOG       FINE   22-JAN-07
     3      287     1.95 ONLINELOG       FINE   21-JAN-07
     3      288     1.95 ONLINELOG       FINE   22-JAN-07
     3      289     1.95 ONLINELOG       FINE   21-JAN-07
     3      290     1.95 ONLINELOG       FINE   22-JAN-07
     3      291     1.95 ONLINELOG       FINE   22-JAN-07
     3      292     1.95 ONLINELOG       FINE   22-JAN-07

40 rows selected.
================================================================
5. chk_asm_file2 : same as chk_asm_file but provide more information



----- script start below this line -----------
#!/bin/csh
# chk_asm_file2
# displays one row for every disk group discovered by the ASM instance
# source ~/.cshrc
10asm
clear
echo
set asm_sid=`ps -ef |grep pmon|grep +ASM|awk '{print$8}'| cut -d"_" -f3`
setenv ORACLE_SID $asm_sid

sqlplus -s '/ as sysdba' << EOF
set pages 100 lines 130 echo on head on flush on veri on feed on

col TYPE form a15
col FILE_NUMBER form 9999 head FILE_NUM
col GROUP_NUMBER form 9999 head GR_NUM
col GB for 9999.99

select GROUP_NUMBER   ,
 FILE_NUMBER          ,
 COMPOUND_INDEX       ,
 INCARNATION          ,
 BLOCK_SIZE           ,
 BLOCKS               ,
 BYTES/1024/1024/1024 GB ,
 TYPE                 ,
 STRIPED              ,
 CREATION_DATE        ,
 MODIFICATION_DATE
from v$asm_file
where TYPE != 'ARCHIVELOG'
/
exit
EOF
echo
exit
# eof chk_asm_file2
----- script finish before this line ----------



Output:

GR_NUM FILE_NUM COMPOUND_INDEX INCARNATION BLOCK_SIZE     BLOCKS       GB TYPE            STRIPE CREATION_ MODIFICAT
------ -------- -------------- ----------- ---------- ---------- -------- --------------- ------ --------- ---------
     1      256       16777472   589938807        512         13      .00 PARAMETERFILE   COARSE 08-MAY-06 22-JAN-07
     1      643       16777859   612467025      16384       1517      .02 CONTROLFILE     FINE   21-JAN-07 22-JAN-07
     1      644       16777860   612467037      16384       1517      .02 CONTROLFILE     FINE   21-JAN-07 22-JAN-07
     3      256       50331904   581368661      16384       1515      .02 CONTROLFILE     FINE   02-FEB-06 08-MAY-06
     3      257       50331905   602197139       8192     435201     3.32 DATAFILE        COARSE 26-SEP-06 21-JAN-07
     3      258       50331906   592234419       8192    3840001    29.30 DATAFILE        COARSE 04-JUN-06 22-JAN-07
     3      259       50331907   581368675       8192     838401     6.40 DATAFILE        COARSE 02-FEB-06 21-JAN-07
     3      260       50331908   581368683       8192    3840001    29.30 DATAFILE        COARSE 02-FEB-06 22-JAN-07
     3      261       50331909   581368685       8192     882769     6.73 DATAFILE        COARSE 02-FEB-06 22-JAN-07
     3      262       50331910   581368687       8192    4194177    32.00 TEMPFILE        COARSE 02-FEB-06 11-JAN-07
     3      263       50331911   581368691       8192    2621441    20.00 DATAFILE        COARSE 02-FEB-06 22-JAN-07
     3      264       50331912   581368733       8192    1697793    12.95 DATAFILE        COARSE 02-FEB-06 21-JAN-07
     3      265       50331913   581368733       8192    3840001    29.30 DATAFILE        COARSE 02-FEB-06 22-JAN-07
     3      266       50331914   592234263       8192    3840001    29.30 DATAFILE        COARSE 04-JUN-06 22-JAN-07
     3      267       50331915   590552237       8192     102401      .78 DATAFILE        COARSE 16-MAY-06 21-JAN-07
     3      268       50331916   581864701        512      43137      .02 CHANGETRACKING  COARSE 08-FEB-06 22-JAN-07
     3      269       50331917   589938393      16384       1517      .02 CONTROLFILE     FINE   08-MAY-06 11-JAN-07
     3      270       50331918   589938393      16384       1517      .02 CONTROLFILE     FINE   08-MAY-06 11-JAN-07
     3      271       50331919   589938393      16384       1517      .02 CONTROLFILE     FINE   08-MAY-06 11-JAN-07
     3      272       50331920   590541187      16384       6401      .10 DATAFILE        COARSE 15-MAY-06 21-JAN-07
     3      273       50331921   590541193      16384     640001     9.77 DATAFILE        COARSE 15-MAY-06 21-JAN-07
     3      274       50331922   590541287      16384     640001     9.77 DATAFILE        COARSE 15-MAY-06 21-JAN-07
     3      275       50331923   590541383      16384     640001     9.77 DATAFILE        COARSE 15-MAY-06 21-JAN-07
     3      276       50331924   590541477      16384     705537    10.77 DATAFILE        COARSE 15-MAY-06 21-JAN-07
     3      277       50331925   590541577      16384     131073     2.00 DATAFILE        COARSE 15-MAY-06 21-JAN-07
     3      278       50331926   590541591      16384      65537     1.00 DATAFILE        COARSE 15-MAY-06 21-JAN-07
     3      279       50331927   590541663       8192    3538945    27.00 DATAFILE        COARSE 15-MAY-06 22-JAN-07
     3      280       50331928   590541677       8192    3407873    26.00 DATAFILE        COARSE 15-MAY-06 22-JAN-07
     3      281       50331929   590541691       8192    3407873    26.00 DATAFILE        COARSE 15-MAY-06 22-JAN-07
     3      282       50331930   590541707       8192    1703937    13.00 DATAFILE        COARSE 15-MAY-06 21-JAN-07
     3      283       50331931   590541723       8192    1835009    14.00 DATAFILE        COARSE 15-MAY-06 21-JAN-07
     3      284       50331932   590541737       8192    1966081    15.00 DATAFILE        COARSE 15-MAY-06 21-JAN-07
     3      285       50331933   590541871        512    4096001     1.95 ONLINELOG       FINE   15-MAY-06 21-JAN-07
     3      286       50331934   590541895        512    4096001     1.95 ONLINELOG       FINE   15-MAY-06 22-JAN-07
     3      287       50331935   590541921        512    4096001     1.95 ONLINELOG       FINE   15-MAY-06 21-JAN-07
     3      288       50331936   590541943        512    4096001     1.95 ONLINELOG       FINE   15-MAY-06 22-JAN-07
     3      289       50331937   590541969        512    4096001     1.95 ONLINELOG       FINE   15-MAY-06 21-JAN-07
     3      290       50331938   590541989        512    4096001     1.95 ONLINELOG       FINE   15-MAY-06 22-JAN-07
     3      291       50331939   590542011        512    4096001     1.95 ONLINELOG       FINE   15-MAY-06 22-JAN-07
     3      292       50331940   590542031        512    4096001     1.95 ONLINELOG       FINE   15-MAY-06 22-JAN-07

40 rows selected.
================================================================
6. chk_asm_file3: List files and total used space



----- script start below this line -----------


#!/bin/csh


# chk_asm_files


# displays one row for every disk group discovered by the ASM instance


# source ~/.cshrc


10asm


clear


echo


set asm_sid=`ps -ef |grep pmon|grep +ASM|awk '{print$8}'| cut -d"_" -f3`


setenv ORACLE_SID $asm_sid


sqlplus -s '/ as sysdba' << EOF


set pages 50000 lines 130 echo on head on flush on veri on feed on





col Disk_Group form a15


col File_Type  form a30


col MB         form '999,999,999'


select


 a.NAME Disk_Group      ,


 b.TYPE File_Type       ,


 b.BYTES/1024/1024 MB


from    v$asm_diskgroup a,


        v$asm_file      b


where   a.GROUP_NUMBER=b.GROUP_NUMBER


order by 1,2


/


exit


EOF


echo


exit


# eof chk_asm_file3


----- script finish before this line ----------



Output:





DISK_GROUP      FILE_TYPE                                MB


--------------- ------------------------------ ------------


CRMARCHDG       ARCHIVELOG                            1,570


CRMARCHDG       ARCHIVELOG                            1,998


CRMARCHDG       ARCHIVELOG                            1,996


CRMARCHDG       ARCHIVELOG                            1,620


CRMARCHDG       ARCHIVELOG                                8


CRMARCHDG       ARCHIVELOG                              205


CRMARCHDG       ARCHIVELOG                               76


CRMARCHDG       ARCHIVELOG                                0


CRMARCHDG       ARCHIVELOG                              370


CRMARCHDG       ARCHIVELOG                            1,221


CRMARCHDG       ARCHIVELOG                              774


CRMARCHDG       ARCHIVELOG                              991


CRMARCHDG       ARCHIVELOG                            1,994


CRMARCHDG       ARCHIVELOG                            1,995


CRMARCHDG       ARCHIVELOG                            2,000


CRMARCHDG       ARCHIVELOG                            1,995


CRMARCHDG       ARCHIVELOG                            1,995


CRMARCHDG       ARCHIVELOG                            1,993


CRMARCHDG       ARCHIVELOG                            1,996


CRMARCHDG       ARCHIVELOG                            1,998


CRMARCHDG       ARCHIVELOG                            1,999


CRMARCHDG       ARCHIVELOG                            1,993


CRMARCHDG       ARCHIVELOG                            1,994


CRMARCHDG       ARCHIVELOG                            1,996


CRMARCHDG       ARCHIVELOG                            1,999


CRMARCHDG       ARCHIVELOG                                0


CRMARCHDG       ARCHIVELOG                              398


CRMARCHDG       ARCHIVELOG                            1,999


CRMARCHDG       ARCHIVELOG                               19


CRMARCHDG       ARCHIVELOG                               66


CRMARCHDG       ARCHIVELOG                                0


CRMARCHDG       ARCHIVELOG                            1,994


CRMARCHDG       ARCHIVELOG                            1,998


CRMARCHDG       CONTROLFILE                              24


CRMARCHDG       CONTROLFILE                              24


CRMARCHDG       PARAMETERFILE                             0


CRMDATADG       CHANGETRACKING                           21


CRMDATADG       CONTROLFILE                              24


CRMDATADG       CONTROLFILE                              24


CRMDATADG       CONTROLFILE                              24


CRMDATADG       CONTROLFILE                              24


CRMDATADG       DATAFILE                              3,400


CRMDATADG       DATAFILE                             30,000


CRMDATADG       DATAFILE                             15,360


CRMDATADG       DATAFILE                             14,336


CRMDATADG       DATAFILE                             13,312


CRMDATADG       DATAFILE                             26,624


CRMDATADG       DATAFILE                             26,624


CRMDATADG       DATAFILE                             27,648


CRMDATADG       DATAFILE                              1,024


CRMDATADG       DATAFILE                              2,048


CRMDATADG       DATAFILE                              6,550


CRMDATADG       DATAFILE                             30,000


CRMDATADG       DATAFILE                              6,919


CRMDATADG       DATAFILE                             20,480


CRMDATADG       DATAFILE                             13,264


CRMDATADG       DATAFILE                             30,000


CRMDATADG       DATAFILE                             30,000


CRMDATADG       DATAFILE                                800


CRMDATADG       DATAFILE                                100


CRMDATADG       DATAFILE                             10,000


CRMDATADG       DATAFILE                             10,000


CRMDATADG       DATAFILE                             10,000


CRMDATADG       DATAFILE                             11,024


CRMDATADG       ONLINELOG                             2,000


CRMDATADG       ONLINELOG                             2,000


CRMDATADG       ONLINELOG                             2,000


CRMDATADG       ONLINELOG                             2,000


CRMDATADG       ONLINELOG                             2,000


CRMDATADG       ONLINELOG                             2,000


CRMDATADG       ONLINELOG                             2,000


CRMDATADG       ONLINELOG                             2,000


CRMDATADG       TEMPFILE                             32,767





73 rows selected.
================================================================
7. chk_asm_grp_stat: Display configuration and statistics of ASM Disk Groups

----- script start below this line -----------
#!/bin/csh
# chk_asm_grp_stat
# displays one row for every disk group discovered by the ASM instance
10asm
clear
echo
set asm_sid=`ps -ef |grep pmon|grep +ASM|awk '{print$8}'| cut -d"_" -f3`
setenv ORACLE_SID $asm_sid

sqlplus -s '/ as sysdba' << EOF
set pages 50000 lines 130 echo on head on flush on veri on feed on

col NAME for a10
col SEC_SZ for 999
col BLK_SZ for 9999
col STR_MB for 99
col GROUP_NUMBER for 99 head GRP
col COMPATIBILITY for a10
col DB_COMPAT for a10
col STATE for a7
col TYPE for a7
col TOT_GB for 999.9
col USABLE_GB for 999.9
col OFF_DSK for 999
col UNBLC for a5
col DB_COMPAT for a10

select GROUP_NUMBER ,
 NAME               ,
 SECTOR_SIZE SEC_SZ ,
 BLOCK_SIZE  BLK_SZ ,
 ALLOCATION_UNIT_SIZE /1024/1024 STR_MB,
 STATE              ,
 TYPE               ,
 TOTAL_MB /1024 TOT_GB,
 USABLE_FILE_MB /1024 USABLE_GB     ,
 OFFLINE_DISKS   OFF_DSK  ,
 UNBALANCED      UNBLC ,
 DATABASE_COMPATIBILITY DB_COMPAT
from v$asm_diskgroup_stat
/
exit
EOF
echo
exit
# eof chk_asm_grp_stat
----- script finish before this line ----------



Output:

GRP NAME       SEC_SZ BLK_SZ STR_MB STATE   TYPE    TOT_GB USABLE_GB OFF_DSK UNBLC DB_COMPAT
--- ---------- ------ ------ ------ ------- ------- ------ --------- ------- ----- ----------
  1 CRMARCHDG     512   4096      1 MOUNTED EXTERN   231.8     189.4       0 N     10.1.0.0.0
  3 CRMDATADG     512   4096      1 MOUNTED EXTERN   927.2     547.7       0 N     10.1.0.0.0

2 rows selected.
================================================================

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.