ORA-01031: insufficient privileges

wonder

oracle 11.2.0.1 单实例ASM存储，使用udev绑定磁盘

[root@szmlserver12_32 ~]# id oracle
uid=501(oracle) gid=6000(oinstall) groups=6000(oinstall),5001(asmdba),6001(dba)
[root@szmlserver12_32 ~]# id grid
uid=500(grid) gid=6000(oinstall) groups=6000(oinstall),5000(asmadmin),5001(asmdba),5002(asmoper)
[root@szmlserver12_32 ~]# ls /dev/asm* -l
brw-rw---- 1 grid asmadmin 8,  64 Jun 20 12:17 /dev/asm-diske
brw-rw---- 1 grid asmadmin 8,  80 Jun 20 12:17 /dev/asm-diskf
brw-rw---- 1 grid asmadmin 8,  96 Jun 20 12:16 /dev/asm-diskg
brw-rw---- 1 grid asmadmin 8, 112 Jun 20 12:16 /dev/asm-diskh
brw-rw---- 1 grid asmadmin 8, 128 Jun 20 12:16 /dev/asm-diski
brw-rw---- 1 grid asmadmin 8, 144 Jun 20 12:16 /dev/asm-diskj
brw-rw---- 1 grid asmadmin 8, 160 Jun 20 12:16 /dev/asm-diskk
brw-rw---- 1 grid asmadmin 8, 176 Jun 20 12:17 /dev/asm-diskl

现象：在DBCA创建库的时候也有ORA-01031 报错，但手动启动库，一切正常，主机重启后，ASM没自动启动，crs没启动，has正常启动，
ora.hidb.db为OFFLINE状态，下面启动也报ORA-01031 错误，但报错之后查看状态为ONLINE

[grid@szmlserver12_32 dbs]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.HI_DATA.dg ora....up.type ONLINE    ONLINE    szml...2_32
ora....ER.lsnr ora....er.type ONLINE    ONLINE    szml...2_32
ora.asm        ora.asm.type   ONLINE    ONLINE    szml...2_32
ora.cssd       ora.cssd.type  ONLINE    ONLINE    szml...2_32
ora.diskmon    ora....on.type ONLINE    ONLINE    szml...2_32
ora.hidb.db    ora....se.type OFFLINE   OFFLINE                     ---这里是OFFLINE         
[grid@szmlserver12_32 dbs]$ crs_start -all
CRS-5702: Resource 'ora.HI_DATA.dg' is already running on 'szmlserver12_32'
CRS-5702: Resource 'ora.LISTENER.lsnr' is already running on 'szmlserver12_32'
CRS-5702: Resource 'ora.asm' is already running on 'szmlserver12_32'
CRS-5702: Resource 'ora.cssd' is already running on 'szmlserver12_32'
CRS-5702: Resource 'ora.diskmon' is already running on 'szmlserver12_32'
Attempting to start `ora.hidb.db` on member `szmlserver12_32`
ORA-01031: insufficient privileges                                            --提示错误，权限不足
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
Start of `ora.hidb.db` on member `szmlserver12_32` succeeded.
CRS-0223: Resource 'ora.HI_DATA.dg' has placement error.

CRS-0223: Resource 'ora.LISTENER.lsnr' has placement error.

CRS-0223: Resource 'ora.asm' has placement error.

CRS-0223: Resource 'ora.cssd' has placement error.

CRS-0223: Resource 'ora.diskmon' has placement error.

[grid@szmlserver12_32 dbs]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.HI_DATA.dg ora....up.type ONLINE    ONLINE    szml...2_32
ora....ER.lsnr ora....er.type ONLINE    ONLINE    szml...2_32
ora.asm        ora.asm.type   ONLINE    ONLINE    szml...2_32
ora.cssd       ora.cssd.type  ONLINE    ONLINE    szml...2_32
ora.diskmon    ora....on.type ONLINE    ONLINE    szml...2_32
ora.hidb.db    ora....se.type ONLINE    ONLINE    szml...2_32      --再次查看正常

请问，是哪里设置不当导致的ORA-01031 错误？谢谢！

[ 本帖最后由 wonder 于 2012-6-20 12:17 编辑 ]

Maclean Liu(刘相兵

action plan:

srvctl  config database -d  $YOUR_DB_NAME -a

ls -l $ORACLE_HOME/dbs/orapw*


上传 $ORACLE_HOME/network/admin/sqlnet.ora

上传 $CRS_HOME/log/$HOSTNAME/racg  目录下的日志

wonder

[grid@szmlserver12_32 grid]$ srvctl config database -d hidb -a
Database unique name: hidb
Database name: hidb
Oracle home: /data/u01/app/oracle
Oracle user: grid
Spfile: +HI_DATA/hidb/spfilehidb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: HI_DATA
Services:
Database is enabled

[oracle@szmlserver12_32 dbs]$ ls -l $ORACLE_HOME/dbs/orapw*
-rw-r----- 1 oracle oinstall 1536 Jun 19 00:14 /data/u01/app/oracle/dbs/orapwhidb

gi安装在grid用户下，grid的环境变量：没有用$CRS_HOME而是用的ORACLE_HOME    --这里是不是有问题？？？
[grid@szmlserver12_32 grid]$ cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/data/g01/app
export ORACLE_HOME=/data/g01/app/grid
export ORACLE_SID=+ASM
export LANG=en
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH

ORACLE用户下没有sqlnet.ora文件：
[oracle@szmlserver12_32 admin]$ ls $ORACLE_HOME/network/admin
samples  shrept.lst  tnsnames.ora

grid用户下$ORACLE_HOME/log/szmlserver12_32/racg下的文件夹全部是空的：

[grid@szmlserver12_32 szmlserver12_32]$ cd racg/
[grid@szmlserver12_32 racg]$ ls
racgeut  racgevtf  racgmain
[grid@szmlserver12_32 racg]$ ls racgeut/
[grid@szmlserver12_32 racg]$ ls racgevtf/
[grid@szmlserver12_32 racg]$ ls racgmain/
[grid@szmlserver12_32 racg]$ pwd
/data/g01/app/grid/log/szmlserver12_32/racg

谢谢！

[ 本帖最后由 wonder 于 2012-6-20 13:05 编辑 ]

Maclean Liu(刘相兵

[grid@vrh1 ~]$ srvctl config database -d VPROD -a
Database unique name: VPROD
Database name: VPROD
Oracle home: /s01/orabase/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/VPROD/spfileVPROD.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: VPROD
Database instances: VPROD1,VPROD2
Disk Groups: DATA,BACKUPDG
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed




database resource 的 Oracle user 应当是oracle 而非grid ，如以下测试

[root@vrh1 ~]#  srvctl modify database -d VPROD -u grid


[root@vrh1 ~]# srvctl stop database -d VPROD


[root@vrh1 ~]# srvctl start database -d VPROD  
PRCR-1079 : Failed to start resource ora.vprod.db
CRS-5010: Update of configuration file "/s01/orabase/product/11.2.0/dbhome_1/srvm/admin/oratab.bak.vrh1" failed: details at "(:CLSN00013:)" in "/g01/11.2.0/grid/log/vrh1/agent/crsd/oraagent_grid/oraagent_grid.log"
ORA-01031: insufficient privileges
CRS-5017: The resource action "ora.vprod.db start" encountered the following error:
ORA-01031: insufficient privileges
. For details refer to "(:CLSN00107:)" in "/g01/11.2.0/grid/log/vrh1/agent/crsd/oraagent_grid/oraagent_grid.log".

CRS-2674: Start of 'ora.vprod.db' on 'vrh1' failed
ORA-01031: insufficient privileges
CRS-2632: There are no more servers to try to place resource 'ora.vprod.db' on that would satisfy its placement policy




srvctl modify database -d $YOUR_DB_NAME -u oracle  把 OS user修改为oracle用户

wonder

[root@szmlserver12_32 bin]# ./srvctl modify database -d hidb -u oracle

[grid@szmlserver12_32 racg]$ srvctl config database -d hidb -a
Database unique name: hidb
Database name: hidb
Oracle home: /data/u01/app/oracle
Oracle user: oracle
Spfile: +HI_DATA/hidb/spfilehidb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: HI_DATA
Services:
Database is enabled

[grid@szmlserver12_32 racg]$ srvctl start database -d dbid
PRCD-1120 : The resource for database dbid could not be found.
PRCR-1001 : Resource ora.dbid.db does not exist

不管是oracle还是grid，root启动都报PRCR-1001 : Resource ora.dbid.db does not exist   ，谢谢！

Maclean Liu(刘相兵

你的数据库叫 hidb  不是 dbid!!!

srvctl start database -d dbid
PRCD-1120 : The resource for database dbid could not be found.
PRCR-1001 : Resource ora.dbid.db does not exist


ora.hidb.db  

wonder

使用oracle执行：
[oracle@szmlserver12_32 data]$ srvctl start database -d hidb
grid查看：
[grid@szmlserver12_32 racg]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.HI_DATA.dg ora....up.type ONLINE    ONLINE    szml...2_32
ora....ER.lsnr ora....er.type ONLINE    ONLINE    szml...2_32
ora.asm        ora.asm.type   ONLINE    ONLINE    szml...2_32
ora.cssd       ora.cssd.type  ONLINE    ONLINE    szml...2_32
ora.diskmon    ora....on.type ONLINE    ONLINE    szml...2_32
ora.hidb.db    ora....se.type ONLINE    ONLINE    szml...2_32
用oracle停止报错，但实际已经OFFLINE：
[oracle@szmlserver12_32 data]$ srvctl stop database -d hidb
PRCD-1124 : Failed to stop database hidb and its services
PRCR-1065 : Failed to stop resource (((((NAME STARTS_WITH ora.hidb.) && (NAME ENDS_WITH .svc)) && (TYPE == ora.service.type)) && ((STATE != OFFLINE) || (TARGET != OFFLINE))) || (((NAME == ora.hidb.db) && (TYPE == ora.database.type)) && (STATE != OFFLINE)))
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
CRS-2675: Stop of 'ora.hidb.db' on 'szmlserver12_32' failed
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
CRS-2675: Stop of 'ora.hidb.db' on 'szmlserver12_32' failed

用grid 查看：
[grid@szmlserver12_32 racg]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.HI_DATA.dg ora....up.type ONLINE    ONLINE    szml...2_32
ora....ER.lsnr ora....er.type ONLINE    ONLINE    szml...2_32
ora.asm        ora.asm.type   ONLINE    ONLINE    szml...2_32
ora.cssd       ora.cssd.type  ONLINE    ONLINE    szml...2_32
ora.diskmon    ora....on.type ONLINE    ONLINE    szml...2_32
ora.hidb.db    ora....se.type OFFLINE   ONLINE    szml...2_32

使用grid用户启动，停止都是一样的报错，请问是什么原因，谢谢！

Maclean Liu(刘相兵

action plan:

srvctl config database -d hidb -a

su -  root
srvctl start database -d hidb

wonder

[grid@szmlserver12_32 racg]$ pwd
/data/g01/app/grid/log/szmlserver12_32/racg
[grid@szmlserver12_32 racg]$ srvctl config database -d hidb -a
Database unique name: hidb
Database name: hidb
Oracle home: /data/u01/app/oracle
Oracle user: oracle
Spfile: +HI_DATA/hidb/spfilehidb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: HI_DATA
Services:
Database is enabled

[root@szmlserver12_32 bin]# ./srvctl stop database -d hidb
PRCC-1016 : hidb was already stopped
[root@szmlserver12_32 bin]# ./srvctl start database -d hidb
[root@szmlserver12_32 bin]# ./srvctl stop database -d hidb
PRCD-1124 : Failed to stop database hidb and its services
PRCR-1065 : Failed to stop resource (((((NAME STARTS_WITH ora.hidb.) && (NAME ENDS_WITH .svc)) && (TYPE == ora.service.type)) && ((STATE != OFFLINE) || (TARGET != OFFLINE))) || (((NAME == ora.hidb.db) && (TYPE == ora.database.type)) && (STATE != OFFLINE)))
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
CRS-2675: Stop of 'ora.hidb.db' on 'szmlserver12_32' failed
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
CRS-2675: Stop of 'ora.hidb.db' on 'szmlserver12_32' failed

root有时候停止不报错，自己启动后，再停止报错

Maclean Liu(刘相兵

action plan:

id  oracle
id  grid

crs_stat -ls  
crsctl stat res ora.hidb.db  -p

su  - oracle

ls -ltr $ORACLE_HOME/bin/oracle

wonder

[root@szmlserver12_32 bin]# id oracle
uid=501(oracle) gid=6000(oinstall) groups=6000(oinstall),5001(asmdba),6001(dba)
[root@szmlserver12_32 bin]# id grid
uid=500(grid) gid=6000(oinstall) groups=6000(oinstall),5000(asmadmin),5001(asmdba),5002(asmoper)
[root@szmlserver12_32 bin]# ./crs_stat -ls
Name           Owner          Primary PrivGrp          Permission  
-----------------------------------------------------------------
ora.HI_DATA.dg grid           oinstall                 rwxrwxr--
ora....ER.lsnr grid           oinstall                 rwxrwxr--
ora.asm        grid           oinstall                 rwxrwxr--
ora.cssd       grid           oinstall                 rwxrwxr--
ora.diskmon    grid           oinstall                 rwxrwxr--
ora.hidb.db    oracle         asmdba                   --x--xr--
[root@szmlserver12_32 bin]# ./crsctl stat res ora.hidb.db -p
NAME=ora.hidb.db
TYPE=ora.database.type
ACL=owner:oracle:--x,pgrp:asmdba:--x,other::r--,group:oinstall:r-x,user:oracle:rwx
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=1
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=1
CHECK_TIMEOUT=600
CLUSTER_DATABASE=false
DB_UNIQUE_NAME=hidb
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database) PROPERTY(DB_UNIQUE_NAME= CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .)) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle Database resource
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=60
FAILURE_THRESHOLD=1
GEN_AUDIT_FILE_DEST=
GEN_USR_ORA_INST_NAME=
HOSTING_MEMBERS=
INSTANCE_FAILOVER=0
LOAD=1
LOGGING_LEVEL=1
MANAGEMENT_POLICY=AUTOMATIC
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
ORACLE_HOME=/data/u01/app/oracle
PLACEMENT=balanced
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=2
ROLE=PRIMARY
SCRIPT_TIMEOUT=60
SERVER_POOLS=
SPFILE=+HI_DATA/hidb/spfilehidb.ora
START_DEPENDENCIES=hard(ora.HI_DATA.dg) weak(type:ora.listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.HI_DATA.dg)
START_TIMEOUT=600
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.HI_DATA.dg)
STOP_TIMEOUT=600
UPTIME_THRESHOLD=1h
USR_ORA_DB_NAME=hidb
USR_ORA_DOMAIN=
USR_ORA_ENV=
USR_ORA_FLAGS=
USR_ORA_INST_NAME=hidb
USR_ORA_OPEN_MODE=open
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.1.0

[root@szmlserver12_32 bin]# su - oracle
[oracle@szmlserver12_32 ~]$ ls -ltr $ORACLE_HOME/bin/oracle
-r-sr-s--x 1 oracle asmadmin 210824714 Jun 18 23:59 /data/u01/app/oracle/bin/oracle

Maclean Liu(刘相兵

ora.hidb.db    oracle         asmdba                   --x--xr--

ora.hidb.db resource的权限存在问题

建议

使用 srvctl remove database -d hidb  删除DB资源后

su - oracle

srvctl add database            切换到oracle用户 重新添加该 DB资源

wonder

[root@szmlserver12_32 bin]# ./srvctl remove database -d hidb
Remove the database hidb? (y/[n]) y

[grid@szmlserver12_32 racg]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.HI_DATA.dg ora....up.type ONLINE    ONLINE    szml...2_32
ora....ER.lsnr ora....er.type ONLINE    ONLINE    szml...2_32
ora.asm        ora.asm.type   ONLINE    ONLINE    szml...2_32
ora.cssd       ora.cssd.type  ONLINE    ONLINE    szml...2_32
ora.diskmon    ora....on.type ONLINE    ONLINE    szml...2_32

[oracle@szmlserver12_32 data]$ srvctl add database -d hidb -o /data/u01/app/oracle -m szmlserver12_32.ea.com -p +HI_DATA/hidb/spfilehidb.ora

[grid@szmlserver12_32 racg]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.HI_DATA.dg ora....up.type ONLINE    ONLINE    szml...2_32
ora....ER.lsnr ora....er.type ONLINE    ONLINE    szml...2_32
ora.asm        ora.asm.type   ONLINE    ONLINE    szml...2_32
ora.cssd       ora.cssd.type  ONLINE    ONLINE    szml...2_32
ora.diskmon    ora....on.type ONLINE    ONLINE    szml...2_32
ora.hidb.db    ora....se.type OFFLINE   OFFLINE

[root@szmlserver12_32 bin]# ./crs_stat -ls
Name           Owner          Primary PrivGrp          Permission  
-----------------------------------------------------------------
ora.HI_DATA.dg grid           oinstall                 rwxrwxr--
ora....ER.lsnr grid           oinstall                 rwxrwxr--
ora.asm        grid           oinstall                 rwxrwxr--
ora.cssd       grid           oinstall                 rwxrwxr--
ora.diskmon    grid           oinstall                 rwxrwxr--
ora.hidb.db    grid           asmdba                   --x--xr--

又回到了owner为grid，权限为 --x--xr--  

[grid@szmlserver12_32 racg]$ crs_start -all
CRS-5702: Resource 'ora.HI_DATA.dg' is already running on 'szmlserver12_32'
CRS-5702: Resource 'ora.LISTENER.lsnr' is already running on 'szmlserver12_32'
CRS-5702: Resource 'ora.asm' is already running on 'szmlserver12_32'
CRS-5702: Resource 'ora.cssd' is already running on 'szmlserver12_32'
CRS-5702: Resource 'ora.diskmon' is already running on 'szmlserver12_32'
Attempting to start `ora.hidb.db` on member `szmlserver12_32`
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
Start of `ora.hidb.db` on member `szmlserver12_32` succeeded.
CRS-0223: Resource 'ora.HI_DATA.dg' has placement error.

CRS-0223: Resource 'ora.LISTENER.lsnr' has placement error.

CRS-0223: Resource 'ora.asm' has placement error.

CRS-0223: Resource 'ora.cssd' has placement error.

CRS-0223: Resource 'ora.diskmon' has placement error.

和刚开始一样的报错了？？？

Maclean Liu(刘相兵

action plan:


srvctl config database -d   XX   -a

crsctl setperm resource ora.hidb.db -o user:oracle:rwx

crs_stat -ls

wonder

[root@szmlserver12_32 bin]# ./srvctl config database -d hidb -a
Database unique name: hidb
Database name:
Oracle home: /data/u01/app/oracle
Oracle user: oracle
Spfile: +HI_DATA/hidb/spfilehidb.ora
Domain: szmlserver12_32.easou.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups:
Services:
Database is enabled
[root@szmlserver12_32 bin]# ./crsctl setperm resource ora.hidb.db -o user:oracle:rwx
CRS-0247:  User name or group name is invalid
CRS-4000: Command Setperm failed, or completed with errors.

运行以上命令报错

wonder

昨天晚上又重新安装了一遍GI,ORACLE软件，库，只建立了两个组oinstall，dba，srvctl stop/start database -d $YOUR_DB_NAME 不会报上面的权限错误，重启主机后，手动启动asm和oracle，告警日志里都没错误，但现在的问题是 ：

1）Oracle user: grid   并不是oracle
[grid@szmlserver12_32 ~]$ srvctl config database -d hidb
Database unique name: hidb
Database name: hidb
Oracle home: /u01/app/oracle
Oracle user: grid
Spfile: +HIDATA/hidb/spfilehidb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: HIDATA
Services:

2）ora.hidb.db    grid           dba                      --x--xr--
[grid@szmlserver12_32 ~]$ crs_stat -ls
Name           Owner          Primary PrivGrp          Permission  
-----------------------------------------------------------------
ora.HIDATA.dg  grid           oinstall                 rwxrwxr--
ora....ER.lsnr grid           oinstall                 rwxrwxr--
ora.asm        grid           oinstall                 rwxrwxr--
ora.cssd       grid           oinstall                 rwxrwxr--
ora.diskmon    grid           oinstall                 rwxrwxr--
ora.hidb.db    grid           dba                      --x--xr--

以上两点和maclean的观点相反，请大家说说看是不哪里还有隐藏的问题，谢谢！