Hi,
Some time ago, after creating a pluggable database from seed, it was simply not open, check it out:
SQL> alter pluggable database mypdb open; Warning: PDB altered with errors.
When checking for the pdb status on v$pdb, pdb, I found it was in restricted mode. Checking the alert.log, not a single error to help to solving the issue, as you can see:
CREATE PLUGGABLE DATABASE mypdb ADMIN USER PDBADMIN identified by * Fri Feb 02 16:06:05 2018 APEX_040200.WWV_FLOW_TEMPLATES (JAVASCRIPT_CODE_ONLOAD) - CLOB populated Fri Feb 02 16:06:37 2018 **************************************************************** Pluggable Database mypdb with pdb id - 7 is created as UNUSABLE. If any errors are encountered before the pdb is marked as NEW, then the pdb must be dropped **************************************************************** This instance was first to open pluggable database mypdb (container=7) Database Characterset for mypdb is WE8MSWIN1252 Deleting old file#2 from file$ Deleting old file#4 from file$ Adding new file#108 to file$(old file#2) Adding new file#109 to file$(old file#4) Successfully created internal service mypdb at open ALTER SYSTEM: Flushing buffer cache inst=1 container=7 local **************************************************************** Post plug operations are now complete. Pluggable database mypdb with pdb id - 7 is now marked as NEW. **************************************************************** Completed: CREATE PLUGGABLE DATABASE mypdb ADMIN USER PDBADMIN identified by * alter pluggable database mypdb open Fri Feb 02 16:06:52 2018 This instance was first to open pluggable database mypdb (container=7) Pluggable database mypdb dictionary check beginning Pluggable Database mypdb Dictionary check complete Database Characterset for mypdb is WE8MSWIN1252 Fri Feb 02 16:07:07 2018 Opening pdb mypdb (7) with no Resource Manager plan active Fri Feb 02 16:07:08 2018 Logminer Bld: Build started Resize operation completed for file# 108, old size 296960K, new size 307200K Resize operation completed for file# 108, old size 307200K, new size 317440K Fri Feb 02 16:07:15 2018 Logminer Bld: Done Pluggable database mypdb opened read write Completed: alter pluggable database mypdb open
In the end of the day, discovered this is a match to MOS Bug 19174942 – After upgrade from 12.1.0.2 new cloned PDB opens with warnings in restricted mode (Doc ID 19174942.8).
To fix the problem you need create the missing default tablespaces for each common user (you can see that from DBA_USERS view in ROOT). In my case, it was the tablespace USERS (didn’t existing by looking up on dba_data_files view on the pdb). Seems it was not created correctly during create of the pluggable. So, to fix it:
create tablespace users datafile '+DGDATA/MYCDB/DATAFILE/user_01.dbf' size 100m;
Then you just have to restart your PDB and it will open without problems:
SYS@MYCDB>alter session set container=CDB$ROOT; SQL> alter pluggable database mypdb close; Pluggable database altered. SQL> alter pluggable database mypdb open; Pluggable database altered.
See you in the next post!