How to Manage Restricted Mode in Oracle Database

Restricted mode in Oracle Database allows only users with the RESTRICTED SESSION privilege to connect, making it useful for maintenance tasks. Below are the steps to check, enable, and disable restricted mode in both single-instance and RAC environments.

Checking If the Database Is in Restricted Mode

To determine if the database is in restricted mode, query the LOGINS column of the v$instance view:

SELECT logins FROM v$instance;

Possible Outputs:

• ALLOWED: Database is not in restricted mode.
• RESTRICTED: Database is in restricted mode.

Example:

LOGINS
----------
ALLOWED

LOGINS
----------
RESTRICTED

Enabling Restricted Mode

Temporarily Restrict New Connections

To enable restricted mode while the database is running:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

Disabling Restricted Mode

To allow all users to connect again:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

Starting the Database in Restricted Mode

To start the database in restricted mode:

STARTUP RESTRICT;

In a RAC Environment

In Real Application Clusters (RAC), use the following command to start the database in restricted mode:

srvctl start database -d <database_name> -o restrict

For example:

srvctl start database -d orcl -o restrict

Verifying Restricted Mode in RAC

To confirm that the RAC database is running in restricted mode:

srvctl status database -d <database_name> -v

Example output:

Instance ORCL is running on node RAC01. Instance status: Restricted Access.
Instance ORCL is running on node RAC02. Instance status: Restricted Access.

By following these steps, you can effectively manage restricted mode in both single-instance and RAC Oracle databases.