{"id":5019,"date":"2015-06-24T05:21:34","date_gmt":"2015-06-24T03:21:34","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/"},"modified":"2015-06-24T05:21:34","modified_gmt":"2015-06-24T03:21:34","slug":"multithreaded-12c-and-connect-as-sysdba","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/","title":{"rendered":"Multithreaded 12c and ‘connect \/ as sysdba’"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nIn Oracle 12c you can run Oracle processes as operating system threads, lowering the number of OS processes. But you can’t use OS authentification: you need to provide a password. Here is a way to set an environment so that you can still ‘connect \/ as sysdba’ to a multithreaded instance.<\/p>\n

Windows<\/h3>\n

I start with Windows because Oracle has always been multithreaded on windows. Are you able to use operating system authentication then? You \u00a0think so because you can ‘connect \/ as sysdba’. But look at your sqlnet.ora:<\/p>\n

SQLNET.AUTHENTICATION_SERVICES = (NTS)<\/code><\/pre>\n
You need NTS to connect locally without a password, the same authentication as when you connect remotely. If you don’t set NTS then both local and remote connections need a password.<\/p>\n
Threaded execution<\/h3>\n
Back to Linux, I’ve set my instance with multithreading:<\/p>\n
NAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nthreaded_execution                   boolean     TRUE<\/code><\/pre>\n
If I try to connect witout a password I got an error:<\/p>\n
SQL> connect \/ as sysdba\nERROR:\nORA-01017: invalid username\/password; logon denied<\/code><\/pre>\n
it’s exacly the same as if I set<\/p>\n
SQLNET.AUTHENTICATION_SERVICES = (NONE)<\/code><\/pre>\n
by default on Unix\/Linux the AUTHENTICATION_SERVICES is not set, which allows operating system suthentication for Bequeath connections.<\/p>\n
When multithreaded, I can only connect with a password:<\/p>\n
SQL> connect sys as sysdba\nEnter password:\nConnected.<\/code><\/pre>\n
But I don’t want that. I want to keep she same scripts and procedures as I had before going to multithread instance. I can put the password in an external password file (wallet) and then connect without typing the password. But then I have to use a network service name. I can use TWO_TASK environment variable to add that network service name to connections transparently, but – for waterver reason – I don’t want to connect through the listener. So let’s see how to set it up.<\/p>\n
TNS_ADMIN<\/h3>\n
I’ll setup my own SQL*Net files in a custom directory and use TNS_ADMIN to use them.<\/p>\n
$ mkdir \/home\/franck\/tns\n$ export TNS_ADMIN=\/home\/franck\/tns<\/code><\/pre>\n
Here are my ORACLE_HOME and ORACLE_SID:<\/p>\n
$ env | grep ORACLE\nORACLE_SID=DEMO11\nORACLE_BASE=\/u01\/app\/oracle\nORACLE_HOME=\/u01\/app\/oracle\/product\/12102EE<\/code><\/pre>\n
External password file<\/h3>\n
It has been described before on our blog<\/a> by Nicolas Jardot.<\/p>\n
$ mkstore -wrl $TNS_ADMIN -create\n$ mkstore -wrl $TNS_ADMIN -createCredential BEQ_DEMO11_SYS SYS<\/code><\/pre>\n
this as created the wallet containing my user (SYS) and password for the network service name BEQ_DEMO111_SYS<\/p>\n
$ ls -l\n-rwxrwx---. 1 root vboxsf 589 Jun 23 23:29 cwallet.sso\n-rwxrwx---. 1 root vboxsf   0 Jun 23 23:29 cwallet.sso.lck\n-rwxrwx---. 1 root vboxsf 544 Jun 23 23:29 ewallet.p12\n-rwxrwx---. 1 root vboxsf   0 Jun 23 23:29 ewallet.p12.lck<\/code><\/pre>\n
I have to declare the wallet in my sqlnet.ora<\/p>\n
$ cat sqlnet.ora\nWALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=\/home\/franck\/tns)))\nSQLNET.WALLET_OVERRIDE=TRUE<\/code><\/pre>\n
Beqeath connection string<\/h3>\n
Now time to define that BEQ_DEMO11_SYS network service name. I want to connect locally (not through the listener) so I define a BEQ connection string:<\/p>\n
$ cat tnsnames.ora\nBEQ_DEMO11_SYS=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=\/u01\/app\/oracle\/product\/12102EE\/bin\/oracle)(ARGV0=oracleDEMO11)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_HOME=\/u01\/app\/oracle\/product\/12102EE,ORACLE_SID=DEMO11'))<\/code><\/pre>\n
Here is how a beaqueath (PROTOCOL=BEQ) connection is defined. You need to define the PROGRAM to be run (the oracle binary) and the ARGS. You need to pass the environement variables – at least ORACLE_HOME and ORACLE_SID<\/p>\n
The ARGV0 is the name that will be displayed by the ps ‘CMD’ command, but you can put whatever you want in it (just saying… have fun but not in prod please). The convention is to add the ORACLE_SID to the binary name ‘oracle’.<\/p>\n
Then I can connect:<\/p>\n
SQL> connect \/@BEQ_DEMO11_SYS as sysdba\nConnected.<\/code><\/pre>\n
TWO_TASK<\/h3>\n
Finally, I don’t want to add the network service name in my scripts, then I can set the TWO_TASK environment variable to it. \u00a0I definitely don’t want to set it for all my environment because it can be misleading (you think you use the ORACLE_SID but you are not, you change environement with oraenv but TWO_TASK remains,…). So i set it locally when I run sqlplus.<\/p>\n
Here is an example where I set TNS_ADMIN and TWO_TASK only when calling sqlplus:<\/p>\n
$ TNS_ADMIN=$PWD TWO_TASK=BEQ_DEMO11_SYS sqlplus \/ as sysdba\n\nSQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 24 10:54:58 2015\n\nCopyright (c) 1982, 2014, Oracle.  All rights reserved.\n\nConnected to:\nOracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\nWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options<\/code><\/pre>\n
If the scripts does a ‘connect \/ as sysdba’ it still work:<\/p>\n
SQL> connect \/ as sysdba\nConnected.<\/code><\/pre>\n
but you should now that if the script is connecting with another user, TWO_TASK is still used:<\/p>\n
SQL> connect scott\/tiger\nConnected.<\/code><\/pre>\n
Note that those sessions are multithreaded even if you don’t set\u00a0DEDICATED_THROUGH_BROKER for the listener, because you’re not connecting through the listener here. More information about it in Martin Bach’s post<\/a>.<\/p>\n
Here is how to check it – process and thread id from v$process:<\/p>\n
SQL> select spid, stid, program, execution_type from v$process where addr=(select paddr from v$session where sid=sys_context('userenv','sid'));\n\nSPID                     STID                     PROGRAM              EXECUTION_TYPE\n------------------------ ------------------------ -------------------- ------------------\n21107                    21107                    oracle@VM111         PROCESS\n<\/code><\/pre>\n
and the info about it from Linux:<\/p>\n
SQL> host ps -Lfp &pid\nUID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD\noracle   21107     1 21107  0    1 11:04 ?        00:00:00 oracleDEMO11 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))\n<\/code><\/pre>\n
 <\/p>\n
TWO_TASK is coming from very old version but will be useful to run old scripts in 12c. Here is an example with threaded instance. You can use it also to connect directly to a PDB (but through listener then – you need a service).<\/p>\n
But…<\/h3>\n
There is one thing that doesn’t work as I want with external password files.\u00a0DGMGRL keeps the password provided and uses it to connect to the remote instance – which is why you need same password for sys on standby. But let’s see if it works with external password file:<\/p>\n
$ TNS_ADMIN=$PWD TWO_TASK=BEQ_DEMO11_SYS dgmgrl \/\nDGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production\n\nCopyright (c) 2000, 2013, Oracle. All rights reserved.\n\nWelcome to DGMGRL, type \"help\" for information.\nConnected as SYSDG.\nDGMGRL> switchover to demo11\nPerforming switchover NOW, please wait...\nNew primary database \"demo11\" is opening...\nOperation requires start up of instance \"DEMO12\" on database \"demo12\"\nStarting instance \"DEMO12\"...\nORA-01017: invalid username\/password; logon denied\n\nWarning: You are no longer connected to ORACLE.\n\nPlease complete the following steps to finish switchover:\n        start up instance \"DEMO12\" of database \"demo12\"\n<\/code><\/pre>\n
I have to finish the switchover manually\u00a0because the password retreived from the wallet is not used here. Same behaviour than OS authentication here. Tip: if you connect to the primary to do the switchover, then the connection to remote is detected at the begining.<\/p>\n
Final note<\/h3>\n
This is not best practice. Using external password file is a good practice of course because we should never put passwords in our scripts or in command line. Passwords are something to be only typed by human fingers. TWO_TASK and BEQ connection string are not a good practice, but only a workaround to keep old scripts compatible with new features.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . In Oracle 12c you can run Oracle processes as operating system threads, lowering the number of OS processes. But you can’t use OS authentification: you need to provide a password. Here is a way to set an environment so that you can still ‘connect \/ as sysdba’ to a multithreaded instance. […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,59],"tags":[209,25],"type_dbi":[],"class_list":["post-5019","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-oracle-12c","tag-security"],"acf":[],"yoast_head":"\nMultithreaded 12c and 'connect \/ as sysdba' - dbi Blog<\/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.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Multithreaded 12c and 'connect \/ as sysdba'\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In Oracle 12c you can run Oracle processes as operating system threads, lowering the number of OS processes. But you can’t use OS authentification: you need to provide a password. Here is a way to set an environment so that you can still ‘connect \/ as sysdba’ to a multithreaded instance. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-06-24T03:21:34+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Multithreaded 12c and ‘connect \/ as sysdba’\",\"datePublished\":\"2015-06-24T03:21:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/\"},\"wordCount\":854,\"commentCount\":0,\"keywords\":[\"Oracle 12c\",\"Security\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/\",\"name\":\"Multithreaded 12c and 'connect \/ as sysdba' - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-06-24T03:21:34+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Multithreaded 12c and ‘connect \/ as sysdba’\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Multithreaded 12c and 'connect \/ as sysdba' - dbi Blog","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.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/","og_locale":"en_US","og_type":"article","og_title":"Multithreaded 12c and 'connect \/ as sysdba'","og_description":"By Franck Pachot . In Oracle 12c you can run Oracle processes as operating system threads, lowering the number of OS processes. But you can’t use OS authentification: you need to provide a password. Here is a way to set an environment so that you can still ‘connect \/ as sysdba’ to a multithreaded instance. […]","og_url":"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/","og_site_name":"dbi Blog","article_published_time":"2015-06-24T03:21:34+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Multithreaded 12c and ‘connect \/ as sysdba’","datePublished":"2015-06-24T03:21:34+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/"},"wordCount":854,"commentCount":0,"keywords":["Oracle 12c","Security"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/","url":"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/","name":"Multithreaded 12c and 'connect \/ as sysdba' - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-06-24T03:21:34+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/multithreaded-12c-and-connect-as-sysdba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Multithreaded 12c and ‘connect \/ as sysdba’"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5019","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5019"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5019\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5019"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5019"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5019"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5019"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}