thanhtt
25-04-07, 04:39 PM
Quiescing a database is a powerful feature that helps Oracle DBAs to do certain kinds of work they would not be able to do otherwise, without putting the database in the restricted mode. Using this feature, after logging in to the SYS or SYSTEM account, the DBA can do queries, PL/SQL, and other transactions. For other users, the system will seem to be inactive or in the HALT stage. All of the user's transactions will be kept in a suspended state and will resume automatically, once the DBA puts the system back into a normal state.
http://www.dbazine.com/images/wad1-1.gif
Figure 1a: Database is in normal state.
http://manage.dbazine.bmc.quintagroup.com/dbazine/sql/sql-articles/wad1-2.gif
Figure 1b: Database is in QUIESCE state.
Figure 1a is the system state when the database is in normal mode, in which both the DBA's and user's transactions are proceeding. Some of the DBA's transactions are restricted because it the database must be in restricted mode. On the other hand, figure 1b is showing the database in the QUIESED state. In this figure, all of the user's transactions are blocked, and all of the DBA transactions are proceeding without any problem and without starting the database in restricted mode.
The database will be quiesed once the user resolves all the active transaction either by COMMIT or ROLLBACK.
Let’s see how it works. The main command to QUIESCE the database is ALTER SYSTEM QUIESCE RESTRICTED; I will first login with SQLPLUS to do this:
C:\> U:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Apr 16 16:08:27 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/change_on_install as sysdba
Connected.
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM QUIESCE RESTRICTED
*
ERROR at line 1:
ORA-25507: resource manager has not been continuously on
The above error indicated the resource manager is not activated. To make the resource manager active, you can alter the system as such:
SQL> alter system set resource_manager_plan='SYSTEM_PLAN' scope=spfile
sid='OR9I';
System altered.
OR9i is my SID. You have to restart the database after this.
SQL> show parameter RESOURCE_MANAGER_PLAN
NAME TYPE VALUE
------------------------------------ ----------- ----------------
resource_manager_plan string SYSTEM_PLAN
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
System altered.
If there are some pending transactions that need to be commited or rolled back, the previous command will hang and wait for the transaction to finish. To determine user sessions, you can query as follows:
SELECT S.SID,S.SERIAL#,S.MACHINE,S.TERMINAL,S.USERNAME
FROM V$SESSION S WHERE S.SID IN
(SELECT SID FROM V$LOCK WHERE TYPE='TX')
/ The result of the query will give you enough information to enable you to ask the user either to commit or roll back the transaction to end it. In the worst-case scenario, you can kill the session, which will automatically roll back the transaction. Once the system is QUIESCED, you can work without interference from the other user. After finishing your work, you can release the system with the following:
SQL> ALTER SYSTEM UNQUIESCE;
System altered.
Scenario 1:
Order of Transaction User Session DBA Session (1) Connected with SCOTT
SQL> update emp3 set
ename='John'
where ename='samir'; Connected with SYS (2) SQL> ALTER SYSTEM QUIESCE RESTRICTED;
Waiting USER SYS to finish active
Transaction initiated by USER SCOTT. (3) SQL> commit;
Commit complete.
(4) System altered. As the first scenario indicates, the DBA cannot QUIESCE the database until all of the running active transactions are completed, either by COMMIT or ROLLBACK. Once the system is QUIESCED, the database appears to other database users to be in the HALT or INACTIVE state. Then, when the database state is changed, all the block or suspended transactions will resume.
Scenario 2:
Order of Transaction User Session DBA Session (1) Connected with Scott User . Connected with SYS.
SQL> ALTER SYSTEM QUIESCE
RESTRICTED;
System altered.
(2) Select * from EMP;
wait for result
(3) SQL> ALTER SYSTEM UNQUIESCE;
System altered.
EMPNO ENAME SALARY
--------- ---------- ----------
1 Sasa 1000
2 John 5000
3 Hema 7000
User can see the results.
As scenario 2 indicates how it effects the users transactions. In short, by this feature the SYSTEM is temporary unavailable to the end user.
Common Questions
(Q)How do you as a DBA determine your database is in what state.
(A) You have to check the ACTIVE_STATE in the V$INSTANCE view to determine this.
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
ACTIVE_ST
---------
NORMAL
The possible value for ACTIVE_STATE is as follows:
Active_state Description Normal DATABASE is in normal state. QUIESCING DATABASE wants to QUIESCED but waiting for active running transactions to finish. Quiesced DATABASE is in QUIESCED STATE now. (Q) How do you determine what sessions are connected and waiting for UNQUIESCING the database?
(A) To determine this issue the following query:
SELECT SID,EVENT,TOTAL_WAITS,TIME_WAITED "TIME WAITED[100 OF SEC]",
AVERAGE_WAIT FROM V$SESSION_EVENT
WHERE EVENT='wait for possible QUIESCE finish'
/
SQL>
SID EVENT TOTAL_WAITS Time Waited[100 of Sec] AVERAGE_WAIT
--- ---------------------------------- ----------- ----------------------- ------------
6 wait for possible QUIESCE finish 412 126532 307 The Event, "wait for possible QUIESCE finish," indicates that the session is waiting to UNQUIESCE the database so that it can proceed with its transaction. Until then, the session will appear in a hung state.
(Q) What is the need for setting resource manager plan before QUIESING a database?
(A) Behind the scenes, when you QUIESE the database, the INTERNAL_QUIESCE resource plan is activated. This sets the ACTIVE_SESS_POOL_P1 parameter to 0 for all the groups except the SYS_GROUPS. Since SYS and SYSTEM both falls under the SYS_GROUPS, they can only connect to the database.
To view the details, query the DBA_RSRC_PLAN_DIRECTIVES database view.
Points to Remember
Only SYS and SYSTEM users are the valid users to do maintenance work in a QUIESCED database; other users having DBA privileges are treated as normal users.
BACKUP OF DATAFILES (COLD BACKUP, copy of datafiles) is not valid in a QUIESED database.
Database cannot be QUIESED if the transactions are still active.
Need to start up the database to set the resource plan. Conclusion
QUIESCING a database is a powerful feature that allows DBAs to have open windows without restarting the database.
http://www.dbazine.com/images/wad1-1.gif
Figure 1a: Database is in normal state.
http://manage.dbazine.bmc.quintagroup.com/dbazine/sql/sql-articles/wad1-2.gif
Figure 1b: Database is in QUIESCE state.
Figure 1a is the system state when the database is in normal mode, in which both the DBA's and user's transactions are proceeding. Some of the DBA's transactions are restricted because it the database must be in restricted mode. On the other hand, figure 1b is showing the database in the QUIESED state. In this figure, all of the user's transactions are blocked, and all of the DBA transactions are proceeding without any problem and without starting the database in restricted mode.
The database will be quiesed once the user resolves all the active transaction either by COMMIT or ROLLBACK.
Let’s see how it works. The main command to QUIESCE the database is ALTER SYSTEM QUIESCE RESTRICTED; I will first login with SQLPLUS to do this:
C:\> U:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Apr 16 16:08:27 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/change_on_install as sysdba
Connected.
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM QUIESCE RESTRICTED
*
ERROR at line 1:
ORA-25507: resource manager has not been continuously on
The above error indicated the resource manager is not activated. To make the resource manager active, you can alter the system as such:
SQL> alter system set resource_manager_plan='SYSTEM_PLAN' scope=spfile
sid='OR9I';
System altered.
OR9i is my SID. You have to restart the database after this.
SQL> show parameter RESOURCE_MANAGER_PLAN
NAME TYPE VALUE
------------------------------------ ----------- ----------------
resource_manager_plan string SYSTEM_PLAN
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
System altered.
If there are some pending transactions that need to be commited or rolled back, the previous command will hang and wait for the transaction to finish. To determine user sessions, you can query as follows:
SELECT S.SID,S.SERIAL#,S.MACHINE,S.TERMINAL,S.USERNAME
FROM V$SESSION S WHERE S.SID IN
(SELECT SID FROM V$LOCK WHERE TYPE='TX')
/ The result of the query will give you enough information to enable you to ask the user either to commit or roll back the transaction to end it. In the worst-case scenario, you can kill the session, which will automatically roll back the transaction. Once the system is QUIESCED, you can work without interference from the other user. After finishing your work, you can release the system with the following:
SQL> ALTER SYSTEM UNQUIESCE;
System altered.
Scenario 1:
Order of Transaction User Session DBA Session (1) Connected with SCOTT
SQL> update emp3 set
ename='John'
where ename='samir'; Connected with SYS (2) SQL> ALTER SYSTEM QUIESCE RESTRICTED;
Waiting USER SYS to finish active
Transaction initiated by USER SCOTT. (3) SQL> commit;
Commit complete.
(4) System altered. As the first scenario indicates, the DBA cannot QUIESCE the database until all of the running active transactions are completed, either by COMMIT or ROLLBACK. Once the system is QUIESCED, the database appears to other database users to be in the HALT or INACTIVE state. Then, when the database state is changed, all the block or suspended transactions will resume.
Scenario 2:
Order of Transaction User Session DBA Session (1) Connected with Scott User . Connected with SYS.
SQL> ALTER SYSTEM QUIESCE
RESTRICTED;
System altered.
(2) Select * from EMP;
wait for result
(3) SQL> ALTER SYSTEM UNQUIESCE;
System altered.
EMPNO ENAME SALARY
--------- ---------- ----------
1 Sasa 1000
2 John 5000
3 Hema 7000
User can see the results.
As scenario 2 indicates how it effects the users transactions. In short, by this feature the SYSTEM is temporary unavailable to the end user.
Common Questions
(Q)How do you as a DBA determine your database is in what state.
(A) You have to check the ACTIVE_STATE in the V$INSTANCE view to determine this.
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
ACTIVE_ST
---------
NORMAL
The possible value for ACTIVE_STATE is as follows:
Active_state Description Normal DATABASE is in normal state. QUIESCING DATABASE wants to QUIESCED but waiting for active running transactions to finish. Quiesced DATABASE is in QUIESCED STATE now. (Q) How do you determine what sessions are connected and waiting for UNQUIESCING the database?
(A) To determine this issue the following query:
SELECT SID,EVENT,TOTAL_WAITS,TIME_WAITED "TIME WAITED[100 OF SEC]",
AVERAGE_WAIT FROM V$SESSION_EVENT
WHERE EVENT='wait for possible QUIESCE finish'
/
SQL>
SID EVENT TOTAL_WAITS Time Waited[100 of Sec] AVERAGE_WAIT
--- ---------------------------------- ----------- ----------------------- ------------
6 wait for possible QUIESCE finish 412 126532 307 The Event, "wait for possible QUIESCE finish," indicates that the session is waiting to UNQUIESCE the database so that it can proceed with its transaction. Until then, the session will appear in a hung state.
(Q) What is the need for setting resource manager plan before QUIESING a database?
(A) Behind the scenes, when you QUIESE the database, the INTERNAL_QUIESCE resource plan is activated. This sets the ACTIVE_SESS_POOL_P1 parameter to 0 for all the groups except the SYS_GROUPS. Since SYS and SYSTEM both falls under the SYS_GROUPS, they can only connect to the database.
To view the details, query the DBA_RSRC_PLAN_DIRECTIVES database view.
Points to Remember
Only SYS and SYSTEM users are the valid users to do maintenance work in a QUIESCED database; other users having DBA privileges are treated as normal users.
BACKUP OF DATAFILES (COLD BACKUP, copy of datafiles) is not valid in a QUIESED database.
Database cannot be QUIESED if the transactions are still active.
Need to start up the database to set the resource plan. Conclusion
QUIESCING a database is a powerful feature that allows DBAs to have open windows without restarting the database.