===============================================================
How to login to ASM command Line
===============================================================
1) login to ASM Instance first
export ORACLE_SID= <look for SID using ps -ef | grep pmon>
2) asmcmd
3)asmcmd > lsdg
===============================================================
Some ASM commands
===============================================================
Commands
-------------------------
asmcmd > ls -l
asmcmd > ls -lt
asmcmd > ls -l UNDO*
asmcmd > lsct
asmcmd > du
Examples
------------
asmcmd> ls -l
State Type Rebal Unbal Name
MOUNTED NORMAL N N DGROUP1/
MOUNTED NORMAL N N DGROUP2/
The following example shows disk space used in the SAMPLE directory in DGROUP1, including all directories below SAMPLE.
ASMCMD [+DGROUP1/SAMPLE] > du
Used_MB Mirror_used_MB
1251 2507
ASMCMD> find +dgroup1 undo*
+dgroup1/SAMPLE/DATAFILE/UNDOTBS1.258.555341963
+dgroup1/SAMPLE/DATAFILE/UNDOTBS1.272.557429239
ASMCMD [+DGROUP1/SAMPLE/DATAFILE] > ls -l
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE APR 18 19:16:07 Y EXAMPLE.269.555342243
DATAFILE MIRROR COARSE MAY 09 22:01:28 Y SYSAUX.257.555341961
DATAFILE MIRROR COARSE APR 19 19:16:24 Y SYSTEM.256.555341961
DATAFILE MIRROR COARSE MAY 05 12:28:42 Y UNDOTBS1.258.555341963
DATAFILE MIRROR COARSE MAY 04 17:27:34 Y UNDOTBS1.272.557429239
DATAFILE MIRROR COARSE APR 18 19:16:07 Y USERS.259.555341963
ASMCMD [+DGROUP1/SAMPLE/DATAFILE] > ls -lt
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE MAY 09 22:01:28 Y SYSAUX.257.555341961
DATAFILE MIRROR COARSE MAY 05 12:28:42 Y UNDOTBS1.258.555341963
DATAFILE MIRROR COARSE MAY 04 17:27:34 Y UNDOTBS1.272.557429239
DATAFILE MIRROR COARSE APR 19 19:16:24 Y SYSTEM.256.555341961
DATAFILE MIRROR COARSE APR 18 19:16:07 Y USERS.259.555341963
DATAFILE MIRROR COARSE APR 18 19:16:07 Y EXAMPLE.269.555342243
ASMCMD [+DGROUP1/SAMPLE/DATAFILE] > ls -l undo*
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE MAY 05 12:28:42 Y UNDOTBS1.258.555341963
DATAFILE MIRROR COARSE MAY 04 17:27:34 Y UNDOTBS1.272.557429239
ASMCMD [+DGROUP1/SAMPLE/DATAFILE] > ls -s
Block_Size Blocks Bytes Space Name
8192 12801 104865792 214958080 EXAMPLE.269.555342243
8192 48641 398467072 802160640 SYSAUX.257.555341961
8192 61441 503324672 1011875840 SYSTEM.256.555341961
8192 6401 52436992 110100480 UNDOTBS1.258.555341963
8192 12801 104865792 214958080 UNDOTBS1.272.557429239
8192 641 5251072 12582912 USERS.259.555341963
lsct
-------
Purpose
Lists information about current ASM clients. A client is a database that uses disk groups managed by the ASM instance that ASMCMD is currently connected to.
If group is specified, information about only that disk group is displayed. The -H flag suppresses column headings.
Example
This example displays information about the client accessing the dgroup1 disk group.
ASMCMD [+] > lsct dgroup1
DB_Name Status Software_Version Compatible_version Instance
lsct: Lists information about current Oracle ASM clients from the V$ASM_CLIENT view.
ASMCMD> lsct
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
+ASM CONNECTED 11.2.0.1.0 11.2.0.1.0 +ASM DATA
testdb CONNECTED 11.2.0.1.0 11.2.0.0.0 testdb DATA
===============================================================
Some ASM Queries
===============================================================
How to Query ASM information using SQL Plus
----------------------------------------------------------
1) login to ASM Instance first
export ORACLE_SID= <look for SID using ps -ef | grep pmon>
2) sqlplus / as sysasm
-- LIST All disks with ASM Lables
set pagesize 300
set linesize 200
col PATH format aa40
select DISK_NUMBER,
MOUNT_STATUS,
HEADER_STATUS,
MODE_STATUS,
STATE,
PATH
FROM V$ASM_DISK
/
-- List Candidate Disks Only
set pagesize 300
set linesize 200
col PATH format aa30
select DISK_NUMBER,
MOUNT_STATUS,
HEADER_STATUS,
MODE_STATUS,
STATE,
PATH
FROM V$ASM_DISK where header_status != 'MEMBER';
-- List All ASM DISK Groups
set pages 40000 lines 200
col NAME for a15
select GROUP_NUMBER DG#,
name,
STATE,
TYPE,
TOTAL_MB,
FREE_MB
from v$asm_diskgroup
/
-- Disk and disk group combined ( Note candidate Disk will not be here)
set linesize 200
col PATH for a16
col DG_NAME for a15
col DG_STATE for a10
col FAILGROUP for a10
select
d.disk_number dsk_no,
d.path,
dg.name dg_name,
dg.state dg_state,
dg.type,
d.mount_status, d.state
from v$asm_diskgroup dg, v$asm_disk d
where dg.group_number=d.group_number
order by dg_name, dsk_no
/
set linesize 200
col PATH for a16
col DG_NAME for a15
col DG_STATE for a10
col FAILGROUP for a10
select
d.disk_number dsk_no,
d.path,
dg.name dg_name,
dg.state dg_state,
dg.type,
d.mount_status, d.state
from v$asm_diskgroup dg, v$asm_disk d
where dg.group_number=d.group_number
order by dg_name, dsk_no
/
-- List databases and there disk groups
SELECT substr(dg.name,1,20) AS diskgrou,
SUBSTR(c.instance_name,1,12) AS
instance,
SUBSTR(c.db_name,1,12) AS dbname
FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c
WHERE dg.group_number = c.group_number
/
===============================================================
Some ASM Operations
===============================================================
a) service oracleasm createdisk [diskLable] [disk_partition_path]
Example root# service oracleasm createdisk d_dg_data1 /dev/xvdb1
Marking disk "d5_dg_data_01" as an ASM disk: [ OK ]
b) service oracleasm deletedisk [diskLable]
Example root# service oracleasm deletedisk d_dg_data1
c) service oracleasm querydisks [os_disk_path example ]
Example root# service oracleasm querydisks /dev/xvdb1
d) service oracleasm scandisks
e) service oracleasm listdisks
f) How to Drop a disk from Disk Group
-----------------------------------------------
export oracle_sid=<your ASM Sid>
export ORACLE_HOME=<your oracle ASM home>
sqlplus / as sysasm [ Note this is as sysasm]
SQL> alter diskgroup dgnormal drop disk [Disk Name];
Note : You must wait to clear from ASM before reusing this or re-labeling it
Confirm this by querieng v$asm_operation
Example
SQL> select group_number,name from v$asm_diskgroup;
GROUP_NUMBER NAME
------------ ------------------------------
1 DGNORMAL
SQL> select path,name from v$asm_disk where group_number= 1;
PATH NAME
-------------------- ------------------------------
/dev/raw/raw5 DGNORMAL_0000
/dev/raw/raw6 DGNORMAL_0001
/dev/raw/raw7 DGNORMAL_0004
/dev/raw/raw8 DGNORMAL_0005
SQL> alter diskgroup dgnormal drop disk dgnormal_0005;
Diskgroup altered.
===============================================================
Match Actual Dive with ASM Disks
===============================================================
root# cat listasmdisks.sh
#!/bin/ksh
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk -p $i | grep emc `
echo $i --- $v_asmdisk
done
