asm添加数据文件时出现ORA-01157错误，文件大小为SIZE -8192！

背向天堂

数据库版本：10.2.0.4.0
操作系统版本：HP-UX B.11.23
单机

前段时间安全检查，修改了sqlnet.ora文件，增加了sysdba不能空口令登录的配置：
sqlnet.REMOTE_LOGIN_PASSWORDFILE=NONE
sqlnet.authentication_services=NONE

今天在添加数据文件的时候出现权限不足的错误，按以往的经验，屏蔽sqlnet.ora里面的配置就能正常操作。

但将sqlnet.ora临时重命名后，执行添加操作仍然报错，错误日志如下：

Wed Nov  5 10:38:34 2014
Unexpected communication failure with ASM instance:
ORA-01031: insufficient privileges
Wed Nov  5 10:38:35 2014
Unexpected communication failure with ASM instance:
ORA-01031: insufficient privileges
Wed Nov  5 10:38:35 2014
Errors in file /oracle/app/oracle/admin/radb/bdump/radb_dbw0_17014.trc:
ORA-01157: cannot identify/lock data file 208 - see DBWR trace file
ORA-01110: data file 208: '+DATADG/radb/tempfile/temp2.366.862828711'
ORA-17503: ksfdopn:2 Failed to open file +DATADG/radb/tempfile/temp2.366.862828711
ORA-01031: insufficient privileges
Wed Nov  5 10:38:35 2014
ORA-1157 signalled during:  alter tablespace temp2 add tempfile size 30g...

通过dbms_metadata包检查该表空间的ddl属性，发现几次失败操作后的数据文件都是 -8192

  CREATE TEMPORARY TABLESPACE "TEMP2" TEMPFILE
  '+DATADG/radb/tempfile/temp2.295.669979099' SIZE 21474836480,
  '+DATADG/radb/tempfile/temp2.296.669979327' SIZE 21474836480,
  '+DATADG/radb/tempfile/temp2.302.716382741' SIZE 21474836480,
  '+DATADG/radb/tempfile/temp2.359.822133641' SIZE 32212254720,
  '+DATADG/radb/tempfile/temp2.368.862828817' SIZE -8192,
  '+DATADG/radb/tempfile/temp2.364.862827905' SIZE -8192,
  '+DATADG/radb/tempfile/temp2.365.862828329' SIZE -8192,
  '+DATADG/radb/tempfile/temp2.366.862828711' SIZE -8192,
  '+DATADG/radb/tempfile/temp2.367.862828811' SIZE -8192,
  '+DATADG/radb/tempfile/temp2.363.862827861' SIZE -8192
  TABLESPACE GROUP TEMP_GRP
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

下面是asm磁盘的一些属性

SQL> show parameter asm_disk;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string      DATADG
asm_diskstring                       string      /dev/rdsk/c15t0d[1,2,3], /dev/
                                                 rdsk/c16t0d2, /dev/rdsk/c20t*

SQL> select name, path from v$asm_disk;

NAME                           PATH
------------------------------ --------------------------------------------------------------------------------
DATADG_0000                    /dev/rdsk/c15t0d1
DATADG_0001                    /dev/rdsk/c15t0d2
DATADG_0002                    /dev/rdsk/c15t0d3
DATADG_0003                    /dev/rdsk/c16t0d2
DATADG_0005                    /dev/rdsk/c20t0d6
DATADG_0006                    /dev/rdsk/c20t0d7
DATADG_0007                    /dev/rdsk/c20t1d0
DATADG_0008                    /dev/rdsk/c20t1d1
DATADG_0004                    /dev/rdsk/c20t0d1

crwxrwxrwx   1 oracle     oinstall   188 0x0f0100 Nov  5 11:58 /dev/rdsk/c15t0d1
crwxrwxrwx   1 oracle     oinstall   188 0x0f0200 Nov  5 11:40 /dev/rdsk/c15t0d2
crwxrwxrwx   1 oracle     oinstall   188 0x0f0300 Sep 17  2013 /dev/rdsk/c15t0d3
crwxrwxrwx   1 oracle     oinstall   188 0x100200 Nov  5 10:25 /dev/rdsk/c16t0d2
crw-r-----   1 oracle     oinstall   188 0x140600 Nov  5 11:08 /dev/rdsk/c20t0d6
crw-r-----   1 oracle     oinstall   188 0x140700 Nov  5 11:01 /dev/rdsk/c20t0d7
crw-r-----   1 oracle     oinstall   188 0x141000 Nov  5 11:51 /dev/rdsk/c20t1d0
crw-r-----   1 oracle     oinstall   188 0x141100 Nov  5 11:51 /dev/rdsk/c20t1d1
crw-r-----   1 oracle     oinstall   188 0x140100 Nov  5 10:25 /dev/rdsk/c20t0d1

SQL> select a.MOUNT_STATUS, a.HEADER_STATUS, a.MODE_STATUS, a.STATE, a.TOTAL_MB, a.FREE_MB, a.NAME from v$asm_disk_stat a;

MOUNT_S HEADER_STATU MODE_ST STATE      TOTAL_MB    FREE_MB NAME
------- ------------ ------- -------- ---------- ---------- ------------------------------
CACHED  MEMBER       ONLINE  NORMAL       204800      90063 DATADG_0000
CACHED  MEMBER       ONLINE  NORMAL       204800      90054 DATADG_0001
CACHED  MEMBER       ONLINE  NORMAL       204800      90064 DATADG_0002
CACHED  MEMBER       ONLINE  NORMAL       450560     198150 DATADG_0003
CACHED  MEMBER       ONLINE  NORMAL       716800     315212 DATADG_0005
CACHED  MEMBER       ONLINE  NORMAL       716800     315212 DATADG_0006
CACHED  MEMBER       ONLINE  NORMAL       716800     315203 DATADG_0007
CACHED  MEMBER       ONLINE  NORMAL       716800     315207 DATADG_0008
CACHED  MEMBER       ONLINE  NORMAL       716800     315203 DATADG_0004

收集ASM诊断信息结果信息，在附件中。

谢谢。
asmdebug.txt (42.95 KB, 下载次数: 1039)

2014-11-5 15:44 上传

点击文件名下载附件
asmdebug

rdbmsdebug.txt (23.98 KB, 下载次数: 1089)

2014-11-5 15:44 上传

点击文件名下载附件
rdbmsdebug

radb_dbw0_17014.zip (712 Bytes, 下载次数: 1311)

2014-11-5 15:51 上传

点击文件名下载附件
dbw trc

sunguo40

为什么是非归档呢 存储不够？

背向天堂

感谢刘大，这套库是单节点的。


$ id oracle
uid=200(oracle) gid=200(oinstall)

$ ls  -l $ORACLE_HOME/bin/oracle
-rwsr-s--x   1 oracle     oinstall   284460824 Nov  1  2008 /oracle/app/oracle/product/10.2.0/db_2/bin/oracle

oracle只有一个组，很少维护，去年出过一次类似的问题，屏蔽sqlnet.ora里面的设置就可以了。

但是这次修改以后，仍然有问题。而且库是非归档的，目前不敢重启，准备让应用的人备份以后在做重启操作。

Maclean Liu(刘相兵

需要的信息

id oracle
ls  -l $ORACLE_HOME/bin/oracle
ls -l $CRS_HOME/bin/oracle

Maclean Liu(刘相兵

odm finding:

2 node RAC - 11107 - Linux x86-64 - ASMLIB

Archive logs getting hung and unable to logging into the database.

alert_POQAF0P2.log:
-------------------------------------------------------------------------------
Thu Jul 19 08:41:33 2012
Errors in file
/usr/app/oracle/diag/rdbms/poqaf0p/POQAF0P2/trace/POQAF0P2_arc2_20569.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '+POQAF0PD/data02/redo04b.log'
ORA-17503: ksfdopn:2 Failed to open file +POQAF0PD/data02/redo04b.log
ORA-01031: insufficient privileges
------------------------------------------------------------------------------



CAUSE

Improper Primary Group for the db_home owner

From all nodes:
* 'id' output for the <db_home> owner is: uid=60011(oracle) gid=60011(dba) groups=501(oinstall),60011(dba)
* The owner:group and file permissions for asm_home and db_home, is oracle:dba and 6751:
  -rwsr-s--x 1 oracle dba 196655349 Jan 21 16:13 /usr/app/oracle/product/asm/bin/oracle
  -rwsr-s--x 1 oracle dba 196658908 Jan 21 16:02 /usr/app/oracle/product/11.1.0/bin/oracle
* The ASM disks are owned by oracle:oinstall and all have 660 permissions
* The <db_home>/rdbms/lib/config.c shows proper values for SS_DBA_GRP (dba) and SS_ASM_GRP (oinstall):
  #define SS_DBA_GRP "dba"
  #define SS_OPER_GRP "oinstall"
  #define SS_ASM_GRP "oinstall"
* There is no sqlnet.ora file from any of the <asm_home>|<db_home>/network/admin directories (here we are looking if the parameter SQLNET.AUTHENTICATION_SERVICES is set to NONE)

SOLUTION

Change the Primary Group for the <db_home> owner from 'dba' to 'oinstall':

For example:

# id oracle
uid=60011(oracle) gid=60011(dba) groups=501(oinstall),60011(dba)

# /usr/sbin/usermod -u 60011 -g 501 -G 60011 oracle

# id oracle
uid=60011(oracle) gid=501(oinstall) groups=501(oinstall),60011(dba)