When Oracle Multitenant was launched Roy and I amongst many other people always mentioned that the requirement of having all options in a Container Database (CDB$ROOT), and therefore also for the PDB$SEED with the immediate result that all PDBs provisioned from the PDB$SEED will have all options as well, will hinder customer adoption significantly. But this has been changed now. You can create Container Databases (CDB) with less options – it’s now supported in Oracle 12.1.0.2.
Create Container Databases (CDB) with less options – it’s now supported in Oracle 12.1.0.2
Almost all customers I have talked to in the past 3-4 years about Oracle Multitenant mentioned immediately that it will be a huge problem for them to install all options as (1) their policy is to install only things they are licensed for to (2) prevent developers, users and DBAs to use things accidentally without even knowing that this or that will require a license.
As it is not allowed to manipulate and change the PDB$SEED. Your best workaround – as PDBs were allowed to have less options – was simply to create a stand-alone Oracle 12c database with exactly the options you’d like to have. This would become your gold standard. Then you’d plug it in under a remarkable name, for instance PDB$MASTER. In addition you need to switch it to read only (recommended but not necessary anymore in Oracle 12.2 onwards). From now on you must make sure that you’ll provision a new PDB always as a clone from PDB$MASTER instead of PDB$SEED.
That would even work in the Single Tenant case. As long as you have only one PDB it is not required to licene the Oracle Multitenant option. But in this case you’d have the PDB$MASTER always unplugged. The provisioning would mean that you’ll have to plug it in as a new PDB. This sounds quite complicated.
Now this will become all obsolete as from now you it is allowed to have a CDB installation with less options. This applies to linked kernel modules (e.g. RAT) as well as to configured database components (e.g. JAVA, OWM, SPATIAL etc).
The important MOS Notes
Please see the following new/rephrased MOS Notes:
- MOS Note: 2001512.1
Creating A Container Database (CDB) With A Subset Of Options - MOS Note: 1616554.1
Customization of Database Options in a Multitenant Setup
MOS Note:2001512.1 basically explains the following steps:
- Do all the click-work in DBCA (Database Creation Assistant) to create a container database – but let DBCA only create the scripts
- Edit the <SID>.sql script and remove the unwanted options according to the dependency table in the MOS Note
- Edit the CreateDBCatalog.sql in case you want to remove OWM (Oracle Workspace Manager) creation as well
- Add the Oracle PERL $ORACLE_HOME/perl/bin in front of your $PATH variable
- Start the <SID>.sh script on the shell prompt
Here’s an example of a CreateDBCatalog.sql and a XXXX.sql creating a CDB with no options except XDB (which is mandatory in Oracle Database 12c):
cat CreateDBCatalog.sql SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/XXXX/scripts/CreateDBCatalog.log append alter session set "_oracle_script"=true; alter pluggable database pdb$seed close; alter pluggable database pdb$seed open; host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b catalog /u01/app/oracle/product/12.1.0.2/rdbms/admin/catalog.sql; host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b catproc /u01/app/oracle/product/12.1.0.2/rdbms/admin/catproc.sql; host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b catoctk /u01/app/oracle/product/12.1.0.2/rdbms/admin/catoctk.sql; -- host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b owminst /u01/app/oracle/product/12.1.0.2/rdbms/admin/owminst.plb; host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b pupbld -u SYSTEM/&&systemPassword /u01/app/oracle/product/12.1.0.2/sqlplus/admin/pupbld.sql; connect "SYSTEM"/"&&systemPassword" set echo on spool /u01/app/oracle/admin/XXXX/scripts/sqlPlusHelp.log append host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b hlpbld -u SYSTEM/&&systemPassword -a 1 /u01/app/oracle/product/12.1.0.2/sqlplus/admin/help/hlpbld.sql 1helpus.sql; spool off spool off
cat XXXX.sql
set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/12.1.0.2/bin/orapwd file=/u01/app/oracle/product/12.1.0.2/dbs/orapwXXXX force=y format=12
@/u01/app/oracle/admin/XXXX/scripts/CreateDB.sql
@/u01/app/oracle/admin/XXXX/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/XXXX/scripts/CreateDBCatalog.sql
-- @/u01/app/oracle/admin/XXXX/scripts/JServer.sql
-- @/u01/app/oracle/admin/XXXX/scripts/context.sql
-- @/u01/app/oracle/admin/XXXX/scripts/ordinst.sql
-- @/u01/app/oracle/admin/XXXX/scripts/interMedia.sql
-- @/u01/app/oracle/admin/XXXX/scripts/cwmlite.sql
-- @/u01/app/oracle/admin/XXXX/scripts/spatial.sql
-- @/u01/app/oracle/admin/XXXX/scripts/labelSecurity.sql
-- @/u01/app/oracle/admin/XXXX/scripts/apex.sql
-- @/u01/app/oracle/admin/XXXX/scripts/datavault.sql
-- @/u01/app/oracle/admin/XXXX/scripts/CreateClustDBViews.sql
@/u01/app/oracle/admin/XXXX/scripts/lockAccount.sql
@/u01/app/oracle/admin/XXXX/scripts/postDBCreation.sql
@/u01/app/oracle/admin/XXXX/scripts/PDBCreation.sql
@/u01/app/oracle/admin/XXXX/scripts/plug_PDB.sql
@/u01/app/oracle/admin/XXXX/scripts/postPDBCreation_PDB.sql
This results in a database having only these components – the minimal component set in Oracle 12.1.0.2:
COMP ID NAME -------- -------------------------------------- CATALOG Oracle Database Catalog View CATPROC Oracle Database Packages and Types XDB Oracle XML Database
How about Oracle Database 12.2 and newer?
Beginning with Oracle Database 12.2.0.1 you don’t have to do such stunts anymore. The DBCA by default allows you to customized your container database – but only if you choose ADVANCED and CUSTOM DATABASE during creation. In all other cases you’ll end up with a preconfigured database having all options.
Why can this be dangerous?
Please be a bit careful to avoid future issues. Once you are settled in the world of Oracle Multitenant you should make sure to have an identical set of options/components deployed. Otherwise you may end up in a situation where you try to pluigin a PDB with more options than the destination CDB.
In this case CHECK_PLUG_COMPATIBILITY will alert you and populate PDB_PLUG_IN_VIOLATIONS with a lot of warnings about component mismatches. In this case you will have to either:
- Remove options from your PDB before unplug
- Install the missing options into the destination CDB
- Create a new CDB with matching components and plug this PDB into it
- Use another technique (not unplug/plug), for instance Data Pump to migrate the contents to a newly created PDB
–Mike
Thanks for sharing !
Welcome 🙂
Mike
Great. You did it finally – this was overdue.
Hi,
Very nice summary! I’m just wondering, is it possible to do it while creating a non-CDB?
Thanks!
With non-CDBs you don’t face the issue as the DBCA does allow you to deselect options.
But you can use the script approach as well of course for non-CDBs.
Cheers
Mike
Hello Mike,
for a POC i am working finally with PDB’s 😉
one thing I noticed is that the PDB_PLUGIN_VIOLATIONS view displays the options missing when you plugin a noncdb
for example :
Database option SDO mismatch: OPTION PDB installed version NULL. CDB installed version 12.1.0.2.0 PENDING Fix the database option in the PDB or the CDB
I want to have it clean so decided to create the cdb exactly with the same options as the noncdb.
So from your blogpost above I understand that the messages in PDB_PLUGIN_VIOLATIONS is strictly informational and not an error ?
Hi Philippe,
the PDB_PLUG_IN_VIOLATRIONS contains a lot of useless information and does not get purged as far as I know. Bugs had been filed years ago but got ignored.
The only thing you can do is query for errors.
select message from pdb_plug_in_violations
where status<> ‘RESOLVED’ and TYPE=’ERROR’;
The issue I see:
Sometimes things which cause a road block are not tagged as error but as WARNING such as "you’ll have to run noncdb_to_pdb.sql".
Sorry for the inconvenience 🙁
Mike
Philippe,
to add:
There are ERRORS and WARNINGS – but there’s no clear rules. Everything needs to be really checked and verified – some things can be ignored (such as the SDO error in your case which is complete nonsense – why should you care that your PDB you’ll plug in has no SDO but the CDB your are gonna plug into has SDO – useless information).
Cheers
Mike
Hi Mike,
Is there a way to not install these options when using templates to create the cdb ? I did try to edit the template and do this however no success.
Thanks
Steve
Hi Steve,
"templates" in DBCA unfortunately don’t allow you this customization as far as I remember. Only the "create scripts" approach will work for CDBs. But for non-CDBs you should have the choice.
Hi Mike
Issue – Some components missing in pdb$seed and PDBs.
I have created the “Create Database” scripts for CDB using Multi-Tenant with the dbca in a 12.2.0.1 environment – using “Advanced configuration” – first of all with all options. I checked out “Create as Container Database” and “Create an empty Container database” – none PDB. All options has been created in my scripts using “Database components.
As mentioned in MyOracleSupport Note 2001512.1 “Creating A Container Database (CDB) With A Subset Of Options” – of course note is for 12.1 … therefore I comment out all unnecessary option – only JServer should by installed.
Therefore I changed the script Database.sql and run the complete create database scripts. CDB created successfully.
After than a PDB database was created manually. Some imports have done – But with errors using Java.
The pdb$seed and all other pdbs do not have any JServer installed. I checked out dba_registry – no Java in pdb$seed and PDBs
I compared your scripts, my older 12.1 scripts and the new 12.2 scripts and found the parameter -c ‘CDB$ROOT’ in my dbca scripts in some *.sql scripts. After removing this parameter in my JServer.sql and recreating the new database the JServer was installed correct in pdb$seed and within all my additional created PDBs.
I hope this helps
Cheers and many thanks for all my other problems solved on your blog
Peter Jensch
Hi Peter,
this is coming from the OPTIONS screen in DBCA. When you create the scripts with DBCA and don’t explicitly click on “PDB” in the options check box (e.g. check JAVAVM but don’t click it for the PDB) then your scripts will contain the extra option -c ‘CDB$RROT’ to indicate that this particular option won’t be present in PDBs. Side effect: it won’t be in the PDB$SEED as well. Meaning, every newly provisioned PDB won’t have it either. But you can easily install it – also into the PDB$SEED or in any of the PDBs separately.
Cheers
Mike
Hi Mike,
Thank you for sharing this post. Notably, your thoughts and explanation surrounding PDB_PLUG_IN_VIOLATRIONS.
Best regards,
jeff
Hi Mike,
I’m in the process of moving some 12.1 EE databases to SE2, and glad I found your post!
However I am getting and error I’m hoping you can help with.
I followed this process:
I started with a 12.1 EE non-cdb (fci3)
Upgraded to 12.1 EE non-cdb (fci3)
Created a new empty 12.2 EE CDB with same settings, char sets, etc. (fciEE)
and
Created a new empty 12.2 SE CDB with same settings, char sets, etc. (fciSE)
Created new PDB (fci3) into the 12.2 EE cdb (fciEE) using manifest file (nocopy tempfile reuse )
This works and I can select my user schema tables.
Then I shutdown the the PDB and unplug it (into manifest file).
Then, used DBMS_PDB.CHECK_PLUG_COMPATIBILITY on the SE2 db (fciSE) and its YES.
CREATE PLUGGABLE DATABASE FCI3 USING the manifest file .
The PDB will not open, and PDB_PLUG_IN_VIOLATIONS shows:
” PDB is Enterprise Edition (8), but CDB is not Enterprise Edition (4) ”
I verified again that all 3 db (the non-cdb 12.2 EE, the new EE CDB and the new SE CDB) all have same in dba_registry .
Can you please help with this error?
Thanks!!
Nathan
Nathan,
I was told that this should work flawless as long as your PDB has no options which don’t exist in SE2.
https://mikedietrichde.com/2017/07/18/switching-from-se-to-ee-with-oracle-multitenant/
Can you please send me your output of:
– COMP_ID, STATUS, CON_ID from CDB_REGISTRY
– PDB_PLUG_IN_VIOLATIONS
– PLUG CHECK COMPATIBILITY
– the manifest file
Thanks,
Mike
PS: Mail is: firstname.lastname@oracle.com
Hey Mike,
I got it it working!
It was my stupid mistake 🙂
In the fciEEcdb, i had unplugged the pdb, but forgot to drop it (still showed mounted in cdb_pdbs ).
Then, in then fciSEcdb, i just closed and re-opend the ee PDB and now i can select data.
Thanks again for your help! It was the gathering of the info you requested that lead me to see it.
Thanks a lot, Nathan!
And glad you made it working!
Cheers,
Mike