Multithreaded 12c and ‘connect / as sysdba’

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.

Windows

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.

Threaded execution

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.

TNS_ADMIN

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.

TWO_TASK

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 12.1.0.2.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 12.1.0.2.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).

But…

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 12.1.0.2.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.

Final note

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.