ORA-65144: ALTER SYSTEM DISABLE RESTRICTED SESSION is not permitted

  

• Recently, one of our Pluggable database has been started with restricted mode.  

SQL> show pdbs     

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE YES

• When we tried to disable restricted mode it gave below error.
  
  

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

• The errors in the alert log indicated an underlying issue related to session privileges and synchronization.
  
  

PDB1(3):Database Characterset for PDB1 is AL32UTF8
2024-08-15T12:09:32.838297-05:00
Violations: Type: 1, Count: 1
Violations: Type: 2, Count: 2
PDB1(3):***************************************************************
PDB1(3):WARNING: Pluggable Database PDB1 with pdb id - 3 is
PDB1(3):         altered with errors or warnings. Please look into
PDB1(3):         PDB_PLUG_IN_VIOLATIONS view for more details.
PDB1(3):***************************************************************
2024-08-15T12:09:33.094704-05:00
PDB1(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
PDB1(3):Opening pdb with no Resource Manager plan active
2024-08-15T12:09:42.393575-05:00

• Queried the DB_PLUG_IN_VIOLATIONS to pinpoint the specific violation causing the restricted mode and found that the root cause was an accidental attempt to reset the password of a local PDB user from the Container Database (CDB).
  
  
  Common user are created in CDB$ROOT and can access all PDB. SYS and SYSTEM are default common users. Username has to start with c## or C## and can have ASCII or EDCDIC characters. Local user are local to a container and can be created only when connected to PDB. Since these are local to PDB, you can use same username in multiple PDB without compromising on security.

SQL> select name, cause, type, message, action from PDB_PLUG_IN_VIOLATIONS where status = 'PENDING' and con_id = 3; 
NAME                 CAUSE                          TYPE                 MESSAGE                                                                                     ACTION
-------------------- ------------------------------ -------------------- ------------------------------------------------------------------------------------------ ----------------------------------------
PDB1                OPTION                         WARNING              Database option APS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. Fix the database option in the PDB or the CDB 
PDB1                OPTION                         WARNING              Database option XOQ mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. Fix the database option in the PDB or the CDB 
PDB1                Sync Failure                   ERROR                Sync PDB failed with ORA-65177 during 'alter user apps identified by *' 

• Removed the problematic entry related to the “alert statement” from the PDB_SYNC$ table in both the CDB and the PDB.

SQL> select ctime,sqlstmt,name,flags,opcode,replay# from PDB_SYNC$ where sqlstmt like '%alter user apps identified by *%';
 
CTIME                SQLSTMT                            NAME                      FLAGS     OPCODE    REPLAY#
-------------------- -------------------------------- -------------------- ---------- ---------- ----------
09-jul-20 16:52:18   alter user apps identified by *   APPS                      0          5         71
25-sep-23 09:26:57   alter user apps identified by *   APPS                      0          5         85 

SQL> delete from PDB_SYNC$ where sqlstmt like ('alter user apps identified by *%');

2 rows deleted. 

SQL> select ctime,sqlstmt,name,flags,opcode,replay# from PDB_SYNC$ where sqlstmt like '%alter user apps identified by *%'; 

no rows selected

SQL> commit; 

Commit complete. 

SQL> alter session set container=PDB1; 

Session altered. 

SQL>  show pdbs  
   
CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                          READ WRITE YES
         
SQL> select ctime,sqlstmt,name,flags,opcode,replay# from PDB_SYNC$ where sqlstmt like '%alter user apps identified by *%';

CTIME                SQLSTMT                             NAME                      FLAGS     OPCODE    REPLAY#
-------------------- -------------------------------    -------------------- ---------- ---------- ----------
25-sep-23 09:26:57   alter user apps identified by *     APPS                      0          5         85 

SQL> delete from PDB_SYNC$ where sqlstmt like ('alter user apps identified by *%'); 

1 row deleted. 

SQL> commit; 

Commit complete.

• After addressing the synchronization discrepancies, the next step was to close and reopen the PDB.

SQL> alter pluggable database PDB1 close; 

Pluggable database altered. 

SQL> alter pluggable database PDB1 open instances=all; 

Pluggable database altered.

• Finally, PDB was out of restrict mode now.
  

SQL> show pdbs    
 
CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO