Oracle Asm

http://www.oracle.com/technetwork/server-storage/linux/asmlib/rhel6-1940776.html

http://www.oracle.com/technetwork/server-storage/linux/asmlib/ol6-1709075.html

report utilizzo

select GROUP_NUMBER,TYPE,sum(bytes)/(1024*1024) MB from v$asm_file group by GROUP_NUMBER,TYPE order by 1,2;

set pagesize 200
set linesize 200
set feedback off
col PATH for a30
col GROUPNAME for a10
col NAME for a15
col FAILGROUP_TYPE for a14
col FAILGROUP for a15
col REDUNDANCY for a10
select g.NAME GROUPNAME
     ,d.FAILGROUP
     ,d.NAME NAME
     ,d.PATH PATH
     ,d.STATE STATE
     ,d.TOTAL_MB TOTAL_MB
     ,d.FREE_MB
     ,d.FREE_MB-(avg(d.FREE_MB) over (partition by g.NAME)) SCO
     ,d.SECTOR_SIZE
     ,d.READS
     ,d.WRITES
from v$asm_disk d, v$asm_diskgroup g
where d.GROUP_NUMBER=g.GROUP_NUMBER(+) order by 1,2;

select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB,
 round(((TOTAL_MB-FREE_MB)/nullif(TOTAL_MB,0))*100,2) "USED%",
 ALLOCATION_UNIT_SIZE,OFFLINE_DISKS
from v$asm_diskgroup order by 1;

ricerca file orfani

DEFINE ASMGROUP="DGDATA"
 
set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80

WITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'),
     v_parentindex AS (SELECT parent_index 
                    FROM v$asm_alias 
              WHERE group_number = (SELECT group_number FROM v_asmgroup) 
                AND alias_index=0),
  v_asmfiles AS (SELECT file_number, type 
              FROM v$asm_file 
           WHERE group_number = (SELECT group_number FROM v_asmgroup)),
 v_dbname AS (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database)
SELECT 'rm '|| files files FROM -- this line show the delete command
(
  SELECT '+&ASMGROUP'||files files, type 
  FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type
        FROM (SELECT file_number,alias_directory,name, reference_index, parent_index 
        FROM v$asm_alias) aa,
             (SELECT parent_index FROM v_parentindex) a,
             (SELECT file_number, type FROM v_asmfiles) b
  WHERE aa.file_number=b.file_number(+)
    AND aa.alias_directory='N'
   -- missing PARAMETERFILE, DATAGUARDCONFIG
   AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')
  START WITH aa.PARENT_INDEX=a.parent_index
  CONNECT BY PRIOR aa.reference_index=aa.parent_index)
  WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM v_dbname)
MINUS (
  SELECT upper(name) files, 'DATAFILE' type FROM v$datafile
    UNION ALL 
  SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile
    UNION ALL
 SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'
    UNION ALL
 SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);

In caso di database in stato "mounted":

DEFINE ASMGROUP="DGDATA"
 
set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80

SELECT 'rm '|| files files FROM -- this line show the delete command
(
  SELECT '+&ASMGROUP'||files files, type 
  FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type
        FROM (SELECT file_number,alias_directory,name, reference_index, parent_index 
        FROM v$asm_alias) aa,
             (SELECT parent_index FROM (SELECT parent_index 
                    FROM v$asm_alias 
              WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP') 
                AND alias_index=0)) a,
             (SELECT file_number, type FROM (SELECT file_number, type 
                                       FROM v$asm_file 
                                    WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'))) b
  WHERE aa.file_number=b.file_number(+)
    AND aa.alias_directory='N'
   -- missing PARAMETERFILE, DATAGUARDCONFIG
   AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')
  START WITH aa.PARENT_INDEX=a.parent_index
  CONNECT BY PRIOR aa.reference_index=aa.parent_index)
  WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database))
MINUS (
  SELECT upper(name) files, 'DATAFILE' type FROM v$datafile
    UNION ALL 
  SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile
    UNION ALL
 SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'
    UNION ALL
 SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);

http://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/

ASM Senza oracleasm

• udev per permission a grid:oracleasm

 cat /etc/udev/rules.d/99-oracle-asmdevices.rules
 #xen
 KERNEL=="xvd?", SUBSYSTEM=="block", ENV{ID_FS_TYPE}=="oracleasm", OWNER="grid", GROUP="asmadmin", MODE="0660"
 #scsi
 KERNEL=="sd?", SUBSYSTEM=="block", ENV{ID_FS_TYPE}=="oracleasm", OWNER="grid", GROUP="asmadmin", MODE="0660"

• udev per /dev/asm