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

Author: SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator. View all posts by SandeepSingh

Manually Register database Services in listener