This discussion is archived
14 Replies Latest reply: Apr 23, 2013 3:03 PM by 1002082 RSS

ORA-12528: TNS: listener: all appropriate instances are blocking new connec

1002082 Newbie
Currently Being Moderated
Hi this is kumar,
i am getting the below error .

ORA-12528: TNS: listener: all appropriate instances are blocking new connections

please find my alertlog file ,in alert log file we are getting opertaion timed out.

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 238
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.254.254.32)(PORT=1599))
  • 1. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    Stefan Abraham Newbie
    Currently Being Moderated
    Hi,

    Are you trying for auxiliary connection?
    Information you have provided is not enough.

    If you are after an auxiliary connection try adding a static entry in listener.ora.

    Thanks
    Stefan
  • 2. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    1002082 Newbie
    Currently Being Moderated
    Hi,
    thank you..

    no we are not trting auxilary connection.

    please let us know what type of information you want then i wil provide the information to u..
  • 3. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    FreddieEssex Pro
    Currently Being Moderated
    Post the output of:
    lsnrctl status listener
    select open_mode from v$database;
    select status, database_status from v$instance;
    Is your database open or mounted? Check your alert log for any errors when the database was started up.
  • 4. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    1002082 Newbie
    Currently Being Moderated
    it is two node rac environment on HP-UX

    my database is open statte only
  • 5. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    FreddieEssex Pro
    Currently Being Moderated
    So can you post the output of the following:
    lsnrctl status <listener_name>
    select inst_id, status, database_status from gv$instance;
    select inst_id, open_mode from gv$database;
    Also post your database version please.
  • 6. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    1002082 Newbie
    Currently Being Moderated
    Hi,

    thank you.

    it is two node rac environment on HP-UX


    Listener LISTENER is enabled
    Listener LISTENER is running on node(s): node01,node02


    $ lsnrctl status

    LSNRCTL for HPUX: Version 11.2.0.2.0 - Production on 22-APR-2013 20:06:18

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for HPUX: Version 11.2.0.2.0 - Production
    Start Date 30-JAN-2013 01:46:21
    Uptime 82 days 18 hr. 19 min. 57 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
    Listener Log File /u01/app/grid/diag/tnslsnr/node01/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.44.83.108)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.44.83.106)(PORT=1521)))
    Services Summary...
    Service "+ASM" has 1 instance(s).
    Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Service "SYS$SYS.KUPC$C_1_20130422200003.instance" has 1 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Service "SYS$SYS.KUPC$S_1_20130422200003.instance" has 1 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Service "instance" has 1 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Service "instanceXDB" has 1 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Service "instance" has 1 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Service "instanceXDB" has 1 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Service "instanceoltp" has 1 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    The command completed successfully
    You have mail in /var/mail/oracle
    $

    SQL> select inst_id, status, database_status from gv$instance;

    INST_ID STATUS DATABASE_STATUS
    ---------- ------------ -----------------
    1 OPEN ACTIVE
    2 OPEN ACTIVE


    SQL> select inst_id, open_mode from gv$database;

    INST_ID OPEN_MODE
    ---------- --------------------
    2 READ WRITE
    1 READ WRITE
  • 7. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    FreddieEssex Pro
    Currently Being Moderated
    Hi,

    You are on 11gR2 so you should be using SCAN listener...correct?

    Post the output of:
    srvctl config scan_listener
    Also post the output of where name is obtained from the output above:
    lsnrctl status scan_listener
    Post your tnsnames or connection string that you are using on the client.

    Please also confirm the following:
    Any changes on the database recently? Was everything working okay before? Is this a new database so you haven't had successful connections to the db before?
  • 8. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    1002082 Newbie
    Currently Being Moderated
    Hi,
    thank you for your quick responce.


    $ srvctl config scan_listener
    SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1526
    SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1526
    SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1526


    $ lsnrctl status LISTENER_SCAN1

    LSNRCTL for HPUX: Version 11.2.0.2.0 - Production on 22-APR-2013 20:38:33

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER_SCAN1
    Version TNSLSNR for HPUX: Version 11.2.0.2.0 - Production
    Start Date 30-JAN-2013 01:46:21
    Uptime 82 days 18 hr. 52 min. 12 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
    Listener Log File /u01/app/11.2.0/grid/log/diag/tnslsnr/dccdb01/listener_scan1/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.44.83.111)(PORT=1526)))
    Services Summary...
    Service "instance" has 2 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Instance "instance2", status READY, has 1 handler(s) for this service...
    Service "instanceXDB" has 2 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Instance "instance2", status READY, has 1 handler(s) for this service...
    Service "instance" has 2 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Instance "instance2", status READY, has 1 handler(s) for this service...
    Service "instanceXDB" has 2 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Instance "instance2", status READY, has 1 handler(s) for this service...
    Service "instanceoltp" has 2 instance(s).
    Instance "instance1", status READY, has 1 handler(s) for this service...
    Instance "instance2", status READY, has 1 handler(s) for this ser
    $


    ABCD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dccdb-cluster-scan)(PORT = 1526))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ABCD)
    )
    )

    ADCDE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dccdb-cluster-scan)(PORT = 1526))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ADCDE)
    )
    )


    we didn't do any changes from the database side,now only my user had raised a complaint
  • 9. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    FreddieEssex Pro
    Currently Being Moderated
    Okay so your database is up, your listener is up ( Presumably all your listeners are up) and your status is ready.

    Furthermore an end user is complaining and nothing has changed from the database end.....hmmm...that old chestnut !!!

    Have you tried connecting to the database yourself and see if you get the same error?

    Presumably you have done a search and replace and replaced your service name and instance names with "instance"?

    Or is your service name really instance and your instance_names are instance1 and instance2?

    You might need to create a correct tnsnames entry as your current tnsnames specifies a service name of ABCD and ABCDE.

    Can you create a dummy entry with the correct service name (whatever that might be) and see if you get the same error please.

    Also post output of the following to see if your tnsnames on the client is using the correct scan name:
    srvctl config scan
  • 10. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    1002082 Newbie
    Currently Being Moderated
    Hi thak you for your quick response..



    $ srvctl config scan
    SCAN name: NODE-cluster-scan, Network: 1/10.44.179.0/255.255.255.0/lan2
    SCAN VIP name: scan1, IP: /NODE-cluster-scan.web.local/10.44.83.111
    SCAN VIP name: scan2, IP: /NODE-cluster-scan.web.local/10.44.83.112
    SCAN VIP name: scan3, IP: /NODE-cluster-scan.web.local/10.44.83.110

    while i tried my self i didn't observed any error.

    it is production database environment.
  • 11. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    FreddieEssex Pro
    Currently Being Moderated
    Hello,

    So that answers your question !!!

    The scan listener name you provided just now is NODE-cluster-scan and the one you provided earlier from the client was dccdb-cluster-scan.

    So I guess if you do the following it will not resolve to the IP's that you have in your last post ie 10.44.83.110, 111 and 112.
    nslookup dccdb-cluster-scan
    Ask your end-user to amend their tnsnames.ora with the entry that worked for you. Then tnsping from your client to ensure you are going to the correct host and service and to ensure connectivity etc is all okay. Finally try to connect.
  • 12. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    1002082 Newbie
    Currently Being Moderated
    sorry my scan name is like below..

    thsnk you..

    $ nslookup just i changed scan name
    Using /etc/hosts on:

    looking up FILES
    Trying DNS
    Name: just i changed scan name
    Addresses: 10.44.83.110, 10.44.83.112, 10.44.83.111
  • 13. Re: ORA-12528: TNS: listener: all appropriate instances are blocking new connec
    FreddieEssex Pro
    Currently Being Moderated
    The output of srvctl config scan is the scan name registered with the clusterware.

    This is the scan name you need to use from your client. You can use whatever service name you want as long as it has been registered with the database.

    Check the services using:
    srvctl status service -d <db_name>
    Your clients tnsnames needs to reflect both the scan name and the service name, as well as the port the scan listener is listening on.

    I'm not quite sue how you got from NODE-cluster-scan to the scan name of "just i changed scan name".

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points