Troubleshooting Oracle Net operating, configuration, and connectivity issues

One, Oracle NET configuration file

Oracle NET is a software layer that supports conversions between different network protocols. Different physical machines can use this software layer to communicate with each other, specifically to enable remote access to Oracle.

The Oracle NET configuration files include Listener.ora,tnsnames.ora,sqlnet.ora and Ldap.ora, and the configuration files are placed in the $oracle_home\network\admin directory.

• Sqlnet.ora file on the client, determine how to parse, through this file to determine how to find a connection in the connection string appears
• Tnsnames.ora file on the client, record each Oracle net alias corresponding to the host and Oracle instance;
• Listener.ora works on the database server side, and is responsible for monitoring client connection requirements that want to access the Oracle database over the network;

1.1 Sqlnet.ora

Sqlnet.ora is used to determine how Oracle parses a string that appears in a connection.

# Sqlnet.ora Network Configuration file:c:\app\oracle\product\12.1.0\dbhome_1\network\admin\sqlnet.ora# Generated by Oracle configuration tools.# This file was actually generated by NETCA. If customers choose to # Install ' software only ', this file wont exist and without the native # authentication, they w Ill not being able to connect to the database on NT. Sqlnet. Authentication_services= (NTS) NAMES. Directory_path= (TNSNames, Ezconnect)

View Code

For example: Sqlplus sys/[email protected] If your Sqlnet.ora is configured like this:

Sqlnet. Authentication_services= (NTS) NAMES. Directory_path= (Tnsnames,hostname)

Then, the client will first find the ORCL record in the Tnsnames.ora file. If there is no corresponding record, try to use ORCL as a hostname, through the network to resolve its IP address and then to connect the IP GLOBAL_DBNAME=ORCL this instance,

If that's the way it is

NAMES. Directory_path= (TNSNames)

Then the client will only look for ORCL records from Tnsnames.ora.

1.2 Tnsnames.ora

This file is similar to the Hosts file for Unix, where the Tnsnames.ora file on the client records each Oracle NET alias corresponding to the host and Oracle instance, and only if Sqlnet.ora is similar to names.directory_path= ( TNSNames) This is when the client resolves the connection string in the order that it has tnsnames, and the Oracle Net service resolves the user-specified service name to the corresponding server host and DB instance according to Tnsnames.ora.

# Tnsnames.ora Network Configuration file:c:\app\oracle\product\12.1.0\dbhome_1\network\admin\tnsnames.ora# Generated by Oracle configuration tools. LISTENER_ORCL = (  ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521)) Oraclr_connection_data =  (DESCRIPTION =    (address_list = (      ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))    )    (Connect_data =      (SID = Clrextproc)      (PRESENTATION = RO)    )  ORCL =  (DESCRIPTION = (    ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))    (Connect_data =      (SERVER = dedicated)      (service_name = ORCL)    )  ) PDBORCL =    (DESCRIPTION = (      ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))      (Connect_data =        (SERVER = dedicated)        (service_name = PDBORCL)      )  )

View Code1.3 Listener.ora

The configuration file for the listener listener process, which works on the database server side, accepts remote access requests to the database and forwards the server process to Oracle.

# Listener.ora Network Configuration file:c:\app\oracle\product\12.1.0\dbhome_1\network\admin\listener.ora# Generated by Oracle configuration tools. Sid_list_listener =  (sid_list = (    Sid_desc =      (sid_name = Clrextproc)      (oracle_home = C:\app\ORACLE\ Product\12.1.0\dbhome_1)      (program = extproc)      (Envs = "extproc_dlls=only:c:\app\oracle\product\12.1.0\ Dbhome_1\bin\oraclr12.dll ")    )  LISTENER =  (description_list = (    DESCRIPTION = (      ADDRESS = ( PROTOCOL = IPC) (KEY = EXTPROC1521))      (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))    )  

View Code1.4 Graphical Configuration

Second, the connection process and working principle

The process of accessing a database consists of client processes and server processes. The client process initiates a request, such as Sqlplus; the server process reads and writes the database, completing various commands submitted by the user. A server process is also called a shadow process, and the server process runs on the same machine as the DB instance. The server-side listener works according to the profile Listener.ora, which is responsible for link the client process and the shadow process as a bridge.

The start and stop of Oracle Net is independent of the database instance, it is only responsible for the request from the client process to the shadow process, the shadow process to complete the database instance read and write.

When two processes are running on the same machine, Oracle uses inter-process (IPC)-based local communication, and when two processes are running on different machines, Oracle uses Oracle NET to implement network communication between the two processes.

2.1 Connection Process

When the client program makes a request string Username/[email Protected]_service_name, the Oracle NET component first locates the Sqlnet.ora file locally to determine the naming method, which is assumed to be local naming, Then the contents of Sqlnet.ora are as follows: NAMES. Directory_path= (tnsnames). Oracle net immediately finds Tnsnames.ora, reads the file, and matches net_service_name. If the Net_service_name match fails, the error is indicated; If successful, send the correct listener.listener according to the Protocol,host,port information in Connect descriptor and then according to the Service_ in connect Descritpor Name is compared to the Oracle services registered with it. If the comparison succeeds, the connection is established, otherwise the error is indicated.

2.2 Kinds of command forms used in connection

1.sqlplus/as SYSDBA This is a typical operating system certification and does not require a listener process

2.sqlplus Sys/oracle This connection method can only connect to the native database, also does not need to listener process

3.sqlplus Sys/[email Protected] This approach requires the listener process to be in a usable state.  Most commonly connected over the Internet. The above connection mode uses the SYS user or other user authentication through the password file does not need the database to be available, the operating system authentication also does not need the database to be available, the ordinary user because is the database authentication, therefore the database must be in the open state.

Iii. diagnosing and resolving client connectivity issues 3.1 connection problem diagnosis process

• Verify that customers can contact the server
• Determine the network path that customers take to large servers
• Verify the local naming configuration file
• Check the multi-client network configuration file
• Check the network file location (if using Tnsname.ora)
• Check Names.directory_path parameters
• Check Names.default_domain parameters
• Check for client protocol adapters that are installed
• Check for any common client error codes

Check client/server contact

Ping

Determine the network route that the customer is using to reach the server

TRACERT command

Check Customer/Listener Contact

Using tnsping, it tries to contact an Oracle listener. It confirms that the customer can contact the listener, but does not confirm that it actually connects to the Oracle server. The tool can also display the time it takes to contact the listener. Followed by a number can tell it the number of attempts to connect. Example: Tnsping ORCL 3

Check the local named profile to check the client's network configuration file

Usually customers only have a copy of the network files (such as Tnsnames.ora and Sqlnet.ora), but if the machine has a lot of Oracle software installed, there may be multiple network files working, need to ensure that these files are consistent, so as not to produce unexpected results.

Check Network File location

If the network file is not in the directory specified by the TNS_ADMIN environment variable, Oracle will not find these files

Check Names.directory_path

Check that the parameters in the Sqlnet.ora file are set correctly, and that the name interpretation is in the correct order of precedence.

Check Names.default_domain

If the parameter is set, such as "vicp.net", when the input sqlplus Sys/[email protected], the name is appended to the default domain name and becomes "Sqlplus sys/[email protected". If the correct service name is only prod, then of course it can't be explained correctly.

Check client-side code 3.2 common error codes for customer agreement adapter Check

• ORA-12154--It's possible tnsnames.ora put it in an incorrect position.
• ORA-12198 and ORA-12203--indicates that Tnsnames.ora is not able to be contacted for the services listed. It is possible to specify an incorrect service name.
• ORA-12533--Indicates that the configuration address is incorrect
• ORA-12541--Indicates that the appropriate listener was not found on the specified port.

Reference

Oracle NET configuration and how it works

Oracle NET Services

Troubleshooting Oracle Net operating, configuration, and connectivity issues