11 Replies Latest reply: Mar 8, 2012 11:49 PM by rcc50886 RSS

    LSNRCTL Status --> BLOCKED

    618702
      Hi gurus,

      When i type lsnrctl status; my primary database's listener is up and shown as READY but my standby database's listener is shown as BLOCKED in the list of lsnrctl status. How can i make my standby database's listener from BLOCKED to READY status?

      Solaris Oracle 10g 2 node RAC.

      Thanks,

      Edited by: Ogan Ozdogan on Oct 26, 2008 1:14 PM

      Besides, i get
      ERROR:
      ORA-12528 : TNS:listener: all appropriate instances are blocking new connections
        • 1. Re: LSNRCTL Status --> BLOCKED
          Aman....
          Can you post your listener.ora? One reason for the blocked status is that Standby database is not mounted and/or is started in some excliusive mode. So this may also shpw the status as blocked. One way could be to edit the listener.ora and enter your sid details in it.
          The another ora message is coming in which database, primary or secondary?
          Check the Metalink Note, 343480.1 for the same error.
          HTH
          Aman....
          • 2. Re: LSNRCTL Status --> BLOCKED
            618702
            SID_LIST_LISTENER =
            (SID_LIST =
            (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = ...)
            (PROGRAM = extproc)
            )
            )

            LISTENER =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
            (ADDRESS = (PROTOCOL = TCP) (host2) (PORT = 1521)
            )
            )

            this is the listener.ora of the standby database. ORA message comes from standby database when i try to connect using sqlplus sys/password@oracle_sid as sysdba (standby)
            • 3. Re: LSNRCTL Status --> BLOCKED
              Aman....
              Did you see the note that I mentioned in my last reply? Why you are trying to connect to Standby? Its not open and the message and error shown are correct. Standby database is going to be in the mount stage most of the time and is not available for usage. Only in 11g , its possible to open it and still put it in the recovery mode.
              Did you open the standby database and switched it to Primary?
              Aman....
              • 4. Re: LSNRCTL Status --> BLOCKED
                Nicolas.Gasparotto
                Aman,
                There are many many reasons to connect onto the standby database, even in Oracle version lower than 11g, especially it is/was really helpful for reporting database in read-only mode, and still available for recovery mode. You could even mahe changes and still available for recovery mode in some specific configurations, not only from 11g onwards.
                Moreover, that should not be an issue to connect as SYS on the database.

                Ogan,
                You should try to connect onto the database directly on the server, not remotely, what did you try ?
                Lastly, what if you set the ORACLE_SID env variable before connect, like that :
                export ORACLE_SID=your_dbname
                sqlplus / as sysdba

                Nicolas.
                • 5. Re: LSNRCTL Status --> BLOCKED
                  Aman....
                  Nicolas,
                  Thanks for the info. My knowledge about Dataguard is not much vast so I didn't know the details that you just mentioned. As per the ML note which I gave to OP, the issue and error seems to be due to Listener.ora and dynamic registration of database with it. I mentioned about standby as it didn't occur to me due to my limited knowledge yet about database that it can be used while being in recovery mode, feature which I believe came in 11g only.
                  Thanks and regards
                  Aman....
                  • 6. Re: LSNRCTL Status --> BLOCKED
                    Nicolas.Gasparotto
                    database that it can be used while being in recovery mode, feature which I believe came in 11g only.
                    I'm not sure to understand your point as well, but a logical standby database can be available for reporting at the same time you are applying redo logs to it since version 9.2.
                    http://download.oracle.com/docs/cd/B10501_01/server.920/a96653/whatsnew.htm#970513

                    Nicolas.
                    • 7. Re: LSNRCTL Status --> BLOCKED
                      Aman....
                      Nicolas,
                      As I mentioned before, it was due to my limited knowledge about Dataguard. Surely enough, the feature is there as you mentioned before.
                      Regards
                      Aman....
                      • 8. Re: LSNRCTL Status --> BLOCKED
                        618702
                        Hi,

                        Thanks for your advance!

                        When the database is in nomount stage, the listener shows status "BLOCKED" and disables all the connections with @ORACLE_SID

                        Afterwards, when you turn the database to mount or open stage, you will see that listener's status is "READY"

                        Thank you Aman.
                        • 9. Re: LSNRCTL Status --> BLOCKED
                          669973
                          Hi,
                          I thought of adding one more point even though it not relevant to DG but in common. If the listener is unable to create process in server ie. processess reached max, in that case also listener status for the particular SID will be marked as BLOCKED.
                          • 10. Re: LSNRCTL Status --> BLOCKED
                            user9277873
                            This is not true, especially in 11gR2. I tested and I can connect remotely to a nomount instance.

                            lamatoragrid3:/u01/app/oracle/product/11.2.0/db_1/dbs $ sqlplus sys/oracle2012@dv200 as sysdba

                            SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 29 10:38:33 2012

                            Copyright (c) 1982, 2011, Oracle. All rights reserved.


                            Connected to:
                            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                            With the Partitioning, Automatic Storage Management, OLAP, Data Mining
                            and Real Application Testing options

                            SQL> select * from v$instance;

                            INSTANCE_NUMBER INSTANCE_NAME
                            --------------- ----------------
                            HOST_NAME
                            ----------------------------------------------------------------
                            VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
                            ----------------- --------- ------------ --- ---------- ------- ---------------
                            LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
                            ---------- --- ----------------- ------------------ --------- ---
                            1 DV200
                            lamatoragrid2
                            11.2.0.3.0 29-FEB-12 STARTED NO 0 STOPPED
                            ALLOWED NO ACTIVE UNKNOWN NORMAL NO


                            SQL> !hostname
                            lamatoragrid3

                            SQL>
                            • 11. Re: LSNRCTL Status  -->  BLOCKED
                              rcc50886
                              You need to create static LISTENER for standby database inorder to connect as sysdba remotely

                              If it is a rac you don't need static listener if you are using service_name

                              -Thanks