潛水珽的異想世界

在資料庫沒有啟動之前，資料庫內建用戶是無法通過資料庫來驗證身份的。

口令檔中存放sysdba/sysoper用戶的用戶名及口令，允許用戶通過口令檔驗證，在資料庫未啟動之前登陸，從而啟動資料庫。

如果沒有口令檔，在資料庫未啟動之前就只能通過作業系統認證。

使用rman，很多時候需要在nomount,mount等狀態對資料庫進行處理。
所以通常要求sysdba許可權如果屬於本地dba組，可以通過作業系統認證登陸。
如果是遠端sysdba登陸，需要通過passwordfile認證。

1.remote_login_passwordfile = none

此時停用口令檔驗證，oracle資料庫不允許遠端sysdba/sysoper身份登錄
無法通過遠端進行資料庫起停等操作管理

local:
[oracle@jumper oracle]$ sqlplus "/ as sysdba"

sql*plus: release 9.2.0 .3.0 - production on thu apr 15 09:58:45 2004

copyright (c) 1982, 2002, oracle corporation. all rights reserved.

connected to:
oracle9i enterprise edition release 9.2.0 .3.0 - production
with the partitioning, olap and oracle data mining options
jserver release 9.2.0.3.0 - production

sql> alter user sys identified by oracle;

user altered.

sql> show parameter pass

name                      type   value
--------------------- ----------- ------------------------------
remote_login_passwordfile string none
   

remote:

e:\oracle\ora92\bin>sqlplus /nologsql*plus: release 9.2.0 .4.0 -
production on 星期四 4月 15 09:39:22 2004copyright (c) 1982, 2002, oracle
corporation. all rights reserved.sql> connect sys/oracle@hsjf as
sysdbaerror:ora-01017: invalid username/password; logon denied
 

此處實際上是無法通過口令檔驗證

2.remote_login_passwordfile = exclusive

sql> alter system set remote_login_passwordfile=exclusive scope=spfile;

system altered.

sql> startup force;
oracle instance started.

total system global area 131142648 bytes
fixed size 451576 bytes
variable size 104857600 bytes
database buffers 25165824 bytes
redo buffers 667648 bytes
database mounted.
database opened.
sql> show parameter pass

name type value
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string exclusive
sql> alter user sys identified by oracle;

user altered.

remote:

e:\oracle\ora92\bin>sqlplus /nologsql*plus: release 9.2.0 .4.0 -
production on 星期四 4月 15 09:47:11 2004copyright (c) 1982, 2002, oracle
corporation. all rights reserved.sql> connect sys/oracle@hsjf as
sysdba已連接。sql> show useruser 為"sys"sql>
 

這實際上就是通過口令檔驗證登錄的

3.進一步測試

如果此時我們刪除passwdfile,sysdba/sysoper將無法認證，也就無法登陸資料庫server:

sql> !
[oracle@jumper oracle]$ cd $oracle_home/dbs
[oracle@jumper dbs]$ ls orapwhsjf
orapwhsjf
[oracle@jumper dbs]$ mv orapwhsjf orapwhsjf.bak
[oracle@jumper dbs]$

remote:

e:\oracle\ora92\bin>sqlplus /nolog

sql*plus: release 9.2.0 .4.0 - production on 星期四 4月 15 09:50:14 2004

copyright (c) 1982, 2002, oracle corporation. all rights reserved.

sql> connect sys/oracle@hsjf as sysdba
error:
ora-01031: insufficient privileges

sql>

這實際上就是無法通過口令檔驗證身份

4.如果遺失了passwdfile

如果使用passwdfile卻意外遺失，此時將不能啟動資料庫

sql> startup force;
oracle instance started.

total system global area 131142648 bytes
fixed size 451576 bytes
variable size 104857600 bytes
database buffers 25165824 bytes
redo buffers 667648 bytes
ora-01990: error opening password file '/opt/oracle/product/ 9.2.0 /dbs/orapw'
ora-27037: unable to obtain file status
linux error: 2: no such file or directory
additional information: 3

此時可以通過orapwd重建口令檔來解決
此處我們恢復口令檔既可
sql> !
[oracle@jumper oracle]$ mv $oracle_home/dbs/orapwhsjf.bak orapwhsjf
[oracle@jumper oracle]$ exit
exit

sql> alter database open;

database altered.

sql>

大致就是如此.

5. remote_login_passwordfile = shared

我們看一下oracle9i文檔中的說明:

shared

more than one database can use a password file. however, the only user recognized by the password file is sys.

意思是說多個資料庫可以共用一個口令檔,但是只可以識別一個用戶:sys

sql> select * from v$pwfile_users;

username sysdb sysop
------------------------------ ----- -----
sys true true

sql> grant sysdba to eygle;
grant sysdba to eygle
*
error at line 1:
ora-01994: grant failed: cannot add users to public password file

sql> show parameter password

name type value
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string shared

我們看到,此時的口令檔中是不能添加用戶的.

很多人的疑問在於:口令檔的預設名稱是orapw<sid>,怎麼能夠共用?

實際上是這樣的: oracle資料庫在啟動時,首先查找的是orapw<sid>的口令檔,如果該檔不存在,則開始查找,orapw的口令檔
如果口令檔命名為orapw,多個資料庫就可以共用.

我們看一下測試:

[oracle@jumper dbs]$ sqlplus "/ as sysdba"

sql*plus: release 9.2.0 .3.0 - production on tue jul 6 09:40:34 2004

copyright (c) 1982, 2002, oracle corporation.  all rights reserved.

connected to:
oracle9i enterprise edition release 9.2.0 .3.0 - production
with the partitioning, olap and oracle data mining options
jserver release 9.2.0.3.0 - production

sql> shutdown immediate
database closed.
database dismounted.
oracle instance shut down.
sql> !
[oracle@jumper dbs]$ ls
hsjf  initdw.ora  inithsjf.ora  init.ora  lkhsjf  orapwhsjf  spfilehsjf.ora
[oracle@jumper dbs]$ mv orapwhsjf orapwhsjf.bak
[oracle@jumper dbs]$ exit
exit

sql> startup
oracle instance started.

total system global area  235999908 bytes
fixed size                   451236 bytes
variable size             201326592 bytes
database buffers           33554432 bytes
redo buffers                 667648 bytes
ora-01990: error opening password file '/opt/oracle/product/ 9.2.0 /dbs/orapw'--這是最後查找的檔
ora-27037: unable to obtain file status
linux error: 2: no such file or directory
additional information: 3

我們建立orapw口令檔,這時候可以打開資料庫.

sql> !
[oracle@jumper dbs]$ ls
hsjf  initdw.ora  inithsjf.ora  init.ora  lkhsjf  orapwhsjf.bak  spfilehsjf.ora
[oracle@jumper dbs]$ cp orapwhsjf.bak orapw
[oracle@jumper dbs]$ exit
exit

sql> alter database open;

database altered.

sql> show parameter passw

name                                 type        value
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      shared
sql> 

那麼你可能會有這樣的疑問,多個exclusive的資料庫是否可以共用一個口令檔(orapw)呢?

我們繼續這個實驗:

sql> show parameter password
name type value
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string shared

[oracle@jumper dbs]$ strings orapw
]\[z
oracle remote password file
internal
ab27b53edc5fef41
8a 8f 025737a 9097a

注意這裏僅記錄著internal/sys的口令

remote_login_passwordfile=exclusive 時

sql> alter system set remote_login_passwordfile=exclusive scope=spfile;
system altered.

sql> startup force;
oracle instance started.

total system global area 235999908 bytes
fixed size 451236 bytes
variable size 201326592 bytes
database buffers 33554432 bytes
redo buffers 667648 bytes
database mounted.
database opened.
sql> !

[oracle@jumper bin]$ cd $oracle_home/dbs
[oracle@jumper dbs]$ strings orapw
]\[z
oracle remote password file
hsjf
internal
ab27b53edc5fef41
8a 8f 025737a 9097a
[oracle@jumper dbs]$ exit
exit

注意這裏,以exclusive 方式啟動以後,實例名稱資訊被寫入口令檔.

此時如果有其他實例以exclusive模式啟動仍然可以使用這個口令檔,口令檔中的實例名稱同時被改寫.

也就是說,資料庫只在啟動過程中才讀取口令檔,資料庫運行過程中並不鎖定該檔,類似於pfile/spfile檔.

sql> select * from v$pwfile_users;
username sysdb sysop
------------------------------ ----- -----
sys true true

sql> grant sysdba to eygle;

grant succeeded.

sql> select * from v$pwfile_users;

username sysdb sysop
------------------------------ ----- -----
sys true true
eygle true false

sql> !
[oracle@jumper bin]$ cd $oracle_home/dbs
[oracle@jumper dbs]$ strings orapw
]\[z
oracle remote password file
hsjf
internal
ab27b53edc5fef41
8a 8f 025737a 9097a
>eygle
b726e09fe 21f 8e83
 

注意此時可以增加sysdba用戶，並且這些信息可以被寫入到口令檔.

一旦口令檔中增加了其他sysdba用戶,此檔不再能夠被其他exclusive的實例共用.

實際上，口令檔對於其他用戶來說就是啟到了一個 sudo 的作用.

6.重建口令檔

如果口令檔遺失,可以使用orapwd可以重建口令檔，語法如下:

[oracle@jumper oracle]$ orapwdusage: orapwd file=<fname> password=<password> entries=<users>
where
file - name of password file (mand),
password - password for sys (mand),
entries - maximum number of distinct dba and opers (opt),
there are no spaces around the equal-to (=) character.

節錄自http://www.esnet.com.cn/Article/jishu/Data/200610/9740.html