1. Configure the Oracle client to connect to the database

Reprinted from: http://blog.csdn.net/leshami/article/details/5917103

The Oracle client connection to the Oracle database server looks different from the network configuration in SQL serve, except that all SQL Server runs on the Windows platform, so many configurations are integrated directly into the operating system. So you don't need to configure the client to connect to the server. Oracle client connections to the database depend on Oracle Net. Oracle provides a number of client-or server-based configuration tools that need to be clear about the terminology in Oracle Net. The terminology is clear, but the configuration method is similar to SQL Server.

I. What is Oracle Net

Oracle Net is used to create a connection session between the client and the server and is responsible for maintaining the session.
Oracle Net acts as a background process component of the application on the client side and contains active processes called listeners on the server side,
Responsible for the interaction between the client and the server.
Oracle Net also supports the connection of heterogeneous databases, such as connecting to Sybase, Informix, DB2, SQL Server, etc.
Usually implemented based on the following configuration
    Network configuration (the network must be Unicom)
    Where the node is located (IP / Hostname)
    application
    Protocols used (TCP / IP, / TCP / IP with SSL, SDP, Named Pipes)
        
--Oracle Net Supported Connection Types
Client-server mode (e.g. SQLPlus)
Java applications (JDBC, etc.)
Web client application
    Use web-based application (App Server) as middleware to implement, you can configure JDBC Oracle Call Interface (OCI) driver or thin JDBC driver
    Connect directly to Oracle server via HTTP, such as OEM
            
    
--Oracle Net implementation mechanism
The user sends a connection request to interact with the user process
The user process interacts with the server process through Oracle Net
The server process interacts with the instance (maintained by Oracle Net)
The instance interacts with the background process to complete the database read and write operations
    
--Oracle Net connection
The database that the local client connects to (that is, the client and the database are on the same server).
    The local connection also uses Oracle Net, because any interaction with the data is done through Oracle Net.
    There is no need to use a Listener, because the local connection uses the IPC protocol, and the IPC protocol allows processes within the host to communicate with each other and is provided by the operating system.
    No configuration is required for the local connection, and several instances can run on the local server.
    Local connection is the only connection type that does not require a listener.
    Connection method: CONNECT username / password
            
All non-local clients connect to the database
    Server-side Listener Process
    The client parses the connection string through name resolution methods such as TNSNAME or Easy connect or LDAP
    Once the connection is successful, the Listener service stops and does not affect the session.
        
--Oracle Net Listener (listener)
Runs on the server side, listens for all connection requests from the client, and provides processing requests for database services
    Location: $ ORACLE_HOME / network / admin / listerer.ora
Ranch
-Corresponds to the default listener (Listener) established under the Listeners item in the (GUI interface) netmgr
$ ORACLE_HOME / network / admin / sqlnet.ora

Ranch
-Corresponds to the profile of (GUI interface) in netmgr
Configure tnsnames on the server
Location: ORACLE_HOME / network / admin / tnsnames.ora

-Corresponds to Service Naming (GUI interface) in netmgr
The information provided by the Listener includes IP address, port number, protocol, service name (service_name), etc.
A Listener can serve multiple database instances
Second, the process of client-to-server session establishment and related terms

 

Execute the following command to establish a connection
SQL> CONNECT username / [email protected] _service_name
       
Connect string: username / [email protected] _service_name
Contains username, password, connection identifier
    For example: SQL> conn scott / [email protected]
    String decomposition
        Username scott
        Password for tiger
        "/" Is used to split user and password
        "@" Indicates user processes required for network connection
        orcl connection identifier
        
Connect identifier
    @ Is the connection identifier, such as orcl in the above example.
    The name of the connection identifier is a simplification that maps to the connection descriptor
    Connection identifier can be network service name / alias, database service name, etc.
    (net service name, database service name, or net service alias.)
            
Connection descriptor
     Use a special format to describe the specific information of the connection, including the location of the database (IP address), service name, port number
     As in tnsnames:
     orcl =-network service name, database alias, equivalent to connection identifier (shields details of how the client connects to the server)
       (DESCRIPTION =
         (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.128) (PORT = 1521))-IP address, port number, protocol
         )
         (CONNECT_DATA =
           (SERVICE_NAME = orcl.robinson.com)-service name
         )
       )
     All the information in the DESCRIPTION above is the connection description information
     Above the descriptor, the left of the equal sign can be the network service name, network service alias, etc. This is equivalent to the connection identifier.
            
--Service name (service_name)
The service name of the client connected to the instance. You can specify one or more service names for this parameter. This parameter is introduced from 9i. Service_name can usually be replaced by SID.
After 9i, Oracle recommends using service_name instead of SID. You can distinguish different user connections by defining different service names. The default format of this parameter is db_name.domain_name.
The instance registers the defined service name with the listener. When the client requests the service, the listener decides which instance will be used to provide the requested service and establish a connection with it based on the service name
Different service names can be defined based on the same database to distinguish different use cases
For example, if service_name is defined as service_names = sales.robinson.com, hr.robinsn.com, the sales department establishes a connection by the sales service name, and the hr department establishes a connection by the hr service name.
It doesn't matter which database is connected.
        
--Demonstrate the connection process (non-local connection)
Environment: server is RHEL 5 + Oracle 10g, client Winxp + 10g Client
See if the server listener is started
[[email protected] admin] $ lsnrctl --Start the lsnrctl listener program
LSNRCTL for Linux: Version 10.2.0.1.0-Production on 03-SEP-2010 13:31:06
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status --View listener status, listener is not started
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521))
TNS-12541: TNS: no listener
 TNS-12560: TNS: protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
   
--Start the default listener
--There is no path to the listener and listener.ora file in the information listed below.
LSNRCTL> start
Starting / u01 / app / oracle / 10g / bin / tnslsnr: please wait ...

TNSLSNR for Linux: Version 10.2.0.1.0-Production
Log messages written to /u01/app/oracle/10g/network/log/listener.log
Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = robinson.com) (PORT = 1521)))
                    
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0-Production
Start Date 03-SEP-2010 13:31:14
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/10g/network/log/listener.log
Listening Endpoints Summary ...
  (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = robinson.com) (PORT = 1521)))
The listener supports no services
The command completed successfully

LSNRCTL> service-I have seen the registered instance information, because 10g supports dynamic registration
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521))
Services Summary ...
Service "orcl.robinson.com" has 1 instance (s).
  Instance "orcl", status READY, has 1 handler (s) for this service ...
    Handler (s):
      "DEDICATED" established: 0 refused: 0 state: ready
         LOCAL SERVER
Service "orclXDB.robinson.com" has 1 instance (s).
  Instance "orcl", status READY, has 1 handler (s) for this service ...
    Handler (s):
      "D000" established: 0 refused: 0 current: 0 max: 1022 state: ready
         DISPATCHER <machine: robinson, pid: 3246>
         (ADDRESS = (PROTOCOL = tcp) (HOST = robinson.com) (PORT = 51473))
Service "orcl_XPT.robinson.com" has 1 instance (s).
  Instance "orcl", status READY, has 1 handler (s) for this service ...
    Handler (s):
      "DEDICATED" established: 0 refused: 0 state: ready
         LOCAL SERVER
The command completed successfully

--View client tnsnames.ora information
C: /> type F: /oracle/product/10.2.0/client_1/NETWORK/ADMIN/tnsnames.ora
# tnsnames.ora Network Configuration File: F: /oracle/product/10.2.0/client_1/NET
WORK / ADMIN / tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.128) (PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.robinson.com)
    )
  )
C: /> sqlplus / nolog

SQL * Plus: Release 10.2.0.1.0-Production on Friday September 3 13:42:02 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott / [email protected]
Connected.
--After changing the service alias in tnsnames.ora to oral, you can connect normally, that is, the network service name can be taken at will.
SQL> conn system / [email protected]
Connected.

-View the service name in the database
SQL> show parameter service_names;

NAME TYPE VALUE
------------------------------------ ----------- --- -----------------------
service_names string orcl.robinson.com
Third, service registration and name resolution

Fourth, commonly used configuration files

ldqp.ora-> used to configure LDAP directory name resolution
cman.ora-> Used to configure Oracle connection management parameters
tnsnames.ora-> used to configure local name resolution for local or remote clients
listener.ora-> Registration information for configuring listeners
sqlnet.ora-> Used to configure the parsing method supported by the server or client

Several configuration modules in netmgr (Net Manager)
    Profile branch-> Corresponds to the sqlnet.ora file, you can set the Oracle options applied on both the client and server
    Sevice Naming branch-> Used to configure client name resolution, corresponding to tnsnames.ora file
    Listener branch-> Used to configure the database listener on the server, corresponding to the listener.ora


Five, listener configuration program (lsnrctl) and TNSPING command

[[email protected] admin] $ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0-Production on 03-SEP-2010 21:30:52
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help-use help to view lsnrctl extended commands
The following operations are available
An asterisk (*) designated a modifier or extended command:

start stop status-> stop, start, view status, etc. (common)
services version reload
save_config trace spawn
change_password quit exit
set * show *

lsnrctl command usage
LSNRCTL> command [listener_name]
    
The lsnrctl command demonstrates the use of the previous example.

tnsping command is used to test the connectivity of the network
C: /> tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0-Production on 03-Sep-2010 21:52:45
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
F: /oracle/product/10.2.0/client_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.1.128) (PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl.robinson.com)))
OK (0 msec)


Summary

 

1. Configure the ORACLE client to connect to the database