结论：
1,remote_login_passwordfile此参数针对通过远程登陆数据库是否使用密码文件
2,远程登陆的含义即采用tnsname.ora登陆数据库
3,如果你直接在数据库服务器上通过sqlplus连接数据库，不使用密码文件
4,如果直接在数据库服务器上通过sqlplus连接数据库，如果ORACLE用户没有配置DBA操作系统组，也无法登陆数据库
5,如果你远程登陆数据库，remote_login_passwordfile必须配置为exclusive或者shared,否则无法登陆数据库
6,如果remote_login_passwordfile配置为exclusive或shared,而密码文件不存在，等同于值为none
测试过程
1,数据库名称
-bash-3.2$ env|grep SID
ORACLE_SID=ora11204
2,数据库版本
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
3，数据库所用的密码文件
-bash-3.2$ ls -l orapwora11204
-rw-r----- 1 oracle11204 oinstall 1536 Mar 25 2014 orapwora11204
-bash-3.2$
4，与密码文件相关的参数
SQL> show parameter remote_login
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
5,参数remote_login_passwordfile的含义
控制数据库是否检查密码文件，此参数可以取值共计3个，分别如下：
- shared 多个数据库可以共用密码文件，密码文件里面的内容包括SYS及非SYS用户
- exclusive 仅一个数据库可以使用密码文件，密码文件里面的内容包括SYS及非SYS用户；这是默认值
- none 数据库直接忽略密码文件，因此特权用户需要要经过操作系统的认证
注意：
- 如果值为exclusive或shared,但密码文件又不存在，此参数值相当于配置值为none
- 如果把值从none改为exclusive或shared,确保密码文件一定要和目录密码保持同步或一致
6，先对密码文件进行备份
-bash-3.2$ ls -l orapwora*
-rw-r----- 1 oracle11204 oinstall 1536 Mar 25 2014 orapwora11204
-bash-3.2$ cp orapwora11204 orapwora11204.origbak
-bash-3.2$
7,移走密码文件
-bash-3.2$ cp orapwora11204 orapwora11204.origbak
-bash-3.2$ mv orapwora11204 orapwora11204_mv
8,移走密码文件，仍可以正常登陆,此时相当于值配置为none
-bash-3.2$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 00:03:57 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
SQL>
9, 查看当前操作系统用户的属组情况,主操作系统组为oinstall,次级组为dba
-bash-3.2$ id
uid=502(oracle11204) gid=500(oinstall) groups=500(oinstall),501(dba)
-bash-3.2$ more /etc/passwd|grep oracle11204
oracle11204:x:502:500::/11204rdbms:/bin/bash
-bash-3.2$ more /etc/group|grep dba
dba:x:501:oracle,oracle11204,grid,ora10g
-bash-3.2$ more /etc/group|grep oinstall
oinstall:x:500:grid
-bash-3.2$
10,我们让oracle11204用户不隶属于组oinstall
[root@seconary ~]# usermod -g root oracle11204
[root@seconary ~]# id oracle11204
uid=502(oracle11204) gid=0(root) groups=0(root),501(dba)
[root@seconary ~]#
12,再次尝试登陆数据库,仍然可以登陆
[root@seconary ~]# su - oracle11204
-bash-3.2$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 00:27:07 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
SQL>
13,把次级操作系统组DBA也移除
[root@seconary ~]# usermod -G root oracle11204
[root@seconary ~]# id oracle11204
uid=502(oracle11204) gid=0(root) groups=0(root)
[root@seconary ~]# su - oracle11204
-bash-3.2$
14,移除次级操作系统组DBA后不能登陆数据库
-bash-3.2$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 00:49:49 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
-bash-3.2$ sqlplus 'sys/system as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 00:50:08 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
15，还原密码文件
-bash-3.2$ cp orapwora11204_mv orapwora11204
-bash-3.2$
16,必须使用正确的密码方可登陆数据库
-bash-3.2$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 00:51:26 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
-bash-3.2$
-bash-3.2$ sqlplus 'sys/system as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 00:51:29 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
SQL>
17,继续测试为none的情况，参数remote_login_passwordfile为静态参数，需要重启库
SQL> select name,type,value,isdefault,isses_modifiable,issys_modifiable,isinstance_modifiable from v$parameter where name='remote_login_passwordfile';
NAME TYPE VALUE ISDEFAULT ISSES ISSYS_MOD ISINS
------------------------------ ---------- -------------------- --------- ----- --------- -----
remote_login_passwordfile 2 EXCLUSIVE TRUE FALSE FALSE FALSE
18,因为当前操作系统不隶属于DBA及OINSTALL操作系统组，所以报权限不足
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-01031: insufficient privileges
SQL>
19,把ORACLE用户所属的操作系统组进行恢复还原
[root@seconary ~]# usermod -g oinstall -G dba oracle11204
[root@seconary ~]#
20,启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 276824968 bytes
Database Buffers 784334848 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
21，调整参数为none
SQL> alter system set remote_login_passwordfile=none scope=spfile;
System altered.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 276824968 bytes
Database Buffers 784334848 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
SQL> show parameter remote_lo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string NONE
-bash-3.2$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 03:13:21 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
-bash-3.2$ sqlplus 'sys/system as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 03:13:27 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
-bash-3.2$ sqlplus 'sys/system22 as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 03:13:32 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
SQL>
23,配置参数为exclusive,不管是否使用密码或密码是否正确皆可本地登陆数据库
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 276824968 bytes
Database Buffers 784334848 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
SQL> show parameter remote_lo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
-bash-3.2$ sqlplus 'sys/system22 as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 03:15:48 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
25,下面我们测试通过远程登陆数据库，配置参数exclusive，必须使用密码登陆
-bash-3.2$ sqlplus sys/x@ORA11204 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 03:20:33 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
-bash-3.2$ sqlplus sys/system@ORA11204 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 03:20:43 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
-bash-3.2$
26，通过远程登陆数据库，配置参数none，无法登陆（因为找不到密码文件）
SQL> show parameter password
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string NONE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
-bash-3.2$ sqlplus sys/system@ORA11204 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 8 03:23:18 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
个人简介
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通4G数据库性能分析与优化
中国联通4G数据库性能分析与优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1794391/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1794391/
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
