oracle12c查看sid,[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后就出现问题,建议还是改用服务名来连接.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2140061/，如需转载，请注明出处，否则将追究法律责任。