Hi,
I have installed oracle client 11.2.0.4 & oracle 12c db onto my desktop Windows 10 x64. I need 11g client to connect to my prod 9i db in which we are in a process of upgrading the db & applications. The windows environment variable is currently set 12c path before 11g path. I also configure TNS_ADMIN=C:\app\oracle\product\12.2.0\dbhome_1\network\admin\tnsnames.ora; however, when I sqlplus to my prod 9i db, 12c sqlplus.exe refuses the connection to my 9i db due to error ORA-03134: Connections to this server version are no longer supported.
In addition, I can connect my prod 9i db, if I execute C:\app\oracle\product\11.2.0\client_1\bin\sqlplus.exe file.
Question: Is there a workaround for this issue? And am I missing something in my listener.ora & tnsnames.ora files? I'm unable to connect to my pdb through listener.
PS Z:\> tnsping orclpdb
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 25-MAY-2018 13:19:41
Used parameter files:
C:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XXX.XXX.X)(PORT=1521)) (CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=ORCLPDB)))
OK (10 msec)
PS Z:\> sqlplus "sys@orclpdb as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 25 13:20:25 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
$ cat sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES=(NTS)
LOG_DIRECTORY_CLIENT=C:\app\oracle\product\12.2.0\dbhome_1\network\log
LOG_FILE_CLIENT=sqlnet_log
SQLNET.EXPIRE_TIME=0
TRACE_DIRECTORY_CLIENT=C:\app\oracle\product\12.2.0\dbhome_1\network\trace
TRACE_FILE_CLIENT=sqlnet_trc
TRACE_FILELEN_CLIENT=100
TRACE_FILENO_CLIENT=3
TRACE_LEVEL_CLIENT=OFF
TRACE_TIMESTAMP_CLIENT=ON
TRACE_UNIQUE_CLIENT=ON
USE_DEDICATED_SERVER=OFF
$ cat listener.ora
# listener.ora Network Configuration File: C:\app\oracle\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl12ctest)
(ORACLE_HOME = C:\app\oracle\product\12.2.0\dbhome_1)
(SID_NAME = ORCL12CTEST)
)
(SID_DESC =
(GLOBAL_DBNAME = orclpdb) <== only have global_name = orcl12ctest. should i use orcl12ctest or orclpdb???????
(ORACLE_HOME = C:\app\oracle\product\12.2.0\dbhome_1)
(SID_NAME = ORCLPDB)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XX)(PORT = 1521)))
)
DIRECT_HANDOFF_TTC_LISTENER=OFF
logging_listener_name=on
$ cat tnsnames.ora
ORCL12CTEST=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP)(HOST=XXX.XX.XX.X)(PORT=1521))
(CONNECT_DATA= (SERVER = DEDICATED) (SERVICE_NAME=ORCL12CTEST)))
ORCLPDB=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XX.XX.X)(PORT=1521))
(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=ORCLPDB)) )
PS C:\> lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 25-MAY-2018 11:48:13
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XXX.XXX.X)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date 25-MAY-2018 11:45:16
Uptime 0 days 0 hr. 3 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\oracle\product\12.2.0\dbhome_1\network\admin\listener.ora
Listener Log File C:\app\oracle\diag\tnslsnr\ret109323\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.X)(PORT=1521)))
Services Summary...
Service "orcl12ctest.xyz.com" has 1 instance(s).
Instance "ORCL12CTEST", status UNKNOWN, has 1 handler(s) for this service...
Service "orclpdb.xyz.com" has 1 instance(s).
Instance "ORCLPDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
PS C:\> lsnrctl services
LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 25-MAY-2018 11:48:28
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XXX.XXX.X)(PORT=1521)))
Services Summary...
Service "orcl12ctest.xyz.com" has 1 instance(s).
Instance "ORCL12CTEST", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orclpdb.xyz.com" has 1 instance(s).
Instance "ORCLPDB", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
Any helps are appreciated.
Thanks in advance,
Chris