結論：

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

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

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

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

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

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

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

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

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

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

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

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

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

ERROR:

ORA-01017: invalid username/password; logon denied

個人簡介

8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。

服務過的客戶：

中國電信

中國移動

中國聯通

中國電通

國家電網

四川達州商業銀行

湖南老百姓大藥房

山西省公安廳

中國郵政

北京302醫院

河北廊坊新奧集團公司

專案經驗:

中國電信3G專案AAA系統資料庫部署及最佳化
中國聯通4G資料庫效能分析與最佳化

中國聯通CRM資料庫效能最佳化

中國移動10086電商平臺資料庫部署及最佳化

湖南老百姓大藥房ERR資料庫sql最佳化專案

四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化

四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化

北京高鐵訊號監控系統RAC資料庫部署及最佳化

河南宇通客車資料庫效能最佳化

中國電信電商平臺核心採購模組表模型設計及最佳化

中國郵政儲蓄系統資料庫效能最佳化及sql最佳化

北京302醫院資料庫遷移實施

河北廊坊新奧data guard部署及最佳化

山西公安廳身份證審計資料庫系統故障評估

國家電網上海災備專案4 node rac+adg
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案

聯絡方式：

手機：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/

測試oracle 11.2.0.4的remote_login_password引數含義

結論：

測試過程

個人簡介

相關文章