Latest Updates

Post Top Ad

26 March, 2020

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



No comments:

Post Top Ad

Your Ad Spot

Pages