Topic: Understand the network structure of Oracle and solve connection problems

I have seen many people talking about issues such as TNS or the inability to log on to the database. I just summarized the following.
First, the network structure of Oracle can be combined with encryption, LDAP, and so on .. We will not discuss it here, focusing on the basic network structure, which is the most common situation.

Three configuration files
Listener. ora, sqlnet. ora, and tnsnames. ora are all stored in the $ ORACLE_HOME/Network/Admin directory.

 

Key points: Functions and usage of the three files
#-----------------------
Sqlnet. ora ----- functions similar to Linux or other UNIX nsswitch. conf files. This file determines how to find a connection string that appears in the connection,
For example, we enter
Sqlplus sys/Oracle @ orcl
Assume that my sqlnet. ora is like the following:

Sqlnet. authentication_services = (CNT)
Names. directory_path = (tnsnames, hostname)

Then, the client will first be in tnsnames. orcl records in the ora file. if there is no corresponding record, try to use orcl as a host name, resolve its IP address through the network, and then connect to the instance global_dbname = orcl on this IP address, of course, here, orcl is not a host name.
If I look like this

Names. directory_path = (tnsnames)

Then the client will only search for orcl records from tnsnames. ora
There are other options in brackets, such as LDAP, which are not commonly used.

#------------------------
Tnsnames. ora ------ this file is similar to the UNIX hosts file. It provides the corresponding tnsname to the host name or IP address, only when the file is similar
Names. directory_path = (tnsnames), that is, the client will try to use this file only when tnsnames is in the order in which the client parses the connection string.

In this example, there are two local IP addresses corresponding to orcl and another IP address corresponding to sales, which also defines whether to use the master server or the Shared Server Mode for connection.

 

# Enter tnsname when you want to connect
Orcl =
(Description =
(Address_list =
# The host, port, and Protocol corresponding to this tnsname
(Address = (Protocol = TCP) (host = 127.0.0.1) (Port = 1521 ))
)
(CONNECT_DATA =
# Use the dedicated server mode to connect to the server. The connection mode must match the server mode. If not, the connection is based on the server mode.
# Automatic Adjustment
(Server = dedicated)
# Corresponding SERVICE_NAME, sqlplus>; show parameter SERVICE_NAME;
# View
(SERVICE_NAME = orcl)
)
)
# The following is similar
Sales =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.188.219) (Port = 1521 ))
)
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = Sales)
)
)

#----------------------
When the client is finished, let's look at the server.
Listener. ora ------ listener process configuration file
The listener process is not much said. The listener process accepts remote database access requests and transfers them to the Oracle server process. Therefore, if you do not use a remote connection, the listener process is not required. Similarly, disabling the listener process does not affect the existing database connection.

Example of listener. ora File

# Listener. ora network configuration file: # E:/Oracle/product/10.1.0/db_2/Network/admin/listener. ora
# Generated by Oracle configuration tools.

# The following defines the instance for which the listener process provides services
# Here Is orcl, And it corresponds to ORACLE_HOME and global_dbname
# Global_dbname is not required unless you use hostname for database connection
Sid_list_listener =
(Sid_list =
(Sid_desc =
(Global_dbname = boway)
(ORACLE_HOME = E:/Oracle/product/10.1.0/db_2)
(Sid_name = orcl)
)
)
# Listener name. A database can have more than one listener.
# Next we will talk to the listener about the protocol, IP address, port, etc. Here we use the tcp1521 port and use the # Host Name
Listener =
(Description =
(Address = (Protocol = TCP) (host = boway) (Port = 1521 ))
)

The above example is the simplest, but also the most common. A listener process provides services for an instance (SID.
Listener Operation Command
$ ORACLE_HOME/bin/LSNRCTL start, others such as stop and status. After you have typed an LSNRCTL, you can view the help information.

The three files mentioned above can be configured through the graphical Configuration tool.
$ ORACLE_HOME/netca wizard
$ ORACLE_HOME/netmgr
I am used to netmgr,

The profile is configured with sqlnet. ora, that is, the name resolution method.
The service name is configured with the tnsnames. ora file.
Listeners configures the listener. ora file, that is, the listener process.
For more information about the configuration, see the configuration file.

In this way, the overall structure is available. Yes
When you enter sqlplus sys/Oracle @ orcl
1. query sqlnet. ora and check the name resolution method. It is tnsname.
2. query the tnsnames. ora file, find the orcl record from the file, and find the host name, port, and SERVICE_NAME.
3. If there is no problem with the listener process, establish a connection with the listener process.
4. Depending on different server modes, such as dedicated server mode or shared server mode, listener takes the next action. The default mode is dedicated server. If there is no problem, the client will connect to the server process of the database.
5. At this time, the network connection has been established, and the historical mission of the listener process has been completed.

#---------------
Commands used for several connections
1. sqlplus/As sysdba this is a typical operating system authentication and does not require the listener process
2. sqlplus sys/Oracle can only connect to the local database, and the listener process is not required.
3. sqlplus sys/Oracle @ orcl requires the listener process to be available. The most common connection is through a network.

The preceding connection method does not require the database to be available, but does not require the database to be available for operating system authentication, because the common user is a database authentication, therefore, the database must be in the open state.

 

Then
#-------------
Usually troubleshooting may be used
1. LSNRCTL status check the status of the listener process on the server
LSNRCTL>; Help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
Start Stop status
Services version reload
Save_config trace change_password
Quit exit Set *
Show *
LSNRCTL>; Status
: Em11:
2. Check whether the sqlnet. ora and tnsname. ora files on the client are correctly configured and the status of the listener process on the corresponding server.
C:/>; tnsping orcl

TNS Ping utility for 32-bit windows: Version 10.1.0.2.0-production on 16-8 months-
09:36:08 2005

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

Used parameter files:
E:/Oracle/product/10.1.0/db_2/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 = (Server = dedicated) (Service _
Name = orcl )))
OK (20 msec)

3.
SQL>; show SGA check whether the instance has been started

SQL>; select open_mode from V $ database; check whether the database is on or on.
Open_mode
----------
Read Write

#-----------------
Example of using hostname to access the database rather than tnsname

Using tnsname to access the database is the default method, but it also brings about some problems, that is, the client needs to configure the tnsnames. ora file. If the address of your database server changes, you need to re-edit the file on the client. Accessing the database through hostname does not cause this problem.

Need to modify
Server-side listener. ora
# Listener Configuration File listener. ora
# Using host naming, The tnsname. ora file is no longer required for local Parsing
# Listener. ora network configuration file: D:/Oracle/product/10.1.0/db_1/Network/admin/listener. ora
# Generated by Oracle configuration tools.

Sid_list_listener =
(Sid_list =
(Sid_desc =
# (Sid_name = plsextproc)
(Sid_name = orcl)
(Global_dbname = boway)
(ORACLE_HOME = D:/Oracle/product/10.1.0/db_1)
# (Program = EXTPROC)
)
)

Listener =
(Description_list =
(Description =
(Address = (Protocol = IPC) (Key = EXTPROC ))
)
(Description =
(Address = (Protocol = TCP) (host = boway) (Port = 1521 ))
)
)

If the client sqlnet. ora does not use tnsname for access, remove tnsnames.
# Sqlnet. ora network configuration file: D:/Oracle/product/10.1.0/db_1/Network/admin/sqlnet. ora
# Generated by Oracle configuration tools.

Sqlnet. authentication_services = (CNT)

Names. directory_path = (hostname)

The tnsnames. ora file does not need to be configured, and deletion does not matter.
The following is a problem with the network and operating system configuration. How can I resolve my host name?
You can connect through the following method:
Sqlplus sys/Oracle @ boway
In this case, the boway server will be connected and the listener will be used to determine the SERVICE_NAME you want to connect.