Be aware of database options selections for PDBs in DBCA

I did blog about several things-to-know with DBCA (Database Configuration Assistant) in the past. And one issue came up in our internal mailing list the other day. Be aware of database options selections for PDBs in DBCA. Missing one or the other “click” may bring you in trouble later on. I blogged about this already a while ago but this was only part of a larger post: Create Multitenant Databases with DBCA – Things to Know.

Be aware of database options selections for PDBs in DBCA

When you create a custom container database with DBCA you will see this …

Continue reading...

Different block sizes when you plugin?

Interesting case Roy came across a week ago with a customer in the US. The customer received a plugin violation because of different DB_BLOCK_SIZE between the source and the destination database. This happened regardless of unplug/plug or plugging in a non-CDB into a CDB. Hence the question came up: Can you have different block sizes when you plugin?

It should be possible – but you may need a tiny little workaround to make it happen.

The documentation as far as I can see was not helpful. MOS Note: 1935365.1 (Multitenant Unplug/Plug Best Practices) wasn’t very helpful either.

Different block sizes

Continue reading...

Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c

Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18cPDB_PLUG_IN_VIOLATIONS is a strange view in the Oracle database universe. It does not follow Oracle naming conventions – and it has too many underscores in its name. Furthermore I get always annoyed by the fact that it doesn’t get purged automatically. In addition it accumulates a massive number of useless information over time. But in Oracle Database 18c there’s a least some relief: A new call gets introduced which allows purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c.


Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c

As others have written a lot already about PDB_PLUG_IN_VIOLATIONS I won’t repeat it but rather share the links with you:

Continue reading...

Unplug a PDB and plugin into 18c in the Cloud

Unplug a PDB and plugin into 18c in the CloudThere’s been silence for a while – but today I’d like to show you how to unplug a PDB and plugin into 18c in the Cloud. I’m doing this exercise because I’ve seen questions already. And I was curious if it works as I’d expect it.

Unplug a PDB and plugin into 18c in the Cloud

As usual I will use our hands-on lab environment – and a freshly deployed 18c OCI-Classic instance (formerly known as DBCS formerly known as DBaaS).

Unplug a PDB and plugin into 18c inthe Cloud

It is a Single Tenant environment with one PDB. If you need for information about how to …

Continue reading...

How to rename a Pluggable Database

Have you ever asked yourself how to rename a pluggable database? I did. And today a colleague from ACS Support told me how easy it is.

Of course I had to try it out. And it seems to work quite nice in Oracle Database In the previous release I found some nits documented in MOS.

How to rename a Pluggable Database

There’s no obvious command to rename a pluggable database such as “alter pluggable database PDB1 rename to PDB2”. But it’s not complicated if you know the right command.

I’m creating a fresh pluggable database:

create pluggable database PDB1 
Continue reading...

Cloning a PDB from Oracle 12.1 to Oracle 12.2

Is cloning a PDB from Oracle 12.1 to Oracle 12.2 possible?

A colleague raised this question recently. And a customer did ask me the same question a few days before at a user group conference. There are several ways to move a PDB from Oracle 12.1. to 12.2. But the documentation does not say explicitly if you can do a cloning operation between Oracle 12.1 and 12.2.

Cloning a PDB from Oracle 12.1 to Oracle 12.2

You can clone a non-CDB and make it a PDB but you need to run noncdb_to_pdb.sql afterwards. When I wrote this blog post, Oracle …

Continue reading...

Can you select a PDB’s character set?

Can you select a PDB's character set?Since Oracle Database a Multitenant container database can host PDBs with different character sets. This was a huge restriction in Oracle Database 12.1., at least outside the US and Canada. Here in Germany alone you may find plenty of different database character sets, for instance WE8ISO8859P1, WE8ISO8859P9, WE8ISO8859P15, WE8MSWIN1252, maybe still some old WE8DEC – and of course the unicode ones UTF8 and AL32UTF8. But the question is: Can you select a PDB’s character set when you provision a new PDB?

Mixing PDB character sets in Oracle Database 12.2.

One of the limiting restrictions in Oracle 12.1 Multitenant …

Continue reading...

Create Multitenant databases with DBCA – Things to Know

Create Multitenant databases with DBCA - Things to KnowWhen you create Multitenant databases with DBCA there are some Things to Know. And in Oracle Database’s Database Creation Assistant (DBCA) there is a common pitfall in the “Options” screen. It was grayed out in the previous release but when you choose to create a “custom” database you can select the options/components now. Be careful …

Previous posts

I did blog several times about the DBCA already:

And …

Continue reading...

Lost AWR snapshots reappear again in Oracle 12.2 in Multitenant

Lost AWR snapshots reappear again in Oracle 12.2 in MultitenantWhat a strange topic. And I thought I never will blog about this. But as I have received the same question for the third time today, I think there’s a bit clarification needed. Lost AWR snapshots reappear in Oracle 12.2 in Multitenant pluggable databases.

Magically …

Lost AWR snapshots reappear again in Oracle 12.2 in Multitenant

In a non-CDB database you have your local AWR data stored in the SYSAUX tablespace. Whenever you create an AWR snapshot:

exec dbms_workload_repository.create_snapshot;

You can query and of course evaluate it afterwards:

select con_id, snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') BEGIN
from CDB_HIST_SNAPSHOT order by 
Continue reading...

Fallback Strategy: Flashback Pluggable Database

Fallback Strategy: Flashback Pluggable Database in Oracle 12.2Since Oracle Database 12.2 an important feature works now for pluggable databases as a Fallback Strategy: Flashback Pluggable Database in Oracle 12.2. This was missing in Oracle Database 12.1.

Actually I missed this feature a lot as I use “Flashback” almost everyday. And you could only rewind the entire deployment with a FLASHBACK DATABASE command.

Fallback Strategy: Flashback Pluggable Database

Fallback Strategy: Flashback Pluggable Database in Oracle 12.2Actually having the possibility to flashback a pluggable database without effecting the other PDBs is really important. Did you ever want to try out what happens if you TRUNCATE TABLE SYS.PROPS$? Give it a try – but set a

Continue reading...

ORA-44787 – Don’t mess with the Default Oracle Service

ORA-44787 - Don't mess with the Default Oracle ServiceAt the moment I work with one of our reference customers, Swiss Mobiliar Insurance, on their Multitenant upgrade to Oracle Database And we encountered an “interesting” issue. After upgrade we received an ORA-44787 making it impossible to switch between containers with “alter session set container=pdb1;” commands. Lesson learned now: If you don’t want ORA-44787 – Don’t mess with the Default Oracle Service.

What has happened?

Very simple case. We approach a database upgrade with a Multitenant deployment of over 90 PDBs. Source database version is Oracle Database, destination version is Oracle Database …

Continue reading...

Why EXCLUDE_SEED_CDB_VIEW is now an underscore in Oracle 12.2

Hiding information is not good - and exclude_seed_cdb_view is now an underscore in Oracle 12.2I have received several questions from customers in the past weeks using Oracle Multitenant about the parameter exclude_seed_cdb_view.It magically disappeared and morphed into _exclude_seed_cdb_view since Oracle Database To be fair, the exclude_seed_cdb_view is listed in the list of obsolete parameters in Oracle Database


This parameter hides everything belonging to the PDB$SEED from the usual queries. For instance you ran a query against CDB_DATA_FILES but you won’t see the data files belonging to PDB$SEED in the result set. Whether this is good or bad, at least RMAN (Recovery Manager) worked always correctly.

But I’m …

Continue reading...

Can you simply switch from SE2 to EE with Oracle Multitenant

Oracle MultitenantThis question is coming up from time to time – and currently not covered in the official Oracle documentation as far as I know: “Can you simply switch from SE2 to EE with Oracle Multitenant?”

Yes, you can!

First of all, the switch is possible and tested. But you’ll have to satisfy a few requirements:

  • Both database versions must be the same
  • Both databases must have the same character set (in Oracle Database 12.1)

That’s it. Simply unplug your Standard Edition 2 PDB and plug it into your Enterprise Edition CDB.

In addition you may have to run several component …

Continue reading...

Why does the upgrade of PDB$SEED always take longer?

Upgrading the PDB$SEED takes always longer than any other container.
What is the reason for an upgrade of the PDB$SEED taking so much longer in a real world environment?

How does an upgrade with Oracle Multitenant work?

Upgrading the PDB$SEED takes always longerI have written several blog posts in the past about the different techniques to upgrade an Oracle Multitenant environment:

  1. Upgrade Everything At Once
  2. Upgrade via Unplug/Plug/Upgrade

The interesting case happens in the “Everything At Once“. The PDB$SEED takes significantly longer to upgrade than any other container.

Upgrading the PDB$SEED takes always longer?

Quick recap: The PDB$SEED is the (I call it) blueprint

Continue reading...

Uninstalling OWM from a Multitenant container database


Let me put a warning first: The intention of this blog post is NOT to recommend to uninstall anything just “because”. It is meant only as a guidance and explanation how to uninstall a component in an Oracle Multitenant container database deployment correctly without braking anything. And as this is not documented yet clearly, I bring up this blog post.

Oracle Workspace Manager

OWM Workspace Tree

Workspace Tree

Oracle Workspace Manager (OWM) is a pretty cool feature of the Oracle Database. It does not require an extra license.

OWM is meant to enable application developers and DBAs to manage current, proposed and …

Continue reading...

noncdb_to_pdb.sql – why does it take so long?

What is noncdb_to_pdb.sql?

noncdb_to_pdb.sql - Why does it take so long?The script noncdb_to_pdb.sql gets run only once in the life span of your database. It gets executed when a regular Oracle database (non-CDB) gets move to become part of a Single-/Multitenant deployment as a pluggable database (PDB). But why does it take so long?

You will never run this script again for this database. The script is a sanity script to clean up and change things in a non-CDB, and unite it finally and irreversible with the CDB.

How long does it take to complete?

Runtime varies a lot. It depends mainly on the number of objects …

Continue reading...

Issue with PDB Archives in Oracle in ASM

There is a fancy new command to unplug a PDB in Oracle Database


The nice thing with this command differing in the file ending of ‘pdb‘ instead of ‘xml as you used it in Oracle 12.1 (and the ‘xml‘ option is still available of course): Instead of just creating an xml description file it zips everything together into a PDB archive.

SQL> show pdbs

---------- ------------------------------ ---------- ----------
SQL> create pluggable 
Continue reading...

Can I restart a failed Multitenant Upgrade as well?

A while back I did blog about the new -R option of the parallel upgrade tool in Oracle Database 12.2.

And in case you will do a real Multitenant upgrade and fail – as it happened to me today due to “no space left on device” (no audit files could be written anymore) I tried the -R option as well based on Joe’s (our lead developer) recommendation:

$ $ORACLE_HOME/perl/bin/perl -n 6 -R -l /home/oracle/mike2 catupgrd.sql

Argument list for []
Run in                c = 0
Do not run in         
Continue reading...

Upgrade Everything at Once – Multitenant Upgrade from Oracle 12.1 to 12.2

I did blog about this topic a while back.

Theory is nice but it’s a big difference when you do it the first time with a non-lab deployment at a customer with a real Multitenant database with almost 100 PDBs.Then you learn that neither the documentation nor my previous blog post contain all the necessary steps.

My test setup

I have a simple deployment of just 5 PDBs. But it will showcase with my limited compute capabilities how the process should work.

SQL> show pdbs

Continue reading...

SGA_MIN_SIZE in Oracle Database 12.2

I did summarize all new init.ora/spfile parameters in Oracle Database a while ago:

And one caught Roy’s and my attention: SGA_MIN_SIZE.

When we present this new parameter as part of the Multitenant slides in our workshops, some people ask themselves:
“What happens if I oversubscribe such a parameter?” For example, if SGA_TARGET=4G and I have 10 PDBs and define in each of them SGA_MIN_SIZE=500M. Will the 9th and 10th PDB not startup anymore? Or will the 7th PDB already in trouble as the CDB$ROOT will need some SGA portion as …

Continue reading...

Upgrade to Oracle Database 12.2 and Converting it into an 12.2 PDB (plugin)

2 customers requested a bit more explanation on how to:
Convert and Plugin an Oracle 12.1 non-CDB database into an Oracle 12.2 container database

Cool, I like that 🙂 I will do this right away and use the approach to Upgrade first, then Plugin afterwards.

Upgrading an Oracle database to Oracle

First the preupgrade.jar must be executed – please download always the most recent version from MOS Note:884522.1 as the version we publish is usually newer including important improvements (and fixes) than the version you’ll get with the release drop. We are working on including always the …

Continue reading...

Install components in Multitenant ALWAYS with

I did blog several times about how to remove an unwanted component from a database. But yesterday I came across this interesting scenario worth a blog post.

How to install a component afterwards in Single-/Multitenant?

I haven’t checked all the MOS Notes but I recognized that some MOS Notes explaining how to (re-)create a component such as JVM are not updated to deal with a Single and/or Multitenant environment as they simply call the scripts from SQL*Plus. But the key to script execution in such an environment is, the perl driver meant to execute database scripts not only in …

Continue reading...

DBMS_QOPATCH does not work in PDBs (right now)

Thanks to Murthy who commented on this blog post and Jeannette Holland (SimCorp) who opened an SR resulting in an ER.

DBMS_QOPATCH in Multitenant

DBMS_QOPATCH will deliver useful information about installed patches only when executed within the CDB$ROOT. It has been designed this way for security reasons in Oracle Database 12.1 but I can easily see a need to check for installed patches within a PDB as well.


I “borrowed” this test case from Jeannette’s SR:



Continue reading...


In Oracle Database 12.2 (available in the Oracle DBaaS Cloud) there’s a new functionality called “PDB LOCKDOWN PROFILES“. Actually the parameter came already in undocumented and not functional in Oracle Database (see here).

PDB Lockdown Profiles

PDB Lockdown Profiles are meant as a granular way to restrict or enable access – mostly under the aspect of using the database in a highly shared environment but requiring security. The idea is to embed restrictions on-top of a grant, i.e. taking away certain things. As an example you could allow a user logged into a certain PDB …

Continue reading...

APEX is in CDB$ROOT again – Journey to the Cloud VII

Well … it’s been a while … but I would like to continue my journey to the cloud …

What happened so far on my Journey to the Cloud?

DBaaS Oracle Cloud

Continue reading...