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…