Skip to Main Content
  • Questions
  • RMAN Auxiliary :ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Oracle Newbie.

Asked: September 27, 2016 - 6:40 am UTC

Last updated: September 28, 2016 - 5:58 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

I wanted to create a standby database from primary database(both are having different VM running Ubuntu 16.04 ). I installed oracle database in secondary and dropped the existing database using rman in secondary.I have restarted the listener and rebooted as well and started the oracle instance in secondary in nomount.Now while i am trying to connect to this secondary instance as auxiliary from rman in primary. I am receiving the error
ORA-12528: TNS:listener: all appropriate instances are blocking new connections . I have tried adding UR=A in listner.ora, but that didn't resolve the issue.

*********************************************************************************************************
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))(CONNECT_DATA=(UR=A)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 27-SEP-2016 02:04:40
Uptime 0 days 0 hr. 2 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ORAUBUNTU/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE))(CONNECT_DATA=(UR=A)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORAUBUNTU)(PORT=1521))(CONNECT_DATA=(UR=A)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
Instance "XE", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
*******************************************************************************************


# listener.ora Network Configuration File:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = ORAUBUNTU)(PORT = 1521))
(CONNECT_DATA=(UR=A))
)
)
***********************************************************************************************************

# tnsnames.ora Network Configuration File:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ubuntu)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
*******************************************************************************************************

initXE.ora


db_name='XE'
*******************************************************************************************************



and Connor said...

To fix that, you could create an explicit entry in your sid list which lists the sid that you wish to connect to. In this way, you are not coming in via a service, but a sid, eg

...
(sid_list=
   (sid_desc=
   (oracle-home=/u01/app/...)
   (sid_name=mysid)))


but I also notice the various references to "XE" in your question. Express edition does not support dataguard, so fixing the listener might not be the last of your issues :-)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

UR=A in tnsnames.ora solved the issue

A reader, September 29, 2016 - 5:27 am UTC

Thanks a lot for your reply.when i add UR=A in tnsnames.ora the issue resolved. But as you said about express edition i am getting more errors.:)

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.