Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Overview of Oracle Net Setup

Oracle Tips by Burleson
 

There are several files that must either be set up manually or can be setup via the Oracle Net Configuration Assistant. These are divided between the server and the client.

Server Oracle Net Files

On the server the listener.ora, sqlnet.ora and maybe the onames.ora files need to be configured depending on whether Oracle Names is being used or not. If other options such as LDAP are configured other files may also be required.

The Listener.ora File

The Listener.ora file tells the listener what instances are available for connection on the server. Beginning in Oracle8i the instances will auto-register when they start if the listener is started. This auto registration of database instances means you no longer have to populate the listener.ora file with entries when new instances are added to a server. However, this is only for Oracle8i and greater versions of Oracle, all other versions of Oracle must be manually entered into the file either via an editor or through the GUI interface provided.

Before you can configure the listener.ora file you must decide whether you want to use a flat model for your Oracle instance structure or the structured model. If you use the flat model (suggested for small Oracle installations where there aren't multiple databases, divisions or offices) then your instance tree begins at WORLD as the root and ends with WORLD, essentially more of an instance lawn than an instance tree.

On the other hand, if you have multiple databases spread across multiple divisions and offices then a more structured instance tree is required. For a more structured instance tree begin with the name of the company or organization as the root of the tree, then each office becomes a branch as does each division inside each office, finally the leaves of the tree are the database instances. The structured instance network is similar to the structure of the internet, except, while the address in the internet would be www.pubs.com an example instance address in Oracle would be consult.chicago.pubs where consult is the instance name, Chicago is the office and pubs is the company. Before beginning to setup the listener.ora file for each office or division you would need to know where on the instance hierarchy it falls.

For example for my test environment I have chosen a root of PUBS, main branches will be the server or computer names and the end leaves will of course be the database name. I have a LINUX server called PUBSGALINUX and a laptop called MRAMOBILE. On PUBSGALINUX I have a database GALINUX1 and on MRAMOBILE I have a database called AULTDB1. Figure 14.2 shows what this configuration looks like graphically.

Figure 14.2 Graphic Representation of Example Network.

The listener.ora file for the PUBSGALINUX Oracle host is shown in listing 14.1.

LISTING 14.2      Example Listener.ora File

# LISTENER.ORA Network Configuration File: /var/oracle/OraHome2/network/admin/listener.ora
# Generated by Oracle configuration tools. 

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = pubsgalinux)(PORT = 1521))
      )
    )
  )
 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /var/oracle/OraHome2)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = galinux1.pubsgalinux.pubs)
      (ORACLE_HOME = /var/oracle/OraHome2)
      (SID_NAME = galinux1)
    )
  )

After configuring the listener.ora file, the listener process needs to be started. On LINUX and UNIX the lsnrctl program is used to start, stop and reload the listener. On Windows based platform the command line version of this program can be used or the service can be stopped and restarted from the control panel services icon. If the listener process is already running (use: lsnrctl status listener_name to find out, default listener name is listener) then you can issue a reload command to reread the listner.ora for all changes except a change in the listing port. To start a listener process the command:

oracle@pubsgalinux: lsnrctl start

is issued where the appropriate listener name is appended to the command if other than listener is used. To stop a listener process the command:

oracle@pubsgalinux: lsnrctl stop

is issued where the appropriate listener name is appended to the command if other than listener is used.

Once the listener file is established and the listener process is running the tnsnames.ora file needs to be populated with the connection information for all databases that you wish to connect with from the server or client. An example tnsnames.ora file for the example network is shown in listing 14.2.

LISTING 14.3 Example tnsnames.ora file

# TNSNAMES.ORA Network Configuration File: /var/oracle/OraHome2/network/admin/tnsnames.ora

# Generated by Oracle configuration tools. 

EXTPROC_CONNECTION_DATA.LOCAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  ) 

AULTDB1.MRAMOBILE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mramobile)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = aultdb1.mramobile.pubs)
    )
  )

AULTDB1.LOCAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mramobile)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = aultdb1.mramobile.pubs)
    )
  )
 

GALINUX1.LOCAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pubsgalinux)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = galinux1.pubsgalinux.pubs)
    )
  ) 

INST1_HTTP.PUBSGALINUX =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pubsgalinux)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = MODOSE)
      (PRESENTATION = http://HRService)
    )
  )

Notice in listing 14.2 that there is a listing qualified with either local or the host value for each database. The listener will attach the value specified in the sqlnet.ora parameter NAMES.DEFAULT_DOMAIN to any unqualified name, so GALINUX1 becomes GALINUX1.LOCAL if it cannot locate an unqualified name, or one that matches the sid.default_domain, then it will try to resolve the name based on the order of naming method values in the sqlnet.ora parameter NAMES.DIRECTORY_PATH which defaults to TNSNAMES, ONAMES, HOSTNAME. An example sqlnet.ora file is shown in listing 14.3

LISTING 14.4 Example SQLNET.ORA file.

# SQLNET.ORA Network Configuration File: /var/oracle/OraHome2/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.
NAMES.DEFAULT_DOMAIN = local
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

As you can see from examining Listing 14.3, there isn't much to a SQLNET.ORA default file, however, this file can be used to add auditing and tracing options.

Connectivity to a remote or local database can be tested using the tnsping program. To use the tnsping program you simply issue the tnsping command followed by the sid of the database you for which you wish to test connectivity.

Oracle Net Configuration Assistant

I have mentioned the Oracle Net Configuration assistant (NETCA) several times in the last few sections. The NETCA is a Java based GUI that makes configuring and testing Oracle Net connections very easy. Figure 14.x shows the top level screen of the NETCA GUI. If you are only configuring a simple Oracle Net setup there are only two options on this NETCA GUI you will need to bother with, for the server setup you will use the Listener Configuration and Local Net Service Name Configuration options, for the client, you need only use the Local Net Service Name Configuration option.

The NETCA GUI can be invoked from the command line using netca as the command, from the OEM Service Manager - Oracle Net manager selection or in Windows from the Start-Programs-Oracle-Network Administration menu. The OEM Oracle net manager is shown in Figure 14.xx. In The OEM Oracle Net manager for a simple topology you should just use the Profile, Service naming and Listeners configuration options under the Local configuration icon. The top level screen for the Oracle net Configuration Assistant is shown in Figure 14.3. The top level screen of the OEM Oracle net manager is shown in Figure 14.4

Figure 14.3 Top Level NETCA GUI Screen

The interfaces are really rather intuitive once you get into them and understand exactly what you want to do. I suggest overlooking the Oracle Net manuals before attempting to configure your system if it is more complex than a couple of databases, a server and a flat network topology.

Figure 14.4 Top Level OEM Net Manager GUI Screen

See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter