我的一台 windows2000 + oracle9i 数据库，运行了一年，都正常。但现在发现个奇怪的问题，我无法用/as sysdba登陆。
C:/Documents and Settings/Administrator>SQLPLUS /NOLOG
SQL*Plus: Release 9.2.0.5.0 - Production on 星期五 4月 15 10:27:49 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL> conn sys/sys as sysdba
已连接。
#重新建立密码文件
SQL> host orapwd file="f:/oracle/ora92/database/PWDzzfw.ora" password=ttdyhxq entries=5
运行成功，没有报错，但运行sqlplus "/as sysdba" 还是报错 ORA-01031: insufficient privileges.
解决方法：
修改sqlnet.ora 文件，将第三、四两行前的“#”去掉，存盘。
# SQLNET.ORA Network Configuration File: D:oracleora92networkadminsqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
运行，sqlplus "/as sysdba" 可以进入啦！！！ ：）
------------------------------------------------------------------------------
说明：http://blog.itpub.net/birdfly2000
原来，修改了sqlnet.ora后，sys帐号的口令已经由pwfile创建时的口令为准
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
如果sysdba不是经过操作系统验证，而是通过口令文件，当初始化参数remote_login_passwordfile
设为exclusive或者shared,在数据库启动过程中是要定位并打开口令文件的，
http://www.cnoug.org/viewthread.php?tid=19962
win是在$oracle_homedatabasePWDSID.ORA.
可以通過v$pwfile_users看誰擁有sysdba,sysoper權限
偶就针对你第一个问题做个简单的解释吧？
口令文件的作用是什么呢？
SQL> connect cybercafe/cybercafe as sysdba
Connected.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning option
JServer Release 9.2.0.1.0 - Production
[uniread] Saved history (1281 lines)
[oracle@ads3 admin]$ less sqlnet.ora
# SQLNET.ORA Network Configuration File: /home/oracle/9.2.0.1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
----------------------------------------------------------------------
修改后的sqlnet.ora
----------------------------------------------------------------------
[oracle@ads3 admin]$ less sqlnet.ora
# SQLNET.ORA Network Configuration File: /home/oracle/9.2.0.1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES=(NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQL> connect cybercafe/zhongguoyidong as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
SQL>
SQL> connect sys/sys as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
-------------------------------------------------------------------------
sys帐号的口令就是sys,修改了sqlnet.ora后，sys帐号的口
令已经由pwfile创建时的口令为准
-------------------------------------------------------------------------
SQL> connect sys/oracle as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> grant sysdba to cybercafe;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
CYBERCAFE TRUE FALSE
SQL> connect cybercafe/oracle as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect cybercafe/cybercafe as sysdba
Connected.
-----------------------------------------------------------------------
其它用户如果想用as sysdba登陆，必须用grant 进行授
权才可以，登陆时口令还是它以前的口令
-----------------------------------------------------------------------
不知偶讲清楚了没有？
++++++++++++++++++++++++++==
$orapwd file=orapwesal password=ORACLE_PASSOWRD entries=99;
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> select * from v$pwfile_users;
no rows selected
SQL> grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01999: password file mode has changed from 'exclusive' to 'shared'
SQL> show parameter password
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> alter system set remote_login_passwordfile=shared scope=both;
alter system set remote_login_passwordfile=shared scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set remote_login_passwordfile=shared scope=spfile;
System altered.
SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=both;
alter system set remote_login_passwordfile=EXCLUSIVE scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
System altered.
SQL> grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01999: password file mode has changed from 'exclusive' to 'shared'
很郁闷,9.2.0.4的v$pwfile_users中没有任何记录，结果用sys用户无论如何
也没有办法登录成功，最后只好将sqlnet.ora中的下面列的一行注释掉.
SQLNET.AUTHENTICATION_SERVICES=(NTS)
这时remote_login_passwordfile参数虽然是exclusive，登陆时就很方便进去啦,
也就是说，真正在连接时起关键作用于的是sqlnet中的AUTHENTICATION_SERVICES
SQL> show parameter password
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
----------------------------------------------------------------------------------------------
以下是10g 10.1.0.2的测试部分
----------------------------------------------------------------------------------------------
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jun 9 16:05:09 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning and Data Mining options
SQL> show parameter password
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
[oracle@DATA oracle]$ less 10.1.0.2/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /home/oracle/10.1.0.2/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES=(NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)
发现9.2.0.4与10.1.0.2一样，同样的操作,sys帐号都是无法登陆.
++++++++++++++++++++++++++++++++++++++
将偶将sqlnet.ora中的SQLNET.AUTHENTICATION_SERVICES注释掉
以后，可以用sqlplus "/ as sysdba"登陆进来，但发现一个比较奇怪的
问题
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning and Data Mining options
SQL> select * from v$pwfile_users;
no rows selected
SQL> grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01999: password file mode has changed from 'exclusive' to 'shared'
征状与9.2.0.4是一样的，与9.2.0.1却又不同，看来oracle在口令管理方面
在9.2.0.4之后有了一些变化。只要在sqlnet.ora中添加了下面这行，oracle
将会清除v$pwfile_users中的信息，大概remote_login_passwordfile必须等
于shared时才可以授权sysdba给其它用户
SQLNET.AUTHENTICATION_SERVICES=（NTS）
+++++++++++++++
SQL> grant sysdba to item;
grant sysdba to item
*
ERROR at line 1:
ORA-01999: password file mode has changed from 'exclusive' to 'shared'
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
remote_archive_enable string FALSE
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
SQL> host oerr ora 01999
01999, 00000, "password file mode has changed from '%s' to '%s'"
// *Cause:
// *Action:
大家看，明明是exclusive，为什么说已经从exclusive转到shared呢？
++++++++++++++++++++++++++
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
CYBERCAFE TRUE FALSE
SQL> grant sysdba to item;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
CYBERCAFE TRUE FALSE
ITEM TRUE FALSE
SQL>
在9.2.0.1中是没有问题的，但到了9.2.0.4与10.1.0.2时已经不可以这样操作
啦，是不是oracle的bug?
++++++++++++++++++++++++++
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> connect item/passofitem as sysdba
Connected.
SQL> revoke sysdba from cybercafe;
Revoke succeeded.
SQL> connect cybercafe/cybercafe as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
SQL> connect cybercafe/cybercafe;
Connected.
SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
SQL> connect sys/sys as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect sys/oracle as sysdba
Connected.
SQL> revoke sysdba from item;
Revoke succeeded.
SQL> connect item/passofitem as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
SQL> connect item/passofitem
Connected.
SQL>
感觉9.2.0.1的测试才符合书上说的。
++++++++++++++++++++
++++++++++++++++++++
++++++++++++++++++++
以如下方式查找注册数据库：
UNIX－注册指定端点上的 oratab 文件中所列的所有数据库。该文件位于 /etc 或 /var/opt/oracle 下，视 UNIX 平台而定。
Windows NT－注册指定端点上的注册表中所列的所有数据库。Oracle 数据库列在 HKEY_LOCAL_MACHINESOFTWAREORACLE 下的注册表中。如果数据库的注册表键已经删除，则无法发现或注册该数据库。
1143
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
