‘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 )
Or
3)
( To Enable )
alter system enable restricted session;
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
