TNS Archives » Musings of an IT Implementor

SAP Secondary Oracle DB Connection–EasyConnect

When you run an SAP system on a non-Oracle database platform, you may sometimes need to connect to a secondary Oracle database (for example, in a SAP BW environment you could need a connection to multiple source database systems).

The process that is usually followed, is to create the TNSNAMES.ora in the appropriate location on *every* SAP application server of the SAP system. Then put the TNS service name and username/password into the DBCO transaction within SAP.

There are a couple of downsides to this approach:

1, You generally have to put the TNSNAMES.ora file in /sapmnt/<SID> as this is already shared across the SAP system’s application servers.

2, You have to keep the TNSNAMES.ora file updated. Any changes require a complete restart of the SAP system in order for the file to be re-read.

This is where the Easy Connect string can be used.
Instead of entering the TNS service name into the DBCO transaction, you simply enter all the service details, removing the need for the TNSNAMES.ora file.

An example of the Easy Connect string is:

“servervname.com:1521/tns-service-name”

We are supposing that:

– “tns-service-name” is the TNS service name for your target database (listened for on the target Oracle listener)
– Port 1521 (default port) is used by the listener.
– The server on which the listener is located is servername.com.

You must include the double quotes in the DBCO entry.

Based on the above entry, you can then dynamically change the value as and when needed.
No need for a restart of the SAP application server.

Reference: SAP note: 808505 – Secondary connections to Oracle database

SAP DBCO Connection Without TNSNAMES

In order to create an external database connection to another database, so that an ABAP program can access it, you normally create the connection details in transaction DBCO.

However, if you use the ST04 transaction, it provides additional fields where you can enter the connection details such as Oracle listener port number, which will allow you to construct a connection string which will not require an entry in the server’s TNSNAMES.ora file.

HowTo: TNSPING without DEFAULT_DOMAIN

Scenario: You want to tnsping an Oracle net service name, but your sqlnet.ora has a DEFAULT_DOMAIN configured.
This means tnsping automatically adds your DEFAULT_DOMAIN onto the end of the service name you want to tnsping.

> tnsping mynetservice


TNS Ping Utility for HPUX: Version 11.2.0.3.0 - Production on 18-APR-2013 18:05:03
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
/oracle/SID/112_64/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

In the sqlnet.ora, you have something like:

NAMES.DEFAULT_DOMAIN = WORLD

All you need to do to ping a net service name without the DEFAULT_DOMAIN, is to append a ‘.’ to the net service name like so:

> tnsping mynetservice. <<- Note the dot.


TNS Ping Utility for HPUX: Version 11.2.0.3.0 - Production on 18-APR-2013 18:05:03
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
/oracle/SID/112_64/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = mynetservice)))

OK (140 msec)

Basta cosi