Oracle to understand the network structure to solve your connectivity problems

Mar 18 posted in Oracle (RSS), 140 views.

Recently, I came across a lot of people said tns or should not sign such a database problem, it simply sums up the following Dongdong.
First of all, the network structure for the oracle, to say the complex can be combined with encryption, LDAP and so on. . Not discussed here, with a focus on the basic structure of the network is our most commonly used such a situation

Three configuration files
listener.ora, sqlnet.ora, tnsnames.ora, are on the $ ORACLE_HOME \ network \ admin directory.

Focus: the role of the three documents and the use of
#-----------------------
sqlnet.ora----- similar to linux or other unix's nsswitch.conf file, the adoption of the document to determine how to find a connection in the connection string there,
For example our client input
sqlplus sys / oracle @ orcl
If my sqlnet.ora like this are the following

SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH = (TNSNAMES, HOSTNAME)

In that case, the client will be first in the tnsnames.ora file to find the records of orcl. If there is no corresponding record is trying to put a host name as orcl, the way to go through the network to resolve its ip address ip then go on to connect this GLOBAL_DBNAME = orcl this instance, of course, orcl me here is not a host name like this if I were

NAMES.DIRECTORY_PATH = (TNSNAMES)

Then the client will only search from the tnsnames.ora records in parentheses orcl have other options, such as LDAP, etc. are not commonly used.

#------------------------
Tnsnames.ora------ This document is similar to the hosts file unix, tnsname provided to the host name or ip counterparts, and only when similar sqlnet.ora
NAMES.DIRECTORY_PATH = (TNSNAMES) In this way, that is, to resolve the client connection string has the sequence are TNSNAMES will try to use the document.

There are two examples, ORCL local counterparts, SALES another corresponding IP address, also the definition of inside with the use of the main server or shared server mode for connectivity, a sentence that

# You have to enter a time to connect TNSNAME
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
# The following is the corresponding TNSNAME host, port, protocol
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
)
(CONNECT_DATA =
# Using a dedicated server mode to connect the required pattern matching with the server, if there is no server on the basis of the model
# Automatic adjustment
(SERVER = DEDICATED)
# Corresponding service_name, SQLPLUS>; show parameter service_name;
# To view
(SERVICE_NAME = orcl)
)
)
# The following similar
SALES =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.188.219) (PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sales)
)
)

#----------------------
Look at the client end of our server-side
listener.ora------ listener process listener configuration file on the listener process that many do not accept remote access to the database and transferred to the oracle application server process. If it is not so used to connect remote, listener process is not necessary if the same listener process and turn off will not affect the already existing database connection.

Examples of documents Listener.ora

# listener.ora Network Configuration File: # E: \ oracle \ product \ 10.1.0 \ Db_2 \ NETWORK \ ADMIN \ listener.ora
# Generated by Oracle configuration tools.

# LISTENER process definition for the following examples which are here to provide services # ORCL, and it corresponds to the ORACLE_HOME and GLOBAL_DBNAME
# One of GLOBAL_DBNAME unless the use is not necessary to do HOSTNAME 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)
)
)
# Name of listener, a database can have more than one listener
# The following is a listener to monitor the protocol, ip, port, etc., where the use of tcp1521 ports, and to # use the host name
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = boway) (PORT = 1521))
)

The above example is one of the most simple example, but it is also the most common. The process of a listener to a instance (SID) to provide services.
Monitor the operation command
$ ORACLE_HOME / bin / lsnrctl start, others, such as stop, status and so on. Lsnrctl after a specific look Knock-out assist.

The three documents mentioned above can be configured through the graphical configuration tool to achieve
$ ORACLE_HOME / netca Wizard form of
$ ORACLE_HOME / netmgr
I am more accustomed to netmgr,

profile are the sqlnet.ora configuration that is the way name resolution
service name is configured tnsnames.ora file
listeners are the listener.ora configuration file, that is, a specific listener configuration process will try my best then look at the profile.

This will have a general structure is
When you enter sqlplus sys / oracle @ orcl time
1. Sqlnet.ora query to see the name of the analytic methods are found TNSNAME
2. Tnsnames.ora file query, from the inside looking orcl records, and to find the host name, port and service_name
3. If the listener does not question the process, then set up the connection with the listener process.
4. According to a different server models such as dedicated server mode or shared server mode, listener action to take next. Are a dedicated server mode by default, there is no problem if the client is connected to the database on the server process.
5. This time has been set up network connection, listener process will finish the mission.

#---------------
Used to connect several order the form of
1.sqlplus / as sysdba This is a typical operating system authentication, the process does not need listener
2.sqlplus sys / oracle such connections can only connect the local database, the same listener process does not require
3.sqlplus sys / oracle @ orcl required in this way can be used in the process of status listener. The most common network connection.

Connections over the use of sys user password or other documents necessary to verify the user is not available in the status database, the operating system does not require certification database is available, because the database user authentication, so the database is in open status necessary.

And that is
#-------------
Normal may be used in troubleshooting
1. lsnrctl status check server-side listener process status
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. sqlnet.ora client tnsping view and right tnsname.ora configuration file or not, and the corresponding server process status listener.
C: \>; tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 16-8 month --
2005 09:36:08

Copyright (c) 1997, 2003, 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 see whether the instance has been launched

SQL>; select open_mode from v $ database; view database status is open or mount.
OPEN_MODE
----------
READ WRITE

#-----------------
Access the database using the hostname rather than an example tnsname

Tnsname access the database used is the default mode, but also points that the client is required to configure the tnsnames.ora file. If your database server address changes on the client need to re-edit the document. Access the database through the hostname of the trouble there would be no.

Need to modify server side listener.ora
# Listener configuration file listener.ora
# Use the host naming files is no longer required to do local analysis tnsname.ora
# 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 sqlnet.ora client will not be used to confirm the visit TNSNAME, you can remove the 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 = (NTS)

NAMES.DIRECTORY_PATH = (HOSTNAME)

Do not need Tnsnames.ora configuration file, delete it Do not care.
The following is the configuration of network and operating system problem, how I can resolve the host name of the problem can be connected through the following
sqlplus sys / oracle @ boway
In that case, will be connected to the server boway and listener to determine what you want to connect the service_name.