This discussion is archived
9 Replies Latest reply: Feb 7, 2013 5:38 AM by mseberg RSS

Blocked instance

$phinx19 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    Hi,

    Could you put V$RESOURCE_LIMIT ?
    Regards,
  • 2. Re: Blocked instance
    Levi-Pereira Guru
    Currently Being Moderated
    $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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi ,

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


    Regards,
    Sphinx
  • 7. Re: Blocked instance
    Levi-Pereira Guru
    Currently Being Moderated
    What was the error?
  • 8. Re: Blocked instance
    $phinx19 Newbie
    Currently Being Moderated
    Hi ,

    The error was saying no listener.

    Regards
  • 9. Re: Blocked instance
    mseberg Guru
    Currently Being Moderated
    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

Legend

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