未能在Oracle 11上启用TCPS

Question

我已经向我的Oracle 11服务器添加了以下设置(粗体文本是更改)-

sqlnet.ora-

SQLNET.AUTHENTICATION_SERVICES = (**TCPS,** NTS)

listener.ora-

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
      **(ADDRESS = (PROTOCOL = TCPS)(HOST = hostname)(PORT = 2484))**
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

tnsnames.ora-

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

**XESSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = hostname)(PORT = 2484))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )**  
 



EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  ) 

重新启动OracleService、XE和OracleXETNSListener。

输出的tnsping和lsnrctl状态-

C:\oraclexe\app\oracle\product\11.2.0\server\bin>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 18-JUL-2022 22:11:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                18-JUL-2022 22:09:15
Uptime                    0 days 0 hr. 1 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\nsanark\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=hostname)(PORT=2484)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=3010))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\oraclexe\app\oracle\product\11.2.0\server\bin>tnsping XE

TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 18-JUL-2022 22:12:17

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
OK (20 msec)

C:\oraclexe\app\oracle\product\11.2.0\server\bin>


C:\oraclexe\app\oracle\product\11.2.0\server\bin>tnsping XESSL

TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 19-JUL-2022 14:13:37

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = hostname)(PORT = 2484)) (CONNECT_DATA = (SERVER = DEDICATED)
TNS-12560: TNS:protocol adapter error

尽管在ora文件中添加了2484端口，但我为什么不打开它呢？我遗漏了什么？

Answer 1

您的lsnrctl status输出显示端口打开：

Listening Endpoints Summary...
  ...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=hostname)(PORT=2484)))

您的tnsping是针对XE，而不是XESSL，所以检查了端口1521。

注意: tnsping只检查目标主机和端口是否有侦听器可用。它不验证任何其他连接参数，包括service_name。