In the OEM and oracle clients, sys cannot log on with sysdba.

 
This morning, a performance test was conducted on a platform of the company. When the database connection was used up, it was necessary to analyze the database access bottleneck problem. The OEM was successfully installed on the database server, during OEM login on the window client, log on to the Windows client using the sysdba role permission through the sys account, prompting that the user name or password is incorrect, directly connect to the server using conn sys/****** as sysdba. The other server successfully logs on to the server using the sysdba role through the OEM on the client, there must be a problem with the server configuration or database configuration that cannot be logged on, because the OEM accesses the server through a browser and the client does not need to be set. The following is a solution:
Oracle user information is generally stored in the data dictionary, so regular users cannot log on when the Oracle database is not started. However, there are two types of users with sysdba or sysoper permissions. There are two login methods for Oracle sysdba or sysoper users: OS authentication and password file verification. Which authentication method is used and whether the login is successful depends on three factors:
1. sqlnet. ora SQLNET. AUTHENTICATION_SERVICES Settings 2. REMOTE_LOGIN_PASSWORDFILE settings in the parameter file 3. The password file orapwd _ $ ORACLE_SID Oracle performs permission verification in the following general order:
1. Determine whether to perform OS verification or password file verification based on the value of SQLNET. AUTHENTICATION_SERVICES. 2. for OS verification, determine whether the user group has the sysdba permission based on the current user group. If OS verification fails, verify the password file. 2. For Password File verification, the value of REMOTE_LOGIN_PASSWORDFILE and the existence of the password file determine whether the verification is successful. Www.2cto.com 1. to enable OS verification, you must go to sqlnet. set SQLNET. AUTHENTICATION_SERVICES = (ETS), create an ora_dba user group in Windows, and add relevant users to this group (e.g ., in this way, the administrator can log in as sysdba without providing the user name and password (or providing any user name and password. Because the operating system has been verified in place of Oracle. Test 1: log on to the server at $ sqlplus/as sysdbaConnected: oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production With the OLAP and Data Mining options SQL> sqlplus sys/change_on_install as sysdba Connected: oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production With the OLAP and Data Mining options SQL> Test 2: windows client Logon
First in the windows client tnsnames. A network service named 1.163 centerSQL: \> sqlplus sys/change_on_install@1.163center as sysdba ERROR: ORA-01031: insufficient privileges Enter user-name: www.2cto.com 2. password File Verification
The password file contains the username and password of the user granted sysdba and sysoper permissions. This is an encrypted file, which is generally stored in the $ ORACLE_HOME/dbs directory and is named orapwd _ $ ORACLE_SID. If you want to use the password file for verification, change sqlnet. ora to SQLNET. AUTHENTICATION_SERVICES = none, or delete sqlnet. AUTHENTICATION_SERVICES from SQLNET. ora. Login to sqlplus from the same anonymous mode will fail, and a user name and password will be provided for successful login. Test 1: Delete the password file. An error occurred while logging in with the username and password! SQL> sqlplus sys/change_on_install as sysdba ERROR: ORA-01031: insufficient privileges Enter user-name: Test 2: Restore the password file, set REMOTE_LOGIN_PASSWORDFILE = none. An error occurred while logging in with the username and password! SQL> alter system set remote_login_passwordfile = none scope = spfile; System altered. $ sqlplus sys/change_on_install as sysdba ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: Test 3: Restore the password file, set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE or SHARED. Log On with the user name and password. Successful! SQL> alter system set remote_login_passwordfile = exclusive scope = spfile; System altered. $ sqlplus sys/change_on_install as sysdba Connected to an idle instance. SQL> www.2cto.com 3. Password File
View users with sysdba or sysoper permissions: SQL> select * from v $ pwfile_users; username sysdb sysop ---------------------------- ------- sys true each time you use grant sysdba/sysoper to grant new users special permissions or alter user command to modify the user password with sysdba/sysoper permissions, oracle automatically synchronizes password files to ensure that users with special permissions can log on to the database for management operations without opening the database.
SQL> grant sysdba to logicgate; Grant succeeded. SQL> select * from v $ pwfile_users; USERNAME SYSDB SYSOP certificate -------- SYS TRUE LOGICGATE TRUE FALSE use the orapwd command to recreate the password file. $ Orapwd Usage: orapwd file = <fname> password = <password> entries = <users> force = <y/n> where file-name of password file (mandatory ), password-password for SYS (mandatory), entries-maximum number of distinct DBA (optional), force-whether to overwrite existing file (optional) where the file name and password are required. Entries sets the maximum number of dba users that the password file can contain. Force defines whether to overwrite the current file. Rebuilding the password file will clear the passwords of all sysdba users except sys users. You must use grant sysdba to synchronize password files. $ Orapwd file = $ ORACLE_HOME/dbs/orapwd _ $ ORACLE_SID password = zdsoft entries = 20 force = y; $ sqlplus sys/zdsoft as sysdba Connected: www.2cto.com Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production With the OLAP and Data Mining options SQL> select * from v $ pwfile_users; username sysdb sysop ------------------------------- -------- sys true configure through the preceding three steps: sqlnet. ora: recreate the password file. After REMOTE_LOGIN_PASSWORDFILE = exclusive is set, the windows client can log on to the database server as sysdba by using the network service name sys, at the same time, the OEM can only log on to the system as normal through sysman. you can log on to the author xujinyang using sysdba.