ORA-12528 tns listener all appropriate instances are blocking new connections

SQL> set ORACLE_SID=ORCL
SQL> startup nomount
ORACLE instance started.

Total System Global Area 263639040 bytes
Fixed Size 1332552 bytes
Variable Size 222300856 bytes
Database Buffers 33554432 bytes
Redo Buffers 6451200 bytes

C:\>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production

Service "ORCL" has 2 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:59
LOCAL SERVER
Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ORCL_XPT" has 1 instance(s).
Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
 
 
Want Solution Do the following : 
 
you can add the new TNS connect string (UR = A) to the tnsnames entry.
 
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
(UR = A)
)
 
if the above Solution not working Make sure your listener look like :
 
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\oraserver\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\oraserver\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME=orcl)
(ORACLE_HOME=D:\app\oraserver\product\11.2.0\dbhome_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
 


Thank you 
Osama Mustafa

50 thoughts on “ORA-12528 tns listener all appropriate instances are blocking new connections

  1. Hi Osama,I tried the proposed solution. but it dint worked out. I am getting following errors at command prompt:ORA-12528: TNS:listener: all appropriate instances are blocking new connections.ORA-24327: need explicit attach before authenticating a user.Thanks for your time,

    Like

  2. Hello Osama,I tried your suggestion but still I am getting the error:LSNRCTL> statusConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))STATUS of the LISTENER————————Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 – ProductionStart Date 07-NOV-2012 17:36:15Uptime 0 days 0 hr. 5 min. 19 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /opt/oracle/112/network/admin/listener.oraListener Log File /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xmlListening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))Services Summary…Service “ORCL” has 1 instance(s). Instance “ORCL”, status BLOCKED, has 1 handler(s) for this service…The command completed successfullyThis is blocking me to configure Oracle Identity Management. The installation failed at the endwith the following error. I think this because of the Blocking of listener.Component Schema=SOA InfrastructureDriver=oracle.jdbc.xa.client.OracleXADataSourceURL=jdbc:oracle:thin:@localhost.localdomain:1521/orclUser=DEV_SOAINFRAPassword=***********SQL Test=select 1 from schema_version_registry where owner=(select user from dual) and mr_type='SOAINFRA' and version='11.1.1.5.0'Listener refused the connection with the following error:ORA-12528, TNS:listener: all appropriate instances are blocking new connections CFGFWK-60850: Test Failed!I am installing Oracle Identity Management 11.1.1.5 on RedHat Liux 5.Please help me and let me know what data you need.Thanks,-arshad

    Like

  3. Hi Osama,I did that and now I am getting a different error when starting the db. Also getting the same error when doing checking the “status” from “lsnrctl”SQL> startupORACLE instance started.LSNRCTL> statusConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))STATUS of the LISTENER————————Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 – ProductionStart Date 12-NOV-2012 10:26:46Uptime 0 days 0 hr. 1 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /opt/oracle/112/network/admin/listener.oraListener Log File /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xmlListening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))Services Summary…Service “ORCL” has 1 instance(s). Instance “ORCL”, status BLOCKED, has 1 handler(s) for this service…Service “orcl.localdomain” has 1 instance(s). Instance “orcl”, status READY, has 1 handler(s) for this service…Service “orclXDB.localdomain” has 1 instance(s). Instance “orcl”, status READY, has 1 handler(s) for this service…The command completed successfullyTotal System Global Area 267227136 bytesFixed Size 2212496 bytesVariable Size 205524336 bytesDatabase Buffers 54525952 bytesRedo Buffers 4964352 bytesORA-01102: cannot mount database in EXCLUSIVE mode

    Like

  4. Sorry my last comment is mixed up:SQL> startupORACLE instance started.Total System Global Area 267227136 bytesFixed Size 2212496 bytesVariable Size 205524336 bytesDatabase Buffers 54525952 bytesRedo Buffers 4964352 bytesORA-01102: cannot mount database in EXCLUSIVE mode

    Like

  5. [oracle@localhost trace]$ ps -ef | grep pmon oracle 9037 1 0 10:27 ? 00:00:00 ora_pmon_ORCLoracle 12325 1 0 Nov09 ? 00:00:06 ora_pmon_orcloracle 14653 7613 0 11:37 pts/1 00:00:00 grep pmon[root@localhost Desktop]# cd /opt/oracle/diag/rdbms/orcl/ORCL/trace[root@localhost trace]# tail -50 alert_ORCL.log remote_login_passwordfile= “EXCLUSIVE” db_domain = “” dispatchers = “(PROTOCOL=TCP) (SERVICE=ORCLXDB)” audit_file_dest = “/opt/oracle/admin/orcl/adump” audit_trail = “DB” db_name = “ORCL” open_cursors = 300 diagnostic_dest = “/opt/oracle”Mon Nov 12 10:27:12 2012PMON started with pid=2, OS id=9037 Mon Nov 12 10:27:12 2012VKTM started with pid=3, OS id=9041 at elevated priorityVKTM running at (10)millisec precision with DBRM quantum (100)msMon Nov 12 10:27:12 2012GEN0 started with pid=4, OS id=9047 Mon Nov 12 10:27:12 2012DIAG started with pid=5, OS id=9051 Mon Nov 12 10:27:12 2012DBRM started with pid=6, OS id=9055 Mon Nov 12 10:27:13 2012PSP0 started with pid=7, OS id=9059 Mon Nov 12 10:27:13 2012DIA0 started with pid=8, OS id=9063 Mon Nov 12 10:27:13 2012MMAN started with pid=9, OS id=9067 Mon Nov 12 10:27:13 2012DBW0 started with pid=10, OS id=9071 Mon Nov 12 10:27:13 2012LGWR started with pid=11, OS id=9075 Mon Nov 12 10:27:13 2012CKPT started with pid=12, OS id=9079 Mon Nov 12 10:27:13 2012SMON started with pid=13, OS id=9083 Mon Nov 12 10:27:13 2012RECO started with pid=14, OS id=9087 Mon Nov 12 10:27:13 2012MMON started with pid=15, OS id=9091 Mon Nov 12 10:27:13 2012MMNL started with pid=16, OS id=9095 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'…starting up 1 shared server(s) …ORACLE_BASE from environment = /opt/oracleMon Nov 12 10:27:14 2012ALTER DATABASE MOUNTsculkget: failed to lock /opt/oracle/112/dbs/lkORCL exclusivesculkget: lock held by PID: 12359ORA-09968: unable to lock fileLinux-x86_64 Error: 11: Resource temporarily unavailableAdditional information: 12359ORA-1102 signalled during: ALTER DATABASE MOUNT…

    Like

  6. Hello Osama,I need your help again as I am getting the same issue when I restarted my Linux machine.I am not sure why there are two instances of Oracle SI, one “ORCL” and another “orcl”.Please let me know how to resolve this issue permanently.When I logged in to the Oracle Enterprise Manager console through “https://localhost:1158/em/” I can see that Oracle SID is “orcl”(lower case).But /home/oracle/.bash_profile file still shows “ORCL”, although did “export ORACLE_SID=orcl” already.[root@localhost ~]# cat /home/oracle/.bash_profile file # .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then . ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/binexport PATHORACLE_BASE=/opt/oracleORACLE_HOME=$ORACLE_BASE/112ORACLE_SID=ORCLLD_LIBRARY_PATH=$ORACLE_HOME/libPATH=$PATH:$ORACLE_HOME/binexport ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH [root@localhost ~]# ps -ef | grep pmon oracle 8383 1 0 12:12 ? 00:00:00 ora_pmon_ORCLroot 9866 5181 0 12:33 pts/1 00:00:00 grep pmonLSNRCTL> statusServices Summary…Service “ORCL” has 1 instance(s). Instance “ORCL”, status BLOCKED, has 1 handler(s) for this service…SQL> startupORACLE instance started.Total System Global Area 267227136 bytesFixed Size 2212496 bytesVariable Size 205524336 bytesDatabase Buffers 54525952 bytesRedo Buffers 4964352 bytesORA-00205: error in identifying control file, check alert log for more infoThanks for your help,-Arshad

    Like

  7. Hello Osama,I tried the same steps that you mentioned in your last comment and now database is up and running.It is not blocking the database instance but I can't connect to Oracle Enterprise Manager using thehttps://localhost:1158/em. Firefox displayed the error message. Also I changed the ORACLE_SID to “orcl”(lower case) in /home/oracle/.bash_profile. I think thats the reason for DB console failure. I am also getting error when installing OIM.When I tried to start it from CLI:[oracle@localhost bin]$ emctl start dbconsoleOC4J Configuration issue. /opt/oracle/112/oc4j/j2ee/OC4J_DBConsole_localhost.localdomain_orcl not foundPlease let me know what should I do so that both Database is running and DB Console should be up too.As as last resort I am thinking to re-install Database, run RCU, install Weblogic server and the finally install the Oracle Identity Management.Thanks,-Arshad

    Like

  8. For this ORA-00205: error in identifying control file, check alert log for more info As you the error is clear you need to check the alert log , and seems you have miss configuration Check your ORACLE_SID and fix it in the bash_profile.

    Like

  9. As I told you before you need to check The ORACLE_SID , After that try to use emctl start dbconsole , if its not working or same error appear to you Use :-emca -deconfig dbcontrol db -repos drop-emctl -config dbcontrol db -repos create

    Like

  10. Hello Osama,I am now able to login to DB console and also “lsnrtcl” donot show that it is blocking an instance.I can connect to database using “sqlplus” successfully and no errors.But when I login to the DB console using “https://lcoalhosy:1158/em”, I got the following message:” Enterprise Manager is not able to connect to the database instance. The state of the components are listed below.”.Although “Database Instance”, “Listener” are UP, and also “Agent Connection to Instance” shows “Succeeded”.Please let me know what should I do next to resolve this issue.Thanks for your help,-Arshad

    Like

  11. hiii osama, I am facing the problem ORA-12528, TNS:listener: all appropriate instances are blocking new connections The Connection descriptor used by the client was: 192.168.6.98:1521:ORCL. though i am not using this IP i am using 192.168.6.115:1521:ORCL, and the earlier IP is no where not even in my tnsnames.ora. please do the needful

    Like

  12. hii osama, Thanks for the time and solution but thats also not working, my problem is that ip address which i am not using anywhere in my machine can you plz tell me how to resolve that ? ThanksVipin

    Like

  13. Could not create connection; – nested throwable: (java.sql.SQLException: Listener refused the connection with the following error: ORA-12528, TNS:listener: all appropriate instances are blocking new connections The Connection descriptor used by the client was: 192.168.6.98:1521:ORCL ); – nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; – nested throwable: (java.sql.SQLException: Listener refused the connection with the following error: ORA-12528, TNS:listener: all appropriate instances are blocking new connections The Connection descriptor used by the client was: 192.168.6.98:1521:ORCL ))

    Like

  14. seems you are using windows ?if yes please do the following :This is performed on Microsoft Windows > Administrative Tools > Services by doing a right-click > Properties to open up the properties window then go to the Dependencies tab to indicate on which the windows service (for example the database startup service) is dependent.

    Like

  15. HI Osama, Could please help me to solve the oracle issueAgent Connection to Instance:Status Failed Details Failed to connect to database instance: ORA-12541: TNS:no listener (DBD ERROR: OCIServerAttach.Listener:Status Up Host BELL-ID Port 1521 Name LISTENER Oracle Home C:\app\bellid\product\11.2.0\dbhome_1 Location C:\app\bellid\product\11.2.0\dbhome_1\network\admin Database Instance:Down

    Like

  16. # tnsnames.ora ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = BELL-ID)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL) (UR = A) ) ) # listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = BELL-ID)(PORT = 1521)) ) )ADR_BASE_LISTENER = C:\app\bellid

    Like

  17. I removed the listener but still the same:Agent Connection to Instance Status Failed Details Failed to connect to database instance: ORA-12541: TNS:no listener (DBD ERROR: OCIServerAttach).

    Like

  18. Hi great Osama, I will so happy if you can help me on this.am trying to build Datagurad and got to the stage of RMAN. Am having error when I ran this command. connect auxiliary sys/oracle@DR..LOOK AT THE ERROR output.RMAN-04006: error from auxiliary database: ORA insufficient privileges……..I went to check my lsnrctl status I discover that the instance has a BLOCKED STATUS. I try to use some of your solution but it's seems not to be working. any help from your end will be appreciated.

    Like

  19. Hello Osama,Can i request for your guidance for my below issue: Instance “GDEV”, status BLOCKED, has 1 handler(s) for this service…I have tried few of the things but wasn't successful. Basically, i have scrapped the previous oracle_home and installed afresh. Now wanted to refresh from backup thru rman.But, I'm getting status BLOCKED, which is not getting rid ofThank you

    Like

  20. Hi, I am working on Upgrade Manager, while installing it I got an issue which shows “ORA-12528 tns listener all appropriate instances are blocking new connections “I don't know database stuff, so can any one help me on this issue. Please suggest me step by step procedure to solve this issue.Thank,Bhaskar.

    Like

  21. Slam 3lykom Osama,It solves the issue and I am able to login RMAN using Auxiliary instanceMy problem now .. after issuing the Duplicate database … when RMAN shutdown the Auxiliary, it cannot start it up again ..I get the Error:RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    Like

  22. Unable to Solve if Oracle Listener Stops Working? Contact to Remote DBA Services Investigate your Oracle audience issue with Cognegic's Database Administration for Oracle or Online Oracle DB Support. Here we have most experienced and expert specialized specialists who give best answer for take care of your Oracle audience issue. Aside from this audience issue our specialists can illuminate some other specialized hiccups like recuperation issue, establishment issue, setup issue et cetera. Simply contact to our specialized specialists through dialing this underneath sans toll number and investigate the worthwhile help. For More Info: https://cognegicsystems.com/ Contact Number: 1-800-450-8670Email Address- info@cognegicsystems.com Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    Like

Leave a Reply to Cognegic Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.