系统环境：
操作系统： AIX 5.3.8
Oracle： Oracle 10gR2
故障现象：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> create diskgroup datafile
2
normal redundancy
failgroup fg1 disk
'/dev/rlv_data1'
3
4
failgroup fg2 disk
'/dev/rlv_data2'
;
create diskgroup datafile
*
ERROR
at
line
1
:
ORA
-15018
: diskgroup cannot be created
ORA
-15033
: disk
'/dev/rlv_data2'
belongs to diskgroup
"RECOVER"
ORA
-15033
: disk
'/dev/rlv_data1'
belongs to diskgroup
"DATA"
SQL> select group_number,name,state
from
v$asm_diskgroup;
GROUP_NUMBER NAME STATE
------------ ------------------------------ -----------
0
REDO DISMOUNTED
0
ARCH DISMOUNTED
0
DATA DISMOUNTED
0
RECOVER DISMOUNTED
|
故障原因：
|
1
2
3
4
5
6
7
8
9
|
[oracle@aix213 dbs]$lsvg -l asmvg
asmvg:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
lv_data1 jfs
64
64
1
open/syncd N/A
lv_data2 jfs
64
64
1
open/syncd N/A
asmredo2 jfs
32
32
1
open/syncd N/A
[oracle@aix213 dbs]$lspv
hdisk1 00c6d0ef0395eef2 rootvg active
hdisk0 0006816a72287441 asmvg active
|
应该是在AIX的ODM中，rlv_data1和rlv_data2，已经属于另外的磁盘组。
解决方法：
1）清理raw头部信息
|
1
2
3
4
5
6
|
[root@aix213 /]#dd
if
=/dev/zero of=/dev/rlv_data1 bs=8k count=
256
256
+
0
records
in
256
+
0
records out
[root@aix213 /]#dd
if
=/dev/zero of=/dev/rlv_data2 bs=8k count=
256
256
+
0
records
in
256
+
0
records out
|
2）重新建立ASM DISKGROUP
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
[oracle@aix213 dbs]$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 24 11:44:29 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name ,state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
REDO DISMOUNTED
ARCH DISMOUNTED
清理raw头部信息后data和recover diskgroup自动被删除 ！
SQL> create diskgroup datafile
2 normal redundancy
3 failgroup fg1 disk
'/dev/rlv_data1'
4 failgroup fg2 disk
'/dev/rlv_data2'
;
Diskgroup created.
磁盘组创建成功 ！
|
3）验证
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> select name ,state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
REDO DISMOUNTED
ARCH DISMOUNTED
DATAFILE MOUNTED
SQL> alter diskgroup redo mount;
Diskgroup altered.
SQL> alter diskgroup arch mount;
Diskgroup altered.
SQL> select name ,state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
REDO MOUNTED
ARCH MOUNTED
DATAFILE MOUNTED
|
@至此，问题被解决 ！
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1529597，如需转载请自行联系原作者
![【数据库】解决 oracle: SQL 错误 [900] [42000]: ORA-00900: 无效 SQL 语句](https://ucc.alicdn.com/pic/developer-ecology/f790f8e9ea28458da1cf5cbb69a7d12a.jpg?x-oss-process=image/resize,h_160,m_lfit)
