SQLPLUS connection string does not work with Oracle 21c CDB/PDB

Question

I have created and Oracle 21 CDB named netcoolc, but because of some reason I cannot connect with the sys user with the right connection string...

[reporter@iqlinkxg02 ~]$ sqlplus sys/"PW"@netcoolc as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Nov 15 12:51:39 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name: 

...however I get correct answer for tnsping...

[reporter@iqlinkxg02 ~]$ tnsping netcoolc

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 15-NOV-2023 12:52:38

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

Used parameter files:
/opt/oracle/product/21c/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = iqlinkxg02)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = netcoolc)))
OK (0 msec)
[reporter@iqlinkxg02 ~]$ 

...and the most weird thing that I can login with the following string...

[reporter@iqlinkxg02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Nov 15 12:53:49 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> 

Below you may find the content of the OS user .bash_profile file...

# Oracle 21c netcool  Environment Variables - START
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=${ORACLE_BASE}/product/21c/dbhome_1; export ORACLE_HOME
ORACLE_SID=netcoolc; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH; export PATH
NLS_LANG=american_america.WE8ISO8859P1; export NLS_LANG
# Oracle 21c netcool Environment Variables - END

...and I'm attaching here the content of the tnsnames.ora file...

netcoolc =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = iqlinkxg02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = netcoolc)
    )
  )

Any advice, top or help would be really appreciated!

I was trying to troubleshoot via an other CDB environment where I have the same settings but with different CDB and username. Everything works fine there, I could not find the minor glitch.

Answer 1

You've established that the database is up, that IPC connections (sqlplus / as sysdba) are working, and that the TNS alias in tnsnames.ora is valid, based on the content of the tnsnames.ora file. Note that tnsping only validates that a listener is active on the target host/port. It does not resolve the service_name specifically with the listener.

The key is your description of this command:

sqlplus sys/"PW"@netcoolc as sysdba

which results in

ORA-12154: TNS:could not resolve the connect identifier specified

We know that the connect identifier of "netcoolc" is valid, so this suggests the command is passing something else to the listener. The only way that can happen is if there's a @ embedded in the password. What sqlplus is actually seeing is this:

sqlplus sys/p@ssword@netcoolc as sysdba

It is then trying to lookup "@ssword" in tnsnames.ora and of course can't find it. Removing the password from the command line, where sqlplus is misinterpreting the content, and entering it separately at the prompt should solve the problem.

This sort of thing is a frequent problem when using Oracle tools from the command line. In my experience, the only special character that doesn't seem to have any issues anywhere is the underscore, _.

That said, I highly recommend using an Oracle Wallet to store database credentials if you need to script things like this from the command line on a regular basis. Exposing credentials on the command line is a very poor security practice. See documentation here: https://www.oracle.com/technetwork/database/security/twp-db-security-secure-ext-pwd-stor-133399.pdf

and here: https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-authentication.html#GUID-3EA07020-A9F3-4FF9-9518-E1AEA3BDDBBE

The basic steps are:

• create a wallet
• add username/password credentials to the wallet
• configure sqlnet.ora to point to the wallet
• connect using something like sqlplus /@pdb_tns_alias

If you need to support different user credentials you would need a separate TNS alias for each user.

Answer 2

Command prompt gives run as administrator then write command lsnrctl start.

If you want to check listener running or not using lsnrctl status