Oracle 11g logon Analysis in different situations

This blog has participated in the 2014 CSDN blog contest, if you think this article is useful to you, please move a small hand to help me vote: http://vote.blog.csdn.net/Article/Details? Articleid = 32939867, thank you.

For Oracle beginners and even Oracle DBAs who have some experience, Oracle account logon problems are often very difficult. Even if you log on to oracle successfully, you still know it. After systematic study and repeated practices, The author finally had a comprehensive understanding of the Oracle login principle and operation details in the spirit of breaking the attitude of the casserole to the end. This article records these experiences and hopes to help Oracle beginners log on to oracle with confidence.

1. Prerequisites for learning this article

Oracle has a lot of knowledge, but some basic terms should be familiar to all DBAs. To better understand the content of this article, you need to understand the following terms: Instance and Database, IP/TCP, sqlplus, Oracle account and operating system account, and Oracle DBA.

2 Oracle logon Overview 2.1 Oracle C/S architecture and communication protocol

The overall architecture of Oracle software is based on C/S and divided into client and server based on functions. The client is responsible for accepting user input and receiving and Displaying results from the server. Common clients include SQL Developer and SqlPlus. The server is responsible for parsing SQL requests from the client, return the result to the client.

The communication part of any C/S architecture is crucial, and the most important thing is the design of the communication protocol. Oracle is no exception. The communication protocol used is known as oracle net. It is an application layer protocol. Currently, oracle net can run on many underlying protocols, such as TCP, secure TCP, named pipe, and SDP. In addition, for different underlying operating system platforms, oracle net also supports the communication protocol between local processes of the operating system.

No matter what the underlying platform is, no matter what the client software is and where it runs, the client and server can only communicate with each other using the unique Oracle net protocol.

2.2 oracle account and Verification Method

Permission control through an account is a method adopted by many software. For example, each OS has its own account. Oracle is no exception. To access Oracle for operations, you must enter the Oracle account as an identity. Oracle accounts are divided into privileged accounts and normal accounts by permissions. Privileged accounts have a great deal of permissions, while general users have a lot of permissions. A typical privileged account is SYS and SYSTEM.

So how does oracle verify the account? There are many ways to verify the Local Password, as well as OS account authentication, LDAP authentication, Kerberos and other external verification. This article only covers the most basic password verification methods.

3 Oracle listening and Password StorageAs a C/S architecture program, the account logon process is divided into two parts: network connection and account verification.
3.1 Two server-side listeners

As mentioned earlier, Oracle net supports multiple underlying communication protocols, including local inter-process communication protocols of the operating system. Regardless of the underlying protocol, the server-side program must listen in some form to wait for connection requests from the customer.

(1) network listener tnslsnr

When the underlying protocol is a network protocol, Oracle tnslsnr provides the listener service. This is a separate process, independent from other processes. The following figure shows how the listener works on an Oracle dedicated server.

As shown in, the client process first connects to the listener (red line) and verifies the account and permissions. If all the verification passes, the listener process is responsible for generating a new server process, the client process and the newly generated server process are directly connected (Blue Line), which is called a session. At the same time, the client is disconnected and there is no relationship with the new session. It can be seen that the listening process plays a leading role. Then, even if the listener stops working, the established sessions will not be affected.

(2) local listeners

The local listener is completely different from the network listener. It does not need a network protocol stack, and the implementation is heavily dependent on the underlying OS. In Oracle, local listening is completely independent of tnslsnr. Local listening on the Linux platform does not need to execute any program. That is to say, local logon does not depend on tnslsnr. This is very practical for DBAs. When the listener fails and cannot work properly, we can still log on to oracle through a local connection for operations.

3.2 two Password Storage Methods

This document only describes how to verify the local password.

(1) The password is stored in the data dictionary.

To verify the password, the database must be in the open state. The passwords of most common users are stored in this way. A problem occurs when the password is stored in the data dictionary. That is, before the database is opened, the data dictionary is unavailable, so that the account password cannot be known and the user cannot log on. This is a fatal problem for DBA. If a database problem occurs one day and the database cannot be loaded, the DBA must log on to the system to fix the problem. If the password of the DBA account is stored in the data dictionary, to log on, you must first open the database. This is not an endless loop. Therefore, oracle requires that the password information of any account with DBA special permissions will be automatically copied and stored in an external file.

(2) passwords are stored in external files.

The password file is usually stored at @ ORACLE_HOME/dbs/orapw $ SID. Note that only accounts and passwords with DBA permissions are stored in external files, and are automatically added when special permissions are granted. You cannot manually modify the external password file.

4. Oracle logon AnalysisFor different listening methods and different Password Storage Methods, you can arrange the following logon scenarios. The client process determines the specific situation based on the connection string. 4.1 local listening + No Password required

Prerequisites:

(1) the client and server run on the same machine;

(2) log on to the OS using the OS account where oracle is installed;

(3) set the $ ORACLE_SID environment variable.

Connection string:

The connection string must be connect/as sysdba.

Use Cases:

It is worth noting that in this case, the only prerequisite for successful login is to specify the $ ORACLE_SID environment variable. No listeners, Database loading, or instance startup. As shown in, the instance is not started. In this case, oracle's sys account is used by default to log on.

In this case, DBA starts the database.

4.2 local listening + external file Password

Prerequisites:

(1) the client and server run on the same machine;

(2) use any account to log on to the OS;

(3) set the $ ORACLE_SID environment variable.

Connection string:

The connection string must be the name and password of the connect privileged account as sysdba.

Use Cases:

Similar to 4.1, the OS account only needs to have the permission to run sqlplus, and does not need to be an Oracle Installation user.

4.3 Local listening + Data Dictionary PasswordPrerequisites: (1) the client and server run on the same machine; (2) the database has been loaded and opened. (3) set the $ ORACLE_SID environment variable.



Connection string: connect username/password.
Application Scenario: this scenario is applicable to common users who log on locally. Note: Because the password is stored in the data dictionary, the database must be open.
4.4 network listener + external file PasswordPrerequisites: (1) tnslsnr must be working properly; (2) Data instance startup; no need to load the database.

Connection string: connect privileged user name/password @ serverIP/servicename as sysdba
Use Case: dba remote login for database maintenance. In this case, the database does not need to be loaded. Note: There is a service name in the connection string, so the listener must know the service name information. However, by default, service information is dynamically registered with the listener only after the database is loaded. The current situation is that only the database instance is started and the database is not loaded. In this case, to let the listener know the service information, you must register the service statically. The method is to modify the listener. ora configuration file on the server, as shown below:


4.5 network listener + Data Dictionary PasswordPrerequisites: (1) tnslsnr must be working properly; (2) Start the data instance, load the database, and open it;

Connection string: connect common user name/password @ serverIP/servicename
Use Cases: This is the most common scenario for common oracle Applications. Most web applications generally use common accounts to remotely access the oracle server to read and write data.
5. Special characters in the password. As mentioned earlier, the client determines the method used to log on to the server based on the connection string. The connection string has a specific syntax format and special characters, such /,@, ". For oracle account names, avoid these special characters in names to prevent conflicts. For the account password, many people like to use special characters. Once the password contains/, @, ", it will inevitably lead to a syntax conflict with the logon string. The following are several examples.
(1) password p @ ssw0rd if the connection string is connect username/p @ ssw0rd at this time, @ will be treated as a keyword, and ssw0rd will be treated as a listening address, resulting in an error. The solution is to enclose the password with double quotation marks. Connect username/"p @ ssw0rd ".
(2) The password 1/2 method is the same as above, connect username/"1/2 ".
(3) password 1 "2 the password contains double quotation marks. I have never seen it before, and I do not know how to create it. Please kindly advise.
When oracle parses the connection string, if the user name is not surrounded by quotation marks, it is automatically converted to uppercase characters, while the password remains unchanged. (4) Note that the metacharacter escape sqlplus of the platform shell allows you to directly provide user name and password parameters on the cui interface of the operating system for logon. For example, sqlplus username/password @ server/service must be escaped when the password contains bash metacharacters or enclosed by quotation marks. Take bash as an example. When the above password is p @ ssw0rd, it must be written as sqlplus username/\ "p @ ssw0rd \" because "itself is a metacharacter in bash, to pass "itself as a part of the parameter to sqlplus, it must be escaped.
Finally, we recommend a OracleDBA book, which clearly describes many Oracle terms and principles, but the translation is a little rough. We recommend that you download the English version for better understanding. Download link for English version: http://download.csdn.net/detail/smstong/7534001.