Category: CDB/PDB

“Database option mismatch: PDB installed version NULL.” in PDB_PLUG_IN_VIOLATIONS

Since 12.2.0.1, CDB can have same or more options installed than PDB

There are warning messages in PDB_PLUG_IN_VIOLATIONS :

SQL>select NAME,CAUSE,MESSAGE,TYPE,CON_ID 
      from PDB_PLUG_IN_VIOLATIONS;

NAME         CAUSE  MESSAGE                                                                                              
----------  ------- ---------------------------------------------
                                                 TYPE      CON_ID
------------ ---------- -------------------- ------------------
RACTESTPDB   OPTION     Database option DV mismatch: PDB installed version NULL. CDB installed version 12.2.0.1.0.           WARNING    3 
RACTESTPDB   OPTION     Database option OLS mismatch: PDB installed version NULL. CDB installed version 12.2.0.1.0.          WARNING    3
RACTESTPDB   OPTION     Database option SDO mismatch: PDB installed version NULL. CDB installed version 12.2.0.1.0.          WARNING    3
RACTESTPDB   APEX       APEX mismatch: PDB installed version NULL CDB installed version 5.0.4.00.12                          WARNING    3

Check what options installed in CDB ( CDB$ROOT ):

SQL> show con_name

CON_NAME
--------------
CDB$ROOT

SQL> select COMP_NAME from dba_registry;

COMP_NAME
----------------------------------------
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
Oracle Real Application Clusters
Oracle XML Database
Oracle Workspace Manager
Oracle Text
Oracle Multimedia
Spatial                    <--- Not in PDB
Oracle Label Security      <--- Not in PDB
Oracle Application Express <--- Not in PDB
Oracle Database Vault      <--- Not in PDB

14 rows selected.

Check what options installed in PDB:

SQL> show con_name

CON_NAME
-----------------
RACTESTPDB

SQL> select COMP_NAME from dba_registry;

COMP_NAME
-------------------------------------
JServer JAVA Virtual Machine
Oracle Database Catalog Views
Oracle Database Java Packages
Oracle Database Packages and Types
Oracle Multimedia
Oracle Real Application Clusters
Oracle Text
Oracle Workspace Manager
Oracle XDK
Oracle XML Database

10 rows selected.

We can see CDB has four more options installed than PDB.

CONCLUSION

It is accepted that CDB not necessarily has same options installed as PDB. But CDB should have same or more options installed than PDB.

So in this situation, the warning messages in PDB_PLUG_IN_VIOLATIONS can be ignored.

Create a Container Database (CDB) With A Subset Of Options Using DBCA

Since 12.2.0.1, CDB and PDB can have different components. PDB can have a subset components of CDB.

For 12.1.0.2,  How to Create a Container Database (CDB) With Selected Options 

For 12.2.0.1, we don’t need create scripts from DBCA first, then manually modify scripts, and run the scripts sequentially to create a CDB/PDB database with required options in both CDB and PDB.

In 12.2.0.1, CDB and PDB can have different options installed. here are the steps to do so :

1)Launch DBCA -> Create a Database -> Advanced Configuration -> Custom Database -> ……->……-> Database Options:

Choose options for CDB and PDB respectively
DBCA: Choose options for CDB and PDB respectively

we can select all components for CDB$ROOT, and subset of options for PDB according to your licence bought from Oracle.

Please note below three components have been disabled as per Oracle Doc ID 948061.1.

Oracle Advanced Analytics
Oracle OLAP
Oracle Real Application Testing

2) Continue the rest steps, and create the database.

3) Check the options installed in both CDB and CDB.

a) in CDB$ROOT:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select COMP_NAME,STATUS from dba_registry order by 1;

COMP_NAME                                    STATUS
-------------------------------------------- -----------------
JServer JAVA Virtual Machine                  VALID
Oracle Application Express                    VALID
Oracle Database Catalog Views                 VALID
Oracle Database Java Packages                 VALID
Oracle Database Packages and Types            VALID
Oracle Database Vault                         VALID
Oracle Label Security                         VALID
Oracle Multimedia                             VALID
Oracle Real Application Clusters              VALID
Oracle Text                                   VALID
Oracle Workspace Manager                      VALID
Oracle XDK                                    VALID
Oracle XML Database                           VALID
Spatial                                       VALID

14 rows selected.

b) In PDB:

SQL> show con_name

CON_NAME
---------------
RACTESTPDB

SQL> select COMP_NAME,STATUS from dba_registry;

COMP_NAME                           STATUS
----------------------------------- ----------
Oracle Database Catalog Views       VALID
Oracle Database Packages and Types  VALID
JServer JAVA Virtual Machine        VALID
Oracle XDK                          VALID
Oracle Database Java Packages       VALID
Oracle Real Application Clusters    VALID
Oracle XML Database                 VALID
Oracle Workspace Manager            VALID
Oracle Text                         VALID
Oracle Multimedia                   VALID

10 rows selected.

PDB_PLUG_IN_VIOLATIONS

Always check PDB_PLUG_IN_VIOLATIONS for inconsistency between CDB and PDB, and run “datapatch” to resolve it

PDB_PLUG_IN_VIOLATIONS displays information about incompatibilities between a PDB and the CDB to which it belongs.

SQL> select name,cause,type,action 
       from pdb_plug_in_violations 
      where status <> 'RESOLVED';

NAME          CAUSE              TYPE
------------- ----------------   ---------
ACTION
-------------------------------------------
PDB$SEED       SQL Patch        ERROR
Call datapatch to install in the PDB or the CDB

PDB$SEED        SQL Patch       ERROR
Call datapatch to install in the PDB or the CDB

Run ‘datapatch’ again:

$./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Tue Sep 26 14:16:33 2017
Copyright (c) 2012, 2016, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_103685_2017_09_26_14_16_33/sqlpatch_invocation.log

Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
 that are in an open state, no patches will be applied to closed PDBs.
 Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
 (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 21555660 (Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)):
 Not installed in the binary or the SQL registry
Patch 26027162 (Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017)):
 Installed in the binary registry and CDB$ROOT RACTESTPDB PDB$SEED
Bundle series PSU:
 ID 170814 in the binary registry and ID 170814 in PDB CDB$ROOT, ID 170814 in PDB PDB$SEED, ID 170814 in PDB RACTESTPDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED RACTESTPDB
 Nothing to roll back
 Nothing to apply

SQL Patching tool complete on Tue Sep 26 14:18:39 2017

Check again without issues anymore.

SQL> select * from pdb_plug_in_violations where status <> 'RESOLVED';

no rows selected

Check what patches installed:

SQL>select target_build_description,PATCH_UID,PATCH_ID,ACTION,STATUS,ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch order by ACTION_TIME;

ORA-65066: The specified changes must apply to all containers

For changing common user password, it should be done in CDB root container ( CDB$ROOT ).

$ sqlplus sys@pdb1 as sysdba
SQL> alter user system identified by “PasswordNew”;
alter user system identified by “PasswordNew”
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers

SQL> show con_name

CON_NAME
————
PDB1

SQL> connect / as sysdba
Connected.
SQL> alter user system identified by “PasswordNew”;

User altered.

SQL> show con_name

CON_NAME
————
CDB$ROOT

Manage Temporary Tablespace in Container Database (CDB) and Pluggable Database (PDB)

Both CDB and PDB has its own dedicated TEMP tablespace.

In a 12cR1, both CDB and PDB have own dedicated TEMP tablespace by default

SQL> show con_id con_name

CON_ID
---------
1

CON_NAME
---------
CDB$ROOT

SQL> select CON_ID,NAME from v$tablespace where name='TEMP';

CON_ID      NAME
---------- -------------
 1         TEMP
 2         TEMP
 3         TEMP

Default Temporary Tablespace in PDB:

SQL> alter session set container=PDB1;

Session altered.

SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP OLTP_TEMP;

Tablespace altered.

SQL> ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE OLTP_TEMP;

Pluggable database altered.

Check the default Temporary Tablespace for PDB:

SQL> show con_id con_name

CON_ID
---------
3

CON_NAME
---------
PDB1

SQL> select * 
       from database_properties 
      where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME           PROPERTY_VALUE  DESCRIPTION
----------------------- --------------- --------------------------
DEFAULT_TEMP_TABLESPACE OLTP_TEMP       Name of default temporary 
                                        tablespace

Check the default Temporary Tablespace for CDB as below, we can see CDB is still using its default temporary tablespace TEMP.

SQL> show con_id con_name

CON_ID
------------------------------
1

CON_NAME
------------------------------
CDB$ROOT

SQL> select * 
       from database_properties 
      where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME             PROPERTY_VALUE  DESCRIPTION
------------------------- --------------- -------------------------
DEFAULT_TEMP_TABLESPACE   TEMP            Name of default temporary
                                          tablespace