ASM Tablespace Utilization Scripts

http://files.meetup.com/1729503/1729503/All_about_ASM.pdf

ps -ef | grep pmon
ps -ef | grep tns
ps -ef | grep d.bin

Set the environment for the database
------------------------------------
uname
cat /etc/oratab OR cat /var/opt/oracle/oratab
export ORACLE_SID=<DBNAME>
export ORACLE_HOME=<>
export PATH=$PATH:$ORACLE_HOME/bin

To Check Database
-----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
select name DB_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,version DB_VERSION,DATABASE_STATUS,DATABASE_ROLE,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

ASM Space Report
----------------

SET LINESIZE 150
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 (GB)'
COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT
    distinct name                            group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                  allocation_unit_size
  , state                                        state
  , type                                         type
  , round(total_mb/1024)                              "total_gb"
  , round(free_mb/1024)                               "free_gb"
  , round((total_mb - free_mb) / 1024)          "used_gb"
  , round((1- (free_mb / total_mb))*100, 2)  "pct_used"
from v$asm_diskgroup ORDER BY name
/

ASM Disk Space Usage Report In Detail
-------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a50 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 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' 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) where b.header_status = 'MEMBER' 
ORDER BY a.name
/

OTHER ASM QUERIES
-----------------

set pages 9999 lines 900
col path for a50
select path,label, group_number, disk_number, NAME,TYPE, state, header_status,mount_status, mount_date,create_date, round(TOTAL_MB/1024) "TOTAL_GB", round(FREE_MB/1024) "FREE_GB",round((TOTAL_MB-FREE_MB) * 100 / TOTAL_MB,2) || '%' "USED_PERCENTAGE", SYSDATE from v$asm_disk order by path;

set pages 9999 lines 900
col path for a50
select path,label, group_number, disk_number, NAME,TYPE, state, header_status,mount_status, mount_date, round(TOTAL_MB/1024) "TOTAL_GB",  round(FREE_MB/1024) "FREE_GB", round((TOTAL_MB-FREE_MB) * 100 / TOTAL_MB,2) || '%' "USED_PERCENTAGE", SYSDATE from v$asm_diskgroup order by path;

SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE FROM V$ASM_DISKGROUP order by NAME;

select table_name from dict where table_name like '%ASM%';

Datafiles of a particular TableSpace
------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB from dba_data_files where tablespace_name='&TABLESPACE_NAME' order by 1,2;

ASM Disk Database Files Report
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15

select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;

All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/

TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;

To resize a datafile (ASM)
---------------------------
ALTER DATABASE DATAFILE '&FILE_NAME' RESIZE 4096M;
ALTER DATABASE DATAFILE '&FILE_NAME' AUTOEXTEND ON MAXSIZE 8G;

To add a new datafile in a tablespace (ASM)
--------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafilename%';

ALTER TABLESPACE <TABLESPACE NAME> ADD DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE NAME> DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Schemas in a tablespace
-----------------------
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/

V$ASM_DISK Header_Status:
------------------------
UNKNOWN - Automatic Storage Management disk header has not been read

CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement

INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software

version.

PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP

statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies

that an additional platform-specific action has been taken by an administrator to make the disk available for

Automatic Storage Management.

MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk

group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option

FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new

disk group with the ALTER DISKGROUP statement.

CONFLICT - Automatic Storage Management disk was not mounted due to a conflict

FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and

OCR disks.

ASM Space Report
----------------
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 500
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a12 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a45 HEAD 'Path'
COLUMN disk_file_name FORMAT a12 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a12 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 ,v$asm_disk b
where a.group_number (+) =b.group_number
ORDER BY a.name;

To find the free space in an ASM disk:
--------------------------------------
select group_number, disk_number, name, failgroup, create_date, path, total_mb,free_mb from v$asm_disk;

To find the free space in an ASM diskgroup:
-------------------------------------------
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;

To see the current ASM operations in Progress:
----------------------------------------------
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;

set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb, round(free_mb/total_mb*100,2) pct_free from v$asm_diskgroup;

Monitor space used in ASM Disk Groups
-------------------------------------
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
/

Disk Group Name File Name File Size (MB) Used Size (MB) Free Size (MB) Pct. Used
-------------------- -------------------- -------------------- -------------- -------------- -------

Space used by database files.No other files found for cleanup.
There are 2 undo tablespaces for this database. Reclaimed some freespace from non-default undo tablespace (i.e:"UNDOTBS1".)

Current status:
---------------
ORADATA diskgroup having xxx gb of freespace out of yyyy gb.

http://dbasanthosh.wordpress.com/2012/05/
-----------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free 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 free_mb on disk_group_name
compute sum label “Grand Total: ” of total_mb used_mb free_mb on report
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name,b.path disk_file_path,b.name disk_file_name,
b.total_mb total_mb,(b.total_mb – b.free_mb) used_mb,b.free_mb free_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.nameUSING (group_number) ORDER BY a.name
/