Saturday, February 18, 2012

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

45 comments:

  1. no luck buddy... still the same...

    ReplyDelete
  2. Can you please post the alert log if you want i will help you with this problem since listener problem have more than one solution

    ReplyDelete
  3. 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,


    ReplyDelete
  4. are you have change the ORACLE_SID in Listener.ora and tnsnames.ora
    Please restart your listener to fix the issue.
    Also Check sqlnet.ora if its located $ORACLE_HOME/network/admin

    ReplyDelete
  5. thank you Osama, your tip helped me !!!

    ReplyDelete
  6. Hello Osama,

    I tried your suggestion but still I am getting the error:
    LSNRCTL> status
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date 07-NOV-2012 17:36:15
    Uptime 0 days 0 hr. 5 min. 19 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /opt/oracle/112/network/admin/listener.ora
    Listener Log File /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening 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 successfully

    This is blocking me to configure Oracle Identity Management. The installation failed at the end
    with the following error. I think this because of the Blocking of listener.

    Component Schema=SOA Infrastructure
    Driver=oracle.jdbc.xa.client.OracleXADataSource
    URL=jdbc:oracle:thin:@localhost.localdomain:1521/orcl
    User=DEV_SOAINFRA
    Password=***********
    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

    ReplyDelete
  7. Hi Arshad Iqbal

    Could you please post :

    show parameter aq_tm_processes

    ReplyDelete
    Replies
    1. SQL> show parameter aq_tm_processes;

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      aq_tm_processes integer 0


      Thanks,
      -Arshad

      Delete
    2. ALTER SYSTEM SET AQ_TM_PROCESSES=1;
      try after change it . if its not working back original value for this parameter .

      Delete
  8. 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> startup
    ORACLE instance started.

    LSNRCTL> status
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date 12-NOV-2012 10:26:46
    Uptime 0 days 0 hr. 1 min. 0 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /opt/oracle/112/network/admin/listener.ora
    Listener Log File /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening 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 successfully
    Total System Global Area 267227136 bytes
    Fixed Size 2212496 bytes
    Variable Size 205524336 bytes
    Database Buffers 54525952 bytes
    Redo Buffers 4964352 bytes
    ORA-01102: cannot mount database in EXCLUSIVE mode

    ReplyDelete
    Replies
    1. First seems the above solution is working with you since lsnrctl status appear that database is register with your listener.

      Delete
  9. Sorry my last comment is mixed up:

    SQL> startup
    ORACLE instance started.

    Total System Global Area 267227136 bytes
    Fixed Size 2212496 bytes
    Variable Size 205524336 bytes
    Database Buffers 54525952 bytes
    Redo Buffers 4964352 bytes
    ORA-01102: cannot mount database in EXCLUSIVE mode

    ReplyDelete
    Replies
    1. Please post the following :
      ps -ef|grep pmon

      Post the last 50 lines in alertlog please

      Delete
  10. [oracle@localhost trace]$ ps -ef | grep pmon
    oracle 9037 1 0 10:27 ? 00:00:00 ora_pmon_ORCL
    oracle 12325 1 0 Nov09 ? 00:00:06 ora_pmon_orcl
    oracle 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 2012
    PMON started with pid=2, OS id=9037
    Mon Nov 12 10:27:12 2012
    VKTM started with pid=3, OS id=9041 at elevated priority
    VKTM running at (10)millisec precision with DBRM quantum (100)ms
    Mon Nov 12 10:27:12 2012
    GEN0 started with pid=4, OS id=9047
    Mon Nov 12 10:27:12 2012
    DIAG started with pid=5, OS id=9051
    Mon Nov 12 10:27:12 2012
    DBRM started with pid=6, OS id=9055
    Mon Nov 12 10:27:13 2012
    PSP0 started with pid=7, OS id=9059
    Mon Nov 12 10:27:13 2012
    DIA0 started with pid=8, OS id=9063
    Mon Nov 12 10:27:13 2012
    MMAN started with pid=9, OS id=9067
    Mon Nov 12 10:27:13 2012
    DBW0 started with pid=10, OS id=9071
    Mon Nov 12 10:27:13 2012
    LGWR started with pid=11, OS id=9075
    Mon Nov 12 10:27:13 2012
    CKPT started with pid=12, OS id=9079
    Mon Nov 12 10:27:13 2012
    SMON started with pid=13, OS id=9083
    Mon Nov 12 10:27:13 2012
    RECO started with pid=14, OS id=9087
    Mon Nov 12 10:27:13 2012
    MMON started with pid=15, OS id=9091
    Mon Nov 12 10:27:13 2012
    MMNL 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/oracle
    Mon Nov 12 10:27:14 2012
    ALTER DATABASE MOUNT
    sculkget: failed to lock /opt/oracle/112/dbs/lkORCL exclusive
    sculkget: lock held by PID: 12359
    ORA-09968: unable to lock file
    Linux-x86_64 Error: 11: Resource temporarily unavailable
    Additional information: 12359
    ORA-1102 signalled during: ALTER DATABASE MOUNT...

    ReplyDelete
  11. Like i expected

    Do the following :

    kill -9 9037
    kill -9 12325

    then
    1-After that fire export ORACLE_SID= (watch case sensitive )
    2-sqlplus / as sysdba
    3-startup ;

    ReplyDelete
  12. Thanks a lot Osama, it worked and I have successfully installed Oracle Identity Management.
    Much appreciated.

    Thanks,
    -Arshad

    ReplyDelete
  13. 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 functions
    if [ -f ~/.bashrc ]; then
    . ~/.bashrc
    fi

    # User specific environment and startup programs

    PATH=$PATH:$HOME/bin

    export PATH
    ORACLE_BASE=/opt/oracle
    ORACLE_HOME=$ORACLE_BASE/112
    ORACLE_SID=ORCL
    LD_LIBRARY_PATH=$ORACLE_HOME/lib
    PATH=$PATH:$ORACLE_HOME/bin

    export 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_ORCL
    root 9866 5181 0 12:33 pts/1 00:00:00 grep pmon

    LSNRCTL> status
    Services Summary...
    Service "ORCL" has 1 instance(s).
    Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...

    SQL> startup
    ORACLE instance started.

    Total System Global Area 267227136 bytes
    Fixed Size 2212496 bytes
    Variable Size 205524336 bytes
    Database Buffers 54525952 bytes
    Redo Buffers 4964352 bytes
    ORA-00205: error in identifying control file, check alert log for more info


    Thanks for your help,
    -Arshad

    ReplyDelete
    Replies
    1. 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.

      Delete
  14. 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 the
    https://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 dbconsole
    OC4J Configuration issue. /opt/oracle/112/oc4j/j2ee/OC4J_DBConsole_localhost.localdomain_orcl not found

    Please 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

    ReplyDelete
    Replies
    1. 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

      Delete
    2. 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

      Delete
    3. check DBSNMP, SYSMAN, and SYS not locked and password not been changed.

      Delete
  15. Could you please tell me how can I check that? SQL queries?

    Thanks,
    -Arshad

    ReplyDelete
    Replies
    1. I guess its time to read some Oracle Documentation and start over .

      SQL > Select username , account_status from dba_users

      Delete
    2. 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

      Delete
    3. if the above solution not working with you could be able to Create a static entry for the SID to the SID_LIST in the listener.ora

      Delete
  16. 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 ?

    Thanks
    Vipin

    ReplyDelete
    Replies
    1. you're welcome , but could you please post more information

      Delete
    2. 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 ))

      Delete
    3. 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.

      Delete
  17. Hii Osama,
    Yes i am using the windows but will u please kindly eloborate more about that dependency tab in services?

    ReplyDelete
    Replies
    1. Check the below link which is describe the dependency tab
      https://svcdependencyviewer.codeplex.com/ also you can check Microsoft site about it.

      Delete
  18. HI Osama,
    Could please help me to solve the oracle issue
    Agent 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


    ReplyDelete
  19. # 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

    ReplyDelete
    Replies
    1. My pleasure delete the listener.ora and without create start it and see what happening

      Delete
  20. Thanks, Below is error message we have:
    Status Metric Collection Error
    This database is not configured
    Status Pending Since May 21, 2013 2:00:00 PM CAT
    Last Known State

    ReplyDelete
    Replies
    1. Enterprise manager error as i see , did you check from the user
      select account_status from dba_users where username = 'DBSNMP';
      if it's locked --> alter user dbsnmp identified by password account unlock;

      Check
      http://www.oracledistilled.com/grid-control/metric-collection-error-after-fresh-install-of-oracle-enterprise-manager-11gr1-grid-control/

      Delete
  21. Still the same issue anothing are change

    ReplyDelete
  22. 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).



    ReplyDelete
    Replies
    1. Check your tnsnames.ora and recreate your listener using netca

      Delete
  23. 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.

    ReplyDelete
    Replies
    1. From where you run duplicate ? it's recommended to run it from auxiliary

      Delete
  24. 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 of

    Thank you

    ReplyDelete