14 Replies Latest reply: Apr 23, 2013 5:03 PM by 1002082 RSS

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

    1002082
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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".