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

SiteGround Hosting Discount

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.

I use SEMRush for Keyword Research & Boost Organic Traffic. Do You?

Highly recommended SEM & SEO Tool for overview of Traffic, Keywords, Backlinks and more. You can search your competitors URL to analyze keywords & crush your competition.



Leave a Reply

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