How to connect using PDB userid on Oracle 18c XE? 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
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.