{"id":1844,"date":"2019-03-20T17:20:16","date_gmt":"2019-03-20T15:20:16","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1844"},"modified":"2020-08-18T16:03:43","modified_gmt":"2020-08-18T14:03:43","slug":"svcstat","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/svcstat\/","title":{"rendered":"Oracle Clusterware Services Status at a glance, fast!"},"content":{"rendered":"<p>If you use Oracle Clusterware or you deploy your databases to the Oracle Cloud, you probably have some application services defined with <strong>srvctl<\/strong> for your database.<\/p>\n<p>If you have many databases, services and nodes, it might be annoying, when doing maintenance or service relocation, to have a quick overview about how services are distributed across the nodes and what&#8217;s their status.<\/p>\n<p>With srvctl (the official tool for that), it is a per-database operation:<\/p>\n<pre class=\"lang:plsql highlight:0 decode:true \">$ srvctl status service\r\nPRKO-2082 : Missing mandatory option -db\r\n<\/pre>\n<p>If you have many databases, you have to run db by db.<\/p>\n<p>It is also slow! For example, this database has 20 services. Getting the status takes 27 seconds:<\/p>\n<pre class=\"lang:plsql highlight:0 decode:true \"># [ oracle@server1:\/home\/oracle\/ [15:52:00] [11.2.0.4.0 [DBMS EE] SID=HRDEV1] 1 ] #\r\n$ time srvctl status service -d hrdev_site1\r\nService SERVICE_NUMBER_01 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_02 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_03 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_04 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_05 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_06 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_07 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_08 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_09 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_10 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_11 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_12 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_13 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_14 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_15 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_16 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_17 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_18 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_19 is running on instance(s) HRDEV4\r\nService SERVICE_NUMBER_20 is running on instance(s) HRDEV4\r\n\r\nreal    0m27.858s\r\nuser    0m1.365s\r\nsys     0m1.143s<\/pre>\n<p>Instead of operating row-by-row (get the status for each service), why not relying on the cluster resources with <strong>crsctl<\/strong> and get the big picture once?<\/p>\n<pre class=\"lang:plsql highlight:0 decode:true\">$ time crsctl stat res -f -w \"(TYPE = ora.service.type)\"\r\n...\r\n...\r\n\r\nreal    0m0.655s\r\nuser    0m0.169s\r\nsys     0m0.098s<\/pre>\n<p><strong>crsctl stat res -f\u00a0<\/strong> returns a list of ATTRIBUTE_NAME=value for each service, eventually more than one if the service is not singleton\/single instance\u00a0 but uniform\/multi instance.<\/p>\n<p>By parsing them with some awk code can provide nice results!<\/p>\n<p>STATE, INTERNAL_STATE and TARGET are useful in this case and might be used to display colours as well.<\/p>\n<ul>\n<li>Green: Status ONLINE, Target ONLINE, STABLE<\/li>\n<li>Black: Status OFFLINE, Target OFFLNE, STABLE<\/li>\n<li>Red: Status ONLINE, Target OFFLINE, STABLE<\/li>\n<li>Yellow: all other cases<\/li>\n<\/ul>\n<p>Here&#8217;s the code:<\/p>\n<pre class=\"lang:sh decode:true\">if [ -f \/etc\/oracle\/olr.loc ] ; then\r\n        export ORA_CLU_HOME=`cat \/etc\/oracle\/olr.loc 2&gt;\/dev\/null | grep crs_home | awk -F= '{print $2}'`\r\n        export CRS_EXISTS=1\r\n        export CRSCTL=$ORA_CLU_HOME\/bin\/crsctl\r\nelse\r\n        export CRS_EXISTS=0\r\nfi\r\n\r\nsvcstat ()\r\n{\r\n    if [ $CRS_EXISTS -eq 1 ]; then\r\n        ${CRSCTL} stat res -f -w \"(TYPE = ora.service.type)\" | awk -F= '\r\nfunction print_row() {\r\n        dbbcol=\"\";\r\n        dbecol=\"\";\r\n        instbcol=\"\";\r\n        instecol=\"\";\r\n        instances=res[\"INSTANCE_COUNT 1\"];\r\n        for(i=1;i&lt;=instances;i++) {\r\n                # if at least one of the services is online, the service is online (then I paint it green)\r\n                if (res[\"STATE \" i] == \"ONLINE\" ) {\r\n                        dbbcol=\"\\033[0;32m\";\r\n                        dbecol=\"\\033[0m\";\r\n                }\r\n        }\r\n        # db unique name is always the second part of the resource name\r\n        # because it does not change, I can get it once from the resource name\r\n        res[\"DB_UNIQUE_NAME\"]=substr(substr(res[\"NAME\"],5),1,index(substr(res[\"NAME\"],5),\".\")-1);\r\n\r\n        # same for service name\r\n        res[\"SERVICE_NAME\"]=substr(res[\"NAME\"],index(substr(res[\"NAME\"],5),\".\")+5,length(substr(res[\"NAME\"],index(substr(res[\"NAME\"],5),\".\")+5))-4);\r\n\r\n        #starting printing the first part of the information\r\n        printf (\"%s%-24s %-30s%s\",dbbcol, res[\"DB_UNIQUE_NAME\"], res[\"SERVICE_NAME\"], dbecol);\r\n\r\n        # here, instance need to map to the correct server.\r\n        # the mapping is node by attribute TARGET_SERVER (not last server)\r\n        for ( n in node ) {\r\n                node_name=node[n];\r\n                status[node_name]=\"\";\r\n                for (i=1; i&lt;=instances; i++) {\r\n                        # we are on the instance that matches the server\r\n                        if (node_name == res[\"TARGET_SERVER \" i]) {\r\n                                res[\"SERVER_NAME \" i]=node_name;\r\n                                if (status[node_name] !~ \"ONLINE\") {\r\n                                        # when a service relocates both instances get the survival target_server\r\n                                        # but just one is ONLINE... so we need to get always the ONLINE one.\r\n                                        #printf(\"was::%s:\", status[node_name]);\r\n                                        status[node_name]=res[\"STATE \" i];\r\n                                }\r\n\r\n                                # colors modes\r\n                                if ( res[\"STATE \" i] == \"ONLINE\" &amp;&amp; res[\"INTERNAL_STATE \" i] == \"STABLE\" ) {\r\n                                        # online and stable: GREEN\r\n                                        status[node_name]=sprintf(\"\\033[0;32m%-14s\\033[0m\", status[node_name]);\r\n                                }\r\n                                else if ( res[\"STATE \" i] != \"ONLINE\" &amp;&amp; res[\"INTERNAL_STATE \" i] == \"STABLE\" ) {\r\n                                        # offline and stable\r\n                                        if ( res[\"TARGET \" i] == \"OFFLINE\" ) {\r\n                                                # offline, stable, target offline: BLACK\r\n                                                status[node_name]=sprintf(\"%-14s\", status[node_name]);\r\n                                        }\r\n                                        else {\r\n                                                # offline, stable, target online: RED\r\n                                                status[node_name]=sprintf(\"\\033[0;31m%-14s\\033[0m\", status[node_name]);\r\n                                        }\r\n                                }\r\n                                else {\r\n                                        # all other cases: offline and starting, online and stopping, clearning, etc.: YELLOW\r\n                                        status[node_name]=sprintf(\"\\033[0;33m%-14s\\033[0m\", status[node_name]);\r\n                                }\r\n                                #printf(\"%s %s %s %s\\n\", status[node_name], node[n], res[\"STATE \" i], res[\"INTERNAL_STATE \" i]);\r\n                        }\r\n                }\r\n               printf(\" %-14s\", status[node_name]);\r\n        }\r\n        printf(\"\\n\");\r\n}\r\nfunction pad (string, len, char) {\r\n        ret = string;\r\n        for ( i = length(string); i&lt;len ; i++) {\r\n                ret = sprintf(\"%s%s\",ret,char);\r\n        }\r\n        return ret;\r\n}\r\nBEGIN {\r\n        debug = 0;\r\n        first = 1;\r\n        afterempty=1;\r\n        # this loop should set:\r\n        # node[1]=server1; node[2]=server2; nodes=2;\r\n        nodes=0;\r\n        while (\"olsnodes\" | getline a) {\r\n                nodes++;\r\n                node[nodes] = a;\r\n        }\r\n        fmt=\"%-24s %-30s\";\r\n        printf (fmt, \"DB_Unique_Name\", \"Service_Name\");\r\n        for ( n in node ) {\r\n                printf (\" %-14s\", node[n]);\r\n        }\r\n        printf (\"\\n\");\r\n        printf (fmt, pad(\"\",24,\"-\"), pad(\"\",30,\"-\"));\r\n        for ( n in node ) {\r\n                printf (\" %s\", pad(\"\",14,\"-\"));\r\n        }\r\n        printf (\"\\n\");\r\n\r\n}\r\n# MAIN awk svcstat\r\n{\r\n        if ( $1 == \"NAME\" ) {\r\n                if ( first != 1 &amp;&amp; res[\"NAME\"] == $2 ) {\r\n                        if ( debug == 1 ) print \"Secondary instance\";\r\n                        instance++;\r\n                }\r\n                else {\r\n                        if ( first != 1 ) {\r\n                                print_row();\r\n                        }\r\n                        first = 0;\r\n                        instance=1;\r\n                        delete res;\r\n                        res[\"NAME\"] = $2;\r\n                }\r\n        }\r\n        else  {\r\n                res[$1 \" \" instance] = $2 ;\r\n\r\n        }\r\n}\r\nEND {\r\n        #if ( debug == 1 ) for (key in res) { print key \": \" res[key] }\r\n        print_row();\r\n}\r\n';\r\n    else\r\n        echo \"svcstat not available on non-clustered environments\";\r\n        false;\r\n    fi\r\n}<\/pre>\n<p>Here&#8217;s what you can expect, for 92 services distributed on 4 nodes and a dozen of databases (the output is snipped and the names are masked):<\/p>\n<pre class=\"lang:plsql highlight:0 decode:true\">$ time svcstat\r\nDB_Unique_Name     Service_Name       server1  server2  server3  server4\r\n------------------ ------------------ -------- -------- -------- --------\r\nhrdev_site1        SERVICE_NUMBER_01                             ONLINE\r\nhrdev_site1        SERVICE_NUMBER_02                             ONLINE\r\n...\r\nhrdev_site1        SERVICE_NUMBER_20                             ONLINE\r\nhrstg_site1        SERVICE_NUMBER_21                    ONLINE  \r\nhrstg_site1        SERVICE_NUMBER_22                    ONLINE  \r\n...\r\nhrstg_site1        SERVICE_NUMBER_41                    ONLINE  \r\nhrtest_site1       SERVICE_NUMBER_42           ONLINE           \r\nhrtest_site1       SERVICE_NUMBER_43           ONLINE           \r\n...\r\nhrtest_site1       SERVICE_NUMBER_62           ONLINE           \r\nhrtest_site1       SERVICE_NUMBER_63           ONLINE           \r\nhrtest_site1       SERVICE_NUMBER_64           ONLINE           \r\nhrtest_site1       SERVICE_NUMBER_65           ONLINE           \r\nhrtest_site1       SERVICE_NUMBER_66           ONLINE           \r\nerpdev_site1       SERVICE_NUMBER_67  ONLINE                    \r\nerptest_site1      SERVICE_NUMBER_68  ONLINE                    \r\ncmsstg_site1       SERVICE_NUMBER_69  ONLINE                    \r\ncmsstg_site1       SERVICE_NUMBER_70  ONLINE                    \r\n...\r\ncmsstg_site1       SERVICE_NUMBER_74  ONLINE                    \r\ncmsstg_site1       SERVICE_NUMBER_75  ONLINE                    \r\ncmstest_site1      SERVICE_NUMBER_76  ONLINE                    \r\n...\r\ncmstest_site1      SERVICE_NUMBER_81  ONLINE                    \r\nkbtest_site1       SERVICE_NUMBER_82                    ONLINE           \r\n...\r\nkbtest_site1       SERVICE_NUMBER_84                    ONLINE           \r\nreporting_site1    SERVICE_NUMBER_85  ONLINE                    \r\npaydev_site1       SERVICE_NUMBER_86           ONLINE           \r\npayrep_site1       SERVICE_NUMBER_87           ONLINE           \r\n...\r\npaytest_site1      SERVICE_NUMBER_90           ONLINE           \r\npaytest_site1      SERVICE_NUMBER_91           ONLINE           \r\ncrm_site1          SERVICE_NUMBER_92                             ONLINE\r\n\r\nreal    0m0.358s\r\nuser    0m0.232s\r\nsys     0m0.134s<\/pre>\n<p>I&#8217;d be curious to know if it works well for your environment, please comment here. \ud83d\ude42<\/p>\n<p>Thanks<\/p>\n<p>&#8212;<\/p>\n<p>Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you use Oracle Clusterware or you deploy your databases to the Oracle Cloud, you probably have some application services defined with srvctl for your database. If you have many databases, services and nodes, it might be annoying, when doing &hellip; <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/svcstat\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[321,327,326,309,3,52,308,149],"tags":[],"class_list":["post-1844","post","type-post","status-publish","format-standard","hentry","category-aced","category-oracle-maa","category-oracle","category-oracle-cloud","category-oracledb","category-12c","category-oracle-database-18c","category-oracle-rac"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1844","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1844"}],"version-history":[{"count":2,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1844\/revisions"}],"predecessor-version":[{"id":1846,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1844\/revisions\/1846"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1844"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1844"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1844"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}