Thursday, December 17, 2015

TNS-12154 on Database Link When Connected Through Listener

I ran into a problem when creating a database link that uses a new TNS entry. The database link seemed to work fine when I created it, but the developers complained about getting TNS-12154 errors when they tried to use it. Checked it again on my side, and the database link worked just fine.
 
After some discussion with the development team, we noticed that the database did work when I connected locally from the database server, but it threw the TNS-12154 when connecting through the listener. So I focussed on why the listener prevented the database link from recognizing my newly created TNS entry. Turns out it has a lot to do with setting a TNS_ADMIN environment variable, the dynamic registration to the listener and the way the listener uses environment variables.
 
So, to paint a picture of the situation:
·        We have a database on Linux (probably any Linux or UNIX variant will have this problem)
·        We have set the TNS_ADMIN environment variable
·        The listener uses both static and dynamic service registration (because we are using OEM)
 
Solutions proposed so far
 
This problem is described in a lot of places, but I could not find a satisfactory solution until recently. The answers I found so far are not satisfactory, some more so than others. They include:
·        Restart the database. When creating a new TNS entry, this will not been seen by the dynamic service registration to the listener. Restarting the database somehow resets this and then the database link also “magically” works.
o   My problem with this: Restarting a (Production) database is not something I want to do when I just want to add a new TNS entry for a database link.
·        Use a different naming method when creating the database link. Instead of the USING clause with a TNS entry, you can specify an EZConnect string or the full description of the TNS entry. Both imply that server and database specific information is to be used in the database link itself.
o   But: I use the tnsnames.ora to have a central location for all descriptions and use that to point to any database/service I need. I don’t want to repeat sevrer/instance information in every database link I create.
·        Set the ENVS clause of the static registration (SID_LIST_LISTENER) to include TNS_ADMIN.
o   This has several drawbacks: You need to use static registration and static registration only. Dynamic takes precedence over Static, and Dynamic cannot be used to set TNS_ADMIN. Even when you decide to use only Static, then you have to maintain the TNS_ADMIN in the environment variable itself and in the listener.ora which means extra maintenance.
·        Don’t use TNS_ADMIN. When not using TNS_ADMIN, all works fine. As the database and the listener will then both use the default location ($ORACLE_HOME/network/admin), they can always find the tnsnames.ora and will pick up new entries right away.
o   The problem is obvious: we use TNS_ADMIN to create a centralized location for our tnsnames.ora file. Not using the TNS_ADMIN would defeat this initial setup.
 
My solution
 
The last “solution”, however, opens up a final solution. When the listener (the dynamic service registration) fails to pick up (or refresh, or whatever you like to call it) changes to tnsnames.ora in the TNS_ADMIN location, it will look at the default location. Creating an OS link there will allow the listener to find the file anyway and use the new TNS entry without having to restart the database. And yes, there is a drawback to this solution as well: you need to create this link in every RDBMS home you created and point it to the TNS_ADMIN location. I think this is still slightly better than not using TNS_ADMIN at all, because we will still have a centralized tnsnames.ora file, and only use links to that file in the RDBMS home directory.
 
Technical “proof”
 
The example sketched below is from an AIX machine, using an 11gR2 database. I also tested this on a 12c database on Linux. Both have the exact same results when it comes to recognizing the new TNS entry.
 
Using the original setup, I modified the tnsnames.ora file in the TNS_ADMIN location:
 
dbserver::../home/oracle:> cd $TNS_ADMIN
dbserver::../oracle/tns:> ls -l
total 16
-rw-r--r--    1 oracle   dba             483 Dec 16 16:37 listener.ora
-rw-r--r--    1 oracle   dba            1581 Dec 16 16:14 tnsnames.ora
dbserver::../oracle/tns:> vi tnsnames.ora
 
I added an “orcldb_self_ref.world” entry, pointing toward my original database. After this, without restarting the database or reloading the listener, I created the database link.
 
dbserver::../oracle/tns:> sqlplus system@orcldb
 
SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 17 09:41:34 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> create database link dblink_test_erik using 'orcldb_self_ref.world';
 
Database link created.
 
SQL> select * from dual@dblink_test_erik;
select * from dual@dblink_test_erik
                   *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
 
And there you have it, the TNS-12154. Normally, you would think that we did something wrong defining the TNS entry or something like that. However, when not connecting through the listener, the same database link does work:
 
dbserver::../oracle/tns:> sqlplus system
 
SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 17 09:41:34 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select * from dual@dblink_test_erik;
 
D
-
X
 
So, on to the next part, creating an OS link:
 
dbserver::../home/oracle:> cd $ORACLE_HOME/network/admin
dbserver::../network/admin:> ln -s $TNS_ADMIN/tnsnames.ora ./tnsnames.ora
dbserver::../network/admin:> ls -l
total 8
drwxr-xr-x  2 oracle dba 256 Oct 23 09:49 samples
-rw-r--r--  1 oracle dba 187 May 07 2007  shrept.lst
lrwxrwxrwx  1 oracle dba  28 Dec 17 09:45 tnsnames.ora -> /u01/oracle/tns/tnsnames.ora
 
Directly after creating the link, our database link works as it should. Just to prove it works right away, I dropped and re-created it.
 
dbserver::../oracle/tns:> sqlplus system@orcldb
 
SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 17 09:49:10 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> drop database link dblink_test_erik;
 
Database link dropped.
 
SQL> create database link dblink_test_erik using 'orcldb_self_ref.world';
 
Database link created.
 
SQL> select * from dual@dblink_test_erik;
 
D
-
X
 
Note that when you are already connected, it might be necessary to re-connect to pick up the changes. But that beats restarting the entire database...
 
Final remarks
 
Though this is my preferred solution, it doesn’t mean that it is the only solution. If any of the other options suits your needs better, then by all means: use them ;-)
 
This solution uses an existing setup with TNS_ADMIN set and dynamic service registrations. Both good things in my opinion. Then, by creating a simple link, you can make sure that any changes to your TNS entries are useable right away, instead of having to configure other files or restarting the database.

Thursday, June 18, 2015

Testing all Oracle database links for all schema's

I had to validate quite a lot of database links this week, and a lot of different schema's were involved. A database link is deemed valid/usable when the statement "select * from dual@db_link" succeeds, so simply executing this code on every database link would suffice.

However, when connected as one user, you can validate the database links for that user and any public links. Validating the database links for other users doesn't seem to be possible from that single connection. You can't prefix the database link name with the schema, as database links themselves can contain dots. Thus, the schema prefix will be treated as part of the link name, causing the statement to fail (because the link cannot be found under that name).

Then I figured that using a stored procedure created in the owner schema could validate the database link in that schema, when created with definer's rights (the default). And because you can use dynamic SQL ("execute immediate" in this case), you can create the procedure in the other schema, invoke it to test the database link in that same schema, and drop it afterwards.

-- Execute as SYS user (because of ORA-01031)

CREATE OR REPLACE PROCEDURE test_any_db_link ( p_owner IN varchar2
                                             , p_link  IN varchar2
                                             )
AS
   l_owner  varchar2(100);
BEGIN
   IF p_owner = 'PUBLIC' THEN
      l_owner := 'SYSTEM';
   ELSE
      l_owner := p_owner;
   END IF;
   --
   BEGIN
     EXECUTE IMMEDIATE 'create procedure '||l_owner||'.test_any_db_link_temp '||
                       'as '||
                       '  l_dummy varchar2(1); '||
                       'begin '||
                       '  select * into l_dummy from dual@'||p_link||'; '||
                       '  dbms_output.put_line(''OK : '||p_owner||'.'||
                                                         p_link||'''); '||
                       'exception '||
                       ' when others then '||
                       '   dbms_output.put_line(''NOK: '||p_owner||'.'||
                                                          p_link||'''||'' ''||
                                                          sqlcode); '||
                       'end;';
     EXECUTE IMMEDIATE 'begin '||l_owner||'.test_any_db_link_temp; end;';
   EXCEPTION
     WHEN OTHERS THEN
       dbms_output.put_line('ERR: '||p_owner||'.'||p_link||' '||sqlcode);
   END;
   EXECUTE IMMEDIATE 'drop procedure '||l_owner||'.test_any_db_link_temp';
END;
/

set serveroutput on size 1000000;

spool test_db_links

BEGIN
  FOR r_stmt IN (select 'begin test_any_db_link('''||owner||''','''||
                                                     db_link||'''); end;' stmt 
                   from dba_db_links 
                  order by owner, db_link
                )
  LOOP
    execute immediate r_stmt.stmt;
  END LOOP;
END;
/

spool off

drop procedure test_any_db_link;

-- done

Main points about this code:
  • You execute it as SYS, because of an ORA-01031 (insufficient privileges) otherwise
  • The script creates a procedure for every test, and drops it afterwards
  • The status of a link is either OK (everything checks out), or ERR (the procedure could not be created, because the link is not valid). In theory, the status could be NOK, when the link is valid, but something else prevents the execution of "select * from dual" from succeeding. So far, I never saw an NOK code
  • This script was used on database versions ranging from 9.2 to 12.1
  • The error output is very short, you could enhance this with dbms_utility.format_error_stack or something similar
  • When a database link is marked as "ERR", investigate further. I have some "ExtProc" call type database links, and those were marked as "ERR", because there is no dual at the other end
  • For PUBLIC links, I translated the user for the procedure to SYSTEM, as you cannot create a procedure under the "schema PUBLIC"