Databases or schemas tend to get moved around between different servers or even datacenters for hardware upgrades, consolidations or other migrations. And while the work that needs to be done is pretty straight forward for DBAs, I find the most annoying aspect of that is updating all client connect strings and tns entries used with new IP addresses and – if not using services – also the SID as the instance name might have changed.
That process can be simplified a lot when following a simple good practice of creating an extra service for each application or schema and along with that service also a DNS name for that IP. With that in place, a database can be migrated without the need to touch client connection parameters or tns aliases. All that is needed will be to migrate the database or schema, create the service name on the new instance and update the DNS record to the new machine.
Here is an example. I am migrating a schema from an 11g single instance on my laptop to a RAC database in the oracle public cloud. I am connecting to that database with blog_demo.pythian.com both as the hostname (faked through /etc/hosts instead of proper DNS for this demo) and the service name. As an application I am connecting to the database with sqlcl and a static connection string. Just remember that the whole, and only point of this demo is to migrate the schema without having to change that connect string.
brost$ ping -c 1 blog_demo.pythian.com PING blog_demo.pythian.com (192.168.78.101): 56 data bytes 64 bytes from 192.168.78.101: icmp_seq=0 ttl=64 time=0.790 ms brost$ ./sqlcl/bin/sql brost/******@blog_demo.pythian.com/blog_demo.pythian.com SQLcl: Release 220.127.116.11.175.1027 RC on Mon Sep 05 17:50:11 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- ORCL
Next I migrated my schema with datapump and imported to a PDB running on a 12c RAC database.
Then added the service name BLOG_DEMO to PDB1 on the database ORCL42.
$ srvctl add service -db orcl42 -pdb pdb1 -service blog_demo -preferred orcl421,orcl422 $ srvctl start service -db orcl42 -service blog_demo
Updated the DNS or as in this simplified demo my /etc/hosts and now I can connect with the same connection string. Note that the IP, the instance_name and the version have changed without the need to modify the connection string.
brost$ ping -c 1 blog_demo.pythian.com PING blog_demo.pythian.com (22.214.171.124): 56 data bytes brost$ ./sqlcl/bin/sql brost/******@blog_demo.pythian.com/blog_demo.pythian.com SQLcl: Release 126.96.36.199.175.1027 RC on Mon Sep 05 18:05:11 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Mon Sep 05 2016 18:04:50 +02:00 Connected to: Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- orcl421
Note that with a proper DNS and a RAC target you would want to create A-records for the 3 SCAN IPs.
Other posts in this series
You can watch me talk briefly about this and other things that you can do with properly configured services in the video below or follow the links to other parts in this series.
When creating services for your applications to connect to a schema, also create a DNS entry for that and use this DNS name and the service for all client and application connections instead of using the hostname and SID. This might initially look like overhead but allows for flexibility when migrating schemas or databases to other systems. Updating DNS and creating a new service on the target machine can be changed in central places and saves updating potentially hundreds of client connect strings or tnsnames across the enterprise.