Fixed – ORA-12528: TNS:listener: all appropriate instances are blocking new connections error

This article will help you to resolve "ORA-12528: TNS listener- all appropriate instances are blocking new connections" error while connecting with Oracle XE 11g database.

Error Message:

ORA-12528: TNS listener- all appropriate instances are blocking new connections.

Resolution:

Open insnames.ora file under server\network\ADMIN\ folder. My tnsnames.ora file was located on below path.


C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Now change above TNS details to below mentioned details. Here I have added (UR=A) in the below TNS.

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
(UR = A)
)
)

Now restart the service and try to connect with database.

C:\lsnrctl reload

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

The command completed successfully

SQL> conn sys/sys@xe as sysdba

Connected.

Note:  The (UR=A) allow privileged or administrative users to connect via listener even when the service handler is blocking new connections.


Subscribe to get free tech advice, tips and in-depth articles.


SiteGround Web Hosting Promo Discount Banner

Leave a Reply

Your email address will not be published. Required fields are marked *