To Add a Listener

When trying to login with the connection alias you get below error

C:\Users\Administrator>sqlplus sys@alpha as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 7 20:56:21 2023

Version 19.3.0.0.0

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

Enter password:

ERROR:
ORA-12541:     TNS:no listener

Enter user name:

In $ORACLE_HOME/network/admin folder check the listener.ora entry It may be something like this

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\ora_home)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\ora_home\bin\oraclr19.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

To add the listener for the container database add the following lines in SID_LIST_LISTENER

(SID_DESC =
      (GLOBAL_DBNAME = ALPHA)
      (ORACLE_HOME = D:\ora_home)
      (SID_NAME = ALPHA)
)

The new listener will look as follows

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\ora_home)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\ora_home\bin\oraclr19.dll")
    )
	
   (SID_DESC =
      (GLOBAL_DBNAME = ALPHA)
      (ORACLE_HOME = D:\ora_home)
      (SID_NAME = ALPHA)
    )
	
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

From the command prompt type the following command

$$lsnrctl
LSNRCTL>stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
The command completed successfully

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
System parameter file is D:\ora_home\network\admin\listener.ora
Log messages written to D:\app\oracle\Administrator\diag\tnslsnr\localhost\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                12-JAN-2023 05:55:44
Uptime                    0 days 0 hr. 0 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\ora_home\network\admin\listener.ora
Listener Log File         D:\app\oracle\Administrator\diag\tnslsnr\localhost\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "ALPHA" has 1 instance(s).
  Instance "ALPHA", status UNKNOWN, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Now when connecting to the database, it should happen without error

  

C:\Users\Administrator>sqlplus sys@alpha as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 12 05:59:52 2023
Version 19.3.0.0.0

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

Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

When connecting to a PDB if you get following error

SQL> conn system/oracle@alphapdb1
ERROR:
ORA-28002: the password will expire within 7 days

When changing the SYSTEM password at PDB level you will get the following error

SQL> alter user system identified by oracle;
alter user system identified by oracle
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers

Logout and connect to the container database

C:\Users\Administrator>sqlplus sys@alpha as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 7 20:56:21 2023
Version 19.3.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Now at the container level, you can alter the system password for all the PDBs

SQL> alter user system identified by oracle;

User altered.

Now you can connect to a PDB as follows

SQL> conn system/oracle@alphapdb1
Connected.
SQL>

References

https://alekciss.com/create-a-listener-in-oracle-database-19c/
https://dba.stackexchange.com/questions/269484/how-to-add-a-new-listener-on-a-new-port-and-restrict-it-to-one-instance-only
https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/configuring-and-administering-oracle-net-listener.html#GUID-0E7C39E3-4627-403A-AE69-E9AA2C7E4C57

Thought for the day

God has not called us to be successful, but to be faithful, to try!!!

Mother Theresa