ASM - Automatic Storage Management

Oracle ASM is Oracle's recommended storage management solution that provides an alternative to conventional volume managers, file systems, and raw devices. Oracle ASM uses disk groups to store data files; an Oracle ASM disk group is a collection of disks that Oracle ASM manages as a unit.

Steps to connect to:

1. Connect to asm host, un:grid, pw:grid
2. Check Oracle instance (unsure is right instance)
   1. echo $ORACLE_HOME to check
      1. /u01/app/oracle/11.2.0/grid
   2. Command ps -ef|grep pmon
3. Connect as sysasm
   1. Open sqlplus with command ,sqlplus /nolog
   2. Connect command, conn /as sysasm

Oracle ASM Useful Commands

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

/

Disks contained within Disk Groups

SET ECHO OFF

SET FEEDBACK 6

SET HEADING ON

SET LINESIZE 180

SET PAGESIZE 50000

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 a25 HEAD 'Disk Group Name'

COLUMN disk_file_path FORMAT a20 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

/

List of ASM files and its’ volume information

SET ECHO OFF

SET FEEDBACK 6

SET HEADING ON

SET LINESIZE 180

SET PAGESIZE 50000

SET TERMOUT ON

SET TIMING OFF

SET TRIMOUT ON

SET TRIMSPOOL ON

SET VERIFY OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN full_path FORMAT a75 HEAD 'ASM File Name / Volume Name / Device Name'

COLUMN system_created FORMAT a8 HEAD 'System|Created?'

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'

COLUMN disk_group_name noprint

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('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path

, db_files.bytes

, db_files.space

, NVL(LPAD(db_files.type, 18), '<DIRECTORY>') type

, db_files.creation_date

, db_files.disk_group_name

, LPAD(db_files.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)

) db_files

WHERE db_files.type IS NOT NULL

START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0

CONNECT BY PRIOR db_files.rindex = db_files.pindex

UNION

SELECT

'+' || volume_files.disk_group_name || ' [' || volume_files.volume_name || '] ' || volume_files.volume_device full_path

, volume_files.bytes

, volume_files.space

, NVL(LPAD(volume_files.type, 18), '<DIRECTORY>') type

, volume_files.creation_date

, volume_files.disk_group_name

, null

FROM

( SELECT

g.name disk_group_name

, v.volume_name volume_name

, v.volume_device volume_device

, 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_volume v USING (group_number, file_number)

JOIN v$asm_diskgroup g USING (group_number)

) volume_files

WHERE volume_files.type IS NOT NULL

/

Disk performance metric

SET ECHO OFF

SET FEEDBACK 6

SET HEADING ON

SET LINESIZE 256

SET PAGESIZE 50000

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 a20 HEAD 'Disk Group Name'

COLUMN disk_path FORMAT a20 HEAD 'Disk Path'

COLUMN reads FORMAT 999,999,999,999 HEAD 'Reads'

COLUMN writes FORMAT 999,999,999,999 HEAD 'Writes'

COLUMN read_errs FORMAT 999,999,999 HEAD 'Read|Errors'

COLUMN write_errs FORMAT 999,999,999 HEAD 'Write|Errors'

COLUMN read_time FORMAT 999,999,999,999 HEAD 'Read|Time'

COLUMN write_time FORMAT 999,999,999,999 HEAD 'Write|Time'

COLUMN bytes_read FORMAT 999,999,999,999,999 HEAD 'Bytes|Read'

COLUMN bytes_written FORMAT 999,999,999,999,999 HEAD '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

/

How to use ASM ??

When ASM creates a datafile for a permanent tablespace, the datafile is set to auto-extensible with an unlimited maximum size and 100 MB default size. You can use the AUTOEXTEND clause to override this default extensibility and the SIZE clause to override the default size.

ASM applies attributes to the datafile, as specified in the system default template for a datafile as shown in the table in Managing disk group templates

If there is a disk group named DATA, you can create a tablespace TEST_DAT in that disk group with the following SQL statement:

CREATE TABLESPACE TEST_DAT DATAFILE ‘+DATA’;

The following example illustrates the usage of Oracle ASM with defaults. This example enables Oracle ASM to create and manage the tablespace data file for you, using Oracle supplied defaults that are adequate for most situations.

Assume the following initialization parameter setting:

DB_CREATE_FILE_DEST = +data

The following statement creates the tablespace and its data file:

CREATE TABLESPACE test_idx;

ASM Problems:

1. ORA-29701: unable to connect to Cluster Synchronization Service
   1. Before you can install Automatic Storage Management (ASM), Oracle Cluster Synchronization Services (CSS) must be configured and running.