Analysis of Oracle Network architecture

Recently saw a lot of people talk about TNS or database can not log in and so on, simply summed up the following dongdong.

First of all, Oracle's network structure, to complex to say can add encryption, LDAP and so on. There is no discussion here, and the focus is on the basic network structure, which is what we use most often.

Three configuration files

Listener.ora, Sqlnet.ora, Tnsnames.ora, are placed in the $oracle_home etworkadmin directory.

Focus: Role and use of three documents

#-----------------------

Sqlnet.ora-----function is similar to Linux or other Unix nsswitch.conf files, which determines how to find a connection string that appears in a connection.

such as our client input

Sqlplus SYS/ORACLE@ORCL

If my sqlnet.ora is 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 host name, through the network to resolve its IP address and then to connect the IP GLOBAL_DBNAME=ORCL this instance, Of course I'm here ORCL is not a host name

If I were this way

NAMES. Directory_path= (TNSNAMES)

Then the client will only find ORCL records from Tnsnames.ora.

There are other options in parentheses, such as LDAP, which are not commonly used.

#------------------------

Tnsnames.ora------This file is similar to the Hosts file for UNIX, providing tnsname to host name or IP correspondence, only if Sqlnet.ora is similar

NAMES. Directory_path= (TNSNAMES) This means that the client resolves the connection string in the order in which it is TNSNAMES to try to use the file.

Examples of two, ORCL corresponding to the local, sales corresponding to another IP address, which also defines the use of the main server or shared server mode to connect, one sentence said

#你所要连接的时候输入得TNSNAME

ORCL =

(DESCRIPTION =

(Address_list =

#下面是这个TNSNAME对应的主机, Port, protocol

(address = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))

)

(Connect_data =

#使用专用服务器模式去连接需要跟服务器的模式匹配, if not, based on the server's mode

#自动调节

(SERVER = dedicated)

#对应service_name, sqlplus>show parameter service_name;

#进行查看

(service_name = ORCL)

)

)

#下面这个类似

SALES =

(DESCRIPTION =

(Address_list =

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

)

(Connect_data =

(SERVER = dedicated)

(service_name = sales)

)

)

#----------------------

The client's over, let's see the server side

Listener.ora------Listener Listener Process configuration file

There is little to say about the listener process, accepting remote access to the database and handing it over to Oracle's server process. So if you are not using a remote connection, the listener process is not required, and if you close the listener process, it will not affect the existing database connection.

Examples of Listener.ora files

#listener. Ora network Configuration File: #E: Oracleproduct10.1.0db_2networkadminlistener.ora

# Generated by Oracle configuration tools.

#下面定义LISTENER进程为哪个实例提供服务

#这里是ORCL, and it corresponds to Oracle_home and global_dbname

#其中GLOBAL_DBNAME不是必需的除非使用HOSTNAME做数据库连接

Sid_list_listener =

(Sid_list =

(Sid_desc =

(Global_dbname = Boway)

(Oracle_home = e:oracleproduct10.1.0db_2)

(Sid_name = ORCL)

)

)

#监听器的名字, a single database can have more than one listener

#再向下面是监听器监听的协议, IP, port, and so on, the tcp1521 port used here, and make the # with the host name

LISTENER =

(DESCRIPTION =

(address = (PROTOCOL = TCP) (HOST = Boway) (PORT = 1521))

)

The above example is one of the simplest examples, but it is also the most common. A listener process provides services to a instance (SID).

Action commands for listeners

$ORACLE _home/bin/lsnrctl Start, others such as Stop,status. After a specific knock on a lsnrctl to see Help.

The three files mentioned above can be configured using the graphical configuration tool

$ORACLE The _HOME/NETCA Wizard form

$ORACLE _home/netmgr

I am more accustomed to netmgr,

Profile is configured with Sqlnet.ora, which means name resolution.

Service name is configured with the Tnsnames.ora file

Listeners is configured with the Listener.ora file, that is, the listener process

A specific configuration can be tried and then look at the configuration file.

So the overall structure is there, when you enter Sqlplus SYS/ORACLE@ORCL.

1. Query Sqlnet.ora look at the name of the resolution method, found that the Tnsname

2. Query Tnsnames.ora file, find ORCL records from inside, and find host name, Port and Service_Name

3. Establish a connection to the listener process if the listener process is not problematic.

4. According to the different server mode, such as dedicated server mode or shared server mode, listener take the next action. The default is dedicated server mode, and if there is no problem, the client connects to the database's server process.

5. At this time the network connection has been established, the historical mission of the listener process is completed.

#---------------

Forms of command used in several connections

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

2.sqlplus Sys/oracle This connection can only be connected to the native database, but also does not require the listener process

3.sqlplus SYS/ORACLE@ORCL This approach requires the listener process to be in a usable state. The most common connection is through the network.

The above connection methods using the SYS user or other authenticated users of the password file do not require the database to be available, the operating system authentication does not require the database to be available, and the normal user is the database authentication, so the database must be in open state.

Then it's

#-------------

You might be able to use the wrong schedule.

1.lsnrctl Status View the state of the server-side listener process

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 the client side Sqlnet.ora and Tnsname.ora files are configured correctly, and the status of the listener process for the corresponding server.

C:>tnsping ORCL

TNS Ping Utility for 32-bit windows:version 10.1.0.2.0-production on 1 June-August-

2005 09:36:08

Copyright (c) 1997, 2003, Oracle. All rights reserved.

Used parameter files:

E:oracleproduct10.1.0db_2 Etworkadminsqlnet.ora

Used TNSNAMES Adapter to resolve the alias

Attempting to contacts (DESCRIPTION = (Address_list = (address = (PROTOCOL = TCP)

(HOST = 127.0.0.1) (PORT = 1521)) (Connect_data = (SERVER = dedicated) (Service_

NAME = ORCL))

OK (msec)

3.sql>show SGA To see if the instance has been restarted

Sql> select Open_mode from V$database; View whether the database is open or mount.

Open_mode

----------

READ WRITE

#-----------------

Example of using hostname to access a database instead of a tnsname

Using Tnsname to access the database is the default, but it also poses a problem, which is that clients need to configure Tnsnames.ora files. If your database server address has changed, you will need to edit the client file again. Access to the database via hostname is not a problem.

Need to modify

Server-Side Listener.ora

#监听器的配置文件listener. Ora

#使用host naming no longer need Tnsname.ora file for local resolution

# Listener.ora Network Configuration File:d:oracleproduct10.1.0db_1networkadminlistener.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:oracleproduct10.1.0db_1)

# (program = Extproc)

)

)

LISTENER =

(Description_list =

(DESCRIPTION =

(address = (PROTOCOL = IPC) (KEY = Extproc))