I am upgrade/migrating from 11g to 12.1.0.2. One of my legacy requirements is that we are able to use OS Authentication, however I have been stumped in trying to get this to work. The database server is running Redhat 7.
From lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-JAN-2016 15:57:43
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=simonsrv-2015.server.rpi.edu)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 09-DEC-2015 18:17:47
Uptime 25 days 21 hr. 39 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/simonsrv-2015/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=simonsrv-2015.server.rpi.edu)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=simonsrv-2015.server.rpi.edu)(PORT=5500))(Security=(my_wallet_directory=/oracle/product/12.1.0/dbhome_1/admin/sim7/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "sim7.server.rpi.edu" has 1 instance(s).
Instance "sim7", status READY, has 1 handler(s) for this service...
Service "sim7XDB.server.rpi.edu" has 1 instance(s).
Instance "sim7", status READY, has 1 handler(s) for this service...
Service "simon.server.rpi.edu" has 1 instance(s).
Instance "sim7", status READY, has 1 handler(s) for this service...
And select service_id,name,pdb from v$services; returns
---------- ---------------------------------------------------------------- ------------------------------
| 6 simon.server.rpi.edu |
|
|
|
|
SIMON |
My Unix environment (edited for space - non oracle values removed)
HOSTNAME=simonsrv-2015.server.rpi.edu
SHELL=/bin/bash
USER=finkej
LD_LIBRARY_PATH=/oracle/product/12.1.0/dbhome_1//lib
TWO_TASK=simon
ORACLE_SID=SIMON
ORACLE_BASE=/oracle/product/12.1.0/dbhome_1/
PATH=/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/oracle/product/12.1.0/dbhome_1//bin
TNS_ADMIN=/oracle/product/12.1.0/dbhome_1/network/admin/
LOGNAME=finkej
ORACLE_HOME=/oracle/product/12.1.0/dbhome_1/
When I try sqlplus, I get: (after the first failure, I entered the password - I then altered the user to be identified externally - still no connection, but now the PW doesn't work)
sqlplus /
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 4 16:42:28 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: ops$finkej
Enter password:
Last Successful login time: Mon Jan 04 2016 16:26:11 -05:00
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
SQL> show user
USER is "OPS$FINKEJ"
SQL> show con_name
CON_NAME
------------------------------
SIMON
SQL> show parameters os_auth
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE
I then tried changing the environment a bit:
-bash-4.2$ export TWO_TASK=simon.server.rpi.edu
-bash-4.2$ export ORACLE_SID=simon.server.rpi.edu
-bash-4.2$ sqlplus /
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 4 16:57:13 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
I do have the following stanza in $TNS_ADMIN/tnsnames.ora : (Although from my understanding of OS Authentication, if we hit TNSNames.Ora, we have already lost)
SIMON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = simonsrv-2015.server.rpi.edu)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = simon.server.rpi.edu)
)
)
SIM7 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = simonsrv-2015.server.rpi.edu)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sim7.server.rpi.edu)
)
)
I also cleared (unset) TWO_TASK, and went through a bunch of ORACLE_SIDs, and got the following (the second number changed, the first was always 3640)
export ORACLE_SID=simon.server.rpi.edu
export ORACLE_SID=simon
export ORACLE_SID=SIMON
export ORACLE_SID=Simon
sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 4 17:19:40 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> connect /
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3640
Additional information: 872272387
Process ID: 0
In reading a lot of different articles and document pages, I never found an example of using an OPS$ account (without password) with a PDB. The target application will actually be using OCI-Connect to connect to the database - but I wanted to see if I could get SQL*PLUS to work first with the OPS$ accounts.