As a DBA sometime you have to take database in restrict mode. Today's article will discuss the details about Database Restrict Mode. Stay Tune!!!
Users that are already connected to the database, prior to placing the database in restricted session, will not get disconnected. These sessions need to be manually killed after.
Why would you put a database in restricted session?
During certain maintenance task, you might want to restrict access to the database.
You want to be able to connect to the database to perform your tasks, but you do not want any other users mocking around, changing the data. Such maintenance tasks could be upgrading a database with an export/import process, fixing replicated data, deleting specific data, reorganizing tables, taking a consistent export. In these cases the restricted session is not a must, you could still proceed with the database open read write, but you might encounter some problems down the road.
Prior to starting up the database in restricted mode, you need to shutdown the running database first.
This script can be used any other time to generate the kill commands.
1. What Is Restricted Mode
When a database is placed in restricted mode, only users that have RESTRICTED SESSION privilege granted to them can connect to the database. Users that lack this privilege will get an error.Users that are already connected to the database, prior to placing the database in restricted session, will not get disconnected. These sessions need to be manually killed after.
Why would you put a database in restricted session?
During certain maintenance task, you might want to restrict access to the database.
You want to be able to connect to the database to perform your tasks, but you do not want any other users mocking around, changing the data. Such maintenance tasks could be upgrading a database with an export/import process, fixing replicated data, deleting specific data, reorganizing tables, taking a consistent export. In these cases the restricted session is not a must, you could still proceed with the database open read write, but you might encounter some problems down the road.
2. Starting Up Database In Restricted Mode
You can start up a database in restricted mode.Prior to starting up the database in restricted mode, you need to shutdown the running database first.
SQL> shutdown immediate; SQL> startup restrict; SQL> select logins from v$instance; LOGINS ---------- RESTRICTED
The good thing about this method is, that all users connected to the database will get disconnected when you shutdown the database. These users will not be able to connect unless they have the RESTRICTED SESSION privilege.
3. Placing Open Database In Restricted Mode
If you do not want to shutdown the database, there is an ALTER SYSTEM command you can use to place the database in restricted mode.SQL> alter system enable restricted session;The downside to this method is that users already connected to the database will not get disconnected. You need to manually kill these sessions.
4. Script To Kill Existing Sessions
In order to help you kill the sessions that are sticking around after the database has been placed in restricted mode you can use the following script.This script can be used any other time to generate the kill commands.
--check what sessions there are in the database
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.sql_id
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND s.username not in ('SYS','SYSTEM')
ORDER BY s.inst_id,s.sid,s.serial#;
INST_ID SID SERIAL# SPID USERNAME PROGRAM SQL_ID
------- ----- -------- ------- --------- ------------------ -------------
1 4 33271 31370 HRAPP JDBC Thin Client
1 5 369 25228 HRAPP JDBC Thin Client 318fujwu02mz1
1 43 59 25232 SYSADM JDBC Thin Client ar3mfzt410h9y
1 46 415 25513 HRQUERY JDBC Thin Client
-- generate the kill command.
-- if you decide to kill the sessions, then run the generated commands.
select 'alter system kill session ' || '''' || s.sid|| ',' || s.serial# || ',@' || s.inst_id || ''';' as "Kill_Session_Command"
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND s.username not in ('SYS','SYSTEM')
ORDER BY s.inst_id,s.sid,s.serial#;
Kill_Session_Command
----------------------------------------
alter system kill session '4,33271,@1';
alter system kill session '5,369,@1';
alter system kill session '43,59,@1';
alter system kill session '46,415,@1';
