==============================================
=== summary report of all 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
===================================================================================
====================================================
– | 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 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD ‘Disk Group Name’
COLUMN disk_path FORMAT a20 HEAD ‘Disk Path’
COLUMN reads FORMAT 999,999,999 HEAD ‘Reads’
COLUMN writes FORMAT 999,999,999 HEAD ‘Writes’
COLUMN read_errs FORMAT 999,999 HEAD ‘Read|Errors’
COLUMN write_errs FORMAT 999,999 HEAD ‘Write|Errors’
COLUMN read_time FORMAT 999,999,999 HEAD ‘Read|Time’
COLUMN write_time FORMAT 999,999,999 HEAD ‘Write|Time’
COLUMN bytes_read FORMAT 999,999,999,999 HEAD ‘Bytes|Read’
COLUMN bytes_written FORMAT 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
/
===================================================================================
================================================================
Mastering ASMCMD
ASMCMD> asmcmd -p
cd : Changes the current directory to the specified directory.
du : Displays the total disk space occupied by ASM files in the specified ASM
directory and all its subdirectories, recursively.
exit : Exits ASMCMD.
find Lists the paths of all occurrences of the specified name (with wildcards)
under the specified directory :
ASMCMD> find +dgroup1 undo* +dgroup1/SAMPLE/DATAFILE/UNDOTBS1.258.555341963
+dgroup1/SAMPLE/DATAFILE/UNDOTBS1.272.557429239
The following example returns the absolute path of all the control files in
the +dgroup1/sample directory :
ASMCMD> find -t CONTROLFILE +dgroup1/sample *
+dgroup1/sample/CONTROLFILE/Current.260.555342185
+dgroup1/sample/CONTROLFILE/Current.261.555342183
ls : Lists the contents of an ASM directory, the attributes of the specified file,
or the names and attributes of all disk groups.
lsct : Lists information about current ASM clients.
lsdg : Lists all disk groups and their attributes.
mkalias : Creates an alias for a system-generated filename.
mkdir : Creates ASM directories.
pwd : Displays the path of the current ASM directory.
rm : Deletes the specified ASM files or directories.
rmalias : Deletes the specified alias, retaining the file that the alias points
to.
===================================================================================
==============================================================================
ASM Dynamic Views: ASM Instance Information
Posted By Sagar Patil
V$ASM_ALIAS Shows every alias for every disk group mounted by the ASM instance
V$ASM_CLIENT Shows which database instance(s) are using any ASM disk groups that
are being mounted by this ASM instance
V$ASM_DISK Lists each disk discovered by the ASM instance, including disks that
are not part of any ASM disk group
V$ASM_DISKGROUP Describes information about ASM disk groups mounted by the ASM
instance
V$ASM_FILE Lists each ASM file in every ASM disk group mounted by the ASM instance
V$ASM_OPERATION Like its counterpart, V$SESSION_LONGOPS, it shows each long-
running ASM operation in the ASM instance
V$ASM_TEMPLATE Lists each template present in every ASM disk group mounted by the
ASM instance
V$ASM_DISKGROUP Shows one row per each ASM disk group that’s mounted by the local
ASM instance
V$ASM_DISK Displays one row per each disk in each ASM disk group that are in use by
the database instance
V$ASM_CLIENT Lists one row per each ASM instance for which the database instance
has any open ASM files
ASM File Names:
Example ASM1DG1.ORCL.DATAFILE.TBSASM.257.1
A fully-qualified file name comprises the following five components, and always
ends with a special number pair:<group> :
The name of the ASM disk group.
<dbname>. The database that owns this ASM file.
<file type>. The Oracle file type (e.g. DATAFILE, ARCHIVELOG, CONTROLFILE).
<tag>. Type-specific information about the file. In this case, it is the
corresponding tablespace name for the datafile.
<file>.<incarnation>. A special number pair that ASM generates to insure
uniqueness.
===================================================================================
==============================================================================
===================================================================================
===================================================================
1-connexion à ASM : shell>asmcmd
2- list disk groups : ASMCMD> lsdg
3- Lister contenu DATA :
ASMCMD> cd DATA
ASMCMD> ls =l
4- List disk group with sqlplus : sqlplus / as sysasm
SQL> SELECT GROUP_NUMBER,
NAME,SECTOR_SIZE,BLOCK_SIZE,ALLOCATION_UNIT_SIZE,STATE,TYPE TOTAL_MB,FREE_MB FROM
V$ASM_DISKGROUP;
5-Disk HEADER_STATUS value CANDIDATE (disk not added) :
SQL> SELECT MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, TOTAL_MB,
FREE_MB, NAME, PATH, LABEL FROM V$ASM_DISK;
6-ASM DISK Mount : SQL> alter diskgroup RECO mount;
7- ASM DISMOUNT : SQL> alter diskgroup all dismount;
8- Disk operations(add and drop asm disks) status : SQL> select * from
v$asm_operation;
9- Add Disks From ASM Disk Group : alter diskgroup DATA add disk
'/dev/oracleasm/disks/ASMDATA08';
10- Add Disks to ASM Disk Group : ALTER DISKGROUP DATA ADD DISK
'/dev/mapper/asmtsk1' NAME DATA_0002 SIZE 102399M REBALANCE POWER 10;
11- List the All ASM Disks and their state : select DISK_NUMBER,name ,PATH,
MOUNT_DATE from v$asm_disk;
12- List the All ASM Disks statistics : select group_number, name, TOTAL_MB,
FREE_MB from V$asm_disk_stat;
13- Drop Disks from ASM Disk Group :
select DISK_NUMBER,name ,PATH, MOUNT_DATE from v$asm_disk;
alter diskgroup DATA drop disk DATA_0032;
