Recently, one of our Pluggable database has been started with restricted mode.
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
Comments
Post a Comment