Restricting Access to an Open Oracle Database

 

Introduction:

Restriction mode in Oracle Database is often used to restrict database use to only admin privileged user. Kill all the current session after placing the database mode to restricted.

Affect on current session after restricted mode:

• No user sessions are terminated
• No affect on any connected session
• Therefore, kill all current user session
• In RAC all session will go offline which is managed by Clusterware
• Any session connected to services at that instance are killed

 Steps to Enable Restricted Mode:

• Source database env file
• Login to sqlplus

          sql> sqlplus "/as sysdba"

• Check the current status of database

                    SQL> select NAME,log_mode, OPEN_MODE from v$database;

                   NAME      LOG_MODE     OPEN_MODE
                   --------- ------------ --------------------
                   TEST        ARCHIVELOG    READ WRITE

       

                   SQL> Select logins from v$instance;

                   LOGINS
                   ----------
                    ALLOWED

• Now enable the restricted mode in open database

                     SQL> alter system enable restricted sessions;

                       SQL> select logins from v$instance;

                           LOGINS

                           ----------
                          RESTRICTED

• Starting up database in Restricted mode

                        SQL> shut immediate;

                        SQL> startup restrict;

                      SQL> select logins from v$instance;

                      LOGINS

                      ----------
                     RESTRICTED

    Steps to kill existing sessions:

 

    - Check the session status    

      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#;

     - Kill session manually

         alter system kill session 'sid,serial#,inst_id' immediate;

     - Generate the kill command ( if you want to kill all session )

   

        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#;

Subscribe to blog.

Follow us on Social Networking Sites.

Whatsapp Chat- For Registration ping - https://wa.me/917607456651
Twitter - https://twitter.com/suggestcloud
Linkedin - https://www.linkedin.com/in/suggest-c...
Instagram- https://www.instagram.com/suggestcloud/
FB- https://www.facebook.com/suggestmecloud