Oracle 11g ASM添加磁盘时遇到ORA-15032,ORA-15031,ORA-15014错误及解决办法

最新推荐文章于 2024-08-14 17:40:30 发布

原创最新推荐文章于 2024-08-14 17:40:30 发布 · 3.1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle RAC ASM

数据库专栏收录该内容

28 篇文章

订阅专栏

一、环境

redhat 6.10 + Oracle 11.2.0.4 +ASM（asmlib）

在搭建RAC时，使用了3块3G的磁盘作为DATA磁盘，磁盘的冗余级别为normal；后来在测试其他内容时，发现ASM磁盘空间不足，因此就想给ASM磁盘扩容。

步骤：

1.1、在存储上划分两块3G的磁盘映射给oracle服务器

1.2、在oracle服务器上扫描并添加ASM磁盘

[root@or11g2 bin]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
VOL4
VOL5
VOL6
VOL7
VOL8
VOL9
[root@or11g2 bin]# 
[root@or11g2 bin]# 
[root@or11g2 bin]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@or11g2 bin]#

将后来映射过来的磁盘创建为ASM磁盘

[root@or11g1 bin]# /etc/init.d/oracleasm createdisk VOL10 /dev/sdk1
Marking disk "VOL10" as an ASM disk:                       [  OK  ]
[root@or11g1 bin]# 
[root@or11g1 bin]# /etc/init.d/oracleasm createdisk DATA01 /dev/sdl1
Marking disk "DATA01" as an ASM disk:                      [  OK  ]
[root@or11g1 bin]#

在节点2上执行扫描磁盘

[root@or11g2 bin]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@or11g2 bin]#

二、往ASM磁盘组中添加磁盘遇到报错

[grid@or11g1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 15 15:16:52 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> set linesize 180
SQL> select group_number,name, TOTAL_MB, REQUIRED_MIRROR_FREE_MB, FREE_MB , USABLE_FILE_MB,ALLOCATION_UNIT_SIZE from V$asm_diskgroup;

GROUP_NUMBER NAME			      TOTAL_MB REQUIRED_MIRROR_FREE_MB	  FREE_MB USABLE_FILE_MB ALLOCATION_UNIT_SIZE
------------ ------------------------------ ---------- ----------------------- ---------- -------------- --------------------
	   1 DATA				  9207			  3069	     4959	     945	      1048576
	   2 FRA				  6138			     0	     5541	    5541	      1048576
	   3 OCRVOTE				  3069			  1023	     2143	     560	      1048576

SQL> alter diskgroup data add disk 'ORA_DATA1' rebalance power 10;
alter diskgroup data add disk 'ORA_DATA1' rebalance power 10
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group


SQL>

这里遇到第一次报错，是因为权限问题。（一直都是用dba角色登陆，忘了11g的ASM管理是sysasm，也就是说在11g中对磁盘拥有最高权限的的是sysasm而非sysdba）

[grid@or11g1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 15 15:19:26 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> 
SQL> select group_number,name, TOTAL_MB, REQUIRED_MIRROR_FREE_MB, FREE_MB , USABLE_FILE_MB,ALLOCATION_UNIT_SIZE from V$asm_diskgroup;

GROUP_NUMBER NAME			      TOTAL_MB REQUIRED_MIRROR_FREE_MB
------------ ------------------------------ ---------- -----------------------
   FREE_MB USABLE_FILE_MB ALLOCATION_UNIT_SIZE
---------- -------------- --------------------
	   1 DATA				  9207			  3069
      4959	      945	       1048576

	   2 FRA				  6138			     0
      5541	     5541	       1048576

	   3 OCRVOTE				  3069			  1023
      2143	      560	       1048576


SQL> set linesize 180
SQL> /

GROUP_NUMBER NAME			      TOTAL_MB REQUIRED_MIRROR_FREE_MB	  FREE_MB USABLE_FILE_MB ALLOCATION_UNIT_SIZE
------------ ------------------------------ ---------- ----------------------- ---------- -------------- --------------------
	   1 DATA				  9207			  3069	     4959	     945	      1048576
	   2 FRA				  6138			     0	     5541	    5541	      1048576
	   3 OCRVOTE				  3069			  1023	     2143	     560	      1048576

SQL>

添加磁盘时，遇到报错：

SQL> alter diskgroup data add disk 'DATA01' rebalance power 10;
alter diskgroup data add disk 'DATA01' rebalance power 10
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15031: disk specification 'DATA01' matches no disks


SQL>

根据报错，去查看磁盘权限问题，发现一切正常

[root@or11g1 bin]# ll /dev/oracleasm/disks/
total 0
brw-rw---- 1 grid asmadmin 8, 177 Apr 15 16:37 DATA01
brw-rw---- 1 grid asmadmin 8,  49 Apr 15 16:37 VOL1
brw-rw---- 1 grid asmadmin 8, 161 Apr 15 16:37 VOL10
brw-rw---- 1 grid asmadmin 8,  33 Apr 15 16:37 VOL2
brw-rw---- 1 grid asmadmin 8,  17 Apr 15 16:37 VOL3
brw-rw---- 1 grid asmadmin 8, 113 Apr 15 16:37 VOL4
brw-rw---- 1 grid asmadmin 8, 129 Apr 15 16:37 VOL5
brw-rw---- 1 grid asmadmin 8, 145 Apr 15 15:38 VOL6
brw-rw---- 1 grid asmadmin 8,  65 Apr 15 16:37 VOL7
brw-rw---- 1 grid asmadmin 8,  81 Apr 15 16:37 VOL8
brw-rw---- 1 grid asmadmin 8,  97 Apr 15 16:37 VOL9
[root@or11g1 bin]#

后来，经过查询资料发现可能是由于asm_diskstring参数的问题

SQL> show parameter asm_diskstring;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring			     string
SQL>

尝试修改该参数

SQL> alter system set asm_diskstring='/dev/oracleasm/disks/*'  scope=spfile;

System altered.

SQL>

重启集群后

SQL> show parameter asm_diskstring;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring			     string	 /dev/oracleasm/disks/*
SQL>

再次添加，依然报错

SQL> alter diskgroup data add disk 'VOL10' rebalance power 10;
alter diskgroup data add disk 'VOL10' rebalance power 10
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15031: disk specification 'VOL10' matches no disks


SQL>

这个报错，是因为磁盘的路径没写全

SQL> alter diskgroup data add disk '/dev/oracleasm/disks/VOL10' rebalance power 10;


Diskgroup altered.

SQL> SQL> 
SQL> alter diskgroup data add disk '/dev/oracleasm/disks/DATA01' rebalance power 10;

Diskgroup altered.

SQL>

路径写全后就好了。

SQL> set linesize 120
SQL> select GROUP_NUMBER,STATE,TOTAL_MB,NAME,FAILGROUP from v$asm_disk;

GROUP_NUMBER STATE	TOTAL_MB NAME				FAILGROUP
------------ -------- ---------- ------------------------------ ------------------------------
	   1 NORMAL	    3069 DATA_0004			DATA_0004
	   1 NORMAL	    3069 DATA_0003			DATA_0003
	   2 NORMAL	    2046 VOL5				VOL5
	   2 NORMAL	    2046 VOL6				VOL6
	   2 NORMAL	    2046 VOL4				VOL4
	   1 NORMAL	    3069 VOL9				VOL9
	   3 NORMAL	    1023 VOL1				VOL1
	   1 NORMAL	    3069 VOL8				VOL8
	   1 NORMAL	    3069 VOL7				VOL7
	   3 NORMAL	    1023 VOL2				VOL2
	   3 NORMAL	    1023 VOL3				VOL3

11 rows selected.

SQL> select GROUP_NUMBER,STATE,TOTAL_MB,NAME,FAILGROUP,FREE_MB from v$asm_disk;

GROUP_NUMBER STATE	TOTAL_MB NAME				FAILGROUP			  FREE_MB
------------ -------- ---------- ------------------------------ ------------------------------ ----------
	   1 NORMAL	    3069 DATA_0004			DATA_0004			     2213
	   1 NORMAL	    3069 DATA_0003			DATA_0003			     2202
	   2 NORMAL	    2046 VOL5				VOL5				     1848
	   2 NORMAL	    2046 VOL6				VOL6				     1849
	   2 NORMAL	    2046 VOL4				VOL4				     1844
	   1 NORMAL	    3069 VOL9				VOL9				     2204
	   3 NORMAL	    1023 VOL1				VOL1				      715
	   1 NORMAL	    3069 VOL8				VOL8				     2207
	   1 NORMAL	    3069 VOL7				VOL7				     2205
	   3 NORMAL	    1023 VOL2				VOL2				      713
	   3 NORMAL	    1023 VOL3				VOL3				      715

11 rows selected.

SQL> select name,total_mb,free_mb from v$asm_diskgroup;

NAME				 TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
DATA				    15345      11031
FRA				     6138	5541
OCRVOTE 			     3069	2143

SQL>