The GV$ASM views,Automatic Storage Management views are very important to gather asm structure and components related details of Oracle ASM,I find some time to have the queries which will help to gather all details with regards to Oracle ASM.
Below are some dynamic views and queries for knowing Oracle asm structure and components details:
SQL> SELECT *FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE '%ASM%';
TABLE_NAME
------------------------------
V$ASM_ACFSSNAPSHOTS
V$ASM_ACFSVOLUMES
V$ASM_ALIAS
V$ASM_ATTRIBUTE
V$ASM_CLIENT
V$ASM_DISK
V$ASM_DISKGROUP
V$ASM_DISKGROUP_STAT
V$ASM_DISK_IOSTAT
V$ASM_DISK_STAT
V$ASM_FILE
TABLE_NAME
------------------------------
V$ASM_FILESYSTEM
V$ASM_OPERATION
V$ASM_TEMPLATE
V$ASM_USER
V$ASM_USERGROUP
V$ASM_USERGROUP_MEMBER
V$ASM_VOLUME
V$ASM_VOLUME_STAT
GV$ASM_ACFSSNAPSHOTS
GV$ASM_ACFSVOLUMES
GV$ASM_ALIAS
TABLE_NAME
------------------------------
GV$ASM_ATTRIBUTE
GV$ASM_CLIENT
GV$ASM_DISK
GV$ASM_DISKGROUP
GV$ASM_DISKGROUP_STAT
GV$ASM_DISK_IOSTAT
GV$ASM_DISK_STAT
GV$ASM_FILE
GV$ASM_FILESYSTEM
GV$ASM_OPERATION
GV$ASM_TEMPLATE
TABLE_NAME
------------------------------
GV$ASM_USER
GV$ASM_USERGROUP
GV$ASM_USERGROUP_MEMBER
GV$ASM_VOLUME
GV$ASM_VOLUME_STAT
38 rows selected.
1)GV$ASM_DISKGROUP:This view displays asm instances related details along with one row for every ASM diskgroup discovered by the ASM instance on the node.
SQL> SELECT INST_ID||' '||NAME ||' '||STATE||' '||TOTAL_MB||' '||FREE_MB
FROM GV$ASM_DISKGROUP;
INST_ID||''||NAME||''||STATE||''||TOTAL_MB||''||FREE_MB
--------------------------------------------------------------------------------
2 GRID_DATA_01 MOUNTED 12084 11152
2 TESTASMDB_REDO_01 CONNECTED 65296 40504
2 TESTASMDB_REDO_02 CONNECTED 65296 40504
2 TESTASMDB_FRA_01 CONNECTED 261872 142831
2 TESTASMDB_DATA_01 CONNECTED 1048016 59453
1 GRID_DATA_01 MOUNTED 12084 11152
1 TESTASMDB_DATA_01 CONNECTED 1048016 59453
1 TESTASMDB_FRA_01 CONNECTED 261872 142831
1 TESTASMDB_REDO_01 CONNECTED 65296 40504
1 TESTASMDB_REDO_02 CONNECTED 65296 40504
2)GV$ASM_ALIAS:This view displays all system and user-defined aliases.
There is one row for every alias present in every diskgroup mounted by the ASM instance. The RDBMS instance displays no rows in this view.
SQL> SELECT INST_ID||''||NAME||''||FILE_NUMBER||''||ALIAS_DIRECTORY
FROM GV$ASM_ALIAS;
INST_ID||''||NAME||''||FILE_NUMBER||''||ALIAS_DIRECTORY
--------------------------------------------------------------------------------
2 USERS.323.750185521 323 N
2 ASMWORK.320.750263763 320 N
2 ASMMASTER.319.750263765 319 N
2 UNDOTBS3.306.768750281 306 N
2 TEMPFILE 4294967295 Y
2 TEMP.325.750185505 325 N
3)GV$ASM_ATTRIBUTE:This Oracle Database 11g view displays one row for each ASM attribute defined.
These attributes are listed when they are defined in CREATE DISKGROUP or ALTER DISKGROUP statements. DISK_REPAIR_TIMER is an example of an attribute.
SQL> SELECT INST_ID||' '||NAME||' '|| GROUP_NUMBER
FROM GV$ASM_ATTRIBUTE;
no rows selected
4)GV$ASM_CLIENT:This view displays one row for each RDBMS instance that has an opened ASM diskgroup.
SQL> SELECT INST_ID||' '||INSTANCE_NAME||' '||DB_NAME||' '||STATUS
FROM GV$ASM_CLIENT;
INST_ID||''||INSTANCE_NAME||''||DB_NAME||''||STATUS
--------------------------------------------------------------------------------
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
5)GV$ASM_DISK:This view contains specifics about all disks discovered by the ASM isntance,
including mount status, disk state, and size.There is one row for every disk discovered by the ASM instance.
SQL> SELECT INST_ID||' '||STATE||' '||TOTAL_MB||' '||FREE_MB||' '||NAME
FROM GV$ASM_DISK;
INST_ID||''||STATE||''||TOTAL_MB||''||FREE_MB||''||NAME
--------------------------------------------------------------------------------
2 NORMAL 2014 1843 GRID_DATA_01_0000
2 NORMAL 2014 1844 GRID_DATA_01_0001
2 NORMAL 2014 1843 GRID_DATA_01_0002
2 NORMAL 2014 1878 GRID_DATA_01_0003
2 NORMAL 2014 1875 GRID_DATA_01_0004
2 NORMAL 2014 1869 GRID_DATA_01_0005
2 NORMAL 8162 5063 TESTASMDB_REDO_01_0000
2 NORMAL 8162 5061 TESTASMDB_REDO_01_0001
2 NORMAL 8162 5065 TESTASMDB_REDO_01_0002
6)GV$ASM_DISK_IOSTAT:This displays information about disk I/O statistics for each ASM Client. If this view is queried from the database instance, only the rows for that instance are shown.
SQL> SELECT INST_ID||' '||INSTNAME||' '||DBNAME||' '||GROUP_NUMBER||' '||DISK_NUMBER||' '||READS||' '||WRITES||' '||READ_TIME||' '||WRITE_TIME
FROM GV$ASM_DISK_IOSTAT;
7)GV$ASM_DISK_STAT:This view contains similar content as the v$ASM_DISK, except v$ASM_DISK_STAT reads disk information from cache and thus performs no disk discovery.
Thsi view is primarily used form quick acces to the disk information without the overhead of disk discovery.
8)GV$ASM_DISKGROUP_STAT:This view contains all the similar view contents as the v$ASM_DISKGROUP,
except that v$ASM_DISK_STAT reads disk information from the cache and thus performs no disk discovery.
This view is primarily used for quick access to the diskgroup information without the overhead of disk discovery.
SQL> SELECT INST_ID||' '||NAME||' '||ALLOCATION_UNIT_SIZE||' '||STATE||' '||TOTAL_MB||' '||FREE_MB
FROM GV$ASM_DISKGROUP_STAT;
INST_ID||''||NAME||''||ALLOCATION_UNIT_SIZE||''||STATE||''||TOTAL_MB||''||FREE_M
--------------------------------------------------------------------------------
1 GRID_DATA_01 1048576 MOUNTED 12084 11152
1 TESTASMDB_DATA_01 1048576 CONNECTED 1048016 59453
1 TESTASMDB_FRA_01 1048576 CONNECTED 261872 142831
1 TESTASMDB_REDO_01 1048576 CONNECTED 65296 40504
1 TESTASMDB_REDO_02 1048576 CONNECTED 65296 40504
2 GRID_DATA_01 1048576 MOUNTED 12084 11152
2 TESTASMDB_REDO_01 1048576 CONNECTED 65296 40504
2 TESTASMDB_REDO_02 1048576 CONNECTED 65296 40504
2 TESTASMDB_FRA_01 1048576 CONNECTED 261872 142831
2 TESTASMDB_DATA_01 1048576 CONNECTED 1048016 59453
10 rows selected.
9)GV$ASM_FILE:This view displays information about ASM files.
There is one row for every ASM file in every diskgroup mounted by the ASM instance. In a RDBMS instance, V$ASM_FILE displays no row.
SELECT INST_ID||''||FILE_NUMBER||''||BLOCK_SIZE||''||CREATION_DATE||''||REDUNDANCY
FROM GV$ASM_FILE;
INST_ID||''||FILE_NUMBER||''||BLOCK_SIZE||''||CREATION_DATE||''||REDUNDANCY
--------------------------------------------------------------------------------
1 335 512 17-MAR-12 UNPROT
1 336 512 15-MAR-12 UNPROT
1 337 512 15-MAR-12 UNPROT
1 339 512 18-MAR-12 UNPROT
1 340 512 23-MAR-12 UNPROT
10)GV$ASM_OPERATION:This view describes the progress of an influx ASM rebalance operation.
In a RDBMS instance,GV$ASM_OPERATION displays no rows.
SQL> SELECT INST_ID||' '||OPERATION||' '||STATE||' '||POWER||' '||ACTUAL||' '||SOFAR
FROM GV$ASM_OPERATION;
11)GV$ASM_TEMPLATE:This view contains information on user and system-defined templated.
GV$ASM_TEMPLATE displays one row for every template present in every diskgroup mounted by the ASM instance.
In a RDBMS instance,GV$ASM_TEMPLATE displays one row for every template present in every diskgroup mounted by the ASM instance with which the RDBMS instance communicates.
SQL> SELECT INST_ID||''||GROUP_NUMBER||''||STRIPE||''||SYSTEM
FROM GV$ASM_TEMPLATE;
SQL> SELECT FS_NAME||' '||VOL_DEVICE||' '||SNAP_NAME||' '||CREATE_TIME
FROM V$ASM_ACFSSNAPSHOTS;
no rows selected
Enjoy Oracle ASM learning........
Best regards,
Rafi
