ASM TABLESPACE Scripts

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

When a tablespace is getting filled up, action need to be taken (Preference-wise)

1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.

Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt

oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon

Set the environment for the database
------------------------------------
uname

cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab

export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin

Database Details
----------------
sqlplus "/as sysdba"

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

ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
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 Script (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 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 '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
/

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 Script
------------------------------
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;

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 '&datafile_name' resize 4096M;

If AUTOEXTEND ON
----------------
alter database datafile '&datafile_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 '%&datafile_name%';

ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;

If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;

To Create a new tablespace  (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;

If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;

Schemas in a tablespace
-----------------------
set pages 9999 lines 300

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
/

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
/