Oracle Database Service

Recently at my work place for the first time, I planned and executed QA environment failover test to standby database and was asked how come Transparent Network Substrate (TNS) entry didn’t need to be update for failover test?

Some background info.

Our setup is simple and very common.  Just one node Primary data guard to one node Standby database.  We have CNAME (alias for physical server) which can be pointed to whichever server is primary.  In TNS entry we are using CNAME and database service name instead of database SID.

Most DBAs working with RAC are aware of database services.  In standalone database environment, database service allows you to create an alias to database SID.  So the listener is listening on SID and service name that was created.

This method allows you to have a TNS entry that is not bound to any physical server or Oracle SID.  With little outage you can failover to any database and update DNS entry to point to current primary server.  That is all (assuming your application is configure with correct TNS name and jdbc url).

Here are the high level steps to perform failover test:

1. Shutdown applications
2. Update DNS entries and flush DNS cache on application servers
3. Failover the database to standby.
4. Create or start the service on new primary server (with same name as new standby)
5. Start the applications

Here are some v$ views that can be used to view database services:

select * from dba_services;
select * from v$services;
select * from V$ACTIVE_SERVICES;
select * from V$SERVICE_STATS;

Here are srvctl commands that can be used to create, check status, start and stop database services.  All of this can also be performed with dbms_service package.

To create service:

srvctl add service -d <database sid> -s <service name> -l primary

To check status:

srvctl status service -d qa

To Start service:

srvctl start service -d qa

To Stop service:

srvctl stop service -d qa

Conclusion:  There are many uses for database service in stand alone and in RAC environment.  For example, you can create one database service that can be used by application A and another service for application B.  This allows you to filer out database performance stats to see which application is using more resources.  In RAC environment you can use it to spread out load or allow certain applications to connect to only one or two nodes.

In following article, Uwe Hesse talks about use of database services to differentiate between applications:

http://uhesse.com/2012/10/31/why-you-should-use-application-services-with-your-oracle-database/

In this article, Tim Hall talks about using it in RAC environment to spread out load:

https://oracle-base.com/articles/10g/database-services-10g

Nicolas Gerard also has some good content regarding database service:

http://gerardnico.com/wiki/database/oracle/database_service