Restricted Mode
Restricted mode is a mode that only allow users who have RESTRICTED SESSION or SYSDBA system privilege to connect to the database. When the mode is enabled, the database notifies the listener to block any other users from accessing it.
Sometimes, you may need to enable the restricted mode for doing some maintenances. For example, changing DB character set or changing the name of a PDB.
Let's see how we enable restricted mode in the following scenarios.
Idle Database
For an idle database, we can start it up with RESTRICT option.
Single-instance DB
SQL> startup restrict
Oracle immediately enters the restricted mode right after the database opens.
RAC DB
To make all instances enter restricted mode, we should start the RAC database like this:
[oracle@primary01 ~]$ srvctl start database -d orclcdb -o restrict
[oracle@primary01 ~]$ srvctl status database -d orclcdb -v
Instance ORCLCDB1 is running on node primary01. Instance status: Restricted Access.
Instance ORCLCDB2 is running on node primary02. Instance status: Restricted Access.
That's the difference.
Running Database
For an opened database, you can follow the steps below to enable restricted mode.
Login as SYS
You can login as SYS either by providing its credential in at client side,
C:\Users\scott>sqlplus sys@orcl as sysdba
...
Enter password:
...
or by OS authentication at server side.
[oracle@test ~]$ sqlplus / as sysdba
...
Then we should check the LOGINS status.
SQL> select logins from v$instance;
LOGINS
----------
ALLOWED
Currently, users are allowed to access.
Enable RESTRICTED SESSION
With the following statement, we can limit users to access the database.
SQL> alter system enable restricted session;
System altered.
If the database is a RAC one, you should do it in all instances.
Let's check current status.
SQL> select logins from v$instance;
LOGINS
----------
RESTRICTED
Now users are limited to access. To connect a restricted database, users need a special system privilege.
To revert the RESTRICTED status, we can disable restricted mode.