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…

 



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

Subscribe via RSS feed

The following two tabs change content below.
Anil Verma
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+

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