Oracle 12c RESTRICTED mode, disabled functionality

Today I learned a lot when our production database went in restricted mode. We had huge issues with our Oracle 12c database that was patched unsuccessfully. After the patch did not finish the database always restarted in RESTRICTED MODE, which means only admin / sys can connect to the database. All of our application could not access the database, which resulted in an all around panic situation. The cause was the half finished patch. The database detected that a patch did not finish correctly and because of that it always started restricted.

The solution was to clear all data from the unsuccessful patch (I don’t know exactly where) and apply the patch again.

Info about restricted database

About it on Oracle page – https://docs.oracle.com/database/121/OSTMG/GUID-74FA4067-04D2-477B-A09C-40150BBCD702.html

Below are some warnings from Oracle page about this special mode. Active sessions are not disconnected when enabling restricted mode. It is a good idea to start the database directly in restricted mode.

To place an instance in restricted mode, where only users with administrative privileges can access it, use the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause. After placing an instance in restricted mode, you should consider killing all current user sessions before performing any administrative tasks.

To lift an instance from restricted mode, use ALTER SYSTEM with the DISABLE RESTRICTED SESSION clause.

Useful commands

You can check the status of the database with:

SELECT logins from v$instance; — If “Allowed” , than the database is normally accessible.

After that you can  enable or disable restricted session with:

alter system enable restricted session;
alter system disable restricted session;

Granting users permission to access the restricted database with:

grant restricted session to joshua;

Disabled functionality

Interesting thing is I could not find any info of what functionality is  disabled when database is in RESTRICTED MODE. I found on my own skin that the following thing do not work:

• Scheduler (only if explicitly enabled)
• APEX ( always asked for XDB username and password when accessing apex web page)
• DB Links (failing with an awesome error as shown below)  After normal start the DB link was still not functioning. I am not sure if restricted mode disables it.

Hope it helps someone.

Bye