When moving to the Oracle 12c multitenant architecture one implication is that you have to switch to using service names in your connection strings for PDBs. Even SYSDBA connects to PDBs must use SERVICE_NAME.
\nWell, not quite necessarily. I guess customers adopting this new technology have not all managed to update their scripts. Or, software vendors and in-house development have hard-coded connection strings that cannot be changed quickly.
\nSo, Oracle hastily added a new listener parameter USE_SID_AS_SERVICE_listener. This will tell the listener to treat the SID in the connect descriptor as SERVICE_NAME.
\nAs long as the SID in the client connect descriptor matches the service name registered on the listener the connection is established.<\/p>\n
\r\n$ cat \/u01\/app\/oracle\/network\/admin\/listener.ora \r\nLISTENER =\r\n\t(ADDRESS_LIST =\r\n\t\t(ADDRESS = \r\n\t\t\t(PROTOCOL = TCP)\r\n\t\t\t(Host = oel6ora12cdb1.localdomain)\r\n\t\t\t(Port = 1521)\r\n\t\t)\r\n\t)\r\n\r\nUSE_SID_AS_SERVICE_LISTENER = ON\r\n\r\n<\/pre>\nI have two PDBs which are registered on the listener as follows.<\/p>\n
\r\n$ lsnrctl status\r\n...\r\nServices Summary...\r\nService "CDB1" has 1 instance(s).\r\n Instance "CDB1", status READY, has 1 handler(s) for this service...\r\nService "testdb1" has 1 instance(s).\r\n Instance "CDB1", status READY, has 1 handler(s) for this service...\r\nService "testdb2" has 1 instance(s).\r\n Instance "CDB1", status READY, has 1 handler(s) for this service...\r\n<\/pre>\nMy client tnsnames.ora holds entries for both PDBs, one with SERVICE_NAME (TESTDB1) and one with SID (TESTDB2).<\/p>\n
\r\n$ cat \/u01\/app\/oracle\/network\/admin\/tnsnames.ora \r\nTESTDB1 =\r\n (DESCRIPTION =\r\n\t(ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12cdb1.localdomain)(PORT = 1521))\r\n\t(CONNECT_DATA =\r\n\t (SERVER = DEDICATED)\r\n\t (SERVICE_NAME = TESTDB1)\r\n\t)\r\n )\r\n\r\nTESTDB2 =\r\n (DESCRIPTION =\r\n\t(ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12cdb1.localdomain)(PORT = 1521))\r\n\t(CONNECT_DATA =\r\n\t (SERVER = DEDICATED)\r\n\t (SID = TESTDB2)\r\n\t)\r\n )\r\n<\/pre>\nNext, I connect to both PDBs using above TNS entries.<\/p>\n
\r\n$ sqlplus \/nolog\r\n\r\nSQL> connect system\/manager@PDB1\r\nConnected.\r\n\r\nSQL> connect system\/manager@PDB2\r\nConnected.\r\n<\/pre>\nWell, that’s all fine.
\nBut, what’s going to happen if we set the DB_DOMAIN, then the db domain is appended to the service name. Ultimately, the SID parameter you have in the client connect descriptor does not match the service name anymore.<\/p>\nSince you most likely use above described feature because you cannot change the client connect descriptor this solution does not work anymore.
\nBack to square one.<\/p>\nJust for fun let’s try it anyway…but of course, for it to work I have to adjust the SID in the TNS entry for TESTDB2.<\/p>\n
\r\nSQL> show parameter db_domain\r\n\r\nNAME\t\t\t\t TYPE\t VALUE\r\n------------------------------------ ----------- ------------------------------\r\ndb_domain\t\t\t string\t localdomain\r\n<\/pre>\nOn the listener there are the two services from my pluggable databases, TESTDB1 and TESTDB2.<\/p>\n
\r\n$ lsnrctl status\r\n...\r\nServices Summary...\r\nService "CDB1.localdomain" has 1 instance(s).\r\n Instance "CDB1", status READY, has 1 handler(s) for this service...\r\nService "testdb1.localdomain" has 1 instance(s).\r\n Instance "CDB1", status READY, has 1 handler(s) for this service...\r\nService "testdb2.localdomain" has 1 instance(s).\r\n Instance "CDB1", status READY, has 1 handler(s) for this service...\r\nThe command completed successfully\r\n<\/pre>\nNow, to match the service name I change the TNS entry for TESTDB2 to following:<\/p>\n
\r\nTESTDB2.localdomain =\r\n (DESCRIPTION =\r\n\t(ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12cdb1.localdomain)(PORT = 1521))\r\n\t(CONNECT_DATA =\r\n\t (SERVER = DEDICATED)\r\n\t (SID = TESTDB2.localdomain)\r\n\t)\r\n )\r\n<\/pre>\nAt first sight it’s a bit strange to see the db domain in the SID parameter as this was never supposed to work this way. But at least we can connect again.<\/p>\n
\r\n$ sqlplus system\/manager@TESTDB2.localdomain\r\nConnected.\r\n<\/pre>\nI thought about creating a service using DBMS_SERVICE.CREATE_SERVICE but Oracle will always append the db domain to the service on the listener. And since a service in the format “PDB_NAME.DB_DOMAIN” already exists you will not be able to create it (ORA-44303: service name exists).<\/p>\n
I file this under half-baked solution…(Not that I think this feature should by used anyway. Go get your scripts and applications fixed!)<\/p>\n","protected":false},"excerpt":{"rendered":"
When moving to the Oracle 12c multitenant architecture one implication is that you have to switch to using service names in your connection strings for PDBs. Even SYSDBA connects to PDBs must use SERVICE_NAME. Well, not quite necessarily. I guess customers adopting this new technology have not all managed to update their scripts. Or, software […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-61","post","type-post","status-publish","format-standard","hentry","category-internals"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/61","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=61"}],"version-history":[{"count":2,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/61\/revisions"}],"predecessor-version":[{"id":63,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/61\/revisions\/63"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=61"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=61"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=61"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}