{"id":127,"date":"2016-01-29T00:05:47","date_gmt":"2016-01-29T06:05:47","guid":{"rendered":"http:\/\/www.aamirharoon.com\/?p=127"},"modified":"2016-01-29T00:06:35","modified_gmt":"2016-01-29T06:06:35","slug":"database-services","status":"publish","type":"post","link":"http:\/\/www.aamirharoon.com\/?p=127","title":{"rendered":"Oracle Database Service"},"content":{"rendered":"

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<\/b>) entry didn’t need to be update for failover test?<\/p>\n

Some background info.<\/h5>\n

Our setup is simple and very common. \u00a0Just one node Primary data guard to one node Standby database. \u00a0We have CNAME (alias for physical server) which can be pointed to whichever server is primary. \u00a0In TNS entry we are using CNAME and database service name instead of database SID.<\/p>\n

Most DBAs working with RAC are aware of database services. \u00a0In standalone database environment, database service allows you to create an alias to database SID.\u00a0 So the listener is listening on SID and service name that was created.<\/p>\n

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

Here are the high level steps to perform failover test:<\/h5>\n
    \n
  1. Shutdown applications<\/li>\n
  2. Update DNS entries and flush DNS cache on application servers<\/li>\n
  3. Failover the database to standby.<\/li>\n
  4. Create or start the service on new primary server (with same name as new standby)<\/li>\n
  5. Start the applications<\/li>\n<\/ol>\n
    Here are some v$ views that can be used to view database services:<\/h5>\n
    select * from dba_services;\r\nselect * from v$services;\r\nselect * from V$ACTIVE_SERVICES;\r\nselect * from V$SERVICE_STATS;\r\n<\/pre>\n
    Here are srvctl commands<\/a> that can be used to create, check status, start and stop database services. \u00a0All of this can also be performed with dbms_service<\/a> package.<\/p>\n
    To create service:<\/h5>\n
    srvctl add service -d <database sid> -s <service name> -l primary<\/pre>\n
    To check status:<\/h5>\n
    srvctl status service -d qa<\/pre>\n
    To Start service:<\/h5>\n
    srvctl start service -d qa<\/pre>\n
    To Stop service:<\/h5>\n
    srvctl stop service -d qa<\/pre>\n
    Conclusion:<\/strong>\u00a0 There are many uses for database service in stand alone and in RAC environment. \u00a0For example, you can create one database service that can\u00a0be used by application A and another service for application B. \u00a0This allows you to filer out database performance stats to see which application is using more resources. \u00a0In RAC environment you can use it to spread out load or allow certain applications to connect to only one or two nodes.<\/p>\n
    \"oracle_database_multiple_services_associated_with_one_database\"<\/a><\/p>\n
    In following\u00a0article, Uwe Hesse talks about use of database services to differentiate between applications:<\/p>\n
    http:\/\/uhesse.com\/2012\/10\/31\/why-you-should-use-application-services-with-your-oracle-database\/<\/a><\/p>\n
    In this\u00a0article, Tim Hall talks about using it in RAC environment to spread out load:<\/p>\n
    https:\/\/oracle-base.com\/articles\/10g\/database-services-10g<\/a><\/p>\n
    Nicolas Gerard also has some good content regarding database service:<\/p>\n
    http:\/\/gerardnico.com\/wiki\/database\/oracle\/database_service<\/a><\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    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<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,3],"tags":[37,14,39,38,36,34,35],"class_list":["post-127","post","type-post","status-publish","format-standard","hentry","category-oem-cc-12c","category-11gr2","tag-11g","tag-12c","tag-data-guard","tag-dataguard","tag-dbms_service","tag-oracle-database-service","tag-srvctl"],"yoast_head":"\nOracle Database Service - The Database Handyman<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.aamirharoon.com\/?p=127\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle Database Service - The Database Handyman\" \/>\n<meta property=\"og:description\" content=\"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\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.aamirharoon.com\/?p=127\" \/>\n<meta property=\"og:site_name\" content=\"The Database Handyman\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-29T06:05:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-01-29T06:06:35+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/01\/oracle_database_multiple_services_associated_with_one_database-300x253.jpg\" \/>\n<meta name=\"author\" content=\"Aamir\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@aamir814\" \/>\n<meta name=\"twitter:site\" content=\"@aamir814\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Aamir\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.aamirharoon.com\/?p=127#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.aamirharoon.com\/?p=127\"},\"author\":{\"name\":\"Aamir\",\"@id\":\"http:\/\/www.aamirharoon.com\/#\/schema\/person\/dbcd256c4a97c6e27b7cc408200a9b63\"},\"headline\":\"Oracle Database Service\",\"datePublished\":\"2016-01-29T06:05:47+00:00\",\"dateModified\":\"2016-01-29T06:06:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.aamirharoon.com\/?p=127\"},\"wordCount\":421,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/www.aamirharoon.com\/#\/schema\/person\/dbcd256c4a97c6e27b7cc408200a9b63\"},\"image\":{\"@id\":\"https:\/\/www.aamirharoon.com\/?p=127#primaryimage\"},\"thumbnailUrl\":\"http:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/01\/oracle_database_multiple_services_associated_with_one_database-300x253.jpg\",\"keywords\":[\"11g\",\"12c\",\"data guard\",\"dataguard\",\"dbms_service\",\"Oracle database service\",\"srvctl\"],\"articleSection\":[\"OEM Cloud Control 12c\",\"Oracle Database 11gR2\"],\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.aamirharoon.com\/?p=127#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.aamirharoon.com\/?p=127\",\"url\":\"https:\/\/www.aamirharoon.com\/?p=127\",\"name\":\"Oracle Database Service - The Database Handyman\",\"isPartOf\":{\"@id\":\"http:\/\/www.aamirharoon.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.aamirharoon.com\/?p=127#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.aamirharoon.com\/?p=127#primaryimage\"},\"thumbnailUrl\":\"http:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/01\/oracle_database_multiple_services_associated_with_one_database-300x253.jpg\",\"datePublished\":\"2016-01-29T06:05:47+00:00\",\"dateModified\":\"2016-01-29T06:06:35+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.aamirharoon.com\/?p=127#breadcrumb\"},\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.aamirharoon.com\/?p=127\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en\",\"@id\":\"https:\/\/www.aamirharoon.com\/?p=127#primaryimage\",\"url\":\"http:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/01\/oracle_database_multiple_services_associated_with_one_database.jpg\",\"contentUrl\":\"http:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/01\/oracle_database_multiple_services_associated_with_one_database.jpg\",\"width\":380,\"height\":320},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.aamirharoon.com\/?p=127#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/www.aamirharoon.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Database Service\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/www.aamirharoon.com\/#website\",\"url\":\"http:\/\/www.aamirharoon.com\/\",\"name\":\"The Database Handyman\",\"description\":\"My notes about Oracle Database, APEX, OEM Cloud Control and whatever I learn\",\"publisher\":{\"@id\":\"http:\/\/www.aamirharoon.com\/#\/schema\/person\/dbcd256c4a97c6e27b7cc408200a9b63\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/www.aamirharoon.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/www.aamirharoon.com\/#\/schema\/person\/dbcd256c4a97c6e27b7cc408200a9b63\",\"name\":\"Aamir\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en\",\"@id\":\"http:\/\/www.aamirharoon.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/63cc5bb07711a1b9719cc47e13a8205072859c1aef30fac28f412baa84b9cf9b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/63cc5bb07711a1b9719cc47e13a8205072859c1aef30fac28f412baa84b9cf9b?s=96&d=mm&r=g\",\"caption\":\"Aamir\"},\"logo\":{\"@id\":\"http:\/\/www.aamirharoon.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/www.aamirharoon.com\/about\/\",\"https:\/\/x.com\/aamir814\"],\"url\":\"http:\/\/www.aamirharoon.com\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Oracle Database Service - The Database Handyman","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.aamirharoon.com\/?p=127","og_locale":"en_US","og_type":"article","og_title":"Oracle Database Service - The Database Handyman","og_description":"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","og_url":"https:\/\/www.aamirharoon.com\/?p=127","og_site_name":"The Database Handyman","article_published_time":"2016-01-29T06:05:47+00:00","article_modified_time":"2016-01-29T06:06:35+00:00","og_image":[{"url":"http:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/01\/oracle_database_multiple_services_associated_with_one_database-300x253.jpg","type":"","width":"","height":""}],"author":"Aamir","twitter_card":"summary_large_image","twitter_creator":"@aamir814","twitter_site":"@aamir814","twitter_misc":{"Written by":"Aamir","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.aamirharoon.com\/?p=127#article","isPartOf":{"@id":"https:\/\/www.aamirharoon.com\/?p=127"},"author":{"name":"Aamir","@id":"http:\/\/www.aamirharoon.com\/#\/schema\/person\/dbcd256c4a97c6e27b7cc408200a9b63"},"headline":"Oracle Database Service","datePublished":"2016-01-29T06:05:47+00:00","dateModified":"2016-01-29T06:06:35+00:00","mainEntityOfPage":{"@id":"https:\/\/www.aamirharoon.com\/?p=127"},"wordCount":421,"commentCount":0,"publisher":{"@id":"http:\/\/www.aamirharoon.com\/#\/schema\/person\/dbcd256c4a97c6e27b7cc408200a9b63"},"image":{"@id":"https:\/\/www.aamirharoon.com\/?p=127#primaryimage"},"thumbnailUrl":"http:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/01\/oracle_database_multiple_services_associated_with_one_database-300x253.jpg","keywords":["11g","12c","data guard","dataguard","dbms_service","Oracle database service","srvctl"],"articleSection":["OEM Cloud Control 12c","Oracle Database 11gR2"],"inLanguage":"en","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.aamirharoon.com\/?p=127#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.aamirharoon.com\/?p=127","url":"https:\/\/www.aamirharoon.com\/?p=127","name":"Oracle Database Service - The Database Handyman","isPartOf":{"@id":"http:\/\/www.aamirharoon.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.aamirharoon.com\/?p=127#primaryimage"},"image":{"@id":"https:\/\/www.aamirharoon.com\/?p=127#primaryimage"},"thumbnailUrl":"http:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/01\/oracle_database_multiple_services_associated_with_one_database-300x253.jpg","datePublished":"2016-01-29T06:05:47+00:00","dateModified":"2016-01-29T06:06:35+00:00","breadcrumb":{"@id":"https:\/\/www.aamirharoon.com\/?p=127#breadcrumb"},"inLanguage":"en","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.aamirharoon.com\/?p=127"]}]},{"@type":"ImageObject","inLanguage":"en","@id":"https:\/\/www.aamirharoon.com\/?p=127#primaryimage","url":"http:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/01\/oracle_database_multiple_services_associated_with_one_database.jpg","contentUrl":"http:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/01\/oracle_database_multiple_services_associated_with_one_database.jpg","width":380,"height":320},{"@type":"BreadcrumbList","@id":"https:\/\/www.aamirharoon.com\/?p=127#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.aamirharoon.com\/"},{"@type":"ListItem","position":2,"name":"Oracle Database Service"}]},{"@type":"WebSite","@id":"http:\/\/www.aamirharoon.com\/#website","url":"http:\/\/www.aamirharoon.com\/","name":"The Database Handyman","description":"My notes about Oracle Database, APEX, OEM Cloud Control and whatever I learn","publisher":{"@id":"http:\/\/www.aamirharoon.com\/#\/schema\/person\/dbcd256c4a97c6e27b7cc408200a9b63"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.aamirharoon.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en"},{"@type":["Person","Organization"],"@id":"http:\/\/www.aamirharoon.com\/#\/schema\/person\/dbcd256c4a97c6e27b7cc408200a9b63","name":"Aamir","image":{"@type":"ImageObject","inLanguage":"en","@id":"http:\/\/www.aamirharoon.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/63cc5bb07711a1b9719cc47e13a8205072859c1aef30fac28f412baa84b9cf9b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/63cc5bb07711a1b9719cc47e13a8205072859c1aef30fac28f412baa84b9cf9b?s=96&d=mm&r=g","caption":"Aamir"},"logo":{"@id":"http:\/\/www.aamirharoon.com\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/www.aamirharoon.com\/about\/","https:\/\/x.com\/aamir814"],"url":"http:\/\/www.aamirharoon.com\/?author=1"}]}},"_links":{"self":[{"href":"http:\/\/www.aamirharoon.com\/index.php?rest_route=\/wp\/v2\/posts\/127","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.aamirharoon.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.aamirharoon.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.aamirharoon.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=127"}],"version-history":[{"count":7,"href":"http:\/\/www.aamirharoon.com\/index.php?rest_route=\/wp\/v2\/posts\/127\/revisions"}],"predecessor-version":[{"id":136,"href":"http:\/\/www.aamirharoon.com\/index.php?rest_route=\/wp\/v2\/posts\/127\/revisions\/136"}],"wp:attachment":[{"href":"http:\/\/www.aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=127"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}