I have a Windows2000 Oracle9i database that is running a year. But now I find a strange question, I can't log in with / as sysdba.
C: / Documents and Settings / Administrator> SQLPLUS / NOLOG
SQL * Plus: Release 9.2.0.5.0 - Production ON Friday April 15 10:27:49 2005
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
SQL> Conn / As SysdbaError: ORA-01031: Insufficient Privileges
SQL> Conn Sys / SYS AS SYSDBA is connected.
# Re-establish password file SQL> host oraPwd file = "f: /oracle/ora92/database/pwdzfw.ora" password = ttdyhxq entries = 5
Run success, no error, but run SQLPLUS "/ as sysdba" or error ORA-01031: Insufficient Privileges.
Workaround: Modify the SQLNET.ORA file, remove the "#" before the third and fourth lines, and save the disk.
# SQLNET.ORA Network Configuration File: D: oracleora92networkadminsqlnet.ora # Generated by Oracle configuration tools.SQLNET.AUTHENTICATION_SERVICES = (NTS) NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME)
Run, SQLPlus "/ as sysdba" can enter! ! ! :)
-------------------------------------------------- ---------------------------- Description: http://blog.itpub.net/birdfly2000
Originally, after modifying SQLNET.ORA, the password of the SYS account has been created by PWFile is quasi-SQLNET.AUTHENTICATION_SERVICES = (NTS) Names.directory_Path = (tnsnames, onames, hostname)
If SYSDBA is not operating system authentication, but through the password file, when the initialization parameter remote_login_passwordfile is set to Exclusive or Shared, the database startup is positioned and opened the password file,
http://www.cnoug.org/viewthread.php?tid=19962
Win is $ oracle_homedatabasepwdsid.ora. You can see who owns Sysdba, Sysoper via V $ PWFILE_USERS.
I am a simple explanation for your first question? What is the role of a password file?
SQL> Connect Cybercafe / Cybercafe As Sysdbaconnected.
SQL> SELECT * FROM V $ PWFILE_USERS;
Username sysdb sysop ------------------------------ --------- Sys true truesql> exitdisconnected from oracle9i Enterprise Edition Release 9.2.0.1.0 - Productionwith The Partitioning OptionjServer Release 9.2.0.1.0 - Production [Uniread] Saved History (1281 Lines)
.
Names.directory_Path = (Tnsnames, onames, hostname)
-------------------------------------------------- -------------------- Changed SQLNET.ORA ------------------------ ---------------------------------------------- [Oracle @ ADS3 Admin] $ Less Sqlnet.ora # SQLNET.ORA NetWork Configuration File: /Home/Orcle/9.2.0.1/neetwork/admin/sqlnet.ora # generated by Oracle Configuration Tools.
SQLNET.AUTHENTICATION_SERVICES = (NTS)
Names.directory_Path = (Tnsnames, onames, hostname)
SQL> Connect cybercafe / zhongguoyidong as sysdbaerror: ora-01031: Insufficient privileges
Warning: you are no longer connection to oracle.sql>
SQL> Connect Sys / Sys as SysdbaError: ORA-01017: Invalid UserName / Password; Logon Denied
-------------------------------------------------- ----------------------- SYS account password is SYS, modify SQLNET.ORA, the password of the SYS account is subject to the password created by PWFile. -------------------------------------------------- ----------------------- SQL> Connect Sys / Oracle As Sysdbaconnected.SQL> Show Useruser IS "SYS"
SQL> Grant sysdba to cybercafe;
Grant succeeded.
SQL> SELECT * FROM V $ PWFILE_USERS;
Username sysdb sysop ------------------------------ ----- ----- Sys True Truecybercafe True Falsql> Connect Cybercafe / Oracle As SysdbaError: ORA-01017: Invalid UserName / Password; Logon Denied
Warning: you are no longer connection to oracle.sql> Connect cybercafe / cybercafe as sysdbaconnected. ---------------------------------------------------------------------------------------------------------------------- ---------------------------------------- Other users If you want to log in with AS SYSDBA, you must Authorization with GRANT can be, the password is still in the password in the way --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------
I don't know if I have clearly understood it?
==
$ orapwd file = ORAPWESAL Password = Oracle_passowrd entries = 99;
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Productionwith The Partitioning OptionjServer 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 EXCLUSIVESQL> 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 can not 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 can not 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'
Very depressed, 9.2.0.4 V $ pWFile_Users have no records, and the result is that SYS users have no way to log in, and finally take off the one line of the following line in SQLNET.AUThentication_Services = (NTS)
At this time, the remote_login_passwordfile parameter is very convenient, which is very convenient to log in, that is, the key role in the SQLNET is actually connected to Authentication_Services in SQLNET.
SQL> Show Parameter Password
Name Type Value --------------------------------- ------------------------------------------------- -remote_login_passwordfile String Exclusive
-------------------------------------------------- ------------------------------------------ The following is 10g 10.1.0.2 Test part --------------------------------------------- -----------------------------------------------
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 - Productionwith The Partitioning and Data Mining Options
SQL> Show Parameter Password
Name Type Value --------------------------------- ------------------------------------------------- -remote_login_passwordfile String ExclusiveSQL> Select * from V $ PWFILE_USERS; UserName Sysdb Sysop --------------------------------- ----- Sys True True
[Oracle @ data oracle] $ less 10.1.0.2/neetwork/admin/sqlnet.ora # SQLNET.ORA NetWork configuration file: /Home/oracle/10.1.0.2/neetwork/admin/sqlnet.ora # generated by Oracle Configuration Tools.
SQLNET.AUTHENTICATION_SERVICES = (NTS)
Names.directory_path = (TNSNAMES)
Found 9.2.0.4, like 10.1.0.2, the same operation, the SYS account cannot be logged in.
After coming out of SQLNET.AUTHENTICATION_SERVICES in SQLNET.ORA, you can use SQLPLUS "/ as sysdba" to log in, but find a weird problem.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Productionwith 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'
The symptoms are the same as 9.2.0.4, but different from 9.2.0.1, it seems that Oracle has some changes after password management in 9.2.0.4. Just add this line in SQLNET.ORA, Oracle will clear the information in V $ PWFILE_USERS, probably remote_login_passwordfile must be equal to Shared, you can authorize SYSDBA to other users.
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 FALSEremote_dependencies_mode string TIMESTAMPremote_listener stringremote_login_passwordfile string EXCLUSIVEremote_os_authent boolean FALSEremote_os_roles boolean FALSESQL> host oerr ora 0199901999, 00000, "password file mode has changed from '% s' to '% s'" // * Cause: // * Action:
Let's see, it is oblosclusive, why have you turned from Exclusive to Shared?
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Productionwith The Partitioning and Oracle Data Mining OptionsJServer Release 9.2.0.1.0 - Production
SQL> Show Parameter Remote
Name Type Value --------------------------------- ------------------------------ remote_archive_enable string trueremote_dependencies_mode string TIMESTAMPremote_listener stringremote_login_passwordfile string EXCLUSIVEremote_os_authent boolean FALSEremote_os_roles boolean FALSESQL> select * from v $ pwfile_users; Username sysdb sysop --------------------------------- ----- Sys True Truecybercafe True False
SQL> Grant sysdba to item;
Grant succeeded.
SQL> SELECT * FROM V $ PWFILE_USERS;
Username sysdb sysop ------------------------------ --------- Sys True Truecybercafe True FalseItem True False
SQL>
There is no problem in 9.2.0.1, but it is no longer possible to operate when 9.2.0.4 and 10.1.0.2, is it Oracle's bug?
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Productionwith The Partitioning and Oracle Data Mining OptionsJServer Release 9.2.0.1.0 - Production
SQL> Connect Item / PassofItem As Sysdbaconnected.sql> Revoke Sysdba from Cybercafe;
Revoke succeeded.
SQL> Connect Cybercafe / Cybercafe As Sysdbaerror: ORA-01031: Insufficient Privileges
Warning: you are no luck> Connect Cybercafe / Cybercafe; Connected.SQL> Connect / as Sysdbaerror: ORA-01031: InsufFFCIENT Privileges
Warning: You are no longer connected to ORACLE.SQL> connect sys / sys as sysdbaERROR: ORA-01017: invalid username / password; logon deniedSQL> connect sys / oracle as sysdbaConnected.SQL> revoke sysdba from item;
Revoke succeeded.
SQL> Connect Item / PassofItem As Sysdbaerror: ORA-01031: Insufficient Privileges
Warning: you are no luck> Connect Item / PassofItemConnected.sql>
Feel the test of 9.2.0.1 in accordance with the book.
Find registration databases as follows:
UNIX-Register all the databases listed in the ORATAB file on the specified endpoint. This file is located under / etc or / var / opt / oracle, depending on the UNIX platform. Windows NT-Register all databases listed in the registry on the specified endpoint. Oracle Database Columns in the registry under HKEY_LOCAL_MACHINESOFTWAREORACLE. If the registry key of the database has been deleted, the database cannot be found or registered.
