I am an Oracle Database Administrator in a company based in Wah Cantt, Pakistan and get quite the moments when you can actually see me plucking my hairs and biting my nails . Sometimes I feel more dumber than usually I feel I am. Mornings like today remind me how hapless I am. The story goes like this.
A developer asked me to create a database link from one database to other. Database link is used to connect two databases in Oracle, so that you can share data. The user in one database (i.e prod) was ss1 and its password was ss1. The user in the other database (i.e. ias) was ss2 and its password was ss2. The prod is our production database and ias is the repository for application server. First I made an entry in tns file (a network configuration file for the connectivity to Oracle databases), and connected to ias db and the ss1 got connected to ss2. But when I created database link, things just got ugly and no connection was made.
I spared no option as far as I knew. I changed the net service name, I changed the host name, I changed the domain name, every thing. But Na Jee. After smashing my head every where, then I logged onto my local db and then created a db link and it worked!!!!!
It was a problem with tns file. The prod db was using the tns file of server and I was making changes to local tns. The funny thing is that SQL*Plus was working ok with local tns, but when it came to db link , it was using severs’ tns.
So my dears , moral of the story is that always keep the servers configuration files like tnsnames and sqlnetora in mind, whenever connecting objects of database server to some other database servers or for any servers for that matter.