View password file fault from ORA-01031 Error

Login connection errors are the most common problems we encounter during Environment configuration, changes, and new Client installation. The logon process involves the client network, operating system, TNS configuration, listener working status, Server Remote Local logon mode, and various parameter configurations. It should be said that if there is a problem at one stage, it will lead to an Oracle user logon error, and the error message may mislead the user.
 
The following describes a connection error that I have solved. Due to the actual environment involved, I used the post-event simulation strategy.

 

1. Problem Overview

 

My colleagues used the "non-standard" method to install the database and found exceptions during local connection and remote connection. The database version is 11gR2.

 

SQL> select * from v $ version;

BANNER

--------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

 

The colleague has configured the TNS name to connect to the local database on the server.

 

[Oracle @ bspdev ~] $ Tnsping wilson

TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 06-MAR-2014 05:19:23
 
 

Copyright (c) 1997,200 9, Oracle. All rights reserved.

 

Used parameter files:

/U01/oracle/network/admin/sqlnet. ora

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = wilson )))
 
OK (10 msec)

 

Because it is a test, you can perform various configuration operations after logging on to the host through the ssh client. You can use anonymous/logon.

 

SQL> conn/as sysdba

Connected.

SQL> conn sys/oracle as sysdba

Connected.

SQL> conn sys/xxx as sysdba

Connected.

 

However, if you add the @ wilson service name to both remote and local logon, you cannot log on.

 

-- Local User Logon

SQL> conn sys/oracle @ wilson as sysdba

ERROR:

ORA-01031: insufficient privileges

 

 

-- Remote Logon

C: \ Users \ 51ibm> sqlplus/nolog

 

SQL * Plus: Release 11.2.0.1.0 Production on Wednesday March 5 21:36:43 2014

 

Copyright (c) 1982,201 0, Oracle. All rights reserved.

 

SQL> conn sys/oracle @ wilson as sysdba

ERROR:

ORA-01031: insufficient privileges

 

Why does an error occur when the password of sys is oracle "?

 

2. Problem Analysis

 

By default, operating system users are in system-level dba groups (names may be different) for both Windows and Linux ). In addition, the server generally chooses the operating system-level authentication method for login.
 
That is to say, as long as we pass the username/password verification at the operating system level, we can directly log on to Oracle. In addition, if such a logon method is enabled, the user name and password will not be verified even if they are entered.
 
This is also why we enter the wrong password xxx and can log on.

 

SQL> conn sys/oracle as sysdba

Connected.

SQL> conn sys/xxx as sysdba

Connected.

 

Then, the problem is transformed into Oracle as a software program running on the operating system layer. How does one determine whether the system is operating system-level verification or password verification?

The answer is: listener program. The following two statements run on the local machine tell us what is going on.

 

SQL> conn sys/mmm as sysdba

Connected.

SQL> conn sys/mmm @ wilson as sysdba

ERROR:

ORA-01017: invalid username/password; logon denied

 

Warning: You are no longer connected to ORACLE.

 

The listener is an important component in the process of connecting client and server processes. Note that "connection is in progress" rather than after connection. After the listener receives the request, it searches for the requested Instance name based on the Instance registration list and requests the corresponding Instance to separate the Server Process. User name and password verification is not performed in the listener.
 
In the previous article, the author discussed whether the listener determines whether to include @ In the connection string @. If @ exists, you must access the listener whether connected to a local or remote instance.

Therefore, for operating system-level verification of the connection, the key point is not whether we remotely log on to the host, but whether we have passed the listener!

If you do not pass through the listener, you can first ensure that the local host is connected, not to other server hosts. If it passes through the listener, it will be determined to be a remote logon and will not be verified by the operating system.

Back to the problem itself, it is normal when colleagues do not use @ connection. As long as the listener is used, an error occurs, regardless of local or remote connection.

Another clue is that an error occurred while logging on to the sys user.

Based on various factors, what authentication method is used when Oracle does not use local logon and sysdba users such as sys log on? The answer is the password file.

After we suspect the password file, we can find the file prefixed with orapw in the $ ORACLE_HOME/dbs directory.

 

 

[Oracle @ bspdev dbs] $ cd $ ORACLE_HOME/dbs

[Oracle @ bspdev dbs] $ ls-l | grep orapw

 

The password file does not exist and is not created. Try to create a file manually:

 

[Oracle @ bspdev dbs] $ orapwd password = oracle file = orapwWILSON

[Oracle @ bspdev dbs] $ ls-l | grep orapw

-Rw-r ----- 1 oracle oinstall 1536 Mar 6 orapwWILSON

 

Try to connect to the database.

 

[Oracle @ bspdev dbs] $ sqlplus/nolog

 

SQL * Plus: Release 11.2.0.1.0 Production on Thu Mar 6 05:54:24 2014

Copyright (c) 1982,200 9, Oracle. All rights reserved.

 

SQL> conn sys/oracle @ wilson as sysdba

ERROR:

ORA-01031: insufficient privileges

 

Still error. Further analysis is needed.

For more details, please continue to read the highlights on the next page:

1 2 Next Page