To connect to a PDB, you have to use the service
name of the pluggable database. But what
when there is an older legacy application that can only use an SID to connect
to a database? And what if we just migrated
an older legacy database to a PDB? You might see an error like this.
C:\Users\179818>sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 17 15:16:35
2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
idle> conn demo/demo@(DESCRIPTION = (ADDRESS = (PROTOCOL =
TCP)(HOST = LT035221)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = PDB1)))
Connected.
demo@PDB1>
demo@PDB1> conn demo/demo@(DESCRIPTION = (ADDRESS = (PROTOCOL
= TCP)(HOST = LT035221)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SID = PDB1)))
ERROR:
ORA-12505: TNS:listener does not currently know of SID
given in connect descriptor
Warning: You are no longer connected to ORACLE.
demo@PDB1>
How to overcome this error, while migrating to
Oracle 12c pluggable database? There is a parameter called USE_SID_AS_SERVICE_listener that can be used to tell the listener
to handle SID like service names. So the listener will interpret the SID given
in the connection string as a service name and will then connect to the
database.
To implement this change, add the following line
the listener.ora configuration file and restart the listener.
USE_SID_AS_SERVICE_listener=on
Here is the content of the listener log after
making the necessary changes.
C:\Users\Vnameit>type
d:\app\Vnameit\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora
# listener.ora Network Configuration File:
D:\app\Vnameit\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
USE_SID_AS_SERVICE_LISTENER_PDB1=on
SID_LIST_LISTENER_PDB1=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =
PDB1)
(ORACLE_HOME =
D:\app\Vnameit\virtual\product\12.2.0\dbhome_1)
(SID_NAME = PDB1)
)
)
LISTENER_PDB1=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL
= IPC)(KEY = EXTPROC1527))
(ADDRESS = (PROTOCOL
= TCP)(HOST = LT035221)(PORT = 1521))
)
)
C:\Users\Vnameit>
Now, restarting the listener
LSNRCTL>
LSNRCTL> stop LISTENER_PDB1
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1527)))
The command completed successfully
LSNRCTL>
LSNRCTL> start LISTENER_PDB1
Starting tnslsnr: please wait...
TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
System parameter file is
D:\app\Vnameit\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora
Log messages written to
D:\app\Vnameit\virtual\diag\tnslsnr\LT035221\listener_pdb1\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1527ipc)))
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT035221.cts.com)(PORT=1521)))
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1527)))
STATUS of the LISTENER
------------------------
Alias LISTENER_PDB1
Version
TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date
17-APR-2017 16:19:19
Uptime
0 days 0 hr. 0 min. 4 sec
Trace Level
off
Security ON: Local OS Authentication
SNMP
OFF
Listener Parameter File
D:\app\Vnameit\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora
Listener Log File
D:\app\Vnameit\virtual\diag\tnslsnr\LT035221\listener_pdb1\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1527ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT035221.cts.com)(PORT=1521)))
Services Summary...
Service "PDB1" has 1 instance(s).
Instance
"PDB1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
After listener restart able to connect to the pluggable
database using both Service name and SID.
C:\Users\179818>sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 17 16:23:04
2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
idle> conn demo/demo@(DESCRIPTION = (ADDRESS = (PROTOCOL =
TCP)(HOST = LT035221)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = PDB1)))
Connected.
demo@PDB1>
demo@PDB1> conn demo/demo@(DESCRIPTION = (ADDRESS = (PROTOCOL
= TCP)(HOST = LT035221)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SID = PDB1)))
Connected.
demo@PDB1>
No comments:
Post a Comment