Solve connection problems in Oracle Network Structure

The ORACLE tutorial is to solve the connection problem in the Oracle network structure. I have recently seen many people talking about issues such as tns or the inability to log on to the database. I simply summarized the following documents.
　　
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 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.
　　

[1] [2] Next page

The ORACLE tutorial is to solve the connection problem in the Oracle network structure. 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
　　
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.

Previous Page [1] [2]