Asm Sql Scripts

ASM SQL Scripts by Jeff Hunter, Sr. Database Administrator

Contents 1. Overview 2. ASM Script Repository

Overview This article provides links to several SQL scripts that can be used to monitor and manage an ASM instance. All of the scripts are meant to be run against the ASM instance logged in with SYSDBA privileges.

ASM Script Repository

OutputScript Name asm_disks.sql asm_files2.sql asm_templates.sql asm_files.sql asm_clients.sql asm_alias.sql asm_disks_perf.sql asm_diskgroups.sql asm_drop_files.sql

Last Update Date

-- +----------------------------------------------------------------------------+ -- | ----------------

Jeffrey M. Hunter

|

| [email protected] | | www.idevelopment.info | |----------------------------------------------------------------------------| | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | |----------------------------------------------------------------------------| | DATABASE : Oracle | | FILE : asm_disks.sql | | CLASS : Automatic Storage Management | | PURPOSE : Provide a summary report of all disks contained within all disk | | groups. This script is also responsible for queriing all | | candidate disks - those that are not assigned to any disk | | group. | | NOTE : As with any code, ensure to test this script in a development | | environment before attempting to run it in production. | +----------------------------------------------------------------------------+

SET LINESIZE SET PAGESIZE SET VERIFY COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN

145 9999 off

disk_group_name disk_file_path disk_file_name disk_file_fail_group total_mb used_mb pct_used

FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT

a20 a17 a20 a20 999,999,999 999,999,999 999.99

HEAD HEAD HEAD HEAD HEAD HEAD HEAD

'Disk Group Name' 'Path' 'File Name' 'Fail Group' 'File Size (MB)' 'Used Size (MB)' '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 / ---------

+----------------------------------------------------------------------------+ | Jeffrey M. Hunter | | [email protected] | | www.idevelopment.info | |----------------------------------------------------------------------------| | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | |----------------------------------------------------------------------------| | DATABASE : Oracle |

--------

| FILE : asm_files2.sql | | CLASS : Automatic Storage Management | | PURPOSE : Provide a summary report of all files (and file metadata) | | information for all ASM disk groups. | | NOTE : As with any code, ensure to test this script in a development | | environment before attempting to run it in production. | +----------------------------------------------------------------------------+

SET LINESIZE SET PAGESIZE SET VERIFY COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN

145 9999 off

disk_group_name file_name bytes space type redundancy striped creation_date

FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT

a20 a30 9,999,999,999,999 9,999,999,999,999 a18 a12 a8 a20

HEAD HEAD HEAD HEAD HEAD HEAD HEAD HEAD

'Disk Group Name' 'File Name' 'Bytes' 'Space' 'File Type' 'Redundancy' 'Striped' '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 / ----------------

+----------------------------------------------------------------------------+ | Jeffrey M. Hunter | | [email protected] | | www.idevelopment.info | |----------------------------------------------------------------------------| | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | |----------------------------------------------------------------------------| | DATABASE : Oracle | | FILE : asm_templates.sql | | CLASS : Automatic Storage Management | | PURPOSE : Provide a summary report of all template information for all | | ASM disk groups. | | NOTE : As with any code, ensure to test this script in a development | | environment before attempting to run it in production. | +----------------------------------------------------------------------------+

SET LINESIZE SET PAGESIZE

145 9999

SET VERIFY COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN

off

disk_group_name entry_number redundancy stripe system template_name

FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT

a20 999 a12 a8 a6 a30

HEAD HEAD HEAD HEAD HEAD HEAD

'Disk Group Name' 'Entry Number' 'Redundancy' 'Stripe' 'System' 'Template Name'

break on report on disk_group_name skip 1 SELECT b.name disk_group_name , a.entry_number entry_number , a.redundancy redundancy , a.stripe stripe , a.system system , a.name template_name FROM v$asm_template a JOIN v$asm_diskgroup b USING (group_number) ORDER BY b.name , a.entry_number /

----------------

+----------------------------------------------------------------------------+ | Jeffrey M. Hunter | | [email protected] | | www.idevelopment.info | |----------------------------------------------------------------------------| | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | |----------------------------------------------------------------------------| | DATABASE : Oracle | | FILE : asm_files.sql | | CLASS : Automatic Storage Management | | PURPOSE : Provide a summary report of all files (and file metadata) | | information for all ASM disk groups. | | NOTE : As with any code, ensure to test this script in a development | | environment before attempting to run it in production. | +----------------------------------------------------------------------------+

SET LINESIZE SET PAGESIZE SET VERIFY COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN

150 9999 off

full_alias_path system_created bytes space type redundancy striped creation_date disk_group_name

FORMAT a63 FORMAT a8 FORMAT 9,999,999,999,999 FORMAT 9,999,999,999,999 FORMAT a18 FORMAT a12 FORMAT a8 FORMAT a20 noprint

HEAD HEAD HEAD HEAD HEAD HEAD HEAD HEAD

'File Name' 'System|Created?' 'Bytes' 'Space' 'File Type' 'Redundancy' 'Striped' '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 CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path , bytes , space , NVL(LPAD(type, 18), '') type , creation_date , disk_group_name , LPAD(system_created, 4) system_created FROM ( SELECT g.name disk_group_name , a.parent_index pindex , a.name alias_name , a.reference_index rindex , a.system_created system_created , 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 RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number) JOIN v$asm_diskgroup g USING (group_number) ) WHERE type IS NOT NULL START WITH (MOD(pindex, POWER(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex / ----------------

+----------------------------------------------------------------------------+ | Jeffrey M. Hunter | | [email protected] | | www.idevelopment.info | |----------------------------------------------------------------------------| | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | |----------------------------------------------------------------------------| | DATABASE : Oracle | | FILE : asm_clients.sql | | CLASS : Automatic Storage Management | | PURPOSE : Provide a summary report of all clients making use of this ASM | | instance. | | NOTE : As with any code, ensure to test this script in a development | | environment before attempting to run it in production. | +----------------------------------------------------------------------------+

SET LINESIZE SET PAGESIZE SET VERIFY COLUMN COLUMN COLUMN COLUMN

145 9999 off

disk_group_name instance_name db_name status

FORMAT FORMAT FORMAT FORMAT

a20 a20 a9 a12

break on report on disk_group_name skip 1 SELECT a.name

disk_group_name

HEAD HEAD HEAD HEAD

'Disk Group Name' 'Instance Name' 'Database Name' 'Status'

, c.instance_name instance_name , c.db_name db_name , c.status status FROM v$asm_diskgroup a JOIN v$asm_client c USING (group_number) ORDER BY a.name / ----------------

+----------------------------------------------------------------------------+ | Jeffrey M. Hunter | | [email protected] | | www.idevelopment.info | |----------------------------------------------------------------------------| | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | |----------------------------------------------------------------------------| | DATABASE : Oracle | | FILE : asm_alias.sql | | CLASS : Automatic Storage Management | | PURPOSE : Provide a summary report of all alias definitions contained | | within all ASM disk groups. | | NOTE : As with any code, ensure to test this script in a development | | environment before attempting to run it in production. | +----------------------------------------------------------------------------+

SET LINESIZE SET PAGESIZE SET VERIFY COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN

145 9999 off

disk_group_name alias_name file_number file_incarnation alias_index alias_incarnation parent_index reference_index alias_directory system_created

FORMAT a20 FORMAT a30

FORMAT a10 FORMAT a8

HEAD HEAD HEAD HEAD HEAD HEAD HEAD HEAD HEAD HEAD

'Disk Group Name' 'Alias Name' 'File|Number' 'File|Incarnation' 'Alias|Index' 'Alias|Incarnation' 'Parent|Index' 'Reference|Index' 'Alias|Directory?' 'System|Created?'

break on report on disk_group_name skip 1 SELECT g.name disk_group_name , a.name alias_name , a.file_number file_number , a.file_incarnation file_incarnation , a.alias_index alias_index , a.alias_incarnation alias_incarnation , a.parent_index parent_index , a.reference_index reference_index , a.alias_directory alias_directory , a.system_created system_created FROM v$asm_alias a JOIN v$asm_diskgroup g USING (group_number) ORDER BY g.name , a.file_number /

----------------

+----------------------------------------------------------------------------+ | Jeffrey M. Hunter | | [email protected] | | www.idevelopment.info | |----------------------------------------------------------------------------| | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | |----------------------------------------------------------------------------| | DATABASE : Oracle | | FILE : asm_disks_perf.sql | | CLASS : Automatic Storage Management | | PURPOSE : Provide a summary report of all disks contained within all ASM | | disk groups along with their performance metrics. | | NOTE : As with any code, ensure to test this script in a development | | environment before attempting to run it in production. | +----------------------------------------------------------------------------+

SET LINESIZE SET PAGESIZE SET VERIFY COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN

145 9999 off

disk_group_name disk_path reads writes read_errs write_errs read_time write_time bytes_read bytes_written

FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT

a20 a20 999,999,999 999,999,999 999,999 999,999 999,999,999 999,999,999 999,999,999,999 999,999,999,999

HEAD HEAD HEAD HEAD HEAD HEAD HEAD HEAD HEAD HEAD

'Disk Group Name' 'Disk Path' 'Reads' 'Writes' 'Read|Errors' 'Write|Errors' 'Read|Time' 'Write|Time' 'Bytes|Read' 'Bytes|Written'

break on report on disk_group_name skip 2 compute sum label "" of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on disk_group_name compute sum label "Grand Total: " of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on report SELECT a.name disk_group_name , b.path disk_path , b.reads reads , b.writes writes , b.read_errs read_errs , b.write_errs write_errs , b.read_time read_time , b.write_time write_time , b.bytes_read bytes_read , b.bytes_written bytes_written FROM v$asm_diskgroup a JOIN v$asm_disk b USING (group_number) ORDER BY a.name / ------

+----------------------------------------------------------------------------+ | Jeffrey M. Hunter | | [email protected] | | www.idevelopment.info | |----------------------------------------------------------------------------|

----------

| Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | |----------------------------------------------------------------------------| | DATABASE : Oracle | | FILE : asm_diskgroups.sql | | CLASS : Automatic Storage Management | | PURPOSE : Provide a summary report of all disk groups. | | NOTE : As with any code, ensure to test this script in a development | | environment before attempting to run it in production. | +----------------------------------------------------------------------------+

SET LINESIZE SET PAGESIZE SET VERIFY COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN

145 9999 off

group_name sector_size block_size allocation_unit_size state type total_mb used_mb pct_used

FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT

a20 99,999 99,999 999,999,999 a11 a6 999,999,999 999,999,999 999.99

HEAD HEAD HEAD HEAD HEAD HEAD HEAD HEAD HEAD

'Disk Group|Name' 'Sector|Size' 'Block|Size' 'Allocation|Unit Size' 'State' 'Type' 'Total Size (MB)' 'Used Size (MB)' '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 , sector_size , block_size , allocation_unit_size , state , type , total_mb , (total_mb - free_mb) , ROUND((1- (free_mb / total_mb))*100, 2) FROM v$asm_diskgroup ORDER BY name / ----------------

group_name sector_size block_size allocation_unit_size state type total_mb used_mb pct_used

+----------------------------------------------------------------------------+ | Jeffrey M. Hunter | | [email protected] | | www.idevelopment.info | |----------------------------------------------------------------------------| | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | |----------------------------------------------------------------------------| | DATABASE : Oracle | | FILE : asm_drop_files.sql | | CLASS : Automatic Storage Management | | PURPOSE : Used to create a SQL script that removes all ASM files | | contained within all diskgroups. | | NOTE : As with any code, ensure to test this script in a development | | environment before attempting to run it in production. | +----------------------------------------------------------------------------+

SET SET SET SET SET

LINESIZE PAGESIZE VERIFY FEEDBACK HEAD

255 9999 off off off

COLUMN full_alias_path COLUMN disk_group_name

FORMAT a255 noprint

HEAD 'File Name'

SELECT 'ALTER DISKGROUP ' || disk_group_name || ' DROP FILE ''' || CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) || ''';' full_alias_path FROM ( SELECT g.name disk_group_name , a.parent_index pindex , a.name alias_name , a.reference_index rindex , f.type type FROM v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number) JOIN v$asm_diskgroup g USING (group_number) ) WHERE type IS NOT NULL START WITH (MOD(pindex, POWER(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex /