Database option SDO mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL

Issue/Problem:

Pluggable database opens in Restricted Session Mode after being created from an XML file/image.

User logins receive following Error

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

Cause:

Pluggable database is created/Re-build from existing XML file (Which might have been used in past without an issue). Creation of PDB ran without an issue except at the end and in Alert log file, there is a message recorded as “Completed with errors”.

To see the exact cause for this error(s), we can check the Dictionary View “pdb_plug_in_violations”.

select

            status, message, action

     from  

            pdb_plug_in_violations

     where

            status !=’RESOLVED’;

In our case, the error was

Database option SDO mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.

The issue was Oracle Spatial (SDO). This particular database feature was removed from Root Container (CDB) and PDB being created from the XML file was still having Oracle Spatial feature enabled.

In Oracle Container Database Architecture, All the PDB’s have to be Subset of what CDB is which means, PDB’s can only have those database features enabled, which are enabled in Root container.

So in this case, Oracle Spatial was removed sometime back from CDB but PDB’s source XML files still have that feature.

So, you are still able to create the PDB but when you open the PDB for Read Write, Oracle Database Server automatically puts this instance of PDB in “Restricted Session” Mode.

Solution:

1. Recreate PDB Image (XML File) from the source after removing Spatial Feature in the source.
2. (If Solution 1 is not feasible for you) – Open the new PDB in Read Write Restricted Mode (By default it will open in this because of error). Remove the Spatial Feature from PDB using below steps.
   1. Connect to the PDB Database which in this case is “MS_PDB_ODL08’.

   2. Execute the script “$ORACLE_HOME/md/admin/mddins.sql”

   3. Close the PDB : Alter pluggable database MS_PDB_ODL8 close;
   4. Open the PDB : Alter Pluggable database MS_PDB_ODL08 open;
3. Check the status of PDB