Tnsnames File

Our database administrators manage a number of databases and schemas for our project. There are a number of production instances, different schemas for development, and separate databases for testing. These counts get multiplied by the different environments required for multiple versions of our system. All of these machines and instances are managed by one common "tnsnames.ora" file. The DBAs put this file out on the network in a globally accessible location. All non-production users point to this file using the TNSADMIN environment variables. Normally this arrangement works well. However once in a while, somebody messes up the tnsnames file. The result is a lot of pain.

Just this past week a couple developers encountered this sort of problem. We have been modifying the design used to print reports from our application suite. Previously we fed reports into an Oracle 6i Reports Server installed on the workstations. After moving to an Oracle 10g database on the back end, we detected some problems running the reports from the Reports Server. So we had a developer code up our own Reports Server using Visual Basic. This work around seemed to run pretty well. We just needed the application to call the Visual Basic app instead of the Reports Server. But then this fix just stopped working. In fact none of the reports seemed to be working correctly.

A couple of us developers spent a few days looking into the problem. I had been uninstalling and reinstalling Oracle software like the Reports Server and Reports Builder. None of this worked. After a while somebody realized that the tnsnames entry for reports was removed from the tnsnames file. It was a relief for the development team. We still were angry that our time was wasted. Luckily we install a local file on all our Production user workstations. The senior DBA decided it was finally time to lock down access to the tnsnames file. The trouble is that development needs access to the file in order to connect to all the databases we use.

So the senior DBA decided to "hide" the tnsnames file on the server. I tested a couple applications that use the file for connectivity. So did the senior DBA. We did not notice any problems. However one of the reports developers started seeing some weird problems in reports. She recalled out tnsnames debacle and started first by trying to research the tnsnames file. She panicked when she could not locate the tnsnames files on the network server (remember it had been hidden). I told her what the senior DBA had done. As of today I have not heard back whether she has resolved her reports problems. Maybe it is related to the hiding of the tnsnames file.

There must be a better way of providing access to the tnsnames file for connectivity while protecting it from accidental modification. Any ideas?

Tkprof to the Rescue

The DBA Manager asked me to give him the SQL for a certain operation in our application. Luckily I knew a lot about it. So I dug into our requirements, looked at the code, and explained what the query did. Then I broke down a simple example of which SQL statements were executed by the application. I was proud of myself.

Then I got another request. The DBA Manager wanted all the SQL for a given report. This was a bit more complicated. The application creates a temporary table. Then it calls an Oracle report which is just a driver for another Oracle report. I knew the type of information this setup retrieved. But it would take a while to extract the SQL. And right now I don't have a lot of time.

The DBA Manager got back to me and told me to run a trace and get the SQL results quickly that way. This was easier said than done. I had some type of bug in my Oracle Report Builder, such that I could not compile in the commands to enable session level tracing. And my database account did not have permission to enable session level tracing either. I got some help from a DBA and an Oracle Reports developer. So I had Oracle generate a trace for a session in which I only ran the report.

Unfortunately I did not have access to the machine that hosted the Oracle database. But a DBA hooked me up with the trace file. This was the first time I had done this. I studied up on the tkprof command, but ended up executing it with all the default options. I was amazed at how simple it was to let the database capture all the SQL. But my instinct told me some SQL from the report was missing. The DBA Manager told me I could just give him the tkprof output. He would take it from there.

I think I need to get a little more practice running traces. And I certainly need to get my Oracle Reports installation fixed so I get make reports changes. Perhaps I can also obtain access to the UNIX box where we run our main Oracle database. Then I can run traces on my own and not need a DBA to get me the trace file each time. I have the feeling that me and tkprof are going to get along very well.