[20170527]12c連線外掛資料庫使用sid

[20170527]12c connect Pluggable database using SID.txt

--//12c 支援所謂的Pluggable database,據說以後oracle的資料庫都是Pluggable型.
--//一些應用連線配置時使用sid,而不是服務名.這樣在連線資料庫時存在問題.
--//透過測試說明:

1.環境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

2.測試:
D:\tools\rlwrap>sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Tue May 30 08:20:05 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

@> conn scott/btbtms@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = test01p)))
Connected.

SCOTT@test01p> conn scott/btbtms@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SID = test01p)))
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
Warning: You are no longer connected to ORACLE.

--//可以發現不再支援使用sid連線資料庫.
--//修改tnsnames.ora,加入如下測試:

D:\app\oracle\product\12.1.0\dbhome_1\NETWORK\ADMIN> cat tnsnames.ora
TEST01P =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zws)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test01p)
    )
  )

TEST01PX =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zws)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = test01p)
    )
  )

--//測試結果如下:
@> connect scott/btbtms@test01p
Connected.
SCOTT@test01p> connect scott/btbtms@test01px
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
Warning: You are no longer connected to ORACLE.

D:\tools\rlwrap>lsnrctl status
lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 30-MAY-2017 08:22:31

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ZWS)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 - Production
Start Date                30-MAY-2017 08:15:21
Uptime                    0 days 0 hr. 7 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\oracle\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\oracle\product\12.1.0\dbhome_1\log\diag\tnslsnr\zws\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zws)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=zws)(PORT=5500))(Security=(my_wallet_directory=D:\APP\ORACLE\admin\TEST\xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=zws)(PORT=5502))(Security=(my_wallet_directory=D:\APP\ORACLE\admin\TEST\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "TEST" has 2 instance(s).
  Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
  Instance "test", status READY, has 1 handler(s) for this service...
Service "test01p" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully

--//你可以發現僅僅支援服務名test01p,而沒有例項名test01p.透過在監聽配置檔案listener.ora中加入如下:
USE_SID_AS_SERVICE_listener=on

--//再重啟監聽:
D:\tools\rlwrap>sc stop OracleOraDB12Home1TNSListener
SERVICE_NAME: OracleOraDB12Home1TNSListener
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 3  STOP_PENDING
                                (STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x1
        WAIT_HINT          : 0xfa0

D:\tools\rlwrap>sc start OracleOraDB12Home1TNSListener
SERVICE_NAME: OracleOraDB12Home1TNSListener
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 2  START_PENDING
                                (NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x2
        WAIT_HINT          : 0xfa0
        PID                : 5800
        FLAGS              :


@> conn scott/btbtms@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SID = test01p)))
Connected.
SCOTT@test01p> connect scott/btbtms@test01px
Connected.
SCOTT@test01px> connect scott/btbtms@test01p
Connected.
SCOTT@test01p> conn scott/btbtms@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = test01p)))
Connected.

--//這樣就ok了.
--//一些8i的應用配置使用sid來連線,升級到12c後就出現問題,建議還是改用服務名來連線.