A mystery issue

A mystery issue just got resolved.

A team complained to me that one of their jobs sometimes failed and sometimes worked well. The job calls a database stored procedure to write a CSV file to a directory on the database server.

So they engaged both the Unix admin and me, the DBA. I had them run the job manually from the server locally using SQLplus, and it worked. Unix admin checked the privileges on the directory and it was 777. 

So I am very sure nothing is wrong on the database server side. Even they insisted that the only thing that changed was that we patched the database server last week.

At first, I thought it was a network blip. But the network team stated there were no issues or maintenance.

Then, I asked the app team to provide the tnsnames.ora file. The TNS entry explains the issue. There were two entries in one that was used to connect to the database. One is pointing to the DR and the other is pointing to Primary. When the primary is used, it works. When the DR is picked, it fails.

The app team said they just copied and pasted so they didn't check.

Sigh...