I have a linked Oracle server on my SQL Server box. I got an email from the server team that they have migrated the Oracle server to a new box, and gave me all the new connection information.
My issue is, I just realized I have 3 tnsnames.ora files on my SQL Server box. They are in:
C:\Oracle\network\admin
C:\Oracle\product\12.2.0\client_1\Network\Admin
C:\Oracle\product\12.2.0\client_2\Network\Admin
I updated all 3 of them with the new connection information. But, of course, the server team has both Oracle servers running concurrently for the next week, so there's no way to know for sure which server I'm actually hitting right now. How do I know which tnsnames.ora file is being used by the linked server? I want to know for sure my remote queries are not going to blow up when they shut down the old Oracle server.
To test, I updated each file (one at a time) with a bogus IP address, and then tested the linked server connection (expecting the connection to fail), but each one still worked fine. So, an additional question is: does a change in the tnsnames.ora file require a reboot on the SQL Server to take effect? That seems strange to me, but who knows.
It's been almost 10 years since I have had to deal with this, but my rusty memory and a quick google makes me think I'm giving decent advice.
I believe there's a registry entry TS_ADMIN in HKLM/Software/Oracle/<home>. That should point to the correct tsnames file. If that entry is not there, then it should just be using the one in the admin folder.
As far as a restart after changing tsnames...I'm just reading that it isn't necessary. But for some reason, my spidey sense makes me want to say that I remember stopping and restarting SQL services, but not rebooting. If restarting won't upset production, then go ahead and do your test again with a service restart after each IP change.
-G
December 9, 2021 at 7:57 pm
Thanks for the info Greg. It was very helpful.
First: Yes, I was able to look in the registry in the Oracle folder. Sure enough, the "client_2" path was utilized in all the entries that had a directory path in it. Specifically, the TNS_ADMIN also had the path "C:\Oracle\product\12.2.0\client_2\Network\Admin" (the third option in my list in the original post)
Second: I played with it some more, as I was getting more and more convinced that the third option (client_2) was the current path being used. I put a fake IP address in the (client_2) tnsnames.ora file, shut down SSMS and restarted it, and then tested the linked server connection, and it failed. Of course, this was good because it proved to me that this was in fact the file being used by SQL Server. Then I put the correct (new) IP address back in the file, saved and closed, and then restarted SSMS. Then when I tested the linked server connection, it worked fine. I'm guessing maybe SSMS caches the info in the tnsnames file, which is why the connection was not breaking?
Anyhoo, now I'm satisfied that I changed the correct tnsnames file. Thanks very much!
Sidenote: this is a production server so I can't reboot it. But I can play with the linked server because I know it's not used that often.
December 11, 2021 at 11:35 am
Changing the IP Address for a machine running SQL Server is not a problem at all. You'll need to restart the SQL Server after changing the IP Address so that the SQL Server will start listening on the new IP address. get-vidmateapp.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply