ALTER SYSTEM QUIESCE RESTRICTED
Reader, November 08, 2003 - 7:22 pm UTC
Tom, Where ALTER SYSTEM QUIESCE RESTRICTED; command will be useful? Also, what is the difference from using alter system enable restricted session? I read the doc and still unable to understand the difference between the two. Thanks.
November 08, 2003 - 9:46 pm UTC
quiesce restricted lets you safely update code and ddl in the database without shutting down and kicking everyone out.
the restricted session only lets people with those privs log in. you would "kick out" people without that and just let dba's in.
ALTER SYSTEM QUIESCE RESTRICTED
Johnny, February 27, 2004 - 6:29 pm UTC
'alter system enable restricted session' does not "kick out" user automatically. It just blocks new user who does not have the 'restrict session' privilege to login, similar to 'ALTER SYSTEM QUIESCE RESTRICTED', which blocks new non-dba users. However, I can scarely see the instances when non-dba users have been granted to the 'restrict session' privilege.
There is a practical advantage to introduce the QUIESCE state, however. It allows all current transactions to complete, and then freezes the user's session. To use 'restrict session' to do the same job, DBA might need to have a procedure that is able to keep monitoring the status of the existing transactions and kill the session as soon as the transaction gets completed.
February 27, 2004 - 7:22 pm UTC
I said "you would KICK OUT", not that "it kicks out".
"the restricted session only lets people with those privs log in. you would
"kick out" people without that and just let dba's in. "
you just said the same thing I did.
restricted session and triggers
A reader, October 25, 2005 - 9:35 am UTC
Hi
I have been testing if restricted session can avoid trigger firing.
I create two users, scott and anna, scott has access to anna´s table and I create a before update trigger in scott schema but referencing anna´s table.
I logon as SYS and enabled restricted session, tried to logon as scott and it gives an error, so far so good.
I then logon as system and updated anna´s table and the trigger..... fired!
Is there anyway to avoid trigger firing other than disable it?
October 26, 2005 - 7:22 am UTC
why would you think restricted session would obviate triggers? all it does is prevent people that don't have restricted session privileges from connecting.
You disable triggers to prevent them firing.
Enabling INTERNAL_QUIESCE resource plan
Rajiv Aggarwal, January 13, 2007 - 5:20 pm UTC
To do quiesce a database until 10GR1, we need to have resource manager plan active since startup. I want to understand why is that. What if I just change the resource_manager_plan to INTERNAL_QUIESCE , what will I miss? I know I need to manually check what all transactions are pending commit/rollback and kill any active sqls before starting my DDL changes on a very active database.
It is very active database and we cannot afford to take bounce of the database to enable resource manager plan. So I wanted this as an alternative.
Appreciate your response.
Thanks
Enabling INTERNAL_QUIESCE resource plan
Rajiv Aggarwal, January 14, 2007 - 2:43 am UTC
To add on above question, I don't have resource manager plan set on my database (resource_manager_plan parameter is null). I want to know instead of bouncing the database with SYSTEM_PLAN and then quiesceing the db, just do change the plan from NULL to "INTERNAL_QUIESE".
Ricardinho, June 26, 2008 - 4:04 am UTC
Hi Tom
Despite quiesce is finished, I still see active transactions in the system from v$session
oracle 10.2.0.3.0
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
System altered.
SQL> select count(*) from v$session where username='ATM' and status='ACTIVE';
COUNT(*)
----------
437
SQL> select count(*) from v$session where username='ATM' and status='ACTIVE';
COUNT(*)
----------
439
SQL> select count(*) from v$session where username='ATM' and status='ACTIVE';
COUNT(*)
----------
440
June 26, 2008 - 4:08 pm UTC
that doesn't show active transactions.
ops$tkyte%ORA10GR2> select status, taddr, count(*) from v$session group by status, taddr;
STATUS TADDR COUNT(*)
-------- -------- ----------
ACTIVE 15
ops$tkyte%ORA10GR2> update t set user_id=user_id;
5 rows updated.
ops$tkyte%ORA10GR2> select status, taddr, count(*) from v$session group by status, taddr;
STATUS TADDR COUNT(*)
-------- -------- ----------
ACTIVE 3F2D6A98 1
ACTIVE 14
first one shows I have 15 active sessions.
second one shows I have 15 active sessions and ONE transaction
Ricardinho, June 27, 2008 - 3:55 am UTC
thanks tom but I am still confused.
1-) select count(*) from v$session where status='ACTIVE'
2-) select count(*) from v$session where taddr is not null
The first query shows total act¿ve sessions including transactions.
and
The second query show only active transactions.
Is this right?
June 27, 2008 - 9:17 am UTC
the first one shows active sessions. Active sessions do NOT have to have a transaction going.
if you issue "select count(*) from very_big_table", that will show you as active, but you haven't modified anything, so you have not created a transaction state object.
You can be active, but read only.
And further, you can be INACTIVE and have a transaction. If I issue "update t set x = 5" hit enter and go to lunch, I'll be inactive - but having a transaction.
look in v$transaction for transactions.
Alexander, June 27, 2008 - 10:49 am UTC
Tom,
I too sometimes struggle with identifying whether or not a connection is "doing anything" or not.
Just yesterday we had a connection from SYS by the job scheduler, it appeared as active, but we could find any evidence that this was true (no sql). It was blocking someone from creating an index.
The documentation states that the status column in v$session when active is
"ACTIVE - Session currently executing SQL"
But you said active doesn't have to have a transaction, but you can't execute sql without a transaction right?
June 27, 2008 - 11:30 am UTC
you do not need a transaction state when you haven't modified anything
If you are read only, only reading, you do not have a transaction object.
begin
loop
null;
end loop;
end;
/
run that, you'll be very active, but you obviously do not need to have a transaction.
You need a transaction object/state when you modify something or you "set transaction" or request a transaction id.
A reader, June 30, 2008 - 2:06 am UTC
Hi Tom,
Is there any diffrence between the two statements.
Do both show the amount of active transactions?
select count(*) from v$transaction
select count(*) from v$session where taddr is not null
July 06, 2008 - 6:35 pm UTC
a session can have more than one transaction....
ops$tkyte%ORA11GR1> create table t ( x int );
Table created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create or replace procedure p( p_n in number )
2 as
3 pragma autonomous_transaction;
4 l_vtrans number;
5 l_vsess number;
6 begin
7 insert into t values ( p_n );
8 if (p_n = 0)
9 then
10 select count(*) into l_vtrans from v$transaction;
11 select count(*) into l_vsess from v$session where taddr is not null;
12 dbms_output.put_line( 'vtrans = ' || l_vtrans || ' vsess = ' || l_vsess );
13 else
14 p(p_n-1);
15 end if;
16 commit;
17 end;
18 /
Procedure created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec p(5)
vtrans = 6 vsess = 1
PL/SQL procedure successfully completed.
does restricted session have something like set transaction read only
Kdev, September 10, 2008 - 3:39 am UTC
Hi Tom,
can we set some thing like this for a specific user and for all sessions initiate from this user;
set transaction read only; this need to be apply to the session once a new session initiated.
This is to avoid 2 way commit phase on a distributed transaction and a delay.
September 11, 2008 - 10:43 am UTC
no, it does not
quiesce, restricted, suspend
A reader, March 14, 2012 - 1:48 am UTC
Hi Tom,
A little bit confused about 'quiesce', 'restricted' and 'suspend'.
Could you please elaborate more on their difference and scenario of usage?
Beside, are below my understanding correct.
1. restricted: must be shutdown first and start up again by 'startup restrict'?
need special privilege to login
2. quiesce: can change to this mode at any time without downtime?
as long as login as DBA, no issue for this session?
3. suspend: will disable IO operations on datafile, redo log file, control file?
Basically what kind of activities(like backup, batch update, imp/exp) should be done under which of them?
March 14, 2012 - 6:50 am UTC
1) restricted limits who may log into the running database. You shutdown, startup in restricted mode - log in as a user who can, do something and then un-restrict the database
2) quiesce causes the database to allow any current transactions to complete, prevents new ones from starting - and when all transactions are complete - returns control to the quiescing session so it can do stuff. You then un-quiesce the database to resume operations
3) suspend immediately suspends IO calls - all of them. designed exclusively to work with 3rd party storage solutions like splitting mirrors and the like
None of them are for batch update, imp/exp. Suspend *might* be part of a backup if you use BCV's or the like.
restrict and quiesce are there to allow the dba to do something like a DDL change without competing for locks.
when Database went into restricted mode
Dhamu, February 12, 2015 - 11:54 am UTC
Hi Tom,
Please let me know, is there any way to find when the database was put into restricted mode.
Alert log have no information on this.
Thanks
Dhamu
Confused because Pluggable DB does not show RESTRICTED
Andrea Morris, August 19, 2021 - 11:41 pm UTC
I put the database in restricted session, but only the container of my single-tenant database (cdb$root) shows restricted when I query v$instance. When I am connected to a pluggable database (e.g. pdbappdb) I see logins as allowed. I did get an error showing that all instances were in restricted session when I tried to connect to the pluggable database. That's what I really care about---no access. But it confused me at first.
sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Aug 19 18:00:08 2021
Version 18.9.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.9.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select instance_name, host_name, logins from v$instance
2 /
INSTANCE_NAME
----------------
HOST_NAME LOGINS
---------------------------------------------------------------- ----------
MYDB
myhost.myuniversity.edu RESTRICTED
SQL> alter session set container=pdbappdb
2 /
Session altered.
SQL> select instance_name, host_name, logins from v$instance
2 /
INSTANCE_NAME
----------------
HOST_NAME LOGINS
---------------------------------------------------------------- ----------
MYDB
myhost.myuniversity.edu ALLOWED
August 23, 2021 - 4:46 am UTC
It might seem counterintuitive but we go this way to mimic the behaviour you see on a non-pluggable database.
On a non-pluggable database:
A) startup mount
=> logins = ALLOWED, but of course no-one can get into the db except sysdba
B) startup restrict
=> database is fully open, but logins = RESTRICTED
C) startup OR alter database open (from mount)
=> logins => ALLOWED
When you do "startup restrict" on the CDB, you'll see that the pluggables are only opened in MOUNT state, which is why the LOGINS = ALLOWED (just like A above).