This manual describes how programmers can use eld, the object file linker for TNS/E. The object file linker for TNS/E, to create loadfiles for execution on HP Integrity. When there are multiple definitions of a data item eld reports an error if the. 1) I created two oracle home on my machine, so I have to TNSNANES.ORA files.Is there any way to have a common tnsnames.ora file for all oracle home. 2) How many oracle home can I create on a single machine. And how many database can be created in single home. 3) can you please suggest me benefits to have multiple oracle home on single machine.
An Oracle 12c database instance isn’t much good if you can’t connect to it. Establishing a reliable, persistent, and secure connection to the database from the client is essential.
Oracle Net is supported by
- Database administrator (DBA)-managed listener processes
- Client- and server-side configuration files
- Command-line utilities
- Optional GUI administration tools
Connections can come into the database via several lighter-weight non-Oracle protocols such as ODBC or JDBC.
Local versus remote connections with Oracle 12c
Connections into the database can be one of two kinds:
- Local (bequeath): A local connection originates from the same server the database is on and doesn’t use the database listener process.
- Remote: All other connections from outside the database server or those from the server using the listener are remote connections. The easy way to determine whether a user is connecting remotely is if you have @TNS_ALIAS in the connect screen.
Communication flow with Oracle 12c
Connections to an Oracle database typically come across from a client located away from the database; over a network infrastructure; to the database server; through a database listener process; and, finally, into the database itself.
On the client side, the program calling the database references tnsnames.ora to find the database server host and protocol to send the request to. The request then leaves the client and goes onto the network utilization OracleNet. The default port for Oracle Net communications is 1521. Over this Oracle Net protocol is where database communications traffic flows between the client and database server.
After a client’s communication request reaches the database server host, it’s handed off to the listener. The database listener is a separate Oracle software process on the database server that listens for incoming requests on the defined OracleNet port (1521). When it gets a request, the listener identifies which database instance is targeted for that request and establishes a connection to that database instance.
On the server side, the listener uses the listener.ora file to make this determination. When the connection is established and the session begins, the listener steps out of the picture and allows communication between the database and client. Each client session has a dedicated server process on the server side. Within this dedicated server process, the user’s session code is executed. This represents the communication flow.
The client contains the client application and tnsnames.ora file. It communicates to the database server over Oracle Net on port 1521. On the database server, a listener process is configured by way of the listener.ora file. The listener routes the incoming request to the target database instance (either dev11g or db01) and establishes the initial connection handshake between the database instance and client.
How to set up tnsnames.ora with Oracle 12c
You must provide the address or location of the database you’re trying to connect to. This information is often stored in the tnsnames.ora text file, which exists on the client you’re connecting from.
Note this “client” can be a user’s workstation, a web application server, or even another database server.
Here is a sample tnsnames.ora file that can be found in ORACLE_HOME_DIRECTORY/network/admin:
This particular tnsnames.ora contains 2 TNS (Transport Network Substrate) aliases, one for dev12c and one for db01. When connecting to a database instance, you actually specify the TNS alias (not database name). The TNS alias can be any name (such as dev12c or something more generic like dev or trainingdb). That flexibility means you can have a generic alias and not hardcode the database name.
Under HOST you specify either the DNS host name or the IP address of the server containing the database instance. Again, try to avoid hardcoded values such as IP address and use DNS names if possible.
PORT is the port the server-side listener process is listening on. It’s also the port you connect across the network on for your OracleNet traffic (thus the firewalls must be open on that port).
SERVICE_NAME is the service name of the database instance you’re attempting to connect to. You can also use SID, although Oracle is promoting the use of SERVICE_NAME instead.
The tnsnames.ora file is text based, and you can edit it by hand. After making changes, it’s not necessary to restart the database or listener process.
How to test the connection with Oracle 12c
The best way to test a connection is via the application, but that isn’t always possible. Preferably, you’re on the client tier and actually go through the same network path as the client applications
To execute a connectivity test, follow these steps to determine whether you can connect to the database instance:
- Go to the client tier.
- See whether Oracle client software such as SQL*Plus and tnsnames.ora is installed.
- Execute a sqlplus username@tns_alias, such as sqlplus mwhalen@dev12c.
- Enter the password to connect to the database.
Using the tnsping utility is an even faster method that doesn’t require a password. This utility connects over the network via the listener and establishes a handshake. It then terminates the connection and reports the results, which you see here:
Testing connections is a good verification step for the DBA. If problems occur, it lets you catch them first instead of relying on users to report them later.
This chapter provides a complete listing of the
tnsnames.ora
file configuration parameters.This chapter contains these topics:
Overview of Local Naming Parameters
This
tnsnames.ora
file is a configuration file that contains net service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses.A net service name is an alias mapped to a database network address contained in a connect descriptor. A connect descriptor contains the location of the listener through a protocol address and the service name of the database to which to connect. Clients and database servers (that are clients of other database servers) use the net service name when making a connection with an application.
By default,
tnsnames.ora
is located in the $ORACLE_HOME/network/admin
directory on UNIX operating systems and in the ORACLE_HOME
networkadmin
directory on Windows operating systems. tnsnames.ora
can also be stored the following locations:- The directory specified by the
TNS_ADMIN
environment variable or registry value - On UNIX operating systems, the global configuration directory. For example, on the Solaris Operating System, this directory is
/var/opt/oracle
.See Also:Oracle operating system-specific documentationGeneral Syntax of tnsnames.ora
The basic syntax for atnsnames.ora
file is shown in .DESCRIPTION
contains the connect descriptor,ADDRESS
contains the protocol address, andCONNECT_DATA
contains the database service identification information.Multiple Descriptions in tnsnames.ora
Atnsnames.ora
file can contain net service names with one or more connect descriptors. Each connect descriptor can contain one or more protocol addresses. Example 6-2 shows two connect descriptors with multiple addresses.DESCRIPTION_LIST
defines a list of connect descriptors.Note:Oracle Net Manager does not support the creation of multiple connect descriptors for a net service name.Multiple Address Lists in tnsnames.ora
Thetnsnames.ora
file also supports connect descriptors with multiple lists of addresses, each with its own characteristics. In , two address lists are presented. The first address list features client load balancing and no connect-time failover, affecting only those protocol adresses within theADDRESS_LIST
. The second protocol address list features connect-time failover and no client load loading balancing, affecting only those protocol addresses within theADDRESS_LIST
. The client first tries either the first or second protocol address at random, then tries protocol addresses three and four sequentially.Note:Oracle Net Manager supports only the creation of one protocol address list for a connect descriptor.Note:Protocol address lists do not have to be embedded in anADDRESS_LIST
if there is only one list, as was the case prior to release 8.1.Connect-Time Failover and Client Load Balancing with Oracle Connection Managers
When a connect descriptor in atnsnames.ora
file contains at least two protocol addresses for Oracle Connection Manager, parameters for connect-time failover and load balancing can be included in the file.illustrates failover of multiple Oracle Connection Manager protocol addresses.Example 6-4 Multiple Oracle Connection Manager Addresses in tnsnames.oraIn :- The client is instructed to connect to an protocol address of the first Oracle Connection Manager, as indicated by:
- The first Oracle Connection Manager is then instructed to connect to the first protocol address of another Oracle Connection Manager. If the first protocol address fails, then it tries the second protocol address. This sequence is specified with the following configuration:
- The Oracle Connection Manager then connects to the database service using the following protocol address:
illustrates client load balancing among two Oracle Connection Managers and two protocol addresses:In :- The client is instructed to pick an
ADDRESS_LIST
at random and to failover to the other if the chosenADDRESS_LIST
fails. This is indicated by theLOAD_BALANCE
andFAILOVER
parameters being set toon
. - When an
ADDRESS_LIST
is chosen, the client first connects to the Oracle Connection Manager, using the Oracle Connection Manager protocol address that uses port 1630 indicated for theADDRESS_LIST
. - The Oracle Connection Manager then connects to the database service, using the protocol address indicated for the
ADDRESS_LIST
.
Local Naming Parameters
This section lists and describes thetnsnames.ora
file parameters that comprise connect descriptors. Configuration parameters fall into the following categories:Connect Descriptor Descriptions
Each connect descriptor is contained within aDESCRIPTION
parameter. Multiple connect descriptors are characterized by theDESCRIPTION_LIST
parameter. These parameters are described next.DESCRIPTION
PurposeUse theDESCRIPTION
parameter as a container for a connect descriptor.Embed this parameter under theDESCRIPTION_LIST
parameter.ExampleDESCRIPTION_LIST
PurposeUse heDESCRIPTION_LIST
parameter defines a list of connect descriptors for a particular net service name.ExampleProtocol Address Section
The protocol address section of thetnsnames.ora
file specifies the protocol addresses of the listener.ADDRESS
PurposeUse the parameterADDRESS
to define a single listener protocol address.Embed this parameter under either theADDRESS_LIST
parameter or theDESCRIPTION
parameter.See Also:Chapter 4 for descriptions of the correct parameters to use for each protocolADDRESS_LIST
PurposeUse the parameterADDRESS_LIST
to define a list of protocol addresses. If there is only address list,ADDRESS_LIST
is not necessary.Embed this parameter under either theDESCRIPTION
parameter or theDESCRIPTION_LIST
parameter.ExampleOptional Parameters for Lists
For multiple addresses, the following parameters are available for usage:FAILOVER
PurposeUse the parameterFAILOVER
to enable or disable connect-time failover for multiple protocol addresses.When you set the parameter toon
,yes
, ortrue
, Oracle Net, at connect time, fails over to a different address if the first protocol address fails. When you set the parameter tooff
,no
, orfalse
, Oracle Net tries one protocol address.Embed this parameter under either theDESCRIPTION_LIST
parameter, theDESCRIPTION
parameter, or theADDRESS_LIST
parameter.Important:Do not set theGLOBAL_DBNAME
parameter in theSID_LIST_
listener_name
section of thelistener.ora
. A statically configured global database name disables connect-time failover.LOAD_BALANCE
PurposeUse the parameterLOAD_BALANCE
to enable or disable client load balancing for multiple protocol addresses.When you set the parameter toon
,yes
, ortrue
, Oracle Net progresses through the list of addresses in a random sequence, balancing the load on the various listener or Oracle Connection Manager protocol addresses. When you set the parameter tooff
,no
, orfalse
, Oracle Net tries the protocol addresses sequentially until one succeeds.Embed this parameter under either theDESCRIPTION_LIST
parameter, theDESCRIPTION
parameter, or theADDRESS_LIST
parameter.Defaulton
forDESCRIPTION_LIST
sValuesExampleRECV_BUF_SIZE
PurposeUse the parameterRECV_BUF_SIZE
to specify, in bytes, the buffer space for receive operations of sessions. This parameter is supported by the TCP/IP, TCP/IP with SSL, and SDP protocols.Note:Additional protocols might support this parameter on certain operating systems. Refer to operating-system specific documentation for information about additional protocols that support this parameter.SDU
PurposeUse the parameterSDU
to instruct Oracle Net to optimize the transfer rate of data packets being sent across the network with the session data unit (SDU) size you specify.Embed this parameter under theDESCRIPTION
parameter.Default2048 bytes (2KB)Values512 bytes to 32768 (32 KB)UsageSetting this parameter in the connect descriptor for a client overrides the DEFAULT_SDU_SIZE parameter at clientsidesqlnet.ora
file.See Also:Oracle Database Net Services Administrator's Guide for complete SDU usage and configuration informationSEND_BUF_SIZE
PurposeUse the parameterSEND_BUF_SIZE
to specify, in bytes, the buffer space for send operations of sessions. This parameter is supported by the TCP/IP, TCP/IP with SSL, and SDP protocols.Note:Additional protocols might support this parameter on certain operating systems. Refer to operating-system specific documentation for information about additional protocols that support this parameter.SOURCE_ROUTE
PurposeUse the parameterSOURCE_ROUTE
to enable routing through multiple protocol addresses.When you set toon
oryes
, Oracle Net uses each address in order until the destination is reached.To use Oracle Connection Manager, an initial connection from the client to Oracle Connection Manager is required, and a second connection from Oracle Connection Manager to the listener is required.Embed this parameter under either theDESCRIPTION_LIST
parameter, theDESCRIPTION
parameter, or theADDRESS_LIST
parameter.See Also:Oracle Database Net Services Administrator's Guide for complete configuration informationTYPE_OF_SERVICE
PurposeUse the parameterTYPE_OF_SERVICE
parameter to specify the type of service to use for an Oracle Rdb database. This parameter should only be used if the application supports both an Oracle Rdb and Oracle database service, and you want the application to load balance between the two.Embed this parameter under theDESCRIPTION
parameter.ExampleConnect Data Section
The connection data section of thetnsnames.or
a file specifies the name of the destination service.CONNECT_DATA
PurposeUse the parameterCONNECT_DATA
to define the service to which to connect.Embed this parameter under theDESCRIPTION
parameter.Usage NotesCONNECT_DATA
permits the following subparameters:ExampleFAILOVER_MODE
PurposeUse the parameterFAILOVER_MODE
to instruct Oracle Net to fail over to a different listener if the first listener fails during runtime. Depending upon the configuration, session or anySELECT
statements which were in progress are automatically failed over.This type of failover is called Transparent Application Failover (TAF) and should not be confused with the connect-time failover FAILOVER parameter.Embed this parameter under theCONNECT_DATA
parameter.See Also:Oracle Database Net Services Administrator's Guide for complete configuration informationGLOBAL_NAME
PurposeUse the parameterGLOBAL_NAME
to identify the Oracle Rdb database.Embed this parameter under theCONNECT_DATA
parameter.ExampleHS
PurposeUse the parameterHS
to instruct Oracle Net to connect to a non-Oracle system through Heterogeneous Services.Embed this parameter under theCONNECT_DATA
parameter.See Also:Oracle Database Net Services Administrator's Guide for complete configuration informationINSTANCE_NAME
PurposeUse the parameterINSTANCE_NAME
to identify the database instance to access. Set the value to the value specified by theINSTANCE_NAME
parameter in the initialization parameter file.Embed this parameter under theCONNECT_DATA
parameter.See Also:Oracle Database Net Services Administrator's Guide for information about the use ofINSTANCE_NAME
RDB_DATABASE
PurposeUse the parameterRDB_DATABASE
parameter to specify the file name of an Oracle Rdb database.Embed this parameter under theCONNECT_DATA
parameter.ExampleSERVER
PurposeUse the parameterSERVER
to instruct the listener to connect the client to a specific type of service handler.Embed this parameter under theCONNECT_DATA
parameter.Valuesdedicated
to specify that client requests be served by dedicated servershared
to specify that client request be served by shared serverNote:Shared server must be configured in the database initialization file in order for the client to connect to the database with a shared server process. See the Oracle Database Net Services Administrator's Guide for configuration information.SERVICE_NAME
PurposeUse the parameterSERVICE_NAME
to identify the Oracle9i or Oracle8 database service to access. Set the value to a value specified by theSERVICE_NAMES
parameter in the initialization parameter file.Embed this parameter under theCONNECT_DATA
parameter.See Also:Oracle Database Net Services Administrator's Guide for information about the use of theSERVICE_NAME
parameterSID
PurposeUse the parameterSID
to identify the Oracle8 database instance by its Oracle System Identifier (SID). If the database is Oracle9i or Oracle8, use the SERVICE_NAME parameter rather than theSID
parameter.See Also:Oracle Database Net Services Administrator's Guide for information about the use ofSID
Security Section
The security section of thetnsnames.or
a file specifies security-related parameters for use with Oracle Advanced Security features.SECURITY
PurposeUse the parameterSECURITY
to enable secure connections.Embed this parameter under theDESCRIPTION
parameter.Usage NotesSECURITY
permits the SSL_SERVER_CERT_DN subparameter.ExampleSSL_SERVER_CERT_DN
PurposeUse the parameterSSL_SERVER_CERT_DN
to specify the distinguished name (DN) of the database server. The client uses this information to obtain the list of DNs it expects for each of the servers, enforcing the database server DN to match its service name.Usage NotesUse this parameter in conjunction with thesqlnet.ora
parameter SSL_SERVER_DN_MATCH to enable server DN matching.See Also:Oracle Advanced Security Administrator's Guide