Author: Sameer Wadhwa
Quiescing a database is a powerful new feature that allows DBA to complete some of the operations that are restricted in a restricted mode. Use this feature that after logging in with a SYS or SYSTEM account, DBA can perform queries, PL / SQL, and other transactions. All other users' sessions will be suspended, once the DBA returns the database back to normal mode, the user's session will automatically run.
Figure 1A: The database is in a normal state.
Figure 1B: The database is in the state.
Figure 1A is a system state in which the database is in a normal mode, in which the DBA and the user's transaction is running. Some DBA transactions are limited because the database must be running these transactions when the database must be in a limited pattern. In contrast, Figure 1b is a case where the database is on the parking state. In the figure, all users' transactions are blocked, without restarting the database to the restricted mode, and the DBA transaction has no problem. .
Once all active sessions are implemented, the database will be paused.
Let's take a look at how it is going. The main commands used by the pause database are Alter System Quiesce Restricted; I will first use SQLPLUS to log in 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 sysdbaconnected.
SQL> ALTER SYSTEM QUIESCE RESTRICTED; ALTER System Quiesce Restricted * Error At Line 1: ORA-25507: Resource Manager Has NOT BEEN Continuously On
If the above error indicates that the resource manager is not active, you can do it like this:
SQL> ALTER system set resource_manager_plan = 'system_plan' scope = SPFILE SID = 'or9i';
SYSTEM altered.
OR9i is my SID.
After this, you have to restart the database.
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 unresolved transactions that need to be submitted or rollback, the previous command will hang and wait for the completion of the transaction. If you want to determine which users are not submitted or rolled, you can use the following statement.
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') / query results Plenty of information will provide you can ask those users to submit, roll back or terminate their transactions. What is worse is that you can kill these sessions, and the session will be automatically rolled back. After the system is in the park, you can work without the interference from other users. After you work, you can use the following command to demonstrate the status:
SQL> ALTER SYSTEM UNQUIESCE;
SYSTEM altered.
Scenario 1:
Transaction order
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 for user Scott to complete the transaction.
(3)
SQL> commit;
COMMIT COMPLETE.
(4)
SYSTEM altered.
The first scenario shows that the DBA cannot stop the database before all activities. Once the database is stopped, the library presented the other user is a state of stopping (HALT) or inactive. Then when the database changes to the normal state, all data blocks and paused transactions continue to run.
Scenario 2:
Transaction order
User session
DBA session
(1)
Connected with Scott User.
Connected with sys.
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
SYSTEM altered.
(2)
SELECT *.
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.
Scenario 2 indicates how it affects the user's session. In short, the system is temporarily invalid for the end user.
Some of the usual issues:
(Q) How do you check your database as DBA?
(A) You can check Active_State in the V $ Instance view on the field.
SQL> SELECT ACTIVE_STATE FROM V $ INSTANCE;
Active_st
---------
Normal
Active_State has the following possible values:
Active_State
Description Normal
The database is in normal state
Quiescing
Database Wants to Quiesced But Waiting for Active Running Transactions To Finish.
The database should be paused, but the running transaction to wait for the event is complete.
Quiesced
The database is on a pause.
(Q) How to determine which session connecting the library is waiting for a database?
(A) You can use the following queries to determine:
SELECT SID, Event, Total_Waits, Time_WaIcted "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
"Wait for Possible Quiesce Finish" This event indicates that the session is waiting for the "positive pause" database so that it cannot perform it. These sessions will present the state of Hung.
(Q) What settings for the resource manager plan (Resource Manager PLAN) before the database is paused?
(A) When you pause the database, the internal_quiesce resource plan is activated. Active_SESS_POOL_P1 in all of SYS_GROUPS should be set to 0. Because SYS and System users belong to the SYS_GROUPS group, only they can be connected to the database.
To view the details of the details, you can query DBA_RSRC_PLAN_DIRECTIVES.
Remember the following:
Databases in a pause state only Sys and System are valid users to perform maintenance; other users with DBA privileges are also considered a general user.
Back up the data file in the pause database (the copy data file) is invalid.
If there is a "activity" transaction in the library, the library cannot be paused. .
You need to start the database to set the resource plan pause data is a new feature of 9i, so it is not available in the previous version.
in conclusion:
A pause database is a powerful feature that makes DBAs to perform some special maintenance work without having to restart the database.
