Configuring the Oracle listener to accept TCPS connections besides the default TCP connections

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

The database has moved to TCSPS using Oracle Wallets, and we need to configure the TDM Portal to use this 

Environment

4.11.2003 and above

Resolution

The configuration in the below steps will allow the Oracle listener to accept TCPS connections besides the default TCP connections

 

Step 1 : To make the changes in server configuration, we have to first stop the oracle listener , open command prompt from any location and type in the below command to stop the listener

 

lsnrctl stop

 

You will get a similar command output like below

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=IPaddress)(PORT=1522)))

The command completed successfully

 

Step 2 : Go to the directory specified in the "TNS_ADMIN" environment variable

 

sample value for the environment variable TNS_ADMIN -> "C:\dbfree\dbhomeFree\network\admin\"

 

Step 3 : Open the file "sqlnet.ora" and add following entries :

 

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)

SSL_CLIENT_AUTHENTICATION=FALSE

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\dbfree\dbhomeFree\NETWORK\ADMIN)))

 

Step 4 : Open the file "listener.ora" and add folowing entries :

 

DEFAULT_SERVICE_LISTENER=FREE

SSL_VERSION=1.2

 

LISTENER=(DESCRIPTION_LIST =

    (DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCPS)(HOST = IPaddress)(PORT = 1522))

  (ADDRESS = (PROTOCOL = TCP)(HOST = IPaddress)(PORT = 1521))

    )

  )

SSL_CLIENT_AUTHENTICATION=FALSE  

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\dbfree\dbhomeFree\NETWORK\ADMIN)))

 

Note : The server hostname and port are only for sample, please replace these values with the actual server IP address and port, and the port should be different for TCP and TCPS connection

 

Step 5 : Open the file "tnsnames.ora" and add following entries :

 

FREE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCPS)(HOST = IPaddress)(PORT = 1522))

(ADDRESS = (PROTOCOL = TCP)(HOST = IPaddress)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = FREEPDB1)

    )

  )

 

LISTENER_FREE =

  (ADDRESS = (PROTOCOL = TCPS)(HOST = IPaddress)(PORT = 1522))

  (ADDRESS = (PROTOCOL = TCP)(HOST = IPaddress)(PORT = 1521))

 

Note : The server host name and port are only for sample , please replace these values with the actual server ip address and port , and the port should be different for TCP and TCPS connection

 

Step 6 : Start the listener by giving this command, tcps will be enabled after this command executes successfully

 

lsnrctl start

 

You should get an output something similar to the below :

 

Starting tnslsnr: please wait...

 

TNSLSNR for 64-bit Windows: Version 23.0.0.0.0 - Production

System parameter file is C:\dbfree\dbhomeFree\network\admin\listener.ora

Log messages written to C:\dbfree\diag\tnslsnr\windows-machine\listener\alert\log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=IPaddress)(PORT=1522)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=IPaddress)(PORT=1521)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=IPaddress)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for 64-bit Windows: Version 23.0.0.0.0 - Production

Start Date                29-APR-2025 11:49:19

Uptime                    0 days 0 hr. 0 min. 12 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Default Service           FREE

Listener Parameter File   C:\dbfree\dbhomeFree\network\admin\listener.ora

Listener Log File         C:\dbfree\diag\tnslsnr\windows-machine\listener\alert\log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=IPaddress)(PORT=1522)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=IPaddress)(PORT=1521)))

The listener supports no services

The command completed successfully

 

 

Note:  Any issues in TNS files should be resolved with your DBA. 

 

Additional Information

To add Oracle wallet and configure TCPS on Oracle listener : Add Oracle wallet and configure TCPS on Oracle listener

Steps to add Oracle Certificate in TDM and FDM CACERTS : Add Oracle Certificate in TDM and FDM CACERTS