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.


Start Your Dream Website Today & Earn Money Online

To Start a dream website, choose a Domain Name & Reliable Hosting Provider, Self-hosted Blogging Platform, WordPress Theme, Email Marketing and SEO Tool for traffic.

SiteGround Hosting Discount - 60% OFF
ConvertKit The Best Email Marketing Software
Free SEMRush SEM and SEO Tool
Get Your Hosting Package with SiteGround, Increase new email subscribers with ConvertKit and Research Keywords with SEMRush to Boost Organic Traffic.





Leave a Reply

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