Warning: PDB altered with errors and PDB_PLUG_IN_VIOLATIONS

If you open your pluggable database and you receive following warning,

SQL> alter pluggable database pdb2 open; Warning: PDB altered with errors.

It would mean that when sync operation of pluggable database was performed while opening it, there was some error and synchronization was not able to complete successfully. Pluggable database would be open, but it will be in restricted mode (as you will see in bellow example). In this case, we should immediately query PDB_PLUG_IN_VIOLATIONS to see what has gone wrong.

For example, when I opened my pluggable database PDB2 above, there was warning and I immediately queried PDB_PLUG_IN_VIOLATIONS and here is what I found in this view.

SQL> select message,time from pdb_plug_in_violations; MESSAGE --------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------- TIME                                                                        NAME --------------------------------------------------------------------------- ------------------------------ Sync PDB failed with ORA-959 during 'create user c##abc identified by * default tablespace test container = all' 02-JUL-15 04.05.43.679000 PM                                                PDB2

This actually means that I have a common user C##ABC created in my container database – which now also needed to be created in all of my pluggable databases. If a pluggable database was not present or not open when a common user was created, a sync operation would be performed when this pluggable database would be opened; and this sync operation would try to create this common user in this pluggable database. If this user could not be created because of some reason, pluggable database would open only in restricted mode as you can see below.

SQL> select name,open_mode,restricted from v$pdbs; NAME                           OPEN_MODE  RESSTRICTED ------------------------------ ---------- ------   -------------------- PDB$SEED                       READ ONLY       NO PDB2                                 READ WRITE     YES

Until this database is opened without restricted mode, CDB_* views (CDB_TABLESPACES, CDB_DATA_FILES etc.) would also not list any information related to this pluggable database.

In my case, C##ABC user was not being created because TEST tablespace was not created in PDB2 which needed to be there as default tablespace of user C##ABC. To resolve the issue, I created tablespace TEST in PDB2 and then opened it again so that C##ABC could be created during the sync operation.

Here you should note that dropping C##ABC would not work because previous sync operation (create C##ABC user) is still in the queue and drop C##ABC would be in sync operations queue after CREATE C##ABC. So only solution is to create TEST tablespace first and then open PDB2. 

SQL> alter session set container= pdb2; Session altered. SQL> create tablespace test datafile 'c:\oracle\oradata\pdb2\tes01.dbf' size 100m; Tablespace created. SQL>alter session set container=CDB$ROOT Session altered. SQL> alter pluggable database pdb2 close; Pluggable database altered. SQL> alter pluggable database pdb2 open; Pluggable database altered. SQL> select name,open_mode,restricted from v$pdbs; NAME                           OPEN_MODE  RESTRICTED ------------------------------ ---------- ---      ------------------- PDB$SEED                  READ ONLY     NO PDB2                           READ WRITE NO SQL>  select * from pdb_plug_in_violations; TIME                                                                        NAME                        CA --------------------------------------------------------------------------- ------------------------------ MESSAGE ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- STATUS    ACTION --------- -------------------------------------------------------------------------------- 02-JUL-15 04.24.39.174000 PM                                                PDB2                        Sy Sync PDB failed with ORA-959 during 'create user c##abc identified by * default tablespace test container RESOLVED