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, /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诊断信息结果信息，在附件中。
谢谢。
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)
需要的信息
id oracle
ls -l $ORACLE_HOME/bin/oracle
ls -l $CRS_HOME/bin/oracle
感谢刘大，这套库是单节点的。
$ 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里面的设置就可以了。
但是这次修改以后，仍然有问题。而且库是非归档的，目前不敢重启，准备让应用的人备份以后在做重启操作。 为什么是非归档呢 存储不够？
页:
[1]