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

I was getting ORA-12528:TNS:listener error while connecting with my Oracle XE 11g database.

SQL> conn sys/sys@xe as sysdba

It gives an error message ORA-12528: TNS listener- all appropriate instances are blocking new connections.

Just follow the steps to resolve ORA-12528:TNS:listener issue.

XE =


(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = XE)

)

)

I changed above TNS details to below mentioned details. Here I 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 your database.

C:\lsnrctl reload

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

The command completed successfully

SQL> conn sys/sys@xe as sysdba

Connected.

If it shows ORA-12541: TNS no listener then click on ORA-12541 to resolve the issue.

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

It works…

 

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

  • MOHD SIRAJUDDIN

    It is really worthful information after all the efforts, I succeeded by adding (UR=A)in TNSNAMES.ora file..

    Thanks a lot Admin…

    • You are welcome. Keep reading other informative articles too.

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)