Tuesday, July 26, 2011

Managing Database Entries (tnsnames.ora)

For the past 18 months or so I've been arguing that if you would just manage your tnsnames.ora file, things would be much easier. I'm talking about through your various SDLC environments, DEV --> QA --> PROD.

This is true for tools like OBIEE.

In OBIEE, you can use the OCI client and specify your tnsname entry for a particular connection. Of course you'll still have to change the password for the connection, but you'll never be at risk of accidentally connecting to the wrong database.

Guess what? My worst case scenario occurred.

Here's what I have suggested; use a generic name for your connection. Let's say TESTING. How it works right now is we have one for each environment, TESTING_DEV, TESTING_QA and TESTING_PROD.

Each server has a tnsnames.ora file with every single connection.

For OBIEE, that means not only changing the password for each environment, but changing the DNS (TNS) entry as well.

Only bad things can come from this.

(I realize there are much more sophisticated ways of managing this, OID (I believe) for instance, but that's outside the scope).

So what happened?

Last week I built out an Informatica PowerCenter server which connected to the Dev database. Following that, I wrote up the instructions, including the particulars of my installation.

The next person up the chain, installing the QA software, read it literally.

Guess what happened?

The Dev server got borked because they used the Dev connection information and all the configuration stuff (technical term) got messed up.

Guess what else?

I get to rebuild the dev machine.

I would contend that the QA server should only have an entry for the QA database...that way this type of thing would never occur. If we had used a generic name for the database, say TESTING, I wouldn't be working tonight.

Something to think about when you end up managing, not only multiple servers, but multiple "platforms" as well.


Martin Berger said...

Can you somehow separate the different systems so the connection can not be established at all?
Depending on your possibilities something like firewalls (if DBs are on different hosts in other subnet), sqlnet.ora - TCP.INVITED_NODES or even logon-trigger can do so.
By arguing for such things, I'm always the bad DBA which does not gives the develoopers the flexibility they would like to have; You just showed sometimes they must be saved from themself.

oraclenerd said...


I'm all for segregation at the lowest possible level.

I'm a developer but I don't want this kind of responsibility...thankfully the installation (Informatica in this case) was relatively simple and we're just getting started...

It's just frustrating. I want, no I need to be protected from myself. I'm a dangerous individual! :)