Oracle中password file的作用及说明

By Sky on November 24, 2005 5:36 PM

 

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

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

SQL*Plus: Release 9.2.0.4.0 - 

Production on 星期四 4月 15 09:39:22 2004

Copyright (c) 1982, 2002, Oracle 

Corporation. All rights reserved.

SQL> connect sys/oracle@hsjf as 

sysdba
ERROR:
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 /nolog

SQL*Plus: Release 9.2.0.4.0 - 

Production on 星期四 4月 15 09:47:11 2004

Copyright (c) 1982, 2002, Oracle 

Corporation. All rights reserved.

SQL> connect sys/oracle@hsjf as 

sysdba
已连接。
SQL> show user
USER 为"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
8A8F025737A9097A


注意这里仅记录着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
8A8F025737A9097A
[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
8A8F025737A9097A
>EYGLE
B726E09FE21F8E83



注意此时可以增加SYSDBA用户，并且这些信息可以被写入到口令文件.


一旦口令文件中增加了其他SYSDBA用户,此文件不再能够被其他Exclusive的实例共享.


实际上，口令文件对于其他用户来说就是启到了一个 sudo 的作用.


6.重建口令文件


如果口令文件丢失,可以使用orapwd可以重建口令文件，语法如下:






[oracle@jumper oracle]$ orapwd
Usage: 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.


 

Categories:

Search

About this Entry

This page contains a single entry by Sky published on November 24, 2005 5:36 PM.

使用 Nid 改变数据库名 was the previous entry in this blog.

Linux必须学会的60个命令 is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.