Over the years I have been involved in several Oracle database migration projects where all databases are moving from point A to point B. When doing these moves the TNS naming and the connect strings as a whole are a major pain point. At one client we came up with a plan to help manage the tangle of things: use DNS aliases to create a unique hostname for each database that is then used in the TNS names file. While this is not new idea (variations on using VIP addresses have been around for decades), I simply do not see enough Oracle database shops using this technique. 

Let me offer a little more detail on why I do this. I was involved with a project to migrate a large number of databases from an Exadata platform to individual virtual machines built on VMware vSphere. This meant that all of the databases on the Exadata were referenced by the same name, in this case the scan name. When it was time to start migrating the databases 1 at a time, life was going to get very complicated. 

Once this configuration issue became apparent the client was able to implement the needed DNS alias changes. Each database received its own unique name to use as a HOST parameter value. This allowed each database to be uniquely referenced in the tnsnames.ora file and the application connection strings. 

This aliasing was done well ahead of the database migrations. The client had frequent application deployments, so they simply made the DNS and TNS changes a part of these deployments. When it came time to migrate to the new VM instances there were no application changes to be made, just change the alias in DNS. 

Another use case for using DNS alias for TNS naming would be for cloud migrations. Again, this technique was used to aid database migrations for a client. The only twist was point A was the on-premises Oracle databases and point B was AWS RDS instances. The RDS instances could just as easily have been EC2 instances. The applications were simply pointed to the alias and then through the magic of DNS the application could easily be switched to the AWS instance. 

In a typical TNS entry the HOST parameter value is the actual hostname of the database host, or the scan name in a RAC configuration. This ties the database to the hostname/scan name. Therefore, any changes are the responsibility of the DBA, and the TNS names file must be updated. As anyone that has updated a TNS names file can attest to, it is very easy to make a typo that will prevent actual connections to the database. 

Typical TNS entry: 

 RPTDEV1 = 

  (DESCRIPTION = 

    (ADDRESS = (PROTOCOL = TCP)(HOST = racdb-scan)(PORT = 1521)) 

    (CONNECT_DATA = 

      (SID = RPTDEV1) 

    ) 

  ) 

The first step in using DNS aliases for TNS naming will be to establish a naming standard to use for the aliases. There are too many different naming standards available to go into too much detail here, however, something similar to what is shown here should suffice for most organizations: 

ORADB<APP><ENV><ITTERATION> 

For example, for an application named Reports, and abbreviated as RPT in a development environment, an alias name would be: 

ORADBRPTDEV01 

or if you prefer hyphens for readability 

ORA-RPT-DEV01 

This makes the new TNS entry: 

 RPTDEV1 = 

  (DESCRIPTION = 

    (ADDRESS = (PROTOCOL = TCP)(HOST = ORA-RPT-DEV01)(PORT = 1521)) 

    (CONNECT_DATA = 

      (SID = RPTDEV1) 

    ) 

  ) 

 Now that the HOST parameter is an alias it never has to be changed for as long as the database exists. Any networking changes are now at the DNS layer. No more updates to the tnsnames.ora file(s). There is a long list of changes that would normally require a change to the value for the HOST parameter for example: DB upgrades, OS upgrades, hardware upgrades, data center moves, etc.; now these tasks are greatly simplified. 

For our use case noted in the 1st paragraph (database migrations), this technique can help reduce the downtime during the switch to the new instance. Rather than spending time reconfiguring the application, no matter how automated the change process may be, pointing the application at the new instance is now a simple DNS entry change. Simply put: 

• stop the application 
• switch to the new database instance 
• switch the DNS aliases to point to the new instance 
• start the application 

 Conditionally, you may not even have to stop the application. I have run across several shops where they can simply force the application to reconnect to the database. Doing this after the DNS alias switch may be even faster and easier. 

As noted, I have done numerous wholesale moves of large numbers of databases for one reason or the other over the years. This method of database naming has simplified those moves when we were allowed to implement it. In addition, it has the potential to simplify ongoing operations.