How to configure Oracle client installation and remote connection

1. Communication between the Oracle client and the server

1. Protocol lenet Protocol

As shown in, Oracle uses the Oracle Net protocol to connect the client to the server and transmit data. OracleNet is a software layer residing on both the Oracle database server and the client. It encapsulates the TCP/IP protocol and is responsible for establishing and maintaining the connection between the client application and the database server.

As shown in, requests sent by the client are first converted to information that can be transmitted over the network through the OracleNet protocol, and the requests are transmitted to the database server over the TCP/IP network; after receiving a request from a customer, the server needs to convert the request to a local command that can be interpreted and executed by the database through the OracleNet protocol, and then execute the command on the server (mainly some input and output operations ), the results are transmitted to the client through TCP/IP and OracleNet.

2. connection process between the client and the server

Before analyzing the connection mechanism between the client and the server, you must define two concepts: An Oracle listener and an Oracle network service name.

The Oracle database server receives connection requests from the client through a component named "monitoring lenet listener. A listener is a backend process on the server. The connection request sent to the database client is first listened by the server listener and forwarded to the corresponding database instance, to establish a connection between the client and the server. After the connection is established, the client and the server can communicate directly without the listener.

To listen to customer requests, the listener must be configured, including the listening port, the Global Database Name of the database where the listener is located, and the database instance.

The Oracle network service name is an identifier, which indicates the configuration information of the Client Connection server (actually the content of the connection request ), including the Database Host address, listening port, and Global Database Name (for details about global databases and database instances, see Oracle database installation ).

The connection process between the client and the server is as follows:

(1) first, there is a resident listener on the server (the listener service must be opened) Listening for connection requests sent by the client.
(2) The user enters the user name, password, and network service name on the client (Enterprise Server or SQL tool, or enter a request similar to "CONNECTusername/password @ net_Service_name" in the SQL command line.
(3) Check the network service configuration file tnsname. ora on the client and map the network service name to the connection descriptor containing the Oracle server address, listening port, and global database name.
(4) The client locates the listener Based on the connection Descriptor and transmits the connection information to the listener through the network.
(5) the listener queries the listener. ora configuration file and finds the database server to be connected.
(6) communication starts between the client and the server.

Ii. Listener Configuration

1. Add and configure listeners

As shown in, use the Oracle Net Configuration Asistant tool to add and configure the server listener:

After the listener is configured, the database system automatically starts the listener.

After adding and configuring listeners, you can also use the Oracle NetManager tool to view and modify listener configurations, such as the listener location and the global database name served by the listener. As shown in:

For this installation, the listener Configuration information is recorded in the E: \ Oracle_Server \ oracle \ ora92 \ network \ admin \ listener. ora file. Its main content is as follows:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = tsinghua-p5z 3mm) (PORT = 1521 ))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E: \ Oracle_Server \ oracle \ ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = mydb. bawei)
(ORACLE_HOME = E: \ Oracle_Server \ oracle \ ora92)
(SID_NAME = mydb)
)
)

3. Install client tools and configure network services

You can install client tools on other machines different from the Database Host, and connect to the database server by configuring the client network service. The Client installation and configuration process is shown in (this installation installs the client in a different directory of the same machine as the database to simulate remote access to the database ):

After installing the client and configuring the client network service name, you can log on to and access the remote database server from the client tool. For this installation, the client network service configuration is recorded in the E: \ Oracle_Client \ oracle \ ora92 \ network \ admin \ tnsnames. ora file. Its main content is as follows:

REMOTEDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.10.6) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb. bawei)
)
)

In this way, when the network service name remotedb is entered in the client tool, the information such as the database address, port, and global database name will be parsed and sent together with the entered user name and password to the server, received by the listener and compared with the listener configuration file, the request is forwarded to the corresponding database instance for processing.

The client network service can be modified using the Oracle Net Manager tool of the client, or a new client network service name can be added.

It should be noted that:

The Connection Mechanism between client tools (SQL PLUS, Enterprise Management Console, etc.) on the server and Database Service is the same as that of remote clients.
After the Oracle database is installed, client tools such as SQLPLUS are installed locally on the database server by default, and E is in the same location as the listening configuration file: the network service connection configuration file tnsnames is provided by default in the \ Oracle_Server \ oracle \ ora92 \ network \ admin folder. ora contains a network service name configuration with the same name as the database instance SID, including the host name or IP address of the database, the listening port, and the database service name (Global Database Name. For this installation, the main content of the tnsnames. ora file is as follows:

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = tsinghua-p5z 3mm) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb. bawei)
)
)

When using the client tool on the database server, it is the same as connecting to the server from a remote client, search for tnsnames Based on the entered username, password, and network service name (or an SQL command line similar to CONNECTusername/password @ net_Service_name. find the network connection service name that is equal to net_Service_name, and obtain the Database Host address, listening port, global database name, and other information.

The request information is also transmitted to the listening port. The listener then sends the request information to the listener in the E: \ Oracle_Server \ oracle \ ora92 \ network \ admin folder. ora listener Configuration File comparison, if listener. if the ora Listener Configuration File contains matched items, the request is forwarded to the corresponding database instance.

It can be seen that the client tool on the server performs the same process as the database service and the remote client connection.
In fact, the client tool on the server is through tnsnames on the server side. ora and listener. ora verifies and establishes a connection. The remote client uses tnsnames of the client. ora and listener on the server. ora performs verification and establishes a connection.