One of the new features in Oracle Database version 23ai is the ConnStr tool. This tool reads the listener log output and, based on it, displays the connection strings for each service in Easy Connect, JDBC, and Python formats. Additionally, it can create net service names for each service in the tnsnames.ora file.
By default, if the listener service is down, running the ConnStr command will result in an error:
[oracle@OEL8 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 23.0.0.0.0 - Developer-Release on 05-SEP-2023 13:30:35
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=OEL8)(PORT=1521)))
The command completed successfully
[oracle@OEL8 ~]$ connstr
Using Listener: LISTENER with Oracle Home: /opt/oracle/product/23ai/dbhomeFree
No listener endpoints found
After starting the listener, if no service is registered, ConnStr displays the message: “The listener supports no services.”
[oracle@OEL8 ~]$ lsnrctl start
The listener supports no services
The command completed successfully
[oracle@OEL8 ~]$ connstr
Using Listener: LISTENER with Oracle Home: /opt/oracle/product/23ai/dbhomeFree
The listener supports no services
As you may know, after a few seconds, LREG automatically registers all services on port 1521:
[oracle@OEL8 ~]$ lsnrctl status
LSNRCTL for Linux: Version 23.0.0.0.0 - Developer-Release on 05-SEP-2023 13:36:47
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=target)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 23.0.0.0.0 - Developer-Release
Start Date 05-SEP-2023 13:31:07
Uptime 0 days 0 hr. 5 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service FREE
Listener Parameter File /opt/oracle/product/23ai/dbhomeFree/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/OEL8/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OEL8)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "FREE" has 1 instance(s).
Instance "FREE", status READY, has 1 handler(s) for this service...
Service "vahidpdb" has 1 instance(s).
Instance "FREE", status READY, has 1 handler(s) for this service...
The command completed successfully
In this situation, if we execute the ConnStr command, the list of services along with their connection strings will be displayed:
[oracle@OEL8 admin]$ connstr
Using Listener: LISTENER with Oracle Home: /opt/oracle/product/23ai/dbhomeFree
Service Name: vahidpdb
Connection String: OEL8:1521/vahidpdb
Service Name: FREE
Connection String: OEL8:1521/FREE
Connection strings can be used to connect to the specified service name.
For SQL*Plus you can use:
SQL> connect username@OEL8:1521/vahidpdb
For Python you can use:
connection = cx_Oracle.connect(user="username", password="password",
dsn="OEL8:1521/vahidpdb")
For JDBC Thin you can use:
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@OEL8:1521/vahidpdb");
ods.setUser("username");
ods.setPassword("password");
Connection conn = ods.getConnection();
Write connect strings to tnsnames.ora (Y/N)? (Default: N):
At the last line, a question is asked. If you select “Y,” all net service names will be added to the tnsnames.ora file:
[oracle@OEL8 ~]$ vi /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
# ***** tnsnames.ora connection strings auto-generated by connstr on Tue Sep 5 11:46:03 2023 *****
# This file is not needed if using Easy Connect strings.
# To use this file, place in your client TNS_ADMIN directory (typically ORACLE_HOME/network/admin/ or instantclient_XX_X/network/admin).
# Or, set your TNS_ADMIN environment variable to the directory this file is located in.
vahidpdb=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = OEL8)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = vahidpdb))
)
FREE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = OEL8)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = FREE))
)
Using the -o switch, we can save the output outside the tnsnames.ora file:
[oracle@OEL8 ~]$ connstr -o /home/oracle/tnsnames.test
Write connect strings to /home/oracle/tnsnames.test (Y/N)? (Default: N): Y
[oracle@OEL8 ~]$ ls -lh /home/oracle/tnsnames.test
-rw-r--r-- 1 oracle oinstall 1.2K Sep 5 14:21 /home/oracle/tnsnames.test
At the beginning of the text, it was emphasized that the ConnStr command works by default with the listener. To execute this command, the listener service must be running. The phrase “by default” is used because executing this command is not limited to the listener service, as ConnStr can also operate through the listener log, even when the listener is down:
[oracle@OEL8 ~]$ lsnrctl status>listener_status.log
[oracle@OEL8 ~]$ lsnrctl stop
[oracle@OEL8 ~]$ connstr --i listener_status.log
Using Listener: LISTENER with Oracle Home: /opt/oracle/product/23ai/dbhomeFree
Service Name: vahidpdb
Connection String: OEL8:1521/vahidpdb
Service Name: FREE
Connection String: OEL8:1521/FREE
Connection strings can be used to connect to the specified service name.
For SQL*Plus you can use:
SQL> connect username@OEL8:1521/vahidpdb
For Python you can use:
connection = cx_Oracle.connect(user="username", password="password",
dsn="OEL8:1521/vahidpdb")
For JDBC Thin you can use:
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@OEL8:1521/vahidpdb");
ods.setUser("username");
ods.setPassword("password");
Connection conn = ods.getConnection();
File tnsnames.ora exists. Append (Y), Overwrite (O), or Do Nothing (N)? (Default: N):
You can also display the connection string for each service individually:
[oracle@OEL8 ~]$ connstr vahidpdb
OEL8:1521/vahidpdb
Using the -j switch, the JDBC connection string will be displayed:
[oracle@OEL8 ~]$ connstr -j vahidpdb
jdbc:oracle:thin:@OEL8:1521/vahidpdb
Top comments (0)