首页 > 数据库 >Oracle ASM磁盘组的常用操作

Oracle ASM磁盘组的常用操作

时间:2023-02-15 15:22:04浏览次数:40  
标签:group name dg number Oracle 磁盘 ASM

1.查看现有磁盘组信息

select group_number gno,name,state,type,total_mb,free_mb,required_mirror_free_mb rmfmb,usable_file_mb ufmb from v$asm_diskgroup;

 

 

 

2.查看磁盘信息

select group_number gno,name,failgroup fgno,state,total_mb,free_mb,header_status from v$asm_disk;

 

 

方法二:

select name,path,state,mount_status,header_status from v$asm_disk;

 

 

ignored状态也是不正常的。

closed表示磁盘未被当前实例使用,member表示这个磁盘已经是某个diskgroup的成员了

 

方法三:fdisk -l查看磁盘状态

查看磁盘组列表

[root@racdb1 /]# oracleasm listdisks

ARCH1
ARCH2
ARCH3
DATA1
DATA2
DATA3
DATA4

OCR1

OCR2

OCR3

在节点2查看

[root@racdb2 /]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...

[root@racdb2 /]# oracleasm listdisks

[root@racdb2 /]#pwd

/dev/oracleasm/disk/

 

3.查看ASM磁盘路径

select group_number,name,path,state from v$asm_disk;

 

红色框中为新添加的磁盘,未分配的磁盘group为0,NAME为空

 

4.添加磁盘到磁盘组(+ASM实例中操作)

SQL> alter diskgroup ARCH add disk '/dev/oracleasm/disks/ARCH3';

Diskgroup altered.

 

补充:

SQL> alter diskgroup ARCH add disk '/dev/oracleasm/disks/ARCH3' rebalance power 10;

因为磁盘要做rebalance,最好在rebalance完后再使用磁盘组

select * from v$asm_operation;

查看磁盘组操作

SQL> select * from v$asm_operation;

 

 

5.查看磁盘状态

select group_number,name,path,state from v$asm_disk

 

 

红色部分自动分配了NAME,并制定了ARCH组对应的组号

 

6.磁盘组的创建和删除

创建磁盘组

create diskgroup dg3 normal redundancy disk 'ORCL:VOL9','ORCL:VOL10';

 

删除磁盘

SQL> alter diskgroup dg1 drop disk VOL9;

Diskgroup altered.

 

取消删除

alter diskgroup dg1 undrop disks;

 

调整大小

ALTER DISKGROUP DG1 RESIZE DISK VOL4 SIZE 1024 M

 

7.unmount和mount磁盘组

SQL> alter diskgroup dg3 dismount;

 Diskgroup altered.

 

 SQL> select group_number gno,name,state,type,total_mb,free_mb,required_mirror_free_mb rmfmb,usable_file_mb ufmb from v$asm_diskgroup;

 

GNO NAME                           STATE                  TYPE                             TOTAL_MB    FREE_MB      RMFMB       UFMB

---------- ------------------------------ ---------------------- ------------------------------ ---------- ---------- ---------- ----------

         1 DG1                            MOUNTED                NORMAL                               4076        575        879       -152

         2 DG2                            MOUNTED                NORMAL                               4076       3531        139       1696

         0 DG3                            DISMOUNTED 

 

SQL> alter diskgroup dg3 mount; 

 Diskgroup altered.

 

 

8.查看磁盘组属性

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;

 

 

9.查看磁盘组兼容属性

SQL> SELECT name,COMPATIBILITY,DATABASE_COMPATIBILITY FROM V$ASM_DISKGROUP_STAT;

 

 

10.查看磁盘客户端信息

SQL> 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;

 

 

11.查看磁盘访问控制信息

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 ='DATA';

 

 

12.查看磁盘访问控制组信息

SQL> 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 ='DATA'

AND ug.owner_number = u.user_number;

 

 

13.查看数据分布信息

SQL> 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 ='DATA';

 

 

14.ASM 磁盘的相关视图

v$asm_disk(_stat) --查看磁盘及其状态信息
v$asm_diskgroup(_stat) --查看磁盘组及其状态信息
v$asm_operation --查看当前磁盘的操作信息
v$asm_client --返回当前连接的客户端实例信息
v$asm_file --返回asm文件的相关信息
v$asm_template --返回asm文件样本的相关信息
v$asm_alias --返回asm文件的别名信息

 

转:https://www.cnblogs.com/zhm1985/p/13279028.html

标签:group,name,dg,number,Oracle,磁盘,ASM
From: https://www.cnblogs.com/redarmy/p/17123184.html

相关文章