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.
I start with Windows because Oracle has always been multithreaded on windows. Are you able to use operating system authentication then? You think so because you can ‘connect / as sysdba’. But look at your sqlnet.ora:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
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.
Back to Linux, I’ve set my instance with multithreading:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ threaded_execution boolean TRUE
If I try to connect witout a password I got an error:
SQL> connect / as sysdba ERROR: ORA-01017: invalid username/password; logon denied
it’s exacly the same as if I set
SQLNET.AUTHENTICATION_SERVICES = (NONE)
by default on Unix/Linux the AUTHENTICATION_SERVICES is not set, which allows operating system suthentication for Bequeath connections.
When multithreaded, I can only connect with a password:
SQL> connect sys as sysdba Enter password: Connected.
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.
I’ll setup my own SQL*Net files in a custom directory and use TNS_ADMIN to use them.
$ mkdir /home/franck/tns $ export TNS_ADMIN=/home/franck/tns
Here are my ORACLE_HOME and ORACLE_SID:
$ env | grep ORACLE ORACLE_SID=DEMO11 ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/12102EE
External password file
It has been described before on our blog by Nicolas Jardot.
$ mkstore -wrl $TNS_ADMIN -create $ mkstore -wrl $TNS_ADMIN -createCredential BEQ_DEMO11_SYS SYS
this as created the wallet containing my user (SYS) and password for the network service name BEQ_DEMO111_SYS
$ ls -l -rwxrwx---. 1 root vboxsf 589 Jun 23 23:29 cwallet.sso -rwxrwx---. 1 root vboxsf 0 Jun 23 23:29 cwallet.sso.lck -rwxrwx---. 1 root vboxsf 544 Jun 23 23:29 ewallet.p12 -rwxrwx---. 1 root vboxsf 0 Jun 23 23:29 ewallet.p12.lck
I have to declare the wallet in my sqlnet.ora
$ cat sqlnet.ora WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/franck/tns))) SQLNET.WALLET_OVERRIDE=TRUE
Beqeath connection string
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:
$ cat tnsnames.ora BEQ_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'))
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
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’.
Then I can connect:
SQL> connect /@BEQ_DEMO11_SYS as sysdba Connected.
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. I 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.
Here is an example where I set TNS_ADMIN and TWO_TASK only when calling sqlplus:
$ TNS_ADMIN=$PWD TWO_TASK=BEQ_DEMO11_SYS sqlplus / as sysdba SQL*Plus: Release 22.214.171.124.0 Production on Wed Jun 24 10:54:58 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 126.96.36.199.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
If the scripts does a ‘connect / as sysdba’ it still work:
SQL> connect / as sysdba Connected.
but you should now that if the script is connecting with another user, TWO_TASK is still used:
SQL> connect scott/tiger Connected.
Note that those sessions are multithreaded even if you don’t set DEDICATED_THROUGH_BROKER for the listener, because you’re not connecting through the listener here. More information about it in Martin Bach’s post.
Here is how to check it – process and thread id from v$process:
SQL> select spid, stid, program, execution_type from v$process where addr=(select paddr from v$session where sid=sys_context('userenv','sid')); SPID STID PROGRAM EXECUTION_TYPE ------------------------ ------------------------ -------------------- ------------------ 21107 21107 oracle@VM111 PROCESS
and the info about it from Linux:
SQL> host ps -Lfp &pid UID PID PPID LWP C NLWP STIME TTY TIME CMD oracle 21107 1 21107 0 1 11:04 ? 00:00:00 oracleDEMO11 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))
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).
There is one thing that doesn’t work as I want with external password files. DGMGRL 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:
$ TNS_ADMIN=$PWD TWO_TASK=BEQ_DEMO11_SYS dgmgrl / DGMGRL for Linux: Version 188.8.131.52.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> switchover to demo11 Performing switchover NOW, please wait... New primary database "demo11" is opening... Operation requires start up of instance "DEMO12" on database "demo12" Starting instance "DEMO12"... ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up instance "DEMO12" of database "demo12"
I have to finish the switchover manually because 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.
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.