ASM

Adding datafile

ALTER TABLESPACE TDW_PROD ADD DATAFILE '+DATA_DISKGRP/bivis/datafile/bivis_28.dbf' SIZE 1024M AUTOEXTEND ON NEXT
1024M MAXSIZE UNLIMITED

queries
select NAME,TOTAL_MB,FREE_MB,FREE_MB/TOTAL_MB*100 from v$ASM_DISK;

select NAME,TOTAL_MB,FREE_MB,FREE_MB/TOTAL_MB*100 from v$ASM_DISKGROUP;

select NAME,TOTAL_MB,FREE_MB,TOTAL_MB-FREE_MB,FREE_MB/TOTAL_MB*100 from v$ASM_DISK

select NAME, REDUNDANCY, FAILGROUP from V$ASM_DISK ;

select operation from v$ASM_OPERATION;

select name,path,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,total_mb,free_mb from v$asm_disk

dropping/adding/cheking diskALTER DISKGROUP CHECK ALL;
ALTER DISKGROUP DROP DISK ; This command drops the disk and also initiates a REBAL operation
We can monitor the status of REBAL operation from v$asm_operation
Run select * from v$asm_disk and get different statuses of hdisk8.
eg:
ALTER DISKGROUP DATA_DISKGRP DROP DISK DATA_DISKGRP_0004;

DATA_DISKGRP_004 is alias for /dev/rhdisk8.

SQL> select OPERATION from v$asm_operation;

OPERA
-----
REBAL


ALTER DISKGROUP DATA_DISKGRP ADD DISK ‘mention the disk path’ NAME DATA_DISKGRP_004 REBALANCE POWER 5;


This query will give more details about disk usage.
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File 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 """" of total_mb used_mb on disk_group_name
compute sum label ""Grand Total: "" of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name
/