Managing TNS lookups in OpenLDAP via PL/SQL


I have an Oracle Express Edition (XE) database running on almost every pc in my home. It’s not so numerous that I can’t manage them by manipulation of TNSNAMES.ORA; but I always thought it would be nice to manage them centrally via LDAP service name lookup.

Unfortunately Oracle Internet Directory (OID) licensing is included with a valid Oracle Database license unless that database is XE. I don’t really need or want a full OID installation anyway. So, I looked into other LDAP alternatives and decided on OpenLDAP. It’s open source, free and I can install it on Linux or Windows. I chose Linux and followed the instructions here…
http://diznix.com/articles/oracle-names-resolution-with-openldap without issue.

configuring one of the Windows XE installs to use my new linux server was simple enough
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP, EZCONNECT)

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\ldap.ora
DIRECTORY_SERVERS= (mylinuxserver:389:636)
DEFAULT_ADMIN_CONTEXT = "dc=home"
DIRECTORY_SERVER_TYPE = OID

The next step was getting my databases defined in the ldap server. I could do it through ldif files as shown in the link above; but I thought it would be nicer to have a sql and pl/sql interface to the server. Oracle facilitates that with the DBMS_LDAP package. To make it even easier, I built a wrapper package for it.

First, set up a user to own the package and, since I’m using 11g XE I need to create an Access Control List to allow my owner to talk to my ldap/oid server. I’m using the default non-SSL port 389.

create user oidowner identified by pa55w0rd;
grant create session to oidowner;
grant create procedure to oidowner;
grant create type to oidowner;

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl(acl => 'oid.xml',
description => 'ACL that lets users see oid server',
principal => 'OIDOWNER',
is_grant => TRUE,
privilege => 'connect'
);

DBMS_NETWORK_ACL_ADMIN.assign_acl('oid.xml', 'mylinuxserver',389,389);
COMMIT;
END;

Next I create my package and a supporting type to make listing the contents easier

create or replace type vcarray as table of varchar2(4000);

Package code is available from my dropbox https://dl.dropboxusercontent.com/u/75336811/sdsoid_pkg_1.3.txt

And finally, I add an entry for an XE installation on one of my PCs.

BEGIN
sdsoid.insert_oid(
'dc=home',
'mylinuxserver',
'seanxe',
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=seanpc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))',
'cn=myadminuser,dc=home',
'myadminpassword'
);
END;

Then I check my work…

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>tnsping seanxe

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 24-AUG-2013 13:38:16

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
c:\oracle\product\11.2.0.2\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=seanpc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))
OK (0 msec)

But, I want a SQL interface to the LDAP entries. This is where the collection type defined before the package comes in.
Using the list_oid table function it’s easy to retrieve some of the entries for other databases I’ve created.

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>sqlplus oidowner/pa55w0rd@seanxe

SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 24 14:01:54 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> set lines 1000
SQL> SELECT * FROM TABLE(sdsoid.list_oid('dc=home', 'mylinuxserver'));

COLUMN_VALUE
-------------------------------------------------------------------------------------------------------------------------
kidsxe: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=familypc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))
testxe: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testpc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))
seanxe: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=seanpc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))

Currently all entries are maintained and retrieved with the full connect descriptor. In my next release I’ll add support for parameterized entries. I have tested this code on 12c databases and it works without modification.

Questions and comments are welcome.

Advertisements

Analogies to help explain views


A not-uncommon request I hear is to explain when the contents of a view are updated.
Sometimes this will be to explain the difference between a “view” and a “materialized view.”

I like analogies as a tool to help describe what I’m trying to say.

I wear glasses.
Querying through a view is like looking at your data through glasses.

When I look at the world without my glasses I get one image.
When I put my glasses on I get another way of looking at the same thing.
If somebody walks into the room where I am, I don’t need to wait for my glasses to update.
What I see reflects the contents of the room just as if I were looking without my glasses and there is no lag.
What I see with or without my glasses is “updated” instantaneously.

If I take a picture of the room with my camera, I get another view through that lens and the view is static.
These are materialized views. Not only is what I see determined by the lens of the camera, that image won’t ever change unless I force the view to refresh (take another picture.) Here the terminology is helpful. One image capture of a picture is called a snapshot, similarly one data capture with a materialized view is also called a snapshot.
In older versions of Oracle, the terms Materialized Views and Snapshots were used interchangeably.

So… if normal views and materialized views are updated instantaneously then why are materialized views often considered “faster” than normal views?
Well, that’s a trick question.

First, neither type of view is actually fast or slow.
A view holds no data, it’s simply a query filter. Like tinted glasses, a view doesn’t really change the data, it just applies an additional set of operations when you look at it.
In the case of glasses, the speed of light makes the analogy break down a little bit because it passes through the filters so fast we can’t perceive the operation.
If our glasses were hundreds of thousands of meters thick we’d be able to detect the difference between looking through the lens and not because the light would move slower through our glasses versus through the air.

In the case of a view, if we assume the view actually does something for us like join to another table, call a function, include a sub-query or a combination of these, then of course we’ll see a lag as opposed to simply looking at a single table’s data.

The view is able to instantly pickup changes in the underlying data; but when you query for that new data, your query still needs to process it through whatever joins, functions or sub-queries you use and it’s those operations that create the lag.

With a materialized view, you can hide some of the work by doing it ahead of time. I can drive to the Grand Canyon and look at it myself or I can look at a picture of it. The picture is faster because the data has already been recorded and delivered to me. However, the content is limited by the delay and scope of the captured image. If you could somehow deliver an image that was simultaneously as vast, detailed and up-to-date as looking at the real thing live, then your image would be completely equivalent to the original. However, that’s not really possible.

So that’s why I look through my own normal view (my glasses), rather than a materialized view.

If however, I merely need a minor update without the full detail, like an update on the current weather; I might we willing to use a small photo taken every hour from a web cam.
That snapshot isn’t completely accurate and doesn’t have all the information; but it captures what I need with adequate frequency to tell me what I need.

I hope these analogies help more than hinder and if I have confused, please feel free to ask and I’ll be happy to try help explain a different way.
If you have other analogies that you’ve used to help explain the topic to others, I’d like to hear about them.

%d bloggers like this: