Using SERVICE_NAMES and DNS to quickly connect clients to the new server
We occasionally run into a situation when migrating a customer to a new Oracle server, where the customer needs to reconfigure a large number of workstations to connect to the new server. On top of this, there is usually a minimal amount of time for the cut-over process. Re-configuring each workstation one at a time can be tedious and time consuming, and there can be connection issues due to simple errors.
One common solution is to keep a single tnsnames.ora file on a network share for all workstations to use. This works fine, as long as you have a rock solid network (doesn’t everybody?). Even minor network disruptions or latencies will be problematic for users. However, this solution may be preferred if you will be upgrading client software on the workstations.
If you aren’t upgrading client software on the workstations, an easy way to save yourself from having to edit each individual workstation is to add the SERVICE_NAME of the original database as an additional entry to the SERVICE_NAMES parameter in the new database. This will give you 2 values for the SERVICE_NAMES parameter in the new database.
Let’s use an example of an existing oracle database that is on an unsupported Windows 2003 OS:
Oracle 220.127.116.11 Database (SERVICE_NAME=PRODDB)
The customer wants to switch to a new supported Windows 2008 server (or maybe even move to Linux), but wants to keep the database on Oracle 18.104.22.168.
This allows us to build-out a complete new database server (SERVICE_NAME=PRODDB2) in parallel on the network, and allow the customer to test their application with the new environment as long as they need. Users in production are not inconvenienced at all during the buildout or testing.
However, when it’s time to do the production cutover to the new server, several things need to be done to repoint the client workstations:
- Take the original database server offline (so it doesn’t conflict on the network).
- Change the hostname of the new DB server to match the original server (Breaks DB Console)
- Reconfigure DNS so that the original hostname now resolves to the new server IP (or change the IP on the new server to match the original server)
- Edit the SERVICE_NAMES parameter in the new server to include the SERVICE_NAME of the original database. Users can then connect using either SERVICE_NAME.
SQL> alter system set SERVICE_NAMES=PRODDB,PRODDB2 scope=BOTH
- After the hostname change, don’t forget to reconfigure DB Console, or drop and re-add the targets in Grid Control, as needed.
This means that all of the client workstations will now be connecting to the new database, using their original tnsnames.ora file. No client side reconfiguration needed, which can save considerable time, and make your cut-over much easier.
About the author
Rick Brown, Senior DBA
Rick Brown brings over 10 years of Oracle database experience to the tech team here at Guardian Eagle. Rick started with Eagle as an Oracle DBA in 2005 and now provides specialized database support and services to our clients as a Senior Oracle DBA. Rick is distinguished as an Oracle Certified Professional DBA with Administrator Certifications in Oracle Database 8i, 9i, and 11g.