This weeks topic is services.
From the beginning for those who are old enough to remember (pre-RAC) connection strings. All we really cared about was
Host:database:port
Then with Oracle 9, service_names was introduced. Over time, if you were like me, you got used to the new connect string format using SERVICE_NAME= rather than the old SID=.
You are probably also wondering why should I care ?
My goal in this weeks post is to convince everyone to take advantage of service_name and actually use service names in your non-RAC Database.
For those familiar with RAC, you probably already know about services, and use them to balance the connections to specific nodes. But in a non-rac environment, service names also have benefits.
In this example (like Oracles example), I am going to say our database name is ACMEDB.
We have 3 applications connecting to ACMEDB..
GL -- online general ledger processing
AP -- online accounts payable processing
BATCH -- Batch reporting.
I would propose the following configuration. Have separater service_names for each application, and don't have your application use the ACMEDB service_name.
What are the advantages of doing this ?
1) You can use resource manager to control the usage of resources by service_name.. You can set the priority of BATCH processing to be low. This means BATCH will only use any "available" resource, rather than fight for resources with AP and GL.
2) You can monitor resource usage by service_name. The AWR report gives a summary by service. Everyone gets the question... where is all the CPU/processing power going ? AP or GL. This will answer that question.
You can also query the catalogs for service usage, and service_name is a column in the v$session view.
select service_name,stat_name,value from v$service_stats
3) You can stop a specific app from connecting, and use an oracle provided procedure to kill current connections.. Want to lock out GL but not AP, this is the way do it.
4) Maintenance windows .. Ever worry about applications connecting and updating the database while you are doing maintenance ? If you shutdown these services (alter system set service_name='acmedb' scope=memory), you stop the application from connecting, but you can still connect using the database service (or your physical standby can still connect).
These are all good reasons for using services, and you should be asking your self .. Why not ??? Why aren't I using services for Non-RAC databases.
Next week.... Service names for RAC.. why should I use it ?