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

[oracle@oracle11g ~]$ sqlplus hr/hr@//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

Tags: multitenantpdb