Resolve connectivity issues in Oracle network architecture
- 2020-05-13 03:44:02
- OfStack
The ORACLE tutorial you are looking at is: resolve connectivity issues in Oracle network architecture. Recently, I have seen many people talk about tns or the database can not be logged in, so I simply summarized the following documents.
First of all, the network structure of Oracle, reciprocating miscellaneous said can add encryption, LDAP and so on. I'm not going to talk about it here, but I'm going to focus on the basic network architecture that we use most often
Three configuration files
listener.ora, sqlnet.ora, tnsnames.ora are all in the $ORACLE_HOME\network\admin directory.
Key points: the role and use of three files
# -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
sqlnet.ora -- nsswitch.conf, which is similar to linux or other unix files, determines how to find the connection string in a connection.
For example, we enter the client
sqlplus sys/oracle @ orcl
Let's say my sqlnet.ora looks like this
SQLNET. AUTHENTICATION_SERVICES = (NTS)
NAMES. DIRECTORY_PATH = (TNSNAMES HOSTNAME)
Then, the client will first look for the record of orcl in the tnsnames.ora file. If there is no corresponding record, it will try to use orcl as a hostname, parse its ip address through the network, and then connect to the instance of GLOBAL_DBNAME=orcl on ip, of course, orcl is not a hostname here
If I look like this
NAMES. DIRECTORY_PATH = (TNSNAMES)
The client will only look up orcl records from tnsnames.ora
There are other options in parentheses, such as LDAP, that are not commonly used.
# -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
Tnsnames.ora -- this file is similar to unix's hosts file, providing tnsname to the hostname or ip, only if sqlnet.ora is similar
NAMES.DIRECTORY_PATH = (TNSNAMES) so that the client has TNSNAMES in the order in which it parses the connection string, it will attempt to use this file.
In the example, there are two local machines corresponding to ORCL and another IP address corresponding to SALES, which also define the connection mode of master server or Shared server
Enter TNSNAME when you want to connect
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
Below is the host, port, and protocol for TNSNAME
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
Connecting using the dedicated server mode matches the server's mode, if not, according to the server's mode
Automatic adjustment
(SERVER = DEDICATED)
# corresponds to service_name, SQLPLUS > show parameter service_name;
# view
(SERVICE_NAME = orcl)
)
)
The bottom one is similar
SALES =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.188.219)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sales)
)
)
# -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
That's the end of the client so let's look at the server side
listener.ora -- configuration file for listener listener process
Not to mention the listener process, which accepts remote access requests to the database and forwards them to the oracle server process. So if you are not using a remote connection, the listener process is not required, and similarly if you close the listener process you will not affect an existing database connection.
Example of Listener.ora file
ora Network Configuration File: # oracle Configuration File: # oracle\ 10.1.0\Db_2\NETWORK\ADMIN\ listener.ora
# Generated by Oracle configuration tools
The following defines which instance the LISTENER process serves
# here is ORCL, and it corresponds to ORACLE_HOME and GLOBAL_DBNAME
GLOBAL_DBNAME is not required unless you use HOSTNAME to make a 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, 1 database can have more than 1 listener
The protocol that # listens to below is the listener, ip, port, etc., tcp1521 port is used here, and # USES the host name
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = boway)(PORT = 1521))
)
The example above is one of the simplest, but also the most common. One listener process serves one instance(SID).
The listener's action command
$ORACLE_HOME/bin/lsnrctl start, others such as stop,status, etc. See help after typing 1 lsnrctl.
All three files mentioned above can be configured using the graphical configuration tool
$ORACLE_HOME/netca wizard
$ORACLE_HOME/netmgr
I'm used to netmgr,
profile is configured with sqlnet.ora is the name resolution method
service name is configured with the tnsnames.ora file
listeners is configured with the listener.ora file, which is the listener process
For the specific configuration, try 1 and then look at the 1 configuration file.
So that gives you the overall structure of 1, which is when you type sqlplus sys/oracle @orcl
1. Query sqlnet.ora to see how the name is resolved, and it turns out to be TNSNAME
2. Query the tnsnames.ora file, find the records of orcl, and find the hostname, port, and service_name
3. If there is no problem with the listener process, establish a connection to the listener process.
4. According to different server modes, such as dedicated server mode or Shared server mode, listener takes the following actions. The default is dedicated server mode. If there is no problem, the client will connect to server process of the database.
5. The network connection has been established and the historical mission of the listener process is complete.
For the above connection mode, sys users or other users authenticated by password files do not need the database to be available, nor does the operating system authentication need the database to be available. For ordinary users, the database must be in the open state because it is the database authentication.
And then there is
# -- -- -- -- -- -- -- -- -- -- -- -- --
It may be used in normal troubleshooting
1.lsnrctl status view the status of the server-side listener process
LSNRCTL > help
The following operations are available
An asterisk (*) denotes modifier or extended command:
start stop status
services version reload
save_config trace change_password
quit exit set *
show *
LSNRCTL > status
2.tnsping check whether the client sqlnet.ora and tnsname.ora files are configured correctly, 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-august -
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 check to see if instance is started
SQL > select open_mode from v $database; Check whether the database is open or in the mount state.
OPEN_MODE
----------
READ WRITE
# -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Example of accessing a database using hostname instead of tnsname
Using tnsname to access the database is the default, but it also presents a problem that the client needs to configure the tnsnames.ora file. If your database server address changes, you will need to edit the client file again. Accessing the database via hostname eliminates this hassle.
Need to modify
Server side listener.ora
The configuration file listener.ora for the listener
host naming no longer requires the tnsname.ora file for local parsing
ora Network File: d:\oracle\ 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))
)
)
The client sqlnet.ora can be removed if it is confirmed that TNSNAME access will not be used
# sqlnet.ora Network File: d:\oracle\ 10.1.0\db_1\NETWORK\ADMIN\ sqlnet.ora
# Generated by Oracle configuration tools
SQLNET. AUTHENTICATION_SERVICES = (NTS)
NAMES. DIRECTORY_PATH = (HOSTNAME)
The Tnsnames.ora file does not need to be configured or deleted.
The following is the network and operating system configuration problem, how to resolve my host name problem
You can connect in the following way
sqlplus sys/oracle @ boway
In this case, the server boway will be connected, and listener will determine which service_name you want to connect to.
First of all, the network structure of Oracle, reciprocating miscellaneous said can add encryption, LDAP and so on. I'm not going to talk about it here, but I'm going to focus on the basic network architecture that we use most often
Three configuration files
listener.ora, sqlnet.ora, tnsnames.ora are all in the $ORACLE_HOME\network\admin directory.
Key points: the role and use of three files
# -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
sqlnet.ora -- nsswitch.conf, which is similar to linux or other unix files, determines how to find the connection string in a connection.
For example, we enter the client
sqlplus sys/oracle @ orcl
Let's say my sqlnet.ora looks like this
SQLNET. AUTHENTICATION_SERVICES = (NTS)
NAMES. DIRECTORY_PATH = (TNSNAMES HOSTNAME)
Then, the client will first look for the record of orcl in the tnsnames.ora file. If there is no corresponding record, it will try to use orcl as a hostname, parse its ip address through the network, and then connect to the instance of GLOBAL_DBNAME=orcl on ip, of course, orcl is not a hostname here
If I look like this
NAMES. DIRECTORY_PATH = (TNSNAMES)
The client will only look up orcl records from tnsnames.ora
There are other options in parentheses, such as LDAP, that are not commonly used.
# -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
Tnsnames.ora -- this file is similar to unix's hosts file, providing tnsname to the hostname or ip, only if sqlnet.ora is similar
NAMES.DIRECTORY_PATH = (TNSNAMES) so that the client has TNSNAMES in the order in which it parses the connection string, it will attempt to use this file.
In the example, there are two local machines corresponding to ORCL and another IP address corresponding to SALES, which also define the connection mode of master server or Shared server
Enter TNSNAME when you want to connect
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
Below is the host, port, and protocol for TNSNAME
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
Connecting using the dedicated server mode matches the server's mode, if not, according to the server's mode
Automatic adjustment
(SERVER = DEDICATED)
# corresponds to service_name, SQLPLUS > show parameter service_name;
# view
(SERVICE_NAME = orcl)
)
)
The bottom one is similar
SALES =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.188.219)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sales)
)
)
# -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
That's the end of the client so let's look at the server side
listener.ora -- configuration file for listener listener process
Not to mention the listener process, which accepts remote access requests to the database and forwards them to the oracle server process. So if you are not using a remote connection, the listener process is not required, and similarly if you close the listener process you will not affect an existing database connection.
Example of Listener.ora file
ora Network Configuration File: # oracle Configuration File: # oracle\ 10.1.0\Db_2\NETWORK\ADMIN\ listener.ora
# Generated by Oracle configuration tools
The following defines which instance the LISTENER process serves
# here is ORCL, and it corresponds to ORACLE_HOME and GLOBAL_DBNAME
GLOBAL_DBNAME is not required unless you use HOSTNAME to make a 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, 1 database can have more than 1 listener
The protocol that # listens to below is the listener, ip, port, etc., tcp1521 port is used here, and # USES the host name
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = boway)(PORT = 1521))
)
The example above is one of the simplest, but also the most common. One listener process serves one instance(SID).
The listener's action command
$ORACLE_HOME/bin/lsnrctl start, others such as stop,status, etc. See help after typing 1 lsnrctl.
All three files mentioned above can be configured using the graphical configuration tool
$ORACLE_HOME/netca wizard
$ORACLE_HOME/netmgr
I'm used to netmgr,
profile is configured with sqlnet.ora is the name resolution method
service name is configured with the tnsnames.ora file
listeners is configured with the listener.ora file, which is the listener process
For the specific configuration, try 1 and then look at the 1 configuration file.
So that gives you the overall structure of 1, which is when you type sqlplus sys/oracle @orcl
1. Query sqlnet.ora to see how the name is resolved, and it turns out to be TNSNAME
2. Query the tnsnames.ora file, find the records of orcl, and find the hostname, port, and service_name
3. If there is no problem with the listener process, establish a connection to the listener process.
4. According to different server modes, such as dedicated server mode or Shared server mode, listener takes the following actions. The default is dedicated server mode. If there is no problem, the client will connect to server process of the database.
5. The network connection has been established and the historical mission of the listener process is complete.
# -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Several types of commands are used for connections
sqlplus/as sysdba this is a typical operating system authentication and does not require the listener process
2.sqlplus sys/oracle this connection can only connect to the native database, again no listener process is required
[1] [2] next page
The ORACLE tutorial you are looking at is: resolving connectivity issues in Oracle network architecture. 3.sqlplus sys/oracle @orcl this approach requires the listener process to be available. The most common connection is through the Internet.For the above connection mode, sys users or other users authenticated by password files do not need the database to be available, nor does the operating system authentication need the database to be available. For ordinary users, the database must be in the open state because it is the database authentication.
And then there is
# -- -- -- -- -- -- -- -- -- -- -- -- --
It may be used in normal troubleshooting
1.lsnrctl status view the status of the server-side listener process
LSNRCTL > help
The following operations are available
An asterisk (*) denotes modifier or extended command:
start stop status
services version reload
save_config trace change_password
quit exit set *
show *
LSNRCTL > status
2.tnsping check whether the client sqlnet.ora and tnsname.ora files are configured correctly, 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-august -
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 check to see if instance is started
SQL > select open_mode from v $database; Check whether the database is open or in the mount state.
OPEN_MODE
----------
READ WRITE
# -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Example of accessing a database using hostname instead of tnsname
Using tnsname to access the database is the default, but it also presents a problem that the client needs to configure the tnsnames.ora file. If your database server address changes, you will need to edit the client file again. Accessing the database via hostname eliminates this hassle.
Need to modify
Server side listener.ora
The configuration file listener.ora for the listener
host naming no longer requires the tnsname.ora file for local parsing
ora Network File: d:\oracle\ 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))
)
)
The client sqlnet.ora can be removed if it is confirmed that TNSNAME access will not be used
# sqlnet.ora Network File: d:\oracle\ 10.1.0\db_1\NETWORK\ADMIN\ sqlnet.ora
# Generated by Oracle configuration tools
SQLNET. AUTHENTICATION_SERVICES = (NTS)
NAMES. DIRECTORY_PATH = (HOSTNAME)
The Tnsnames.ora file does not need to be configured or deleted.
The following is the network and operating system configuration problem, how to resolve my host name problem
You can connect in the following way
sqlplus sys/oracle @ boway
In this case, the server boway will be connected, and listener will determine which service_name you want to connect to.
Previous page [1] [2]