12.1.0.2 PDB fails to come out of restricted mode

This one is a nasty bug 🙂 I was trying to setup Oracle PDB in a test environment for the first time and got stuck with ORA-01035 error

[[email protected] ~]$ sqlplus hr/[email protected]//oracle11g:1522/engg

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 3 07:34:58 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Checking the status of this pluggable database, I could see that DB was open but it was in restricted mode

SQL>  select con_id,logins,status from v$instance;

    CON_ID LOGINS     STATUS
---------- ---------- ------------
         0 RESTRICTED OPEN

SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 ENGG                           READ WRITE

--Had got following error while opening/creating PDB

SQL> alter pluggable database engg open;

Warning: PDB altered with errors.

I tried creating PDB manually / DBCA multiple times but all PDB remained in restricted state. Alert log also didn't report any error or explanation for this errors. Tried searching on internet but didn't find any relevant hit. Finally MOS pointed out (Bug 19174942) that this could happen in 12.1.0.2 ,if a common user has default tablespace which is not present in PDB. I knew this had to be my issue as I had created a common user with default tablespace users but PDB's didn't had this tablespace. Fix was to create this tablespace in all the PDB's and restart the PDB

SQL> alter session set container=engg;

Session altered.

SQL> create tablespace users datafile '/oracle/oradata1/orcl12c/pdbseed/user_01.dbf' size 100m ;

Tablespace created.

SQL> alter pluggable database engg close;

Pluggable database altered.

SQL> alter pluggable database engg open;

Pluggable database altered.

SQL>  select instance_name,status,logins from v$instance;

INSTANCE_NAME    STATUS       LOGINS
---------------- ------------ ----------
orcl12c          OPEN         ALLOWED

 

2 thoughts on “12.1.0.2 PDB fails to come out of restricted mode

  1. Another scenario that gives this behavior is a patch update not being installed on the PDB (If datapatch was run when this PDB wasn’t open for example). You can see if this is the case by running this from the root container “select * from PDB_PLUG_IN_VIOLATIONS;” As per the SR I raised…

    1) Invoke datapatch:
    % cd $ORACLE_HOME/OPatch
    % datapatch

    2) Close and reopen the affected PDB
    Connect as SYS
    alter pluggable database close instances =all;
    alter pluggable database open read write instances =all;

Leave a Reply