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 rowsV$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>
