This error occurs when there was no default service configured for the listener and SERVICE_NAME was missing from the request received by the listener.

When a user tries to connect to the listener, it may be servicing several different databases. If user specifies a service name in the connect request then the listener will connect the user to that specific database but if there is no service name in the listener it will connect the user to the service name specified by the DEFAULT_SERVICE parameter.

To fix this issue there are two solutions:-

Sol 1: Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service name:-

LISTENER=
   (DESCRIPTION=
     (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=myserver.example.com)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
 SID_LIST_LISTENER=
   (SID_LIST=
     (SID_DESC=
       (GLOBAL_DBNAME=orcl.example.com)
       (SID_NAME=orcl))
 DEFAULT_SERVICE_LISTENER=oracle.example.com

Once the DEFAULT_SERVICE parameter is configured, reload the listener to bring this change into effect:-

lsnrctl reload

Sol 2. If using a service name, check that the connect descriptor corresponding to the service name in TNSNAMES.ORA of the client has a SERVICE_NAME or SID component in the CONNECT_DATA.

#When using SERVICE_NAME in CONNECT_DATA component
orcl.example.com =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = orcl)
     )
   )

#When using SID in CONNECT_DATA component 
orcl.example.com =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SID = orcl)
     )
   )