Oracle中password file的作用及说明

在数据库没有启动之前，数据库内建用户是无法通过数据库来验证身份的。

口令文件中存放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

  1<sid>,怎么能够共享? 
  2
  3实际上是这样的: Oracle数据库在启动时,首先查找的是orapw<sid>的口令文件,如果该文件不存在,则开始查找,orapw的口令文件   
  4如果口令文件命名为orapw,多个数据库就可以共享. 
  5
  6我们看一下测试: 
  7
  8[oracle@jumper dbs]$ sqlplus "/ as sysdba" 
  9
 10SQL*Plus: Release 9.2.0.3.0 - Production on Tue Jul 6 09:40:34 2004 
 11
 12Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 
 13
 14  
 15Connected to:   
 16Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production   
 17With the Partitioning, OLAP and Oracle Data Mining options   
 18JServer Release 9.2.0.3.0 - Production 
 19
 20SQL&gt; shutdown immediate   
 21Database closed.   
 22Database dismounted.   
 23ORACLE instance shut down.   
 24SQL&gt; !   
 25[oracle@jumper dbs]$ ls   
 26hsjf initdw.ora inithsjf.ora init.ora lkHSJF orapwhsjf spfilehsjf.ora   
 27[oracle@jumper dbs]$ mv orapwhsjf orapwhsjf.bak   
 28[oracle@jumper dbs]$ exit   
 29exit 
 30
 31SQL&gt; startup   
 32ORACLE instance started. 
 33
 34Total System Global Area 235999908 bytes   
 35Fixed Size 451236 bytes   
 36Variable Size 201326592 bytes   
 37Database Buffers 33554432 bytes   
 38Redo Buffers 667648 bytes   
 39ORA-01990: error opening password file '/opt/oracle/product/9.2.0/dbs/orapw'--这是最后查找的文件   
 40ORA-27037: unable to obtain file status   
 41Linux Error: 2: No such file or directory   
 42Additional information: 3 
 43
 44  
 45
 46
 47我们建立orapw口令文件,这时候可以打开数据库. 
 48
 49SQL&gt; !   
 50[oracle@jumper dbs]$ ls   
 51hsjf initdw.ora inithsjf.ora init.ora lkHSJF orapwhsjf.bak spfilehsjf.ora   
 52[oracle@jumper dbs]$ cp orapwhsjf.bak orapw   
 53[oracle@jumper dbs]$ exit   
 54exit 
 55
 56SQL&gt; alter database open; 
 57
 58Database altered. 
 59
 60SQL&gt; show parameter passw 
 61
 62NAME TYPE VALUE   
 63\------------------------------------ ----------- ------------------------------   
 64remote_login_passwordfile string SHARED   
 65SQL&gt;   
 66  
 67
 68
 69那么你可能会有这样的疑问,多个Exclusive的数据库是否可以共享一个口令文件(orapw)呢? 
 70
 71我们继续这个实验: 
 72
 73SQL&gt; show parameter password   
 74NAME TYPE VALUE   
 75\------------------------------------ ----------- ------------------------------   
 76remote_login_passwordfile string SHARED 
 77
 78  
 79[oracle@jumper dbs]$ strings orapw   
 80]\\[Z   
 81ORACLE Remote Password file   
 82INTERNAL   
 83AB27B53EDC5FEF41   
 848A8F025737A9097A   
 85
 86
 87注意这里仅记录着INTERNAL/SYS的口令 
 88
 89REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 时 
 90
 91SQL&gt; alter system set remote_login_passwordfile=exclusive scope=spfile;   
 92System altered. 
 93
 94SQL&gt; startup force;   
 95ORACLE instance started. 
 96
 97Total System Global Area 235999908 bytes   
 98Fixed Size 451236 bytes   
 99Variable Size 201326592 bytes   
100Database Buffers 33554432 bytes   
101Redo Buffers 667648 bytes   
102Database mounted.   
103Database opened.   
104SQL&gt; ! 
105
106[oracle@jumper bin]$ cd $ORACLE_HOME/dbs   
107[oracle@jumper dbs]$ strings orapw   
108]\\[Z   
109ORACLE Remote Password file   
110HSJF   
111INTERNAL   
112AB27B53EDC5FEF41   
1138A8F025737A9097A   
114[oracle@jumper dbs]$ exit   
115exit   
116
117
118注意这里,以EXCLUSIVE 方式启动以后,实例名称信息被写入口令文件. 
119
120此时如果有其他实例以Exclusive模式启动仍然可以使用这个口令文件,口令文件中的实例名称同时被改写. 
121
122也就是说,数据库只在启动过程中才读取口令文件,数据库运行过程中并不锁定该文件,类似于pfile/spfile文件. 
123
124  
125SQL&gt; select * from v$pwfile_users;   
126USERNAME SYSDB SYSOP   
127\------------------------------ ----- -----   
128SYS TRUE TRUE 
129
130SQL&gt; grant sysdba to eygle; 
131
132Grant succeeded. 
133
134SQL&gt; select * from v$pwfile_users; 
135
136USERNAME SYSDB SYSOP   
137\------------------------------ ----- -----   
138SYS TRUE TRUE   
139EYGLE TRUE FALSE 
140
141SQL&gt; !   
142[oracle@jumper bin]$ cd $ORACLE_HOME/dbs   
143[oracle@jumper dbs]$ strings orapw   
144]\\[Z   
145ORACLE Remote Password file   
146HSJF   
147INTERNAL   
148AB27B53EDC5FEF41   
1498A8F025737A9097A   
150&gt;EYGLE   
151B726E09FE21F8E83   
152
153
154  
155注意此时可以增加SYSDBA用户，并且这些信息可以被写入到口令文件. 
156
157一旦口令文件中增加了其他SYSDBA用户,此文件不再能够被其他Exclusive的实例共享. 
158
159  
160实际上，口令文件对于其他用户来说就是启到了一个 sudo 的作用. 
161
1626.重建口令文件 
163
164如果口令文件丢失,可以使用orapwd可以重建口令文件，语法如下: 
165
166[oracle@jumper oracle]$ orapwdUsage: orapwd file=<fname> password=<password> entries=<users>   
167where   
168file - name of password file (mand),   
169password - password for SYS (mand),   
170entries - maximum number of distinct DBA and OPERs (opt),   
171There are no spaces around the equal-to (=) character.</users></password></fname></sid></sid>
Published At
Categories with 数据库类
Tagged with
comments powered by Disqus