查询磁盘状态
set linesize 1000 pagesize 1000
col name for a20
col path for a30
col group_number for 9999
select name,path,group_number from v$asm_disk_stat ;
NAME PATH GROUP_NUMBER
-------------------- ------------------------------ ------------
DATADG_0003 /dev/sdh1 2
DATADG_0002 /dev/sde1 2
DATADG_0001 /dev/sdc1 2
DATADG_0000 /dev/sdb1 2
CRSDG_0002 /dev/sdg1 1
CRSDG_0001 /dev/sdf1 1
CRSDG_0000 /dev/sdd1 1
7 rows selected.
查询磁盘组可用空间大小(看USABLE_FILE_MB字段,这个是保证磁盘组能达到normal状态冗余的情况下的可用大小)
SQL> set linesize 1000 pagesize 1000
SQL> col name for a20
SQL> col path for a30
SQL> col group_number for 9999
SQL> select state,name,type,total_mb, free_mb,USABLE_FILE_MB from v$asm_diskgroup_stat;
STATE NAME TYPE TOTAL_MB FREE_MB USABLE_FILE_MB
---------------------- -------------------- ------------ ---------- ---------- --------------
MOUNTED CRSDG NORMAL 49140 48272 15946
MOUNTED DATADG NORMAL 409584 409288 102248
SQL>
查询磁盘组是否在做rebanlance操作
SQL> select * from v$asm_operation ;
no rows selected
SQL>
查看磁盘组属性(磁盘组所有属性,比较多)
col DISKGROUP for a10
col name for a20
col VALUE for a20
col READ_ONLY for a10
SELECT dg.name AS diskgroup,SUBSTR(a.name,1,18) AS name,
SUBSTR(a.value,1,24) AS value,
read_only
FROM V$ASM_DISKGROUP_STAT dg,V$ASM_ATTRIBUTE a
WHERE dg.group_number = a.group_number ;
SQL> SELECT dg.name AS diskgroup,SUBSTR(a.name,1,18) AS name,
2 SUBSTR(a.value,1,24) AS value,
3 read_only
4 FROM V$ASM_DISKGROUP_STAT dg,V$ASM_ATTRIBUTE a
5 WHERE dg.group_number = a.group_number ;
DISKGROUP NAME VALUE READ_ONLY
---------- -------------------- -------------------- ----------
DATADG idp.type dynamic N
DATADG idp.boundary auto N
DATADG disk_repair_time 72h N
DATADG phys_meta_replicat true Y
DATADG failgroup_repair_t 24.0h N
DATADG thin_provisioned FALSE N
DATADG preferred_read.ena FALSE N
DATADG sector_size 512 N
DATADG logical_sector_siz 512 N
DATADG content.type data N
DATADG content.check FALSE N
DATADG au_size 4194304 Y
DATADG appliance._partner GENERIC Y
DATADG compatible.asm 12.2.0.1.0 N
DATADG compatible.rdbms 12.2.0.0.0 N
DATADG compatible.advm 12.2.0.1.0 N
DATADG cell.smart_scan_ca FALSE N
DATADG cell.sparse_dg allnonsparse N
DATADG access_control.ena FALSE N
DATADG access_control.uma 066 N
DATADG scrub_async_limit 1 N
DATADG scrub_metadata.ena FALSE N
DATADG template_version 186646528 N
DATADG template.PARAMETER 0 N
DATADG template.PARAMETER 18 N
DATADG template.PARAMETER 0 N
DATADG template.PARAMETER 0 N
DATADG template.ASMPARAME 0 N
DATADG template.ASMPARAME 18 N
DATADG template.ASMPARAME 0 N
DATADG template.ASMPARAME 0 N
DATADG template.VOTINGFIL 0 N
DATADG template.VOTINGFIL 18 N
DATADG template.VOTINGFIL 0 N
DATADG template.VOTINGFIL 0 N
DATADG template.DUMPSET.s 0 N
DATADG template.DUMPSET.r 18 N
DATADG template.DUMPSET.p 0 N
DATADG template.DUMPSET.m 0 N
DATADG template.CONTROLFI 1 N
DATADG template.CONTROLFI 19 N
DATADG template.CONTROLFI 0 N
DATADG template.CONTROLFI 0 N
DATADG template.FLASHFILE 0 N
DATADG template.FLASHFILE 18 N
DATADG template.FLASHFILE 0 N
DATADG template.FLASHFILE 0 N
DATADG template.ARCHIVELO 0 N
DATADG template.ARCHIVELO 18 N
DATADG template.ARCHIVELO 0 N
DATADG template.ARCHIVELO 0 N
DATADG template.ONLINELOG 0 N
DATADG template.ONLINELOG 18 N
DATADG template.ONLINELOG 0 N
DATADG template.ONLINELOG 0 N
DATADG template.DATAFILE. 0 N
DATADG template.DATAFILE. 18 N
DATADG template.DATAFILE. 0 N
DATADG template.DATAFILE. 0 N
DATADG template.TEMPFILE. 0 N
DATADG template.TEMPFILE. 18 N
DATADG template.TEMPFILE. 0 N
DATADG template.TEMPFILE. 0 N
DATADG template.BACKUPSET 0 N
DATADG template.BACKUPSET 18 N
DATADG template.BACKUPSET 0 N
DATADG template.BACKUPSET 0 N
DATADG template.XTRANSPOR 0 N
DATADG template.XTRANSPOR 18 N
DATADG template.XTRANSPOR 0 N
DATADG template.XTRANSPOR 0 N
DATADG template.INCR XTRA 0 N
DATADG template.INCR XTRA 18 N
DATADG template.INCR XTRA 0 N
DATADG template.INCR XTRA 0 N
DATADG template.AUTOBACKU 0 N
DATADG template.AUTOBACKU 18 N
DATADG template.AUTOBACKU 0 N
DATADG template.AUTOBACKU 0 N
DATADG template.XTRANSPOR 0 N
DATADG template.XTRANSPOR 18 N
DATADG template.XTRANSPOR 0 N
DATADG template.XTRANSPOR 0 N
DATADG template.CHANGETRA 0 N
DATADG template.CHANGETRA 18 N
DATADG template.CHANGETRA 0 N
DATADG template.CHANGETRA 0 N
DATADG template.FLASHBACK 0 N
DATADG template.FLASHBACK 18 N
DATADG template.FLASHBACK 0 N
DATADG template.FLASHBACK 0 N
DATADG template.KEY_STORE 0 N
DATADG template.KEY_STORE 18 N
DATADG template.KEY_STORE 0 N
DATADG template.KEY_STORE 0 N
DATADG template.AUTOLOGIN 0 N
DATADG template.AUTOLOGIN 18 N
DATADG template.AUTOLOGIN 0 N
DATADG template.AUTOLOGIN 0 N
DATADG template.AUDIT_SPI 0 N
DATADG template.AUDIT_SPI 18 N
DATADG template.AUDIT_SPI 0 N
DATADG template.AUDIT_SPI 0 N
DATADG template.DATAGUARD 0 N
DATADG template.DATAGUARD 18 N
DATADG template.DATAGUARD 0 N
DATADG template.DATAGUARD 0 N
DATADG template.OCRFILE.s 0 N
DATADG template.OCRFILE.r 18 N
DATADG template.OCRFILE.p 0 N
DATADG template.OCRFILE.m 0 N
111 rows selected.
SQL>
ALTER DISKGROUP DATADG SET ATTRIBUTE 'compatible.rdbms' = '12.2.0.0.0';
ALTER DISKGROUP CRSDG SET ATTRIBUTE 'compatible.rdbms' = '12.2.0.0.0';
set line 200
col value for a20
col name for a40
col DISKGROUP_NAME for a20
select a.GROUP_NUMBER,a.name DISKGROUP_NAME,b.name,value from v$asm_diskgroup a,v$asm_attribute b where a.GROUP_NUMBER=b.GROUP_NUMBER and b.name like 'compatible%';
GROUP_NUMBER DISKGROUP_NAME NAME VALUE
------------ -------------------- ---------------------------------------- --------------------
1 CRSDG compatible.asm 12.2.0.1.0
1 CRSDG compatible.rdbms 12.2.0.0.0
1 CRSDG compatible.advm 12.2.0.1.0
2 DATADG compatible.asm 12.2.0.1.0
2 DATADG compatible.rdbms 12.2.0.0.0
2 DATADG compatible.advm 12.2.0.1.0
6 rows selected.
SQL>
备注:当compatible.rdbms参数低于11.1.0.0.0,offline disk或改disk_repair_time都会提示需要11.1或以上版本报错。
修改磁盘组兼容性参数版本
11gR2:
ALTER DISKGROUP OCRDG SET ATTRIBUTE 'compatible.rdbms' = '11.2.0.0.0';
ALTER DISKGROUP SYSDG SET ATTRIBUTE 'compatible.rdbms' = '11.2.0.0.0';
ALTER DISKGROUP ARCHDG SET ATTRIBUTE 'compatible.rdbms' = '11.2.0.0.0';
ALTER DISKGROUP DATADG SET ATTRIBUTE 'compatible.rdbms' = '11.2.0.0.0';
ALTER DISKGROUP CRSDG SET ATTRIBUTE 'compatible.asm' = '19.3.0.0.0';
12cR2
ALTER DISKGROUP DATADG SET ATTRIBUTE 'compatible.rdbms' = '12.2.0.0.0';
ALTER DISKGROUP DATADG SET ATTRIBUTE 'compatible.asm' = '12.2.0.0.0';
####################################################################################################
查看diskgroup的repair_time
col name for a20
col value for a20
col DISKGROUP_NAME for a20
select a.GROUP_NUMBER,a.name DISKGROUP_NAME ,b.name,value from v$asm_diskgroup a,v$asm_attribute b where a.GROUP_NUMBER=b.GROUP_NUMBER and b.name like 'disk_repair_time%';
GROUP_NUMBER DISKGROUP_NAME NAME VALUE
------------ -------------------- -------------------- --------------------
1 CRSDG disk_repair_time 72h
2 DATADG disk_repair_time 72h
SQL>
修改disk_repair_time
alter diskgroup CRSDG set ATTRIBUTE 'disk_repair_time'='72h' ;
alter diskgroup DATADG set ATTRIBUTE 'disk_repair_time'='72h' ;
查看磁盘组兼容性属性
col COMPATIBILITY for a20
col DATABASE_COMPATIBILITY for a20
SELECT name,COMPATIBILITY,DATABASE_COMPATIBILITY FROM V$ASM_DISKGROUP_STAT;
NAME COMPATIBILITY DATABASE_COMPATIBILI
-------------------- -------------------- --------------------
CRSDG 12.2.0.1.0 12.2.0.0.0
DATADG 12.2.0.1.0 12.2.0.0.0
SQL>
查看磁盘状态
col asmdisk for a20
col mount_status for a10
col HEADER_STATUS for a10
col state for a10
col MODE_STATUS for a10
col REDUNDANCY for a10
col diskgroup for a10
col FAILGROUP for a15
col PATH for a20
SELECT SUBSTR(d.name,1,16) AS asmdisk,
d.mount_status,
HEADER_STATUS,
d.state,
d.MODE_STATUS,
d.REDUNDANCY,
dg.name AS diskgroup,
d.FAILGROUP,
d.PATH
FROM V$ASM_DISKGROUP_STAT dg, V$ASM_DISK_STAT d
WHERE dg.group_number = d.group_number ;
ASMDISK MOUNT_STAT HEADER_STA STATE MODE_STATU REDUNDANCY DISKGROUP FAILGROUP PATH
-------------------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- --------------------
CRSDG_0001 CACHED MEMBER NORMAL ONLINE UNKNOWN CRSDG CRSDG_0001 /dev/sdf1
CRSDG_0002 CACHED MEMBER NORMAL ONLINE UNKNOWN CRSDG CRSDG_0002 /dev/sdg1
CRSDG_0000 CACHED MEMBER NORMAL ONLINE UNKNOWN CRSDG CRSDG_0000 /dev/sdd1
DATADG_0001 CACHED MEMBER NORMAL ONLINE UNKNOWN DATADG DG_FG_000 /dev/sdc1
DATADG_0002 CACHED MEMBER NORMAL ONLINE UNKNOWN DATADG DG_FG_001 /dev/sde1
DATADG_0003 CACHED MEMBER NORMAL ONLINE UNKNOWN DATADG DG_FG_001 /dev/sdh1
DATADG_0000 CACHED MEMBER NORMAL ONLINE UNKNOWN DATADG DG_FG_000 /dev/sdb1
7 rows selected.
SQL>
set line 400
col name for a20
col path for a30
col failgroup for a20
select DISK_NUMBER,name,path,failgroup,mount_status,mode_status,state from v$asm_disk order by 3;
查看磁盘客户端信息
SELECT dg.name AS diskgroup,
SUBSTR(c.instance_name,1,12) AS instance,
SUBSTR(c.db_name,1,12) AS dbname,
SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,
SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible
FROM V$ASM_DISKGROUP_STAT dg, V$ASM_CLIENT c
WHERE dg.group_number = c.group_number;
no rows selected
SQL>
查看磁盘访问控制用户信息
SELECT dg.name AS diskgroup,
u.group_number,
u.user_number,
u.os_id,
u.os_name
FROM V$ASM_DISKGROUP_STAT dg, V$ASM_USER u
WHERE dg.group_number = u.group_number
AND dg.name ='DATADG';
查看磁盘访问控制组信息
SELECT dg.name AS diskgroup,
ug.group_number,
ug.owner_number,
u.os_name,
ug.usergroup_number,
ug.name
FROM V$ASM_DISKGROUP_STAT dg, V$ASM_USER u, V$ASM_USERGROUP ug
WHERE dg.group_number = ug.group_number
AND dg.name ='DATADG'
AND ug.owner_number = u.user_number;
查看智能数据分布信息
SELECT dg.name AS diskgroup,
f.file_number,
f.primary_region,
f.mirror_region,
f.hot_reads,f.hot_writes,
f.cold_reads,
f.cold_writes
FROM V$ASM_DISKGROUP_STAT dg, V$ASM_FILE f
WHERE dg.group_number = f.group_number
and dg.name ='DATADG';