Shulou(Shulou.com)06/01 Report--

This article mainly introduces what network configuration files oracle has, which can be used for reference by interested friends. I hope you can learn a lot after reading this article.

In the network configuration of oracle, we often encounter these three configuration files of sqlnet.ora,tnsnames.ora,listener.ora, which can be summarized in order to facilitate the specific application.

1. Sqlnet.ora file: a nsswitch.conf file that acts like linux or other unix to determine how to find a connection string (connect descriptor) that appears in a connection. (sqlnet.ora can also restrict the IP of a specific client to restrict client access to the ORACLE database. As such a restriction, the client will report an ORA-12537 error when connecting to the database.)

If sqlnet.ora looks like this

NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)

When the client enters sqlplus sys/oracle@orcl, it will first look for the orcl record in the tnsnames.ora file. If there is no corresponding record, try to use orcl as a host name. Parse its ip address through the network and then connect to the instance of GLOBAL_DBNAME=orcl on this ip. Of course, here orcl is not a hostname.

If I look like NAMES.DIRECTORY_PATH= (TNSNAMES), then the client will only look up orcl records from tnsnames.ora. There are other options in parentheses, such as LDAP, etc., but HOSTNAME,LDAP is usually not commonly used, usually only TNSNAME.

2. Tnsnames.ora file: provide the correspondence from tnsname to hostname or ip (also the configuration file of client listener)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521)) # corresponding SOCKET information

(CONNECT_DATA =

(SERVER = DEDICATED) # use dedicated server mode to connect (server=shared sharing mode)

(SERVICE_NAME = orcl) # fill in the corresponding service_name here

)

SALES =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.188.219) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = sales)

)

)

To configure the service_name in tnsname.ora, you can view it with the following command:

SQL > show parameter service_name

NAME TYPE VALUE

-

Service_names string orcl

Tnsnames.ora is designed for oracle clients to access databases, not for remote clients to connect to oracle servers.

Delete tnsnames.ora

[oracle@oracle admin] $rm tnsnames.ora

Restart oracle and you will find that the local client cannot access the database

[oracle@oracle ~] $sqlplus scott/scott@orcl

SQL*Plus: Release 10.2.0.1.0-Production on Tue Feb 16 17:32:41 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

At the remote end (another machine), connect to the oracle database through sqlplus, no problem; connect to the orcle database through jdbc, no problem.

3. Listener.ora file: a server process that accepts remote access requests to the database and transfers them to oracle. So if you are not using a remote connection, the listener process is not required, and if you shut down the listener process, it will not affect the existing database connection. (server-side listener profile)

Listener.ora

# SID_LIST_LISTENER definition, which defines the LISTENER process listening SID

SID_LIST_LISTENER =

(SID_LIST = # you can listen to multiple SID, all in one SID table

(SID_DESC =

(GLOBAL_DBNAME = boway) # GLOBAL_DBNAME is not required unless HOSTNAME is used for database connection

(ORACLE_HOME = E:\ oracle\ product\ 10.1.0\ Db_2)

(SID_NAME = orcl)

)

)

# listeners define that a database can have more than one listener

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = boway) (PORT = 1521))

)

The URL of the JDBC connection request corresponds to the Listener.ora

Jdbc:oracle:thin:@192.168.3.98:1521:orcl

Summary:

1. All three configuration files are placed in the $ORACLE_HOME\ network\ admin directory.

2. Sqlnet.ora determines how names are resolved.

3. Set SID_NAME on listener.ora, which is usually used for JDBC access, and the corresponding error code is 12505.

4. SERVICE_NAME is set on tnsnames.ora, which is usually used on linux sqlplus clients, and the corresponding error code is 12514.

Troubleshooting methods for common problems on the server side of client connection:

To troubleshoot the connection between the client and the server, first check whether the client configuration is correct (the client configuration must be consistent with the database server listening configuration), and then resolve it according to the error prompt. Here are several common connection problems:

1. ORA-12541: TNS: without listeners, it is obvious that the listeners on the server side are not started, and check whether the client IP address or port is entered correctly. Start the listener: $lsnrctl start or C:lsnrctl start

2. ORA-12500: TNS: the listener cannot start the dedicated server process. For Windows, the Oracle instance service is not started. Start the instance service: C:oradim-startup-sid myoracle

3. ORA-12535: TNS: there are many reasons for operation timeout, but it is mainly related to the network. To solve this problem, first check whether the network between the client and the server is smooth, and if the network is connected, check whether the firewall at both ends is blocking the connection.

4. ORA-12154: TNS: unable to process the service name to check whether the entered service name is consistent with the configured service name. Also note that there can be no spaces before the first line service name of each service in the generated local service name file (such as D:oracleora92networkadmin tnsnames.ora,Linux/Unix / network/admin/tnsnames.ora under Windows).

5. ORA-12514: TNS: the listener process cannot parse the SERVICE_NAME given in the connection descriptor to open the Net Manager. Select the service name and check whether the service name is entered correctly in the service identification column. The service name must be the same as the global database name configured by the server-side listener.

6. ORA-12518 TNS: the listener cannot distribute client connections for two reasons: in shared mode, there are too few scheduled processes (dispatchers), and in exclusive mode, the number of processes (proces ses) exceeds the default maximum number of processes in the database.

Steps to solve: 1. Show parameter process to view the maximum number of processes allowed in the database

2. Select count (*) from session; check the number of processes in the current system. If the number of processes is not enough, you can increase the number of processes by expanding PGA: alter system set workarea_size_policy=auto scope=both

Alter system set pga_aggregate_target=512m scope=both

3. Show parameter dispatchers check the number of scheduling processes. If there are too few scheduling processes, you can execute: alter system set dispatchers='(protocol=tcp) (dispatchers=3) (service=oracle10xdb)'

7. Start listening service prompt under Windows cannot find path with command or start listening prompt in service window cannot find path, or listening service starts abnormally. Open the registry, enter the HKEY_LOCAL_MACHINE/SYSTEM/Current ControlSet/Services/OracleOraHome92TNSListener key, check whether the ImagePath string key exists, if not, set the value to D:oracleora92BINTNSLSNR, different installation path settings to change accordingly. This method is also applicable to the Oracle instance service. As above, find the HKEY_LOCAL_MACHINE/SYSTEM/Current ControlSet/Services/Oracle ServiceMYORACLE entry and check whether the ImagePath string entry exists. If not, create a new one, and set the value to d:oracleora92 binORACLE.EXE MYORACLE. The above are some common problems on the server side of Oracle client connection, of course, not all connection exceptions can be covered. The key to solving the problem lies in the methods and ideas, not that every question has a fixed answer.

It may be used to troubleshoot at ordinary times.

1.lsnrctl status to view the status of server-side listener processes

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.tnsping view client sqlnet.ora and tnsname.ora files are configured correctly with

No, and the status of the listener process of the corresponding server.

C:\ > tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0-

Production on 16-August-

2010 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 to see if instance has been started

SQL >; select open_mode from vault database; check whether the database is open or

Mount status.

OPEN_MODE

-

READ WRITE

Thank you for reading this article carefully. I hope the article "what are the network configuration files of oracle" shared by the editor will be helpful to everyone? at the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.