How do you retrieve connect strings for Oracle database links if not in TNS

Question

I am DBA for an Oracle 19C database which has a number of database links in it. Some of these links are local, to other instances within our firewall, and others are remote, linking to databases managed by third parties. I inherited these links when I took over the role and therefore have limited knowledge of them.

I have an issue where, for one of the third party links, I've been getting reports that a scheduled job to transfer data through the link to the third party is not delivering content, and the network traffic is zero. However, no errors are being generated by the job and I can manually query the remote database tables. I need the connection method to confirm/deny to my IT colleagues whether the network traffic route they're monitoring is the correct one.

I have identified which of my database links is set up for this data transfer, and I have searched in DBA_DB_LINKS:

When I execute

select * from dba_db_links;

I get the following entry for the database link I am interested in.

BODCDB BSA MARINE BSA 29/05/2014 11:38:45 NO NO YES NO

This does not provide any details of the connection string that the BSA link uses, and this is the key information I need. It is not in TNSNAMES.ORA so is there somewhere else in the database that holds this?

Many thanks

Sean

Answer 1

Are you sure?

For the DB-Link the tnsnames.ora file stored on the database server is used, not the file on your local PC.

Oracle provides four different naming methods for client name resolution, see DIRECTORY_PATH

• tnsnames (local naming method):

  Set to resolve a network service name through the tnsnames.ora file on the client.

• ldap (directory naming method):

  Set to resolve a database service name, net service name, or network service alias through a directory server.

• ezconnect or hostname (Easy Connect naming method):

  Select to enable clients to use TCP/IP connect identifier, consisting of a host name and optional port and service name.

• nis (external naming method):

  Set to resolve service information through an existing Network Information Service (NIS).

Try tnsping BSA, it should reply the full connection string with all information.

Or you may get the information from the remote database directly:

SELECT * FROM v$listener_network@BSA;

If you lack the select privileges then you can try

SELECT 
 SYS_CONTEXT('USERENV', 'DB_DOMAIN'),
 SYS_CONTEXT('USERENV', 'DB_NAME'),
 SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'),
 SYS_CONTEXT('USERENV', 'INSTANCE'),
 SYS_CONTEXT('USERENV', 'INSTANCE_NAME'),
 SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL'),
 SYS_CONTEXT('USERENV', 'SERVER_HOST'),
 SYS_CONTEXT('USERENV', 'SERVICE_NAME')
FROM dual@BSA;

It should return all information apart from the port :-(

Answer 2

I'm not a DBA.

This is 11g, but never mind that.

SQL> select * from v$version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Your dba_db_links contains some more info than mine:

SQL> desc dba_db_links
 Name Null? Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER NOT NULL VARCHAR2(30)
 DB_LINK NOT NULL VARCHAR2(128)
 USERNAME VARCHAR2(30)
 HOST VARCHAR2(2000)
 CREATED NOT NULL DATE

One of database links:

SQL> select * from dba_db_links where rownum = 1;
OWNER DB_LINK USERNAME HOST CREATED
--------------- --------------- -------------------- ------------------------------ ----------
SCOTT DBL_EE_LINK MICHAEL dborcl:1521/ORCL 24.10.2016

To connect to that user, you'd use:

connect USERNAME/password@HOST

Password is something you should know; I'm not sure you can "extract" it from somewhere.

SQL> connect michael/lion@dborcl:1521/orcl
Connected.
SQL>

---

Or, in your case,

connect marine/password@bsa

---

If that helps, good; otherwise, wait for a proper DBA to see & answer your question.

Also, we're mostly developers here. This question might be moved to a more appropriate place, SE for DBA (Oracle). Bookmark it, if you want.

• SeanGaff: Thanks for your feedback @littlefoot but what I need is the connection information for the host - your example has an actual string with a port in it, whereas I only have an identifier 'BSA'. What I'm after is the connection string that BSA will use if I try the connect marine/password@BSA query. I will follow your advice and post on the SE for DBA thread as well

Answer 3

Name resolution can happen in numerous ways. Here's how I suggest proceeding. Log into the database server as the Oracle owner (typically 'oracle'). Then:

1. First, identify what the $TNS_ADMIN environment variable is set to as inherited by the LISTENER process (all shadow processes created by the listener inherit the listener's environment, so they will get their TNS_ADMIN setting). You can interrogate the environment of any process you own in all Unix-style OS's except for HP-UX. In Linux, get the pid of the listener and do:

   strings /proc/[pid]/environ | grep TNS
   

2. Look in the directory specified by that $TNS_ADMIN, if one is set. You're looking for sqlnet.ora, tnsnames.ora and ldap.ora. If you don't find anything, look also at /etc (for Linux), /var/opt/oracle (for Solaris), $HOME (oracle's) and $ORACLE_HOME/network/admin. There's a specific order in which Oracle looks for these files, but I would look at all of these locations.

3. Assuming you have found these files, look inside sqlnet.ora and find what NAMES.DIRECTORY_PATH is set to. If it is not present or there is no sqlnet.ora file, the default value is (tnsnames,ldap,ezconnect). Oracle will resolve names using these directories in order from left to right. So if tnsnames is first, it will first look at the tnsnames.ora file located using the same logic as we used to find sqlnet.ora (typically in the same directory).

4. If you find tnsnames.ora, you should find your alias defined inside it and get your TNS string.

5. If there is no tnsnames.ora or the alias isn't defined in it, then go to the next method, e.g. ldap. Look for ldap.ora and see if the DIRECTORY_SERVERS parameter is defined. If it is, you are likely using a centralized LDAP service to lookup the TNS string.

6. Test it at the operating system level. Logged in as the oracle owner, set your Oracle environment (including $ORACLE_HOME/bin in your $PATH), and set $TNS_ADMIN to whatever you found it was set to in #1 above, and then do a tnsping of your alias:

    tnsping bsa
   

If it gets a result, look at the top just before the TNS string it produces. If it used LDAP, it will say:

Used LDAP adapter to resolve the alias

If it used tnsnames, it will say:

Used TNSNAMES adapter to resolve the alias

Lastly, I don't have experience with EZConnect but I believe I looked into it years ago and found that you could actually get a connection a databse by specifying the hostname all by itself... Oracle assuming the default 1521 port and default service defined by the listener. So check to see if "bsa" is a hostname on your network (nslookup bsa)