Manually Register database Services in listener
In Oracle Database, Sometime we need to register service for database manually in Listener of Oracle.
Sometime dynamic register is not work. we have to do it manually.
Following are the three ways to do manually registeration of service in Oracle Listener:
1. Alter system command.
2. NETMGR utility.
3. Add manually entry in Listener.ora file.
ALTER system command
-- Connect with database and run the following command:
--For NON-CDB database:
SQLPLUS sys as sysdba
password
SQL> alter system register;
System altered.
--For CDB databases:
SQLPLUS sys as sysdba
password
SQL> alter session set container=PDB2;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------ --------------- ---------- ----------
5 PDB2 READ WRITE NO
SQL> alter system register;
System altered.
SQL> select name,pdb from all_services;
NAME PDB --------- -------------- PDB2 PDB2
NETMGR utility
1. Open NET MANAGER Oracle app in windows from START button.
2. Expand Local ---> Expand Listeners --> Click on Listener at left windows.
3. Choose the Database Services on Right window from drop down.
4. Add the Database fields as shown below:
Global Database Name: PDB/CDB name
Oracle Home Directory: Path of Oracle home
SID: Instance name
5. Save the services setting by clicking FILE --> Save Network Configuration.
6. Reload the services and check the lsnrctl services command for new entry:
lsnrctl services
.................
.................
Service "PDB5" has 1 instance(s).
Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Manually make entry for Servies in listener file
Open the listener file and make entry in the listener
Add entry in SID_LIST_LISTENER bracket.
(SID_DESC =
(GLOBAL_DBNAME = PDB5)
(ORACLE_HOME = C:\Oracle\dbhomeXE)
(SID_NAME = XE)
)
Example of listener file with added entry
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\Oracle\dbhomeXE)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\dbhomeXE\bin\oraclr18.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = PDB5)
(ORACLE_HOME = C:\Oracle\dbhomeXE)
(SID_NAME = XE))
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NODE1.ORACLE.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = C:\Oracle\dbhomeXE\log
Smart way of Technology 
Pingback: Listener and TNS entry of PDB database for connectivity in Oracle | Smart way of Technology
The last option worked for me. Thank you very much.
LikeLiked by 1 person