Does the V$BLOCKING_QUIESCE provide efficient information..

I had started my journey to dig up the each bit by bit  and concept wise of Oracle Documentation of 11g Release 2, While going through the oracle documentation I came across regarding the database quiesce state,as every other DBA knows the important aspect of this command reference.

Let me put up some important aspects which I came across today, might be others are well known with these things, but I am slight not well aware of some thing, when compared to other versions.

I won’t be describing the things, what is all about the DATABASE QUIESCE STATE.  Please observe the below test case.

Session 1 – Login as Sysdba

   1: C:\Users\Pavan>sqlplus / as sysdba

   2:  

   3: SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 15 17:11:49 2010

   4:  

   5: Copyright (c) 1982, 2010, Oracle.  All rights reserved.

   6:  

   7:  

   8: Connected to:

   9: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  10: With the Partitioning, OLAP, Data Mining and Real Application Testing options

  11:  

  12: SQL> spool C:\spool\quiesce_state.txt

  13: SQL> desc v$blocking_quiesce

  14:  Name                                      Null?    Type

  15:  ----------------------------------------- -------- ----------------------------

  16:  SID                                                NUMBER

  17:  

  18: SQL> select * from v$blocking_quiesce

  19:   2  ;

  20:  

  21: no rows selected

  22:  

  23: SQL> alter system quiesce restricted;

Session 2 – login as sysdba

Check the status

   1: C:\Users\Pavan>sqlplus / as sysdba

   2:  

   3: SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 15 17:37:04 2010

   4:  

   5: Copyright (c) 1982, 2010, Oracle.  All rights reserved.

   6:  

   7:  

   8: Connected to:

   9: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  10: With the Partitioning, OLAP, Data Mining and Real Application Testing options

  11:  

  12: SQL>  select

  13:   2    decode(qui_state, 0,'NORMAL', 1,'QUIESCING', 2,'QUIESCED','UNKNOWN') status

  14:   3    from x$quiesce ;

  15:  

  16: STATUS

  17: ---------

  18: QUIESCING

  19:  

  20: SQL> disconnect

 

Session 1 -

Let me test the initial quiesce state of database and put my database in quiesce restricted mode, perhaps let’s check what details we found from the view v$blocking_quiesce.

   1: SQL> select * from v$blocking_quiesce;

   2:  

   3: no rows selected

   4:  

   5: SQL> ALTER SYSTEM QUIESCE RESTRICTED;

   6:  

   7: System altered.

Session 2 – login as scott

   1: Microsoft Windows [Version 6.1.7600]

   2: Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

   3:  

   4: C:\Users\Pavan>sqlplus scott/tiger@orcl

   5:  

   6: SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 15 17:47:57 2010

   7:  

   8: Copyright (c) 1982, 2010, Oracle.  All rights reserved.

   9:  

  10:  

 The session was hanging and could not able to login. Let’s go back to Session and check the information from the v$blocking_quiesce.

   1: SQL> desc gv$blocking_quiesce

   2:  Name                                      Null?    Type

   3:  ----------------------------------------- -------- ----------

   4:  INST_ID                                            NUMBER

   5:  SID                                                NUMBER

   6:  

   7: SQL> desc v$blocking_quiesce

   8:  Name                                      Null?    Type

   9:  ----------------------------------------- -------- ----------

  10:  SID   

  11:  

  12: SQL> select object_type from dba_objects where object_name='V$BLOCKING_QUIESCE';

  13:  

  14: OBJECT_TYPE

  15: -------------------

  16: SYNONYM

  17:  

  18: SQL> select owner,synonym_name,table_owner,table_name from dba_synonyms where synonym_name='V$BLOCKING_QUIESCE';

  19:  

  20: OWNER                          SYNONYM_NAME

  21: ------------------------------ ------------------------------

  22: TABLE_OWNER                    TABLE_NAME

  23: ------------------------------ ------------------------------

  24: PUBLIC                         V$BLOCKING_QUIESCE

  25: SYS                            V_$BLOCKING_QUIESCE

we get the information from the x$tables, kindly refer to the v$instance view and its respective
based tables from which details are fetched out.

   1: SQL>  select

   2:   2    decode(qui_state, 0,'NORMAL', 1,'QUIESCING', 2,'QUIESCED','UNKNOWN') status

   3:   3    from x$quiesce ;

   4:  

   5: STATUS

   6: ---------

   7: QUIESCED

Hopefully this might be issued with respect this current release or might be bug. Let’s wait for any patch (or)  update from oracle regarding this.

Let’s release back to unquiesce state and check the session 2.

Session 1

SQL> alter system unquiesce;

System altered.

Session 2

   1: Microsoft Windows [Version 6.1.7600]

   2: Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

   3:  

   4: C:\Users\Pavan>sqlplus scott/tiger@orcl

   5:  

   6: SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 15 17:47:57 2010

   7:  

   8: Copyright (c) 1982, 2010, Oracle.  All rights reserved.

   9:  

  10:  

  11: Connected to:

  12: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  13: With the Partitioning, OLAP, Data Mining and Real Application Testing options

  14:  

  15: SQL>

Hope  this helps… !!