1. View list and comments
View | ASM Instance | DB Instance |
---|---|---|
V$ASM_ALIAS | Displays a row for each alias present in every disk group mounted by the ASM instance. | Returns no rows |
V$ASM_CLIENT | Displays 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_DISK | Displays 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_DISKGROUP | Displays 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_FILE | Displays a row for each file for each disk group mounted by the ASM instance. | Displays no rows. |
V$ASM_OPERATION | Displays a row for each file for each long running operation executing in the ASM instance. | Displays no rows. |
V$ASM_TEMPLATE | Displays 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>