ASM - Automatic Storage Management - Tips&Tricks

Depuis la version 12.2 d'Oracle, l'utilisation d'ASM devient quasi-obligatoire. Mais est utilisable depuis la 11gR2.

Quelques commandes utiles

ASMCMD
La commande asmcmd permet de gérer ASM en ligne de commande. Il faut que les variables ORACLE_HOME et ORACLE_SID soient positionnées vers l'instance ASM, que l'utilisateur soit celui qui a lancé l'instance; et, jusqu'à la version 12.1, que cet utilisateur (oracle ou autre utilisateur dédié ASM) soit autorisé :

asmcmd --privilege sysdba

https://docs.oracle.com/en/database/oracle/oracle-database/19/ostmg/about-asmcmd.html#GUID-65775DDE-611D-4C59-9696-BB91DB83B367
Notamment :
https://docs.oracle.com/en/database/oracle/oracle-database/19/ostmg/asmcmd-diskgroup-commands.html#GUID-3BA40908-0543-4968-B834-BD4D873E1059
https://hhutzler.de/blog/asm_commands

ASMCMD> lsdg
ASMCMD> lsdsk -k -g

ORACLEASM (gestion optionnelle d'ASM par ASMLib)
https://www.thegeekdiary.com/oracleasm-command-examples/

oracleasm est lancé sous l'utilisateur "root"

[root@prooem03 root]# oracleasm createdisk 'DISK1' '/dev/sdb1'
[root@prooem03 root]# oracleasm createdisk 'DISK2' '/dev/sdc1'
[root@prooem03 root]# oracleasm listdisks

Quelques requêtes utiles

https://techgoeasy.com/asm-lesson-9-asm-queries/
(sur cette page voir notamment "Migrating to ASM Using RMAN")

Connexion à la base ASM avec SQLPLUS en SYSASM (avec l'utilisateur système créateur de l'instance ASM)

[oragrid ~] export ORACLE_BASE=/u01/app/grid_base
[oragrid ~] export ORACLE_HOME=/u01/app/grid/19c_home1
[oragrid ~] export ORACLE_SID=+ASM
[oragrid ~] sqlplus / as sysasm

HOW TO CREATE A DISK GROUP

-- Si le disque n'a pas déjà été nommé (via oracleasm par exemple)
SQL> CREATE DISKGROUP DATA1 EXTERNAL REDUNDANCY disk '/dev/sdb1' NAME DISK1;

-- Si le disque a déjà un nom
SQL> CREATE DISKGROUP DATA1 EXTERNAL REDUNDANCY disk 'ORCL:DISK1' NAME DISK1;

HOW TO ADD DISK IN A DISK GROUP

ALTER DISKGROUP DATA ADD DISK '/dev/sdc1';
ALTER DISKGROUP DATA ADD DISK'/dev/sdc*'REBALANCE POWER 5 WAIT;
ALTER DISKGROUP DATA ADD DISK'/dev/sdc5' NAME DISK5,'/dev/sdc6' NAME DISK6;

HOW TO ADD REDO LOGS IN ASM (DATA1 & DATA2 ARE DISKGROUPS)

ALTER DATABASE ADD LOGFILE (+DATA1,+DATA2);

HOW TO DROP DISK OR DISKGROUP

ALTER DISKGROUP DATA1 DROP DISK DISK2;
DROP DISKGROUP DATA1 INCLUDING CONTENTS;

ASM DISK GROUPS and DISKS INFORMATION

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

-- DISKGROUPS
set pages 40000 lines 120
col NAME for a15
select GROUP_NUMBER DG#, name, ALLOCATION_UNIT_SIZE AU_SZ, STATE,TYPE, TOTAL_MB, FREE_MB, OFFLINE_DISKS from v$asm_diskgroup;

col PATH for a15
col DG_NAME for a15
col DG_STATE for a10
col FAILGROUP for a10
select dg.name dg_name, dg.state dg_state, dg.type, d.disk_number dsk_no,d.path, d.mount_status, d.FAILGROUP, d.state
from v$asm_diskgroup dg, v$asm_disk d
where dg.group_number=d.group_number order by dg_name, dsk_no;

-- DISKS
set pages 40000 lines 120
col PATH for a30
select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,PATH FROM V$ASM_DISK;

QUERY TO FIND THE FILES IN USE BY AN ASM INSTANCE

set lines 2000
col full_alias_path format a100
SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex 
      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;

REBALANCE INFORMATION

select GROUP_NUMBER, OPERATION, STATE, ACTUAL, SOFAR, EST_MINUTES from v$asm_operation

QUERY TO DETECT FILES IN AN ASM DISKGROUP (replace "MDDX1" by required diskgroup name)

set lines 2000
col full_alias_path format a100
SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number
      AND g.name = 'MDDX1')
START WITH (mod(pindex, power(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex;

QUERY TO DETERMINE WHAT DISKGROUPS EXIST AND HOW FULL THEY ARE

SELECT NAME,TOTAL_MB,USABLE_FILE_MB FROM V$ASM_DISKGROUP;

QUERY TO DETERMINE THE STATE AND BALANCE OF DISKGROUPS
Starting in 10.2 this can be easily done with one query

SELECT NAME,STATE,UNBALANCED FROM V$ASM_DISKGROUP;

QUERY TO DETERMINE THE STATE OF THE DISKS WITHIN A DISKGROUP

col name format a12
col path format a25
col mount_status format a7
col header_status format a12
col mode_status format a7
col state format a8
SELECT D.NAME, D.PATH, D.MOUNT_STATUS, D.HEADER_STATUS, D.MODE_STATUS, D.STATE
FROM V$ASM_DISK D, V$ASM_DISKGROUP G
WHERE G.NAME = '&1'AND D.GROUP_NUMBER = G.GROUP_NUMBER;