Oracle database Enable / Disable Restricted session

 

‘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,

For  Non Rac 

1) 

 ( To  Enable ) 

STARTUP RESTRICT   

 ( To  Disable ) 

Shu immediate 

Startup 

Or 

2)   

 ( To  Enable ) 

 startup mount;

 ALTER SYSTEM ENABLE RESTRICTED SESSION;

Alter database Open ;

 ( To  Disable ) 

Shu immediate 

Startup 

Or  

3)  

 ( To  Enable ) 

alter system enable restricted session;

 ( To  Disable ) 

alter system disable restricted session;

For   Rac 

alter system enable restricted session command enables restricted mode only on the node on which it runs.

srvctl start database -d orclcdb -o restrict  /  srvctl start database -d orclcdb -startoption restrict

srvctl start instance -d orclcdb -i ORCLCDB  -o restrict     ( for instance ) 

For   Container Database 

SQL> alter session set container = pdb1;

 

Session altered.

 

SQL> alter system enable restricted session;

 

System altered.

 

SQL> select logins from v$instance;

 

LOGINS

----------

RESTRICTED

 

SQL> alter system disable restricted session;

alter system disable restricted session

*

ERROR at line 1:

ORA-65144: ALTER SYSTEM DISABLE RESTRICTED SESSION is not permitted

You can get out of the predicament, by force opening the pluggable database as shown below, but probably best to look at the latest 12c PSU, which contains a fix (unverified)

 

SQL> conn / as sysdba

Connected.

 

SQL> alter pluggable database pdb1 open force;

 

Pluggable database altered.

 

SQL> alter session set container = pdb1;

 

Session altered.

 

SQL> select logins from v$instance;

 

LOGINS

----------

ALLOWED

QUIESCE RESTRICTED  and UNQUIESCE

ALTER SYSTEM QUIESCE RESTRICTED;

Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active. For example, If a user issues a SQL query in an attempt to force an inactive session to become active, the query will appear to be hung. When the database is later unquiesced, the session is resumed, and the blocked action is processed.

The following statement restores the database to normal operation:

ALTER SYSTEM UNQUIESCE;

All non-DBA activity is allowed to proceed. In an Oracle Real Application Clusters environment, this statement is not required to be issued from the same session, or even the same instance, as that which quiesced the database. If the session issuing the ALTER SYSTEM UNQUIESCE statement terminates abnormally, then the Oracle Database server ensures that the unquiesce operation completes.

The ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance. The column values has one of these values:

NORMAL: Normal unquiesced state.

QUIESCING: Being quiesced, but some non-DBA sessions are still active.

QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

Views to  check  :

 

set linesize 200

col HOST_NAME for a30

 col STATUS for a20

 select instance_name, host_name, archiver, thread#, decode(STATUS,'STARTED','STARTUP***NOMOUNT',status) status,LOGINS, STARTUP_TIME from gv$instance ;

set head off verify off echo off pages 150 linesize 120 feedback off

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

select distinct

'DATABASE_NAME..............................................: '|| NAME             ,

'INSTANCE NAME..............................................: '|| INSTANCE_NAME    ,

'LOG_MODE...................................................: '|| LOG_MODE         ,

'OPEN MODE..................................................: '|| OPEN_MODE        ,

'INSTANCE_NAME..............................................: '|| INSTANCE_NAME    ,

'HOSTNAME...................................................: '|| HOST_NAME        ,

'STATUS.....................................................: '|| STATUS           ,

'LOGINS.....................................................: '|| LOGINS           ,

'STARTUP-TIME...............................................: '|| STARTUP_TIME     ,

'DATAGUARD BROKER...........................................: '|| DATAGUARD_BROKER ,

'GUARD STATUS...............................................: '|| GUARD_STATUS     ,

'FLASHBACK ON...............................................: '|| FLASHBACK_ON     ,

'PROTECTION MODE............................................: '|| PROTECTION_MODE  ,

'CONTROLFILE TYPE...........................................: '|| CONTROLFILE_TYPE

from gv$instance i, v$database d

-- where 1=1  

-- and i.inst_id=d.inst_id

order by 2;

set head on verify on echo on feedback on