在数据库没有启动之前,数据库内建用户是无法通过数据库来验证身份的。
口令文件中存放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> shutdown immediate
21Database closed.
22Database dismounted.
23ORACLE instance shut down.
24SQL> !
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> 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> !
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> alter database open;
57
58Database altered.
59
60SQL> show parameter passw
61
62NAME TYPE VALUE
63\------------------------------------ ----------- ------------------------------
64remote_login_passwordfile string SHARED
65SQL>
66
67
68
69那么你可能会有这样的疑问,多个Exclusive的数据库是否可以共享一个口令文件(orapw)呢?
70
71我们继续这个实验:
72
73SQL> 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> alter system set remote_login_passwordfile=exclusive scope=spfile;
92System altered.
93
94SQL> 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> !
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> select * from v$pwfile_users;
126USERNAME SYSDB SYSOP
127\------------------------------ ----- -----
128SYS TRUE TRUE
129
130SQL> grant sysdba to eygle;
131
132Grant succeeded.
133
134SQL> select * from v$pwfile_users;
135
136USERNAME SYSDB SYSOP
137\------------------------------ ----- -----
138SYS TRUE TRUE
139EYGLE TRUE FALSE
140
141SQL> !
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>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>