Skip to content
Home » Oracle » How Oracle Enable Restricted Mode

How Oracle Enable Restricted Mode

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.

  1. Idle Database
  2. Running Database

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.

Leave a Reply

Your email address will not be published. Required fields are marked *