ASM queries

 

SELECT name, free_mb, total_mb, total_mb-free_mb "USED_MB", free_mb/total_mb*100 "%FREE" FROM v$asm_diskgroup;

SELECT name, free_mb, total_mb, total_mb-free_mb "USED_MB" FROM v$asm_diskgroup;

set lines 150
column path format a40
column name format a30

select name,path,mount_status,header_status,mode_status,state,total_mb,free_mb from v$asm_disk order by 2;

Run in ASM environment(ASM_HOME)

set lines 132
set pages 50
column path format a20

select name,path, group_number group_#, disk_number disk_#, mount_status,header_status, state, total_mb, free_mb from v$asm_disk order by group_number;

 

alter diskgroup DATA add disk 'ORCL:ASM114P1';
 
alter diskgroup DATA add disk 'ORCL:ASM113P1','ORCL:ASM115P1','ORCL:ASM116P1' REBALANCE POWER 8;

ALTER DISKGROUP PSF92DEV_DATA ADD DISK
'/dev/oracleasm/disks/PSF92_DATA08' NAME PSF92D_DATA_0008,
'/dev/oracleasm/disks/PSF92_DATA09' NAME PSF92_DATA_0009 REBALANCE POWER 8;




alter diskgroup TSPROD_LTR_DATA add disk 'ORCL:TSPROD_LTRDATA07' REBALANCE POWER 8;

or

SQL> ALTER DISKGROUP PSF92DEV_DATA REBALANCE POWER 8;

SQL> select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION;

> select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION where GROUP_NUMBER=1;


set lines 150
column path format a30
column DiskGroup format a15
column DiskName format a30
col free_mb for 999,999,999 
compute sum of total_mb on DiskGroup 
compute sum of free_mb on DiskGroup 
break on DiskGroup skip 1 on report 
set pages 255 

select a.name DiskGroup, 
 b.disk_number Disk#, 
 b.name DiskName, 
 b.total_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 
and b.header_status != 'FOREIGN'
order by b.group_number, b.disk_number, b.name;

SQL> 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
/




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
FROM
 v$asm_diskgroup
ORDER BY
 name
/

**********************************

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off

COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN file_name FORMAT a30 HEAD 'File Name'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'

break on report on disk_group_name skip 1
compute sum label "" of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report

SELECT
 g.name disk_group_name
 , a.name file_name
 , f.bytes bytes
 , f.space space
 , f.type type
 , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
 v$asm_file f JOIN v$asm_alias a USING (group_number, file_number)
 JOIN v$asm_diskgroup g USING (group_number)
WHERE
 system_created = 'Y'
ORDER BY
 g.name
 , file_number
/

#### useful cmd #######

asmcmd lsdsk -p
asmcmd lsdsk --candidate -p

-- if you use ASMLib

/usr/sbin/oracleasm listdisks
/usr/sbin/oracleasm querydisk {DISKNAME | devicename} --check all disk

2. parameters

show parameters asm

3. permission of asmdisk --from all nodes
ll <value_of_asm_diskstring>

4. from live node2
crsctl query css votedisk
ocrcheck
crsctl stat res -t




sudo /etc/init.d/oracleasm querydisk /dev/sd* |grep OEM|awk -F" " '{print $2, $11}'

sudo /etc/init.d/oracleasm querydisk /dev/dm* | grep MOB | awk -F" " '{print $2, $11}'




Ensure that the allocated devices can be seen in /dev/mpath
cd /dev/mpath

ls -l /dev/mpath

Ensure that the devices can be seen in /dev/mapper:
ls -l /dev/mapper




ll /dev/oracleasm/disks

## lun id using Multipath

sudo /sbin/multipath -l

sudo /etc/init.d/oracleasm configure




sudo /etc/init.d/oracleasm scandisks
sudo /etc/init.d/oracleasm start
sudo /etc/init.d/oracleasm listdisks
sudo /etc/init.d/oracleasm status

+ASM 09:19 PM :/opt/app/oracle/product/10.2.0/db_1/bin

> kfod status=TRUE asm_diskstring='/dev/oracleasm/disks/*' disk=all
--------------------------------------------------------------------------------
 Disk Size Header Path
================================================================================
 1: 102400 Mb CANDIDATE /dev/oracleasm/disks/TSOLTP_HTC_DATA01
 2: 102400 Mb CANDIDATE /dev/oracleasm/disks/TSOLTP_HTC_DATA02
 3: 102400 Mb CANDIDATE /dev/oracleasm/disks/TSOLTP_HTC_DATA03
 4: 102400 Mb CANDIDATE /dev/oracleasm/disks/TSOLTP_HTC_DATA04
 5: 102400 Mb CANDIDATE /dev/oracleasm/disks/TSOLTP_HTC_FLASH01
 6: 102400 Mb CANDIDATE /dev/oracleasm/disks/TSOLTP_HTC_FLASH02
 7: 102400 Mb CANDIDATE /dev/oracleasm/disks/TSOLTP_HTC_FLASH03
 8: 102400 Mb CANDIDATE /dev/oracleasm/disks/TSOLTP_HTC_FLASH04
--------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
================================================================================
 +ASM /opt/app/oracle/product/10.2.0/db_1

############ DROP DISK from DISKGROUP in CMD mode #############

--add new disk MOB10G_DATA_VSP_DISK01 to disk group PRIME_DATA:

ALTER DISKGROUP PRIME_FRA ADD DISK 'ORCL:MOB10G_DATA_VSP_DISK01' SIZE 5115 M REBALANCE POWER 8

-- the above cmd will rebalce 1/2 of data from current disk MOB10G_DATA_DISK01 to new disk

--Now drop the old disk (MOB10G_DATA_DISK01) using below command:

ALTER DISKGROUP PRIME_DATA DROP DISK MOB10G_DATA_DISK01 REBALANCE POWER 8

--after this cmd all data will be moved from old disk (MOB10G_DATA_DISK01) to new disk (MOB10G_DATA_VSP_DISK01)

-- you can also do it using Grid Control GUI.




ALTER DISKGROUP PRIME_FRA ADD DISK 'ORCL:MOB10G_FRA_VSP_DISK01' SIZE 5115 M REBALANCE POWER 8

ALTER DISKGROUP PRIME_FRA DROP DISK MOB10G_FRA_DISK01 REBALANCE POWER 8

ALTER DISKGROUP OCR_DG2 DROP DISK OCR_VSP_DISK2;

ALTER DISKGROUP OCR_DG1 DROP DISK OCR_VSP_DISK1;

alter diskgroup VOTING_DG drop disk 'VOTE_VSP_DISK1','VOTE_VSP_DISK2','VOTE_VSP_DISK3';

--or you can use like below (optional - if not mention it will take the refault rebalance power )




alter diskgroup VOTING_DG drop disk 'VOTE_VSP_DISK1','VOTE_VSP_DISK2','VOTE_VSP_DISK3' REBALANCE POWER 5;

-- check lun id:

>sudo /etc/init.d/oracleasm querydisk /dev/dm*

Device "/dev/dm-0" is not marked as an ASM disk

Device "/dev/dm-1" is not marked as an ASM disk

Device "/dev/dm-10" is marked an ASM disk with the label "MOB_HTC_FLASH01"

Device "/dev/dm-11" is not marked as an ASM disk

Device "/dev/dm-12" is marked an ASM disk with the label "PROD_LTRDATA01"

Device "/dev/dm-13" is marked an ASM disk with the label "PROD_LTRDATA02"

Device "/dev/dm-14" is marked an ASM disk with the label "PROD_LTRDATA03"

Device "/dev/dm-15" is marked an ASM disk with the label "PROD_LTRDATA04"

 

$ ls -ltr /dev/mpath/ | grep dm-12

lrwxrwxrwx  1 root root 8 Nov  3  2012 360060e8016013300000101330000111b -> ../dm-12




[oracle@pdorclts001 ~]$ sudo /sbin/multipath -l | grep 360060e8016013300000101330000111b -A 4

360060e8016013300000101330000111b

[size=300 GB][features="1 queue_if_no_path"][hwhandler="0"]

\_ round-robin 0 [active]

\_ 4:0:0:39 sdah 66:16  [active]

\_ 3:0:0:39 sdk  8:160  [active]

#################### for 11g or 12c lun id on physical:

ls -la /dev/disk/by-id/

############ Standalone /dev/oracleasm/disks disk naming ##########


ALTER DISKGROUP PSF92DEV_DATA ADD DISK
'/dev/oracleasm/disks/PSF92_DATA08' NAME PSF92DEV_DATA_0008,
'/dev/oracleasm/disks/PSF92_DATA09' NAME PSF92DEV_DATA_0009 REBALANCE POWER 8;



ALTER DISKGROUP PSF92DEV_DATA ADD DISK
'/dev/oracleasm/disks/PSF92_DATA06' NAME PSF92DEV_DATA_0006,
'/dev/oracleasm/disks/PSF92_DATA07' NAME PSF92DEV_DATA_0007;


select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION;

########### create disk group with name disks #########

CREATE DISKGROUP dgroup1 EXTERNAL REDUNDANCY
FAILGROUP controller1 DISK
'/devices/diska1' NAME diska1,
'/devices/diska2' NAME diska2,
'/devices/diska3' NAME diska3,
'/devices/diska4' NAME diska4
ATTRIBUTE 'au_size'='1M',
 'compatible.asm' = '12.1', 
 'compatible.rdbms' = '11.2';

########## rename asm disk lables #############

sudo /etc/init.d/oracleasm force-renamedisk FLMRMAN_DATA_VSP_DISK1 FLMRMAN_FRM_VSP_DISK2
 
sudo /etc/init.d/oracleasm force-renamedisk HYPDEV_FLASH FLMRMAN_DATA_VSP_DISK1

############# 12c




12c 
SQL> alter diskgroup data2 dismount;
 Diskgroup altered.

SQL> alter diskgroup data2 mount restricted;
 Diskgroup altered.

Then use the following SQL to rename the disks.

SQL> alter diskgroup data2 rename disk 'DATA2_0001' to 'DATA2_VMAX_0001', 'DATA2_0000' to 'DATA2_VMAX_0000';
Diskgroup altered.

################ ASM DB growth from OEM 12c/13c ############

with pivot_data AS (
 select key_value2, rollup_timestamp, average
 from mgmt$metric_daily
 where target_name = '+ASM_pdabcdb-scan' and key_value = 'ABC_DATA' and column_label = 'Total Bytes'
 )
select * from pivot_data
pivot
 ( sum(average/1024/1024/1024)
 for key_value2
 in ( 'ABC' )
 )
order by rollup_timestamp desc;




select target_name, column_label, key_value, key_value2, average, rollup_timestamp 
 from mgmt$metric_daily
 where target_name = '+ASM_pdabcdb-scan' and key_value = 'ABC_DATA' 
 and column_label = 'Total Bytes' and trunc(rollup_timestamp) = trunc(sysdate)-1;