9 Replies Latest reply: Feb 7, 2013 7:38 AM by mseberg RSS

    Blocked instance

    $phinx19
      Hi All,

      OS:Windows
      DB:11g

      I have faced these problem several time while creating a clone of DB or creating a Standby database of the same using RMAN.

      The problem is when i nomount my clone/standby instance, the instance status is blocked in
      lsnrctl status
      Services Summary...
      Service "TESTCLON" has 1 instance(s).
      Instance "testclone", status READY, has 1 handler(s) for this service...
      Service "test" has 2 instance(s).
      Instance "test", status UNKNOWN, has 1 handler(s) for this service...
      Instance "test", status READY, has 1 handler(s) for this service...
      Service "testXDB" has 2 instance(s).
      Instance "test", status READY, has 1 handler(s) for this service...
      Instance "testclone", status READY, has 1 handler(s) for this service...
      Service "testdr" has 1 instance(s).
      Instance "testdr", status BLOCKED, has 1 handler(s) for this service...
      The command completed successfully4803962

      I have tried the interim solutions like
      Restarting the listener.
      Restarting the instance.
      Is there a permanent way to fix this problem.

      Regards,
      Sphinx
        • 1. Re: Blocked instance
          asahide
          Hi,

          Could you put V$RESOURCE_LIMIT ?
          Regards,
          • 2. Re: Blocked instance
            Levi Pereira
            $phinx19 wrote:
            Hi All,

            OS:Windows
            DB:11g

            I have faced these problem several time while creating a clone of DB or creating a Standby database of the same using RMAN.

            The problem is when i nomount my clone/standby instance, the instance status is blocked in
            lsnrctl status
            Services Summary...
            Service "TESTCLON" has 1 instance(s).
            Instance "testclone", status READY, has 1 handler(s) for this service...
            Service "test" has 2 instance(s).
            Instance "test", status UNKNOWN, has 1 handler(s) for this service...
            Instance "test", status READY, has 1 handler(s) for this service...
            Service "testXDB" has 2 instance(s).
            Instance "test", status READY, has 1 handler(s) for this service...
            Instance "testclone", status READY, has 1 handler(s) for this service...
            Service "testdr" has 1 instance(s).
            Instance "testdr", status BLOCKED, has 1 handler(s) for this service...
            The command completed successfully4803962

            I have tried the interim solutions like
            Restarting the listener.
            Restarting the instance.
            Is there a permanent way to fix this problem.
            Hi,

            Isn't it a normal behaviour? Since your instance is in status Mount or Nomount and can't accept connection of normal users.

            A BLOCKED status means that the instance cannot accept connections, only administratives session (i.e from SYS user).

            Note:
            http://docs.oracle.com/cd/E11882_01/network.112/e10836/listenercfg.htm#NETAG307

            Regards,
            Levi Pereira
            • 3. Re: Blocked instance
              $phinx19
              Hi,

              Here is the o/p:

              RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
              ------------------------------ ------------------- --------------- ---------- ----------
              processes 30 35 150 150
              sessions 38 43 248 248
              enqueue_locks 16 27 3080 3080
              enqueue_resources 15 38 1308 UNLIMITED
              ges_procs 0 0 0 0
              ges_ress 0 0 0 UNLIMITED
              ges_locks 0 0 0 UNLIMITED
              ges_cache_ress 0 0 0 UNLIMITED
              ges_reg_msgs 0 0 0 UNLIMITED
              ges_big_msgs 0 0 0 UNLIMITED
              ges_rsv_msgs 0 0 0 0
              gcs_resources 0 0 0 0
              gcs_shadows 0 0 0 0
              dml_locks 0 0 1088 UNLIMITED
              temporary_table_locks 0 0 UNLIMITED UNLIMITED
              transactions 0 0 272 UNLIMITED
              branches 0 0 272 UNLIMITED
              cmtcallbk 0 1 272 UNLIMITED
              max_rollback_segments 11 11 272 65535
              sort_segment_locks 0 1 UNLIMITED UNLIMITED
              k2q_locks 0 0 496 UNLIMITED
              max_shared_servers 1 1 UNLIMITED UNLIMITED
              parallel_max_servers 0 0 40 3600
              Can you tell me what are you getting to ?

              Regards

              Edited by: $phinx19 on Feb 5, 2013 5:18 AM
              • 4. Re: Blocked instance
                $phinx19
                Hi Levi,

                If this is a normal behavior, then how you expect to create a standby database using rman duplicate command wherein which just before hitting the duplicate command, we connect to primary database as :

                At Primary:
                RMAN target / auxiliary sys/passwd@testdr nocatalog.
                Regards,
                • 5. Re: Blocked instance
                  Levi Pereira
                  Hi,

                  You must connect with SYS (i.e or any user with SYSDBA grant) user, what is allowed even if instance is in BLOCKED status.

                  The connection below is allowed even when Instance is in BLOCKED status.
                  RMAN target / auxiliary sys/passwd@testdr nocatalog.
                  Why you are asking for "BLOCKED" instance... Did you have any issue while connecting on Auxilliary Instance?

                  Regards,
                  Levi Pereira

                  Edited by: Levi Pereira on Feb 5, 2013 12:16 PM
                  • 6. Re: Blocked instance
                    $phinx19
                    Hi ,

                    Yes, I faced the same issue while connecting to RMAN using this command.


                    Regards,
                    Sphinx
                    • 7. Re: Blocked instance
                      Levi Pereira
                      What was the error?
                      • 8. Re: Blocked instance
                        $phinx19
                        Hi ,

                        The error was saying no listener.

                        Regards
                        • 9. Re: Blocked instance
                          mseberg
                          Hello;

                          Common issue with duplicate. Most often a static entry is created for the new clone and it is started no mount. ( SID_LIST_LISTENER is the static part )

                          Example
                          The listener.ora and tnsnames.ora are setup for the Primary and Standby Database.
                          
                          Primary
                          # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.2/network/admin/listener.ora
                          # Generated by Oracle configuration tools.
                          
                          LISTENER =
                              (DESCRIPTION_LIST =
                                (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
                                )
                              )
                          
                          SID_LIST_LISTENER =
                              (SID_LIST =
                                (SID_DESC =
                                (global_dbname = PRIMARY_DGMGRL.hostname)
                                (ORACLE_HOME = /u01/app/oracle/product/11.2.0.2)
                                (sid_name = PRIMARY)
                              )
                          )
                          
                          Standby
                          # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.2/network/admin/listener.ora
                          # Generated by Oracle configuration tools.
                          
                          
                          LISTENER =
                              (DESCRIPTION_LIST =
                                (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
                                )
                              )
                          
                          SID_LIST_LISTENER =
                              (SID_LIST =
                                (SID_DESC =
                                (global_dbname = STANDBY_DGMGRL.hostname)
                                (ORACLE_HOME = /u01/app/oracle/product/11.2.0.2)
                                (sid_name = STANDBY)
                                )
                                (SID_DESC =
                                (global_dbname = STANDBY.hostname)
                                (ORACLE_HOME = /u01/app/oracle/product/11.2.0.2)
                                (sid_name = STANDBY)
                          )
                          
                          Notice hard-coded network entries for the auxiliary database.
                          
                          This is key for the RMAN connection later.
                          
                          (SID_DESC =
                              (global_dbname = STANDBY.hostname)
                              (ORACLE_HOME = /u01/app/oracle/product/11.2.0.2)
                              (sid_name = STANDBY)
                          )
                          
                          Stopping and starting the listener is a good idea.
                          Best Regards

                          mseberg