ORA-01031: insufficient privileges问题解决办法

opiuytr1

  在操作系统本地在oracle用户下使用sqlplus / as sysdba连接sqlplus的时候提示ORA-01031: insufficient privileges查看操作系统版本信息

1 2 3 4 5 6

[oracle@OracleBak admin]$ lsb_release -a LSB Version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch Distributor ID: CentOS Description:    CentOS release 6.6 (Final) Release:        6.6 Codename:       Final

正常本地登陆的时候应该不需要授权的，检查sqlnet.ora文件，文件路径为$ORACLE_HOME/network/admin/sqlnet.ora,

1 2 3 4 5 6 7

[oracle@OracleBak admin]$ cat sqlnet.ora # sqlnet.ora Network Configuration File: /opt/ora11/product/11.2/network/admin/sqlnet.ora # Generated by Oracle configuration tools. SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)

检查监听和tnsping均正常，查阅资料后发现sqlnet.ora中的SQLNET.AUTHENTICATION_SERVICES
1、在windows下，SQLNET.AUTHENTICATION_SERVICES必须设置为NTS才能使用OS认证；
不设置或者设置为其他任何值都不能使用OS认证。
2、在linux下，在SQLNET.AUTHENTICATION_SERVICES的值设置为ALL，或者不设置的情况下，OS验证才能成功；
将此参数修改为ALL后即可使用sqlplus / as sysdba;直接登陆了，可是使用sqlplus sys/oracle@orcl as sysdba;登陆的时候提示ORA-12641: Authentication service failed to initialize，将sqlnet.ora中的

1	SQLNET.AUTHENTICATION_SERVICES= (ALL)

这一行去掉之后再使用用户名和密码登陆可以正常登陆了
sqlnet.ora文件内容

1 2 3 4 5 6

# sqlnet.ora Network Configuration File: /opt/ora11/product/11.2/network/admin/sqlnet.ora # Generated by Oracle configuration tools. #SQLNET.AUTHENTICATION_SERVICES= (ALL) NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)

tnsnames.ora

1 2 3 4 5 6 7 8 9 10 11 12 13

# tnsnames.ora Network Configuration File: /opt/ora11/product/11.2/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = OracleBak)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = orcl)     )   )

listener.ora

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

# listener.ora Network Configuration File: /opt/ora11/product/11.2/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST =   (SID_DESC =   (GLOBAL_DBNAME = orcl)   (SID_NAME = orcl)   ) ) ORCL =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.242)(PORT = 1521))     )   ) ADR_BASE_GNNT = /opt/ora11