ORACLE : Quiescing database

ORACLE : Quiescing database

8 Aprile 2016 Oracle Database 0

Schema changes, reorganizations or other administrative tasks are difficult to make while users area conducting live transactions in the database.
You have to perform these activity during a schedulated maintenance or you have to shut down the database and reopen in “restricted mode”.
For obviate  this problem , “quescing database” gives the possibility to put the database in single mode without having to ever shutdown the database.
When a database is in “quiesced state” the following conditions apply:
 

  • All inactive sessions are prevented from issuing any database commands until the database in in “unquiesced state”.
  • All active sessions are allowed to compile.
  • All new login attempts will be queued. A user trying to login during the time the database is a “quiesced state” won’t get an error message.
  • Only dba queries, transactions and plsql statements will be allowed in the database. To be more precise, queries and statements by all users in the Oracle Resource Manager SYS_GROUP group are allowed.

 
 
To place a database in “quiesced state” :
 
SQL>  Alter system quiesce restricted;
System altered.
To place a database in “unquiesced state” :
SQL>  Alter system unquiesce;
System altered.
 
Once the database in unquiesced all the queued logins are allowed into the database and all the inactive transactions are once again allowed to turn active by letting them execute their DML statements.

 

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *