Preconditions
Connected as oracle user on the DB host, set the environment to the +ASM instance.
For example:
OS> export ORACLE_HOME=/u00/app/grid/11.2.0.4.0.P0_GI
OS> export ORACLE_SID=+ASM
Logon to the +ASM instance as SYSDBA:
OS> sqlplus / as sysdba
SQL>
Queries
Diskgroup Space Usage Overview
Lists for each diskgroup, the total size, free size, usable size and used size in GB.
- The "usable size" is the amount of free space taking in account the space needed for mirroring.
It is calculated by subtracting required_missor_free_mb (not displayed here) from free_mb.
For example, if normal redundancy is used, the files are mirrored. So they use twice their size space.
In this case, if you have 10 GB free space the "usable size" would show around 5 GB.
Since no redundancy is used in the output example below (we are using Data Guard for high availability),
free and usable sizes are equal.
SELECT name, total_mb/1024 AS "Total GB", free_mb/1024 AS "Free GB", usable_file_mb/1024 AS "Usable GB", (total_mb-usable_file_mb)/1024 AS "Used GB"
FROM v$asm_diskgroup;
Output example:
NAME Total GB Free GB Usable GB Used GB
------------------------------ ---------- ---------- ---------- ----------
U01 1600 713.609375 713.609375 886.390625
U02 800 757.382813 757.382813 42.6171875
Files Overview
Lists files with their type, size, creation date and file number:
COLUMN gname FORMAT A9 HEADING "Diskgroup|Name"
COLUMN fname FORMAT A35 HEADING "File|Name"
COLUMN type FORMAT A20 HEADING "File|Type"
COLUMN sizemb FORMAT 9,999,999.99 HEADING "File|Size MB"
COLUMN credate FORMAT A16 HEADING "File|Creation Date"
SELECT g.name gname, a.name fname, f.type, f.bytes/(1024*1024) sizemb, TO_CHAR(f.creation_date,'YYYY.MM.DD HH24:MI') credate, f.file_number
FROM v$asm_file f, v$asm_alias a, v$asm_diskgroup g
WHERE g.group_number = a.group_number
AND f.group_number = a.group_number
AND f.file_number = a.file_number
AND f.type NOT IN ('FLASHBACK','ARCHIVELOG')
ORDER BY 2, 1;
Output example:
Diskgroup File File File File
Name Name Type Size MB Creation Date FILE_NUMBER
--------- ----------------------------------- -------------------- ------------- ---------------- -----------
U01 Current.256.904578613 CONTROLFILE 32.36 2016.02.23 15:50 256
U02 Current.256.904578613 CONTROLFILE 32.36 2016.02.23 15:50 256
U01 SYSAUX.261.904578637 DATAFILE 4,096.01 2016.02.23 15:50 261
U01 SYSTEM.260.904578633 DATAFILE 1,024.01 2016.02.23 15:50 260
U01 TEMP.263.904578645 TEMPFILE 6,144.01 2016.02.23 15:50 263
U01 TOOLS.273.904745439 DATAFILE 10.01 2016.02.25 14:10 273
U01 TS_APP01_DATA.306.907928707 DATAFILE 2,048.01 2016.03.31 10:25 306
U01 TS_APP02_DATA.269.904748123 DATAFILE 300.01 2016.02.25 14:55 270
U01 TS_APP02_IDX.274.904748203 DATAFILE 120.01 2016.02.25 14:56 274
U01 TS_APP03_ARCH.275.904745853 DATAFILE 32,520.01 2016.02.25 14:17 275
U01 TS_APP03_DATA.268.904748393 DATAFILE 21,504.01 2016.02.25 14:59 268
U01 TS_APP03_IDX.268.904748394 DATAFILE 21,504.01 2016.02.25 15:01 269
. . .
I/O and Error Statistics per DB-Instance, Disk-Group and Disk
COLUMN instname FORMAT A10 HEADING "Instance"
COLUMN name FORMAT A9 HEADING "Diskgroup|Name"
COLUMN group_number FORMAT 999 HEADING "Diskgroup|Number"
COLUMN disk_number FORMAT 999 HEADING "Disk|Number"
COLUMN reads FORMAT 999,999,999 HEADING "Total Read|Requests"
COLUMN writes FORMAT 999,999,999 HEADING "Total Write|Requests"
COLUMN read_time FORMAT 999,999 HEADING "Total Read|Time [1/100s]"
COLUMN write_time FORMAT 999,999 HEADING "Total Write|Time [1/100s]"
COLUMN read FORMAT 999,999,999 HEADING "Total Read|[MB]"
COLUMN written FORMAT 999,999,999 HEADING "Total Write|[MB]"
SELECT d.instname, g.name, d.group_number, d.disk_number,
d.reads, d.writes, d.read_errs, d.write_errs,
d.read_time, d.write_time, d.bytes_read/(1024*1024) read, d.bytes_written/(1024*1024) written
FROM v$asm_disk_iostat d, v$asm_diskgroup g
WHERE g.group_number = d.group_number
ORDER BY 1, 2, 3;
Output example:
Diskgroup Diskgroup Disk Total Read Total Write Total Read Total Write Total Read Total Write Total Read Total Write
Instance Name Number Number Requests Requests Errors Errors Time [1/100s] Time [1/100s] [MB] [MB]
---------- --------- --------- ------ ------------ ------------ ---------- ----------- ------------- ------------- ------------ ------------
TEST_DB U01 1 1 16,174,066 8,985,315 0 0 546,514 464,285 3,175,899 837,536
TEST_DB U01 1 0 16,860,897 9,075,988 0 0 597,154 462,099 3,183,002 836,577
TEST_DB U02 2 0 1,716,125 3,428,283 0 0 78,300 119,095 721,684 1,708,365
Status and Error Statistics per Disk-Group and Disk
COLUMN name FORMAT A9 HEADING "Diskgroup|Name"
COLUMN mount_status FORMAT A12
COLUMN header_status FORMAT A13
COLUMN mode_status FORMAT A11
SELECT g.name, d.group_number, d.disk_number, d.mount_status, d.header_status, d.mode_status, d.state, d.read_errs, d.write_errs
FROM v$asm_disk d, v$asm_diskgroup g
WHERE g.group_number = d.group_number
ORDER BY 1, 2;
Output example:
Diskgroup Diskgroup Disk Total Read Total Write
Name Number Number MOUNT_STATUS HEADER_STATUS MODE_STATUS STATE Errors Errors
--------- --------- ------ ------------ ------------- ----------- -------- ---------- -----------
U01 1 1 CACHED MEMBER ONLINE NORMAL 0 0
U01 1 0 CACHED MEMBER ONLINE NORMAL 0 0
U02 2 0 CACHED MEMBER ONLINE NORMAL 0 0
Tags: Oracle, ASM, Usage, Disk, Error, Space, Publish
August 19, 2011 at 01:37PM
No comments :
Post a Comment