1 2 Previous Next 28 Replies Latest reply: May 19, 2013 7:58 PM by 1009792 RSS

    ORA-12528: TNS:listener: all approriate instances are blocking new connecti

    vikas singhal
      HI all ,
      i am getting this error while log on on database 10g on operating system is WINDOW XP

      ORA-12528: TNS:listener: all approriate instances are blocking new connection

      Please GUide me how to resolve this issue


      Thanks And regards
      Vikas Singhal
        • 1. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
          SatishKandi
          ORA-12528:     TNS:listener: all appropriate instances are blocking new connections
          Cause:      All instances supporting the service requested by the client reported that they were blocking the new connections. This condition may be temporary, such as at instance startup.
          Action:      Attempt the connection again. If error persists, then contact the administrator to check the status of the instances.
          • 2. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
            Chinar
            vikas singhal wrote:
            HI all ,
            i am getting this error while log on on database 10g on operating system is WINDOW XP

            ORA-12528: TNS:listener: all approriate instances are blocking new connection

            Please GUide me how to resolve this issue


            Thanks And regards
            Vikas Singhal
            Try following:
            set ORACLE_SID=<YOUR_SID>
            sqlplus "/as sysdba"
            alter system disable restricted session;
            • 3. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
              rajeysh
              The error is typically displayed when the database is in NOMOUNT or RESTRICTED state.
              You can check the services of the listener to see if the service is in BLOCKED state:

              refer the link, this will helps you.
              http://askanantha.blogspot.com/2008/02/ora-12528-tns-listener-all-appropriate.html

              Edited by: rajeysh on Jul 13, 2010 1:24 PM
              • 4. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
                vikas singhal
                HOw to find listener to see if the service is in BLOCKED state:

                Thanks And Regards
                Vikas Singhal
                • 5. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
                  Chinar
                  vikas singhal wrote:
                  HOw to find listener to see if the service is in BLOCKED state:

                  Thanks And Regards
                  Vikas Singhal
                  You can use lsnrctl services as
                  C:\Documents and Settings\chinar>lsnrctl services
                  
                  LSNRCTL for 32-bit Windows: Version 10.2.0.2.0 - Production on 13-JUL-2010 14:30
                  :26
                  
                  Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                  
                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=chinar.bank.local)(PORT=
                  1521)))
                  Services Summary...
                  Service "+ASM_XPT" has 1 instance(s).
                    Instance "+asm", status BLOCKED, has 1 handler(s) for this service...
                      Handler(s):
                        "DEDICATED" established:0 refused:0 state:ready
                           LOCAL SERVER
                  Service "+asm" has 1 instance(s).
                    Instance "+asm", status BLOCKED, has 1 handler(s) for this service...
                      Handler(s):
                        "DEDICATED" established:0 refused:0 state:ready
                           LOCAL SERVER
                  Service "TEST" has 2 instance(s).
                    Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
                      Handler(s):
                        "DEDICATED" established:387 refused:0
                           LOCAL SERVER
                    Instance "test", status RESTRICTED, has 1 handler(s) for this service...
                      Handler(s):
                        "DEDICATED" established:0 refused:0 state:ready
                           LOCAL SERVER
                  Service "TESTXDB" has 1 instance(s).
                    Instance "test", status RESTRICTED, has 1 handler(s) for this service...
                      Handler(s):
                        "D000" established:0 refused:0 current:0 max:1002 state:ready
                           DISPATCHER <machine: CHINAR, pid: 7472>
                           (ADDRESS=(PROTOCOL=tcp)(HOST=chinar.bank.local)(PORT=4790))
                  Service "TEST_XPT" has 1 instance(s).
                    Instance "test", status RESTRICTED, has 1 handler(s) for this service...
                      Handler(s):
                        "DEDICATED" established:0 refused:0 state:ready
                           LOCAL SERVER
                  Service "chinar.bank.local" has 1 instance(s).
                    Instance "SB", status UNKNOWN, has 1 handler(s) for this service...
                      Handler(s):
                        "DEDICATED" established:0 refused:0
                           LOCAL SERVER
                  The command completed successfully
                  
                  C:\Documents and Settings\chinar>
                  • 6. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
                    rajeysh
                    C:\>lsnrctl

                    LSNRCTL> services

                    post the output here.

                    also

                    SQL> select instance_name, status, database_status from v$instance;
                        ORA-12528 error is new in 10g.
                        it is happen when you connect with "TNS_ENTRY" with username/password and try to startup database followed by shutdown command.
                    
                        Solution:
                        1.change listener registration from STATIC to DYNAMIC.
                    
                        2. Or connect without TNS_ENTRY.
                    
                    answered by Mohammed Taj
                    
                    refer the link mentioned by me.
                    Edited by: rajeysh on Jul 13, 2010 3:07 PM
                    • 8. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
                      vikas singhal
                      i did this but the problum still exists

                      Thanks And Regards
                      vikas
                      • 9. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
                        vikas singhal
                        C:\Documents and Settings\FAY>lsnrctl

                        LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 13-JUL-2010 15:06
                        :51

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

                        Welcome to LSNRCTL, type "help" for information.

                        LSNRCTL> services
                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                        Services Summary...
                        Service "PLSExtProc" has 1 instance(s).
                        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                        Handler(s):
                        "DEDICATED" established:0 refused:0
                        LOCAL SERVER
                        Service "orcl" 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
                        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
                        The command completed successfully
                        LSNRCTL>



                        SQL> select instance_name, status, database_status from v$instance;
                        
                        INSTANCE_NAME    STATUS       DATABASE_STATUS
                        ---------------- ------------ -----------------
                        orcl             STARTED      ACTIVE
                        Thanks ANd Regards
                        Vikas Singhal

                        Edited by: vikas singhal on Jul 13, 2010 3:10 PM
                        • 10. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
                          Chinar
                          vikas singhal wrote:
                          i did this but the problum still exists

                          Thanks And Regards
                          vikas
                          Did you do following?
                          set ORACLE_SID=<YOUR_SID>
                          sqlplus "/as sysdba"
                          alter system disable restricted session;
                          • 11. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
                            vikas singhal
                            Yes i did this but nothing happens the problum still exists

                            Thanks ANd Regards
                            Vikas Singhal
                            • 12. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
                              Anand...
                              Hi Vikas,


                              Paste the output of :-


                              select name,open_mode,database_role from v$database;

                              Regards,
                              Anand
                              • 13. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
                                Chinar
                                Post there tnsnames.ora and listener.ora file
                                • 14. Re: ORA-12528: TNS:listener: all approriate instances are blocking new connecti
                                  vikas singhal
                                  tnsnames.ora

                                  # tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
                                  # Generated by Oracle configuration tools.
                                  ORCL =
                                    (DESCRIPTION =
                                      (ADDRESS = (PROTOCOL = TCP)(HOST = vikas)(PORT = 1521))
                                      (CONNECT_DATA =
                                        (SERVER = DEDICATED)
                                        (SERVICE_NAME = orcl)
                                      )
                                    )
                                  
                                  EXTPROC_CONNECTION_DATA =
                                    (DESCRIPTION =
                                      (ADDRESS_LIST =
                                        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                                      )
                                      (CONNECT_DATA =
                                        (SID = PLSExtProc)
                                        (PRESENTATION = RO)
                                      )
                                    )
                                  listener.ora
                                  # listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
                                  # Generated by Oracle configuration tools.
                                  
                                  SID_LIST_LISTENER =
                                    (SID_LIST =
                                      (SID_DESC =
                                        (SID_NAME = PLSExtProc)
                                        (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
                                        (PROGRAM = extproc)
                                      )
                                    )
                                  
                                  LISTENER =
                                    (DESCRIPTION_LIST =
                                      (DESCRIPTION =
                                        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                                        (ADDRESS = (PROTOCOL = TCP)(HOST = vikas)(PORT = 1521))
                                      )
                                    )
                                  Thanks ANd Regards
                                  vikas Singhal
                                  1 2 Previous Next