How to connect using PDB userid on Oracle 18c XE?

Question

I have created a PDB on Oracle 18c XE using following SQL command

create pluggable database virlux
admin user test1admin identified by test1pwd
file_name_convert = ('/pdbseed/', '/test1/')
;

Now, I try to connect directly to this PDB using new Userid using following command

sqlplus test1admin/test1pwd@virlux

and Oracle answer by error ORA-12541: no listener

I have then tried with SYSTEM userid with following command

sqlplus SYSTEM/syspwd@virlux

and I have obtained same error number !

What happens ?

I have set ORACLE_HOME and SET_ADMIN variables correctly.

My tnsnames.ora file found in %TNS_ADMIN% folder contains following lines

# tnsnames.ora Network Configuration File: D:\Oracle\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XE =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = XE)
 )
 )
VIRLUX =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = VIRLUX)
 )
 ) 
XEPDB1 =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = XEPDB1)
 )
 ) 
LISTENER_XE =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 )
 (CONNECT_DATA =
 (SID = CLRExtProc)
 (PRESENTATION = RO)
 )
 )

I have just added VIRLUX in this file.

I have also stopped and started all Oracle services using net use commands.

The listener.ora file contains following lines

# listener.ora Network Configuration File: D:\Oracle\dbhomeXE\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
DEFAULT_SERVICE_LISTENER = XE
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = CLRExtProc)
 (ORACLE_HOME = D:\Oracle\dbhomeXE)
 (PROGRAM = extproc)
 (ENVS = "EXTPROC_DLLS=ONLY:D:\Oracle\dbhomeXE\bin\oraclr18.dll")
 )
 )
LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 )
 )

When I start LSNRCTL STATUS command, I obtain

enter image description here

• Balazs Papp: Check the status of the listener (lsnrctl status) and start it if it is not running (lsnrctl start).
• schlebe: @Balazs: If have started your command and put returned information in question.
• Balazs Papp: Your database is not even registered in the listener, but that is not the root cause of the problem. It is possible that virlux can be resolved as a hostname with ezconnect, and the client tries to connect somewhere else, this can be confirmed with the output of tnsping virlux. By the way, if you have HOST=localhost in listener.ora, your listener should not listen on HOST=192.168.0.14, so it is quite possible that it is an environmental issue.

Answer 1

I have finally found a solution to my problem and I will explain to help other users in same case.

I have no problem to connect to XE instance of Oracle Database using following sqlplus command

sqlplus SYSTEM/password@XE

My first problem is that PDB is not always open and every time that I stop and start DB services, I must restart PDB.

To avoid this, I have executed following SQL command

alter PLUGGABLE DATABASE ALL OPEN;
alter PLUGGABLE DATABASE ALL SAVE STATE;

as explained on asktom.oracle.com

Now every time I stop and restart the Oracle database services, the VIRLUX PDB is correctly started but following sqlplus command continue to return an Oracle error that now is ORA-01017.

sqlplus test1admin/test1pwd@virlux
SQL*Plus: Release 18.0.0.0.0 - Production on Mar. Janv. 14 08:41:41 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
ERROR: ORA-01017: invalid username/password; Logon denied

But, if I enter following command, I'm connected to Oracle PDB !

sqlplus test1admin/test1pwd@localhost/virlux

I think that old well known sqlplus connection command don't work for PDB and that the only solution is to prefix PDB name by hostname that has been defined in TNSNAMES.ORA file.

What is surprising is that a command that return an error's message saying that User/password are invalid is corrected without changind password !

This solution works now well on my PC on Windows Home 10.

Answer 2

Your listener listens on 192.168.0.14 while you are trying to connect with a TNS entry that uses localhost as the address. This would work on Linux with default settings, but it will not work on Windows like that, so it is expected behaviour.

C:\Windows\system32>lsnrctl status
LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 10-JAN-2020 22:57
:53
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.241)(PORT=1521
)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 18.0.0.0.0 - Produ
ction
Start Date 10-JAN-2020 22:54:03
Uptime 0 days 0 hr. 3 min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\app\oracle\product18円.0.0\dbhomeXE\network\admin\li
stener.ora
Listener Log File C:\app\oracle\product18円.0.0\diag\tnslsnr\W7VPN1\liste
ner\alert\log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.241)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
 Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
C:\Windows\system32>tnsping xe
TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 - Production on 10-JAN-2
020 22:57:56
Copyright (c) 1997, 2018, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle\product18円.0.0\dbhomeXE\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
TNS-12541: TNS:no listener
C:\Windows\system32>

Either change the listener to listen on localhost or change the tnsnames.ora entries (LISTENER_XE, virlux) to use 192.168.0.14.