转载自:http://blog.csdn.net/lzx_bupt/article/details/6780856
1、先看下命令说明:orapwd
- Usage:
orapwd file= entries= force= ignorecase= nosysdba= -
-
where -
file - name of password file (required), -
password - password for SYS will be prompted if not specified at command line, -
entries - maximum number of distinct DBA (optional), -
force - whether to overwrite existing file (optional), -
ignorecase - passwords are case-insensitive (optional), -
nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only). -
-
There must be no spaces around the equal-to (=) character.
2、再看下使用范例:
- orapwd
file=orapwmercury password=lzx123 entries=10
范例说明:
- file:变量orapwmercury为'orapw$ORACLE_SID'格式,如sid为test则命令文件为orapwtest,这点必须按格式来,并且该文件要放置在$ORACLE_HOME/dbs/目录下。
- password:变量lzx123为设定给sys用户的密码
- entries:10表明可以有10个sysdba权限用户,不可超过10个,目前已经定义了sys一个。
注:改动生效需要重启数据库。
3、应用说明:
orapwd命令是用来创建口令文件的,所以需要明白什么时候需要这个口令文件,执行下列命令查看
- [oracle@centos
~]$ sqlplus / as sysdba; -
- SQL*Plus:
Release 11.2.0.1.0 Production on Sat Sep 17 11:21:52 2011 -
- Copyright
(c) 1982, 2009, Oracle. All rights reserved. -
-
- Connected
to: - Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production - With
the Partitioning, OLAP, Data Mining and Real Application Testing options -
- SQL>
show parameter remote_login_passwordfile; -
- NAME
TYPE VALUE - ------------------------------------
----------- ------------------------------ - remote_login_passwordfile
string EXCLUSIVE - SQL>
保证数据库open情况下,执行命令show parameter remote_login_passwordfile;查看值是不是EXCLUSIVE。这里的值是pfile中定义的,可以查看你的initSID.ora文件。对该字段的描述是:
***********************************************FROM ORACLE11G DOCS******************************************
REMOTE_LOGIN_PASSWORDFILE = { shared | exclusive | none }
REMOTE_LOGIN_PASSWORDFILE
Values:
-
shared
One or more databases can use the password file. The password file can contain
SYS
as well as non- SYS
users. -
exclusive
The password file can be used by only one database. The password file can contain
SYS
as well as non- SYS
users. -
none
Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.
Notes:
-
When
REMOTE_LOGIN_PASSWORDFILE
is set to either exclusive
or shared
, but the password file does not exist, then the behavior is the same as settingREMOTE_LOGIN_PASSWORDFILE
to none
. -
If you change
REMOTE_LOGIN_PASSWORDFILE
to exclusive
or shared
from none
, then ensure that the password file is in sync with the dictionary passwords. SeeOracle Database Administrator's Guidef or more information.
*******************************************************************************************************************
从描述中可以知道如果值是none的话,表明口令文件模式不起作用,必须用操作系统级的sysdba权限用户登录,如系统用户oracle。而没有指定值得话等同于none。因此如果要使用口令文件必须是exclusive或是shared模式,并且用orapwd命令创建,值得一提的是该口令文件指保存具有sysdba等超级权限的用户。
注:假如你不能用sys加上口令登录,那就用系统用户oracle,使用sqlplus / as sysdba;登录到数据库(如不能登录,这里有个sqlnet.ora文件请查阅http://blog.csdn.net/lzx_bupt/article/details/6781332)。
4、常见问题
(1)尝试使用sysdba权限用户远程登录的时候提示,尤其是pl/sql使用 "sys/密码 as sysdba"登录时
ORA-01031: insufficient privileges
对该问题首要考虑就是1:口令文件的缺失,造成没有找到口令、2:REMOTE_LOGIN_PASSWORDFILE
值没有指定或是指定了none。
(2)select * from v$pwfile_users; 结果为 no rows select如
- SQL>
show user; - USER
is "SYS" - SQL>
select * from v$pwfile_users; -
- no
rows selected
也说明口令文件不存在或是不起作用,需要按上述调整好,重启数据库,登录,然后检验:
- SQL>
startup open; - ORACLE
instance started. -
- Total
System Global Area 801701888 bytes - Fixed
Size 2217632 bytes - Variable
Size 469764448 bytes - Database
Buffers 322961408 bytes - Redo
Buffers 6758400 bytes - Database
mounted. - Database
opened. - SQL>
show parameter remote_login_passwordfile; -
- NAME
TYPE VALUE - ------------------------------------
----------- ------------------------------ - remote_login_passwordfile
string EXCLUSIVE - SQL>
select * from v$pwfile_users; -
- USERNAME
SYSDB SYSOP SYSAS - ------------------------------
----- ----- ----- - SYS
TRUE TRUE FALSE -
- SQL>
grant sysdba to cat; -
- Grant
succeeded. -
- SQL>
select * from v$pwfile_users; -
- USERNAME
SYSDB SYSOP SYSAS - ------------------------------
----- ----- ----- - SYS
TRUE TRUE FALSE - CAT
TRUE FALSE FALSE
可说明,口令文件工作了!
注:本人截止到20110916仍在使用oracle,关于本话题如再有疑问请留言。