ASM Views

1. View list and comments

View ASM Instance DB Instance V$ASM_ALIASDisplays a row for each alias present in every disk group mounted by the ASM instance.Returns no rowsV$ASM_CLIENTDisplays a row for each database instance using a disk group managed by the ASM instance.Displays a row for the ASM instance if the database has open ASM files.V$ASM_DISKDisplays a row for each disk discovered by the ASM instance, including disks which are not part of any disk group.Displays a row for each disk in disk groups in use by the database instance.V$ASM_DISKGROUPDisplays a row for each disk group discovered by the ASM instance.Displays a row for each disk group mounted by the local ASM instance.V$ASM_FILEDisplays a row for each file for each disk group mounted by the ASM instance.Displays no rows.V$ASM_OPERATIONDisplays a row for each file for each long running operation executing in the ASM instance.Displays no rows.V$ASM_TEMPLATEDisplays a row for each template present in each disk group mounted by the ASM instance.Displays a row for each template present in each disk group mounted by the ASM instance with which the database instance communicates.

 

2. 查询现有文件情况

column name format a50;
column type format a16;
column groupname format a16;
column bytes format 999,999,999;
column space format 999,999,999;
set pagesize 100
set linesize 256
select V$ASM_ALIAS.NAME,V$ASM_FILE.TYPE,V$ASM_FILE.BYTES,V$ASM_FILE.space,
V$ASM_DISKGROUP.name groupname ,TOTAL_MB,FREE_MB
from V$ASM_FILE,V$ASM_ALIAS,V$ASM_DISKGROUP
where V$ASM_FILE.FILE_NUMBER=V$ASM_ALIAS.FILE_NUMBER
and V$ASM_DISKGROUP.GROUP_NUMBER=V$ASM_FILE.GROUP_NUMBER
order by type, groupname,V$ASM_ALIAS.NAME ;

 

NAME                                               TYPE                    BYTES        SPACE GROUPNAME          TOTAL_MB    FREE_MB
-------------------------------------------------- ---------------- ------------ ------------ ---------------- ---------- ----------
1_37_744812413.dbf                                 ARCHIVELOG             11,776    1,048,576 DATA                   5740       4380
1_38_744812413.dbf                                 ARCHIVELOG             30,208    1,048,576 DATA                   5740       4380
1_39_744812413.dbf                                 ARCHIVELOG              3,584    1,048,576 DATA                   5740       4380
thread_1_seq_37.289.745021075                      ARCHIVELOG             11,776    1,048,576 DATA                   5740       4380
thread_1_seq_38.290.745021179                      ARCHIVELOG             30,208    1,048,576 DATA                   5740       4380
thread_1_seq_39.291.745021185                      ARCHIVELOG              3,584    1,048,576 DATA                   5740       4380
Current.256.744812423                              CONTROLFILE         7,192,576    8,388,608 DATA                   5740       4380
Current.256.744812423                              CONTROLFILE         7,192,576    8,388,608 DATA                   5740       4380
Current.256.744812423                              CONTROLFILE         7,192,576    8,388,608 FLASH                  2870       2643
Current.256.744812423                              CONTROLFILE         7,192,576    8,388,608 FLASH                  2870       2643
EXAMPLE.267.744929121                              DATAFILE          104,865,792  106,954,752 DATA                   5740       4380
SYSAUX.262.744812457                               DATAFILE          283,123,712  285,212,672 DATA                   5740       4380
SYSTEM.260.744812435                               DATAFILE          503,324,672  505,413,632 DATA                   5740       4380
UNDOTBS1.261.744812451                             DATAFILE          246,423,552  248,512,512 DATA                   5740       4380
USERS.264.744812467                                DATAFILE            5,251,072    6,291,456 DATA                   5740       4380
group_1.257.744812425                              ONLINELOG          52,429,312   58,720,256 DATA                   5740       4380
group_1.257.744812425                              ONLINELOG          52,429,312   58,720,256 DATA                   5740       4380
group_2.258.744812427                              ONLINELOG          52,429,312   58,720,256 DATA                   5740       4380
group_2.258.744812429                              ONLINELOG          52,429,312   58,720,256 DATA                   5740       4380
group_3.259.744812431                              ONLINELOG          52,429,312   58,720,256 DATA                   5740       4380
group_3.259.744812433                              ONLINELOG          52,429,312   58,720,256 DATA                   5740       4380
group_1.257.744812425                              ONLINELOG          52,429,312   58,720,256 FLASH                  2870       2643
group_1.257.744812425                              ONLINELOG          52,429,312   58,720,256 FLASH                  2870       2643
group_2.258.744812427                              ONLINELOG          52,429,312   58,720,256 FLASH                  2870       2643
group_2.258.744812429                              ONLINELOG          52,429,312   58,720,256 FLASH                  2870       2643
group_3.259.744812431                              ONLINELOG          52,429,312   58,720,256 FLASH                  2870       2643
group_3.259.744812433                              ONLINELOG          52,429,312   58,720,256 FLASH                  2870       2643
spfile.265.744812945                               PARAMETERFILE           3,584    1,048,576 DATA                   5740       4380
spfileRAC1.ora                                     PARAMETERFILE           3,584    1,048,576 DATA                   5740       4380
TEMP.263.744812461                                 TEMPFILE           28,319,744   29,360,128 DATA                   5740       4380

30 rows selected.

SQL>

3. 现有磁盘使用情况
column name format a20;
column path format a16
select name,PATH,GROUP_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB
from V$ASM_DISK
order by path,GROUP_NUMBER

NAME                 PATH             GROUP_NUMBER MOUNT_S   TOTAL_MB    FREE_MB
-------------------- ---------------- ------------ ------- ---------- ----------
FLASH_0000           /dev/raw/raw1               2 CACHED        2870       2643
                     /dev/raw/raw10              0 CLOSED        1961          0
DATA_0000            /dev/raw/raw2               1 CACHED        2870       2190
DATA_0001            /dev/raw/raw3               1 CACHED        2870       2190
                     /dev/raw/raw4               0 CLOSED        1623          0
                     /dev/raw/raw5               0 CLOSED         494          0
                     /dev/raw/raw6               0 CLOSED         494          0
                     /dev/raw/raw7               0 CLOSED         980          0
                     /dev/raw/raw8               0 CLOSED        1961          0
                     /dev/raw/raw9               0 CLOSED        1961          0

10 rows selected.

SQL>

4.创建diskgroup,检查可用空间;

SQL>  CREATE DISKGROUP DiskGroup1 NORMAL  REDUNDANCY
  2    FAILGROUP failure_group_1 DISK
  3     '/dev/raw/raw10' NAME diska10
  4    FAILGROUP failure_group_2 DISK
  5       '/dev/raw/raw9' NAME diskb9;

Diskgroup created.

SQL>  select GROUP_NUMBER,NAME,TOTAL_MB,FREE_MB,USABLE_FILE_MB from v$ASM_DISKGROUP order by  GROUP_NUMBER;

GROUP_NUMBER NAME                             TOTAL_MB    FREE_MB USABLE_FILE_MB
------------ ------------------------------ ---------- ---------- --------------
           1 DATA                                 5740       4380           4380
           2 FLASH                                2870       2643           2643
           3 DISKGROUP1                           3922       3820           1910

SQL>   

此时,磁盘使用情况如下, 注意name和group_number.

SQL> column name format a20;
SQL> column path format a16
SQL> select name,PATH,GROUP_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB
  2  from V$ASM_DISK
  3  order by path,GROUP_NUMBER ;

NAME                 PATH             GROUP_NUMBER MOUNT_S   TOTAL_MB    FREE_MB
-------------------- ---------------- ------------ ------- ---------- ----------
FLASH_0000           /dev/raw/raw1               2 CACHED        2870       2643
DISKA10              /dev/raw/raw10              3 CACHED        1961       1910
DATA_0000            /dev/raw/raw2               1 CACHED        2870       2190
DATA_0001            /dev/raw/raw3               1 CACHED        2870       2190
                     /dev/raw/raw4               0 CLOSED        1623          0
                     /dev/raw/raw5               0 CLOSED         494          0
                     /dev/raw/raw6               0 CLOSED         494          0
                     /dev/raw/raw7               0 CLOSED         980          0
                     /dev/raw/raw8               0 CLOSED        1961          0
DISKB9               /dev/raw/raw9               3 CACHED        1961       1910

10 rows selected.

SQL>