ASM

From dbawiki
Jump to: navigation, search

Useful ASM commands[edit]

Migrate From Database File system to ASM[edit]

See what disk groups exist[edit]

select group_number
,      name
from   v$asm_diskgroup;

Check space available in disk groups using query below[edit]

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 CONNECTED   EXTERN          61,436         61,426     99.98
FRA                      512   4,096    1,048,576 CONNECTED   EXTERN           8,190          7,638     93.26
REDO                     512   4,096    1,048,576 CONNECTED   EXTERN           4,094          1,891     46.19
                                                                     --------------- --------------
Grand Total:                                                                  73,720         70,955

See how the disk groups are linked to their paths[edit]

set lines 2000
col label for a20
col path for a20
select mount_status
,      header_status
,      mode_status
,      state
,      total_mb
,      free_mb
,      name
,      path
,      label
from   v$asm_disk;

gives... 

MOUNT_S HEADER_STATU MODE_ST STATE      TOTAL_MB    FREE_MB NAME                           PATH                 LABEL
------- ------------ ------- -------- ---------- ---------- ------------------------------ -------------------- ----------
CLOSED  PROVISIONED  ONLINE  NORMAL            0          0                                ORCL:DATA04          DATA04
CLOSED  PROVISIONED  ONLINE  NORMAL            0          0                                ORCL:FRA03           FRA03
CLOSED  PROVISIONED  ONLINE  NORMAL            0          0                                ORCL:FRA04           FRA04
CACHED  MEMBER       ONLINE  NORMAL        20479          4 DATA01                         ORCL:DATA01          DATA01
CACHED  MEMBER       ONLINE  NORMAL        20479          0 DATA02                         ORCL:DATA02          DATA02
CACHED  MEMBER       ONLINE  NORMAL         4095        272 FRA01                          ORCL:FRA01           FRA01
CACHED  MEMBER       ONLINE  NORMAL         4095        280 FRA02                          ORCL:FRA02           FRA02
CACHED  MEMBER       ONLINE  NORMAL         2047       1103 REDO01                         ORCL:REDO01          REDO01
CACHED  MEMBER       ONLINE  NORMAL         2047       1100 REDO02                         ORCL:REDO02          REDO02
CACHED  MEMBER       ONLINE  NORMAL        20478          6 DATA03                         ORCL:DATA03          DATA03

10 rows selected.

Add disk to a disk group[edit]

alter diskgroup data add disk 'ORCL:DATA04' name data04;
alter diskgroup fra add disk 'ORCL:FRA03' name fra03;
alter diskgroup fra add disk 'ORCL:FRA04' name fra04;

Now the same disk query as above gives an updated view 

MOUNT_S HEADER_STATU MODE_ST STATE      TOTAL_MB    FREE_MB NAME                           PATH                 LABEL
------- ------------ ------- -------- ---------- ---------- ------------------------------ -------------------- ----------
CACHED  MEMBER       ONLINE  NORMAL        20479       5121 DATA01                         ORCL:DATA01          DATA01
CACHED  MEMBER       ONLINE  NORMAL        20479       5120 DATA02                         ORCL:DATA02          DATA02
CACHED  MEMBER       ONLINE  NORMAL         4095        903 FRA01                          ORCL:FRA01           FRA01
CACHED  MEMBER       ONLINE  NORMAL         4095        908 FRA02                          ORCL:FRA02           FRA02
CACHED  MEMBER       ONLINE  NORMAL         2047       1103 REDO01                         ORCL:REDO01          REDO01
CACHED  MEMBER       ONLINE  NORMAL         2047       1100 REDO02                         ORCL:REDO02          REDO02
CACHED  MEMBER       ONLINE  NORMAL        20478       5122 DATA03                         ORCL:DATA03          DATA03
CACHED  MEMBER       ONLINE  NORMAL        20479       5124 DATA04                         ORCL:DATA04          DATA04
CACHED  MEMBER       ONLINE  NORMAL         4093       3461 FRA03                          ORCL:FRA03           FRA03
CACHED  MEMBER       ONLINE  NORMAL         4094       3463 FRA04                          ORCL:FRA04           FRA04

10 rows selected.

Recheck space available in disk groups using query below[edit]

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     EXTERN          81,915         61,478     75.05
FRA                      512   4,096    1,048,576 MOUNTED     EXTERN          16,377          7,829     47.80
REDO                     512   4,096    1,048,576 MOUNTED     EXTERN           4,094          1,891     46.19
                                                                     --------------- --------------
Grand Total:                                                                 102,386         71,198

Restart ASM[edit]

cat /opt/oracle/oak/onecmd/tmp/restartasm.sh 

/u01/app/11.2.0.4/grid/bin/crsctl stop res ora.crsd -init
/u01/app/11.2.0.4/grid/bin/crsctl stop res ora.asm -init
/u01/app/11.2.0.4/grid/bin/crsctl start res ora.asm -init
ps -ef | grep  -v grep | grep -q smon_+ASM
if [ $? -ne 0 ]
then
echo 'asm instance did not start in 1st attempt, sleeping for 30 secs and retrying'
sleep 30
/u01/app/11.2.0.4/grid/bin/crsctl start res ora.asm -init
fi
/u01/app/11.2.0.4/grid/bin/crsctl start res ora.crsd -init

Where are the real disk group devices?[edit]

#!/bin/ksh
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk  '{print $2}'`
v_minor=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
v_major=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device  [$v_minor $v_major]"
done

or check in 

ls -al /dev/oracleasm/disks

The major and minor numbers should match up.

Check free space in ASM diskgroups[edit]

cat /opt/oracle/oak/onecmd/tmp/chekDGSpaceSql.sh 

#! /bin/sh
export ORACLE_HOME=/u01/app/12.1.0.2/grid
export ORACLE_SID=+ASM1
$ORACLE_HOME/bin/sqlplus '/as sysasm' <<EOF
column path format a40
column name format a35
set linesize 200
select name, total_mb,free_mb from v\$asm_diskgroup;
quit
EOF

gives 

NAME                                  TOTAL_MB    FREE_MB
----------------------------------- ---------- ----------
RECO                                   2424000    2379084
REDO                                    763120     687700
DATA                                  14745600    2930908

or 

set linesize 145
set pagesize 9999
set verify off

column group_name format a20 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
order  by name;

Scripts[edit]

asm_capacity.sql[edit]

Check resistance of an ASM cluster against a disk / cell failure 

SPOOL asm_disk_capacity.output

SET SERVEROUTPUT ON
SET LINES 155
SET PAGES 0
SET TRIMSPOOL ON

DECLARE
   v_num_disks    NUMBER;
   v_group_number   NUMBER;
   v_max_total_mb   NUMBER;

   v_required_free_mb   NUMBER;
   v_usable_mb      NUMBER;
   v_cell_usable_mb   NUMBER;
   v_one_cell_usable_mb   NUMBER;
   v_enuf_free      BOOLEAN := FALSE;
   v_enuf_free_cell   BOOLEAN := FALSE;

   v_req_mirror_free_adj_factor   NUMBER := 1.10;
   v_req_mirror_free_adj         NUMBER := 0;
   v_one_cell_req_mir_free_mb     NUMBER  := 0;

   v_disk_desc      VARCHAR(10) := 'SINGLE';
   v_offset      NUMBER := 50;

   v_db_version   VARCHAR2(8);
   v_inst_name    VARCHAR2(1);

   v_cfc_fail_msg VARCHAR2(152);

BEGIN

   SELECT substr(version,1,8), substr(instance_name,1,1)    INTO v_db_version, v_inst_name    FROM v$instance;

   IF v_inst_name <> '+' THEN
      DBMS_OUTPUT.PUT_LINE('ERROR: THIS IS NOT AN ASM INSTANCE!  PLEASE LOG ON TO AN ASM INSTANCE AND RE-RUN THIS SCRIPT.');
      GOTO the_end;
   END IF;

    DBMS_OUTPUT.PUT_LINE('------ DISK and CELL Failure Diskgroup Space Reserve Requirements  ------');
    DBMS_OUTPUT.PUT_LINE(' This procedure determines how much space you need to survive a DISK or CELL failure. It also shows the usable space ');
    DBMS_OUTPUT.PUT_LINE(' available when reserving space for disk or cell failure.  ');
   DBMS_OUTPUT.PUT_LINE(' Please see MOS note 1551288.1 for more information.  ');
   DBMS_OUTPUT.PUT_LINE('.  .  .');
    DBMS_OUTPUT.PUT_LINE(' Description of Derived Values:');
    DBMS_OUTPUT.PUT_LINE(' One Cell Required Mirror Free MB : Required Mirror Free MB to permit successful rebalance after losing largest CELL regardless of redundancy type');
    DBMS_OUTPUT.PUT_LINE(' Disk Required Mirror Free MB     : Space needed to rebalance after loss of single or double disk failure (for normal or high redundancy)');
    DBMS_OUTPUT.PUT_LINE(' Disk Usable File MB              : Usable space available after reserving space for disk failure and accounting for mirroring');
    DBMS_OUTPUT.PUT_LINE(' Cell Usable File MB              : Usable space available after reserving space for SINGLE cell failure and accounting for mirroring');
   DBMS_OUTPUT.PUT_LINE('.  .  .');

   IF (v_db_version = '11.2.0.3') OR (v_db_version = '11.2.0.4') OR (v_db_version = '12.1.0.1')  THEN
      v_req_mirror_free_adj_factor := 1.10;
      DBMS_OUTPUT.PUT_LINE('ASM Version: '||v_db_version);
   ELSE
      v_req_mirror_free_adj_factor := 1.5;
      DBMS_OUTPUT.PUT_LINE('ASM Version: '||v_db_version||'  - WARNING DISK FAILURE COVERAGE ESTIMATES HAVE NOT BEEN VERIFIED ON THIS VERSION!');
   END IF;

   DBMS_OUTPUT.PUT_LINE('.  .  .');
-- Set up headings
     DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------------------------------------------------------');
      DBMS_OUTPUT.PUT('|          ');
      DBMS_OUTPUT.PUT('|         ');
      DBMS_OUTPUT.PUT('|     ');
      DBMS_OUTPUT.PUT('|          ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|Cell Req''d  ');
      DBMS_OUTPUT.PUT('|Disk Req''d  ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|    ');
      DBMS_OUTPUT.PUT('|    ');
      DBMS_OUTPUT.PUT('|       ');
      DBMS_OUTPUT.PUT_Line('|');
      DBMS_OUTPUT.PUT('|          ');
      DBMS_OUTPUT.PUT('|DG       ');
      DBMS_OUTPUT.PUT('|Num  ');
      DBMS_OUTPUT.PUT('|Disk Size ');
      DBMS_OUTPUT.PUT('|DG Total    ');
      DBMS_OUTPUT.PUT('|DG Used     ');
      DBMS_OUTPUT.PUT('|DG Free     ');
      DBMS_OUTPUT.PUT('|Mirror Free ');
      DBMS_OUTPUT.PUT('|Mirror Free ');
      DBMS_OUTPUT.PUT('|Disk Usable ');
      DBMS_OUTPUT.PUT('|Cell Usable ');
      DBMS_OUTPUT.PUT('|    ');
      DBMS_OUTPUT.PUT('|    ');
      DBMS_OUTPUT.PUT_LINE('|PCT    |');
      DBMS_OUTPUT.PUT('|DG Name   ');
      DBMS_OUTPUT.PUT('|Type     ');
      DBMS_OUTPUT.PUT('|Disks');
      DBMS_OUTPUT.PUT('|MB        ');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|File MB     ');
      DBMS_OUTPUT.PUT('|File MB     ');
      DBMS_OUTPUT.PUT('|DFC ');
      DBMS_OUTPUT.PUT('|CFC ');
      DBMS_OUTPUT.PUT_LINE('|Util   |');
     DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------------------------------------------------------');

   FOR dg IN (SELECT name, type, group_number, total_mb, free_mb, required_mirror_free_mb FROM v$asm_diskgroup ORDER BY name) LOOP

      v_enuf_free := FALSE;

     v_req_mirror_free_adj := dg.required_mirror_free_mb * v_req_mirror_free_adj_factor;

      -- Find largest amount of space allocated to a cell
      SELECT sum(disk_cnt), max(max_total_mb), max(sum_total_mb)*v_req_mirror_free_adj_factor
     INTO v_num_disks, v_max_total_mb, v_one_cell_req_mir_free_mb
      FROM (SELECT count(1) disk_cnt, max(total_mb) max_total_mb, sum(total_mb) sum_total_mb
      FROM v$asm_disk
     WHERE group_number = dg.group_number
     GROUP BY failgroup);

      -- Eighth Rack
      IF dg.type = 'NORMAL' THEN

         -- Eighth Rack
         IF (v_num_disks < 36) THEN
            -- Use eqn: y = 1.21344 x+ 17429.8
            v_required_free_mb :=  1.21344 * v_max_total_mb + 17429.8;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Quarter Rack
         ELSIF (v_num_disks >= 36 AND v_num_disks < 84) THEN
            -- Use eqn: y = 1.07687 x+ 19699.3
                        -- Revised 2/21/14 for 11.2.0.4 to use eqn: y=0.803199x + 156867, more space but safer
            v_required_free_mb := 0.803199 * v_max_total_mb + 156867;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Half Rack
         ELSIF (v_num_disks >= 84 AND v_num_disks < 168) THEN
            -- Use eqn: y = 1.02475 x+53731.3
            v_required_free_mb := 1.02475 * v_max_total_mb + 53731.3;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Full rack is most conservative, it will be default
         ELSE
            -- Use eqn: y = 1.33333 x+83220.
            v_required_free_mb := 1.33333 * v_max_total_mb + 83220;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

         END IF;

         -- DISK usable file MB
         v_usable_mb := ROUND((dg.free_mb - v_required_free_mb)/2);
         v_disk_desc := 'ONE disk';

         -- CELL usable file MB
         v_cell_usable_mb := ROUND( (dg.free_mb - v_one_cell_req_mir_free_mb)/2 );
         v_one_cell_usable_mb := v_cell_usable_mb;

      ELSE
         -- HIGH redundancy

         -- Eighth Rack
         IF (v_num_disks <= 18) THEN
            -- Use eqn: y = 4x + 0
                        -- Updated for 11.2.0.4 to higher value:  y = 3.84213x + 84466.4
            v_required_free_mb :=  3.84213 * v_max_total_mb + 84466.4;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Quarter Rack
         ELSIF (v_num_disks > 18 AND v_num_disks <= 36) THEN
            -- Use eqn: y = 3.87356 x+417692.
            v_required_free_mb := 3.87356 * v_max_total_mb + 417692;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Half Rack
         ELSIF (v_num_disks > 36 AND v_num_disks <= 84) THEN
            -- Use eqn: y = 2.02222 x+56441.6
            v_required_free_mb := 2.02222 * v_max_total_mb + 56441.6;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Full rack is most conservative, it will be default
         ELSE
            -- Use eqn: y = 2.14077 x+54276.4
            v_required_free_mb := 2.14077 * v_max_total_mb + 54276.4;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

         END IF;

         -- DISK usable file MB
         v_usable_mb := ROUND((dg.free_mb - v_required_free_mb)/3);
         v_disk_desc := 'TWO disks';

         -- CELL usable file MB
         v_one_cell_usable_mb := ROUND( (dg.free_mb - v_one_cell_req_mir_free_mb)/3 );

      END IF;
      DBMS_OUTPUT.PUT('|'||RPAD(dg.name,v_offset-40));
      DBMS_OUTPUT.PUT('|'||RPAD(nvl(dg.type,'  '),v_offset-41));
      DBMS_OUTPUT.PUT('|'||LPAD(TO_CHAR(v_num_disks),v_offset-45));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(v_max_total_mb,'9,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(dg.total_mb,'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(dg.total_mb - dg.free_mb,'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(dg.free_mb,'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(ROUND(v_one_cell_req_mir_free_mb),'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(ROUND(v_required_free_mb),'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(ROUND(v_usable_mb),'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(ROUND(v_one_cell_usable_mb),'999,999,999'));

      IF v_enuf_free THEN
         DBMS_OUTPUT.PUT('|'||'PASS');
      ELSE
         DBMS_OUTPUT.PUT('|'||'FAIL');
      END IF;

     IF dg.type = 'NORMAL' THEN
        -- Calc Free Space for Rebalance Due to Cell Failure
        IF v_req_mirror_free_adj < dg.free_mb THEN
           DBMS_OUTPUT.PUT('|'||'PASS');
        ELSE
            DBMS_OUTPUT.PUT('|'||'FAIL');
            v_cfc_fail_msg := 'WARNING: Not enough free space to rebalance after loss of ONE cell (however, cell failure is very rare)';
        END IF;
     ELSE
        -- Calc Free Space for Rebalance Due to Single Cell Failure
        IF v_one_cell_req_mir_free_mb < dg.free_mb THEN
           DBMS_OUTPUT.PUT('|'||'PASS');
        ELSE
           DBMS_OUTPUT.PUT('|'||'FAIL');
           v_cfc_fail_msg := 'WARNING: Not enough free space to rebalance after loss of ONE cell(However, cell failure is very rare and high redundancy offers ample protection already)';
        END IF;

     END IF;

     -- Calc Disk Utilization Percentage
        IF dg.total_mb > 0 THEN
           DBMS_OUTPUT.PUT_LINE('|'||TO_CHAR((((dg.total_mb - dg.free_mb)/dg.total_mb)*100),'999.9')||CHR(37)||'|');
        ELSE
           DBMS_OUTPUT.PUT_LINE('|       |');
        END IF;

   END LOOP;

     DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------------------------------------------------------');
   <<the_end>>

   IF v_cfc_fail_msg is not null THEN
      DBMS_OUTPUT.PUT_LINE('Cell Failure Coverage Freespace Failures Detected. Warning Message Follows.');
      DBMS_OUTPUT.PUT_LINE(v_cfc_fail_msg);
   END IF;

   DBMS_OUTPUT.PUT_LINE('.  .  .');
   DBMS_OUTPUT.PUT_LINE('Script completed.');

END;
/

WHENEVER SQLERROR EXIT FAILURE;

SPOOL OFF

asmdisks.ksh[edit]

#!/usr/bin/ksh
export ORACLE_SID=+ASM`$GRID_HOME/bin/olsnodes -l -n | cut  -f2`
export ORACLE_HOME=`grep "^+ASM" /etc/oratab | cut -d: -f2`
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus -s "/ as sysdba" <<EOF

prompt
prompt
prompt =================== OVERVIEW OF ASM DISKGROUPS ==================================
set lines 150
set pages 50000
Col name format a15
Col nr_disks format 99
Col pctused format 9999 heading "%Usd"
Col used format 9999990 heading "Used"
select name, state, total_mb, (total_mb-free_mb) as used,
       case when total_mb > 0 then trunc(100*(total_mb-free_mb)/total_mb) else null end as pctused,
       (select count(*) from v\$asm_disk
         where group_number = g.group_number) as nr_disks,
       type
 from v\$asm_diskgroup g
;

prompt
prompt ==================== OVERVIEW OF ASM DISKS ======================================
set feed off
set pages 50000
Set lines 150
Col path format a30
Col label format a10
Col failgroup format a11
Col diskgroup format a10
Col state format a10
Col total_mb format 9999990 heading "Total"
Col used format 9999990 heading "Used"
Col pctused format 9999 Heading "%Usd"
Col name format a15 heading "DiskName"
Col header_status format a15 heading "Header"
break on header_status skip 1 dup on diskgroup skip 0 nodup
select g.name as diskgroup, d.path, d.total_mb, (d.total_mb-d.free_mb) as used,
       case when d.total_mb > 0 then decode(d.total_mb,0,0,trunc(100*(d.total_mb-d.free_mb)/d.total_mb)) else null end as pctused,
       substr(d.mode_status,1,3) as st, header_status,
       d.failgroup, d.state, /*substr(g.type,1,3) as red,*/ d.name, mount_status
from v\$asm_disk d, (select * from v\$asm_diskgroup where group_number <> 0) g
Where d.group_number = g.group_number(+)
Order by g.name, d.path;

prompt
prompt
prompt =================== OVERVIEW OF ASM OPERATIONS ==================================
Col name format a15
select g.name, o.operation, o.state, o.sofar, o.est_work, o.est_minutes, o.power
 from v\$asm_diskgroup g, gv\$asm_operation o
 where o.group_number = g.group_number
;


prompt
exit;
EOF

asmdu.ksh[edit]

#!/usr/bin/ksh
export ORACLE_SID=+ASM`$GRID_HOME/bin/olsnodes -l -n | cut  -f2`
export ORACLE_HOME=`grep "^+ASM" /etc/oratab | head -1 | cut -d: -f2`
export PATH=$ORACLE_HOME/bin:$PATH

## If Argument, then set bind variable ( filtering on file_path )
if [ -z $1 ]
then
  filter=""
else
  filter=$1
  filter=`echo $filter|tr [a-z] [A-Z]`
fi

sqlplus -s "/ as sysdba" <<EOF

Set pages 100
set lines 100
Col diskgroup format a10
Col dbuniquename format a32
Col ftype format a16
break on diskgroup on dbuniquename on ftype
SELECT diskgroup, dbuniquename, ftype, round(sum(bytes)/(1024*1024),2) as total_MB, count(*) as number_of_files
FROM (
        SELECT g.name diskgroup
             , SUBSTR(a.fpath,2,INSTR(a.fpath,'/',1,2)-1-1) as dbuniquename
             , SUBSTR(a.fpath,INSTR(a.fpath,'/',1,2)+1,INSTR(a.fpath,'/',1,3)-1-INSTR(a.fpath,'/',1,2)) as ftype
             , f.bytes as bytes
          FROM v\$asm_file f
             , (
                SELECT sys_connect_by_path(name, '/') as fpath, reference_index, parent_index, group_number, file_number
                FROM v\$asm_alias
                START WITH (mod(parent_index, power(2, 24))) = 0
                CONNECT BY PRIOR reference_index = parent_index
               ) a
             , v\$asm_diskgroup g
          WHERE f.file_number = a.file_number
            AND f.group_number = a.group_number
            AND g.group_number = a.group_number
     )
  where dbuniquename like '%$filter%'
  GROUP BY diskgroup, dbuniquename ,ftype
/

exit;
EOF

asmfiles.ksh[edit]

#!/usr/bin/ksh
export ORACLE_SID=+ASM`$GRID_HOME/bin/olsnodes -l -n | cut  -f2`
export ORACLE_HOME=`grep "^+ASM" /etc/oratab | cut -d: -f2`
export PATH=$ORACLE_HOME/bin:$PATH

## If Argument, then set bind variable ( filtering on file_path )
if [ -z $1 ]
then
  filter=""
else
  filter=$1
  filter=`echo $filter|tr [a-z] [A-Z]`
fi

sqlplus -s "/ as sysdba" <<EOF

Set pages 100
--Col full_alias_path format a70
--SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, system_created
-- FROM (SELECT g.name gname, a.parent_index pindex, a.name aname
--              ,a.reference_index rindex , a.system_created
--         FROM v\$asm_alias a, v\$asm_diskgroup g
--         WHERE a.group_number = g.group_number)
-- START WITH (mod(pindex, power(2, 24))) = 0
-- CONNECT BY PRIOR rindex = pindex;


set feed off
col max_width new_value maxwidth noprint
select to_char(max(length(file_path))) as max_width from
         (select sys_connect_by_path(a.name, '/') as file_path
            from v\$asm_alias a, v\$asm_diskgroup g
            where a.group_number = g.group_number
            START WITH (mod(parent_index, power(2, 24))) = 0
            CONNECT BY PRIOR reference_index = parent_index)
;


alter session set nls_numeric_characters = ',.';

set lines 150
col disk_group format a11 heading "Disk Group"
col file_path format a&maxwidth heading "File [ >>> alias ]" word_wrapped
col bytes heading "Bytes" justify right format 9g999g999g999g999
col created format a20
break on report on disk_group skip 1
compute sum label "Group Total" of bytes on disk_group
compute sum label "Total" of bytes on report

SELECT
       disk_group, file_path||
       case when alias_path is not null then ' >>>'||alias_path else null end as file_path,
--       case when bytes < 1024           then lpad(to_char(bytes),8)
--            when bytes < 1024*1024      then lpad(round(bytes/1024),8)||' K'
--            when bytes < 1024*1024*1024 then lpad(round(bytes/1024/1024),8)||' M'
--            else lpad(round(bytes/1024/1024/1024),8)||' G'
--       end  as bytes,
       bytes,
       to_char(creation_date,'dd-mon-yy hh24:mi') as created,
       redundancy, striped
 FROM (SELECT a.group_number, a.file_number, f.bytes, creation_date, redundancy, striped
         FROM v\$asm_alias a, v\$asm_file f
         WHERE a.group_number = f.group_number
         and   a.file_number = f.file_number
         and   a.system_created = 'Y'
       ) x1,
       (select * from
         (select '+'||g.name as disk_group, sys_connect_by_path(a.name, '/') as file_path, a.system_created,
                 a.group_number, a.file_number
            from v\$asm_alias a, v\$asm_diskgroup g
            where a.group_number = g.group_number
            START WITH (mod(parent_index, power(2, 24))) = 0
            CONNECT BY PRIOR reference_index = parent_index)
         where system_created = 'Y'
       ) x2,
       (select * from
         (select concat('+'||g.name, sys_connect_by_path(a.name, '/')) as alias_path, a.system_created,
                 a.group_number, a.file_number
            from v\$asm_alias a, v\$asm_diskgroup g
            where a.group_number = g.group_number
            START WITH (mod(parent_index, power(2, 24))) = 0
            CONNECT BY PRIOR reference_index = parent_index)
         where system_created = 'N'
       ) x3
 where x1.group_number = x2.group_number
 and   x1.file_number = x2.file_number
 and   x1.group_number = x3.group_number(+)
 and   x1.file_number = x3.file_number(+)
 AND file_path like '%$filter%';

exit;
EOF

asmlog.pl[edit]

#!/usr/bin/perl
#
use Env;
use Time::Local;

if ( !$ENV{"GRID_HOME"} ) {
 print "Please set GRID_HOME for home dir of Oracle CRS\n";
 exit 1;
}

use vars qw/ %opt /;
use Getopt::Std;

my $opt_string = 'f';
getopts( "$opt_string", \%opt ) or usage();

$retention=$ARGV[0];
$retention = 3600 if !defined $retention;
print "Retention: $retention\n";

$starttime=timegm(localtime());
print "Starttime: $starttime\n";

print "==========================================================================\n";
print "==========================================================================\n";
print "==========================================================================\n";
print "==========================================================================\n";
print "==========================================================================\n";
print "==========================================================================\n";
print "\n";
print "================= ASM LOG =================\n";
$printing=0;

$Tmp=`$GRID_HOME/bin/olsnodes -n -l`;
($NodeName,$NodeId)=split(/\s+/,$Tmp);
print "NodeName=$NodeName, Id=$NodeId\n";

$temp=`$GRID_HOME/bin/srvctl config asm`;
$temp=~/ASM\shome:\s(.*)/;
$OraHome=$1;
$Instance=`cat /etc/oratab|grep "^+ASM"|cut -f1 -d":"`;
if (! $Instance )
{
    $Instance=`cat /var/opt/oracle/oratab|grep "^+ASM"|cut -f1 -d":"`;
}
chomp($Instance);
print "ASM Instance: $Instance\n";
print "ASM Home: $OraHome\n";
$Bdump=`ORACLE_HOME=$GRID_HOME ORACLE_SID=$Instance sqlplus -s "/ as sysdba" <<EOF
set head off
set feed off
set pages 0
select value from v\\\$parameter where name = 'background_dump_dest';
exit
EOF
`;
chop($Bdump);
print "Bdump=$Bdump\n";

if ($opt{f}) {
 open(ALRT,"tail -f $Bdump/alert_$Instance.log|");
} else {
 open(ALRT,"cat $Bdump/alert_$Instance.log|") || die "Cannot open $Bdump/alert_$Instance.log";
}

while(<ALRT>) {
 chop;
 #print "Processing: $_\n";
 &pr("$_");
}
close ALRT;

print "\n\n";

sub date2secs {
        my $date = shift;
        my @date =(0,0,0,1,0,0);
        @date = reverse (split /[\s:-]/, $date);
#        $date[4]=${{Jan => 0, Feb => 1, Mar => 2, Apr => 3, May => 4, Jun => 5, Jul => 6,
#                    Aug => 7, Sep => 8, Oct => 9, Nov => 10, Dec => 11, }}{$date[4]};
#        $date[4]=${{"01" => 0, "02" => 1, "03" => 2, "04" => 3, "05" => 4, "06" => 5, "07" => 6,
#                    "08" => 7, "09" => 8, "10" => 9, "11" => 10, "12" => 11, }}{$date[4]};
        $date[4]=${{"Jan" => 0, "Feb" => 1, "Mar" => 2, "Apr" => 3, "May" => 4, "Jun" => 5, "Jul" => 6,
                    "Aug" => 7, "Sep" => 8, "Oct" => 9, "Nov" => 10, "Dec" => 11,
                    "01" => 0, "02" => 1, "03" => 2, "04" => 3, "05" => 4, "06" => 5, "07" => 6,
                    "08" => 7, "09" => 8, "10" => 9, "11" => 10, "12" => 11, }}{$date[4]};
          return timegm(@date);
}

#print &date2secs($time1)."\n";
#print timegm(localtime())."\n";

sub printwrap {
 my $line=join " ",@_;
 if (length($line) > 100) {
  my $part1=substr($line,0,rindex($line,' ',80));
  my $part2=substr($line,rindex($line,' ',80)+1);
  print "$part1\n";
  print "           $part2\n";
 } else {
  print "$line\n";
 }

}

sub pr {
 # We need YYYY-MON-DD HH24:MI:SS
 my $line=$_;
 if (/^(...) (...) +(\d+) (\d\d:\d\d:\d\d) (\d\d\d\d)/) {#Alert file
  $datetime="$5-$2-$3 $4";
  $lasttime="$4";
 } elsif (/^(\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d)/) {
  $datetime=$1;
  $lasttime="$1";
 } elsif (/^(\d\d)-(...)-(\d\d\d\d) (\d\d:\d\d:\d\d)/) {
  $datetime="$3-$2-$1 $4";
  $lasttime="$4";
 } else {
  $datetime="";
 }

 if ($datetime) {
  $linetime=&date2secs($datetime);
  if ($linetime > $starttime-$retention) {
   $printing=1;
  } else {
  }
 } else {
  if ($printing) {
    printwrap "$lasttime\+: $line";
  }
 }

}

crsstat.pl[edit]

unless ($#ARGV < 1) {
        &usage;
}

# Usage
sub usage {
    print <<ENDOFUSAGE;
        crsstat {filter}
             filter             Filters the resources bases on NAME.
ENDOFUSAGE
  exit(0);
}

if (@ARGV)
{
    $filter='-w "NAME co '.$ARGV[0].'"';
}

@output=`crsctl status resource -t $filter`;

#--------------------------------------------------------------
# Rapport Status
format STDOUT_TOP =
     RESOURCE NAME                 TARGET    STATE        SERVER STATE_DETAIL
----------------------------------------------------------------------------------------------
.

format =
     @<<<<<<<<<<<<<<<<<<<<<<<<<<<  @<<<<<<<< @<<<<<<<<<<<< @* @*
   $resource,$target,$status, $server, $state_details
.

write();
foreach (@output)
{
    chomp($_);
    if ( /^\w+\.\w+/ )
    { # resource line
        $resource=$_;
    }
    elsif (/^\s+\d+\s+(\w+)\s+(\w+)\s+$/)
    {
        $target=$1;
        $status=$2;
        $server=$3;

        write();
    }
    elsif ( /^\s+\d+\s+(\w+)\s+(\w+)\s+(\w+)\s(.*)$/)
    { # status line Cluster Resources
        $target=$1;
        $status=$2;
        $server=$3;
        $state_details=$4;
        $state_details=~ s/^\s+//;

        write();
        $state_details="";
    }
    elsif ( /^\s+(\w+)\s+(\w+)\s+(\w+)\s(.*)$/)
    { # status line Local Resources
        $target=$1;
        $status=$2;
        $server=$3;
        $state_details=$4;
        $state_details=~ s/^\s+//;

        write();
        $state_details="";
    }
    elsif (/NAME/ || /----/)
    { # Titles line
      # skip
    }
    else
    { # Header Lines
        print $_ . "\n";
        $resource="";
        $target="";
        $server="";
        $state_details="";
    }
}

csslog.pl[edit]

#!/usr/bin/perl
#
use Env;
use Time::Local;

if ( !$ENV{"GRID_HOME"} ) {
 print "Please set GRID_HOME for home dir of Oracle CRS\n";
 exit 1;
}

use vars qw/ %opt /;
use Getopt::Std;

my $opt_string = 'f';
getopts( "$opt_string", \%opt ) or usage();


sub usage {
 print "Usage: csslog [-f]\n";
 exit 0
}

$retention=$ARGV[0];
$retention = 3600 if !defined $retention;
print "Retention: $retention\n";

$starttime=timegm(localtime());

print "==========================================================================\n";
print "==========================================================================\n";
print "==========================================================================\n";
print "==========================================================================\n";
print "==========================================================================\n";
print "==========================================================================\n";
print "\n";

$Tmp=`$GRID_HOME/bin/olsnodes -n -l`;
($NodeName,$NodeId)=split(/\s+/,$Tmp);
print "NodeName=$NodeName, Id=$NodeId\n";

print "================= CSS LOG =================\n";
$printing=0;
$NrIgnored=0;
$Hostname=`hostname -s`;
chomp($Hostname);
if ( ! $NodeName )
{
   $NodeName=$Hostname;
}
$cssdfile="$GRID_HOME/log/$NodeName/cssd/ocssd.log";
print "Logfile: $cssdfile\n";
if ($opt{f}) {
 open(CSS,"tail -f $cssdfile|");
} else {
 open(CSS,"<$cssdfile");
}
while (<CSS>) {
 chop;
 if (!/clssgmDeleteClientListener: cleanup for proc/ &&
     !/clssgmClientConnectMsg: Connect from con/ &&
     !/clssnmSendingThread: sent \d status msgs to all nodes/ &&
     !/clssnmSendingThread: sending status msg to all nodes/ &&
     !/clssgmClientConnectMsg: properties of cmProc/ &&
     !/clsc_receive: .* Connection failed, transport error/ &&
     !/clscreceive: .* Physical connection .* not active, rc 11$/ &&
     !/clsc_receive: .* Remote disconnect$/ &&
     !/^$/) {
  &pr("$_");
 } else {
  $NrIgnored++;
 }
}
close CSS;
print "Ignored $NrIgnored messages\n";
print "\n\n";

# grep -v "clssgmDeleteClientListener: cleanup for proc" | \
# grep -v "clssgmClientConnectMsg: Connect from con" | \
# grep -v "Connection failed, transport error" | \
# grep -v "not active, rc 11" | \
# grep -v "Remote disconnect$" | \
# grep -v "^$"
#echo
#echo

sub date2secs {
        my $date = shift;
        my @date =(0,0,0,1,0,0);
        @date = reverse (split /[\s:-]/, $date);
#        $date[4]=${{Jan => 0, Feb => 1, Mar => 2, Apr => 3, May => 4, Jun => 5, Jul => 6,
#                    Aug => 7, Sep => 8, Oct => 9, Nov => 10, Dec => 11, }}{$date[4]};
#        $date[4]=${{"01" => 0, "02" => 1, "03" => 2, "04" => 3, "05" => 4, "06" => 5, "07" => 6,
#                    "08" => 7, "09" => 8, "10" => 9, "11" => 10, "12" => 11, }}{$date[4]};
        $date[4]=${{"Jan" => 0, "Feb" => 1, "Mar" => 2, "Apr" => 3, "May" => 4, "Jun" => 5, "Jul" => 6,
                    "Aug" => 7, "Sep" => 8, "Oct" => 9, "Nov" => 10, "Dec" => 11,
                    "01" => 0, "02" => 1, "03" => 2, "04" => 3, "05" => 4, "06" => 5, "07" => 6,
                    "08" => 7, "09" => 8, "10" => 9, "11" => 10, "12" => 11, }}{$date[4]};
          return timegm(@date);
}

#print &date2secs($time1)."\n";
#print timegm(localtime())."\n";

sub printwrap {
 if (length($_) > 1000) {
  $part1=substr($_,0,rindex($_,' ',80));
  $part2=substr($_,rindex($_,' ',80)+1);
  print "$part1\n";
  print "                    $part2\n";
 }
 else {
  print "$_\n";
 }
}

sub pr {
 my $line=$_;
 if (/^\[    CSSD\](\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d)/) {
  $datetime=$1;
 } elsif (/^\[    CSSD\](\d\d)-(...)-(\d\d\d\d) (\d\d:\d\d:\d\d)/) {
  $datetime="$3-$2-$1 $4";
 } else {
  $datetime="";
 }

 if ($datetime) {
  $linetime=&date2secs($datetime);
  if ($linetime > $starttime-$retention) {
   printwrap "$line\n";
  }
 }
 else {
    printwrap "$line\n";
 }
}
Retrieved from "https://mailnest.com/dbawiki/index.php?title=ASM&oldid=3268"