Oracle quiesce database, only DBA sessions

Sometimes you need to perform operations on a BD when opened, for example moving datafiles or put tablespaces in read only.

These transactions generally can be performed with the BD in production, but the problem arises when we have a high activity and we have to make many changes. In these cases it is best to restrict access to users, allowing access only DBA (sys or system if we have not created any other).

We can put the database in quiesce mode (still or inactive), only SYS and SYSTEM can create new sessions, the rest remain until they complete the transaction.

To view the status of the database:

Source   
select active_state from v$instance;

The result can be:
NORMAL: Normal state of the database.
QUIESCING: Beginning of quiesced mode, we still have sessions that are not DBA active but can not create new sessions.
QUIESCED: Now only sessions DBA, new sessions not DBA not allowed.

To put the database in quiesce mode (from sqlplus):

Source   
ALTER SYSTEM QUIESCE RESTRICTED;

Wait for it to finish all sessions other than SYS or SYSTEM, to see that it is waiting session:

Source   
select bl.sid, user, osuser, type, program
from v$blocking_quiesce bl, v$session se
where bl.sid = se.sid;

After waiting a reasonable time and once identified, we can stop the proprietary application sessions or kill them directly.

We can now perform administrative tasks without delays generated by a high turnout in the database.

To get it out the database from quiesce mode:

Source   
ALTER SYSTEM UNQUIESCE;

Leave a Reply