Cloning with Pluggable Databases in Oracle 18c

We had several discussions on the internal mailing lists the other day about cloning with Pluggable Databases in Oracle 18c (or And I blogged about this topic a while ago but realized I may need to refresh this a bit.

Cloning with Pluggable Databases in Oracle 18c

My example from the old blog post still works fine. But I realized that I switched my database into read-only mode. Hence, I like to repeat it and check different options.

Cloning a 12.1 PDB into Oracle 18c

In the source CDB1 I create a fresh PDB first, then open it and create a cloning user with the necessary …

Continue reading...

Oracle 18.3.0 Multitenant: Compatibility Check does not work

Oracle 18.3.0 Multitenant: Compatibility Check does not workWhen I changed our Hands-On Lab to Oracle 18c, I encountered one strange issue in case I upgraded the UPGR database to 18.3.0 and wanted to plug it in afterwards. Before plugin I usually do a compatibility check. But this seems to fail. I checked my file, my paths, everything. But I couldn’t find the root cause. And yesterday I received an email from a customer who has a case open for over a week encountering a similar problem. And we both came to the conclusion for Oracle 18.3.0 Multitenant: Compatibility Check does not work under certain circumstances.…

Continue reading...

Drop a tablespace in a PDB with a Guaranteed Restore Point being active

Drop a tablespace in a PDB with a Guaranteed Restore Point being activeThis was an interesting question I received from Alain Fuhrer a couple of weeks ago: “Why can’t I drop a tablespace in a PDB whit a Guaranteed Restore Point being active?”. It’s important to note that the GRP is not active in the same but in another PDB.

An SR resolved the issue. But in case you face the same situation, here’s the solution.

Drop a tablespace in a PDB with a Guaranteed Restore Point being active

Simple test scenario. These are my PDBs:

show pdbs

---------- ------------------------------ ---------- ----------
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...

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...

Swiss Mobiliar Boosts Performance by Upgrading its Oracle Databases

Swiss Mobiliar Boosts Performance by Upgrading its Oracle Databases

You may have read already some stories about our successful collaboration with Switzerland’s most trusted insurer, Swiss Mobiliar Insurance on this blog. But today I can add an official reference story in 3 languages about how Swiss Mobiliar Boosts Performance by Upgrading its Oracle Databases.

Reference Story: Mobiliar’s Upgrade Project

And I’m very excited to link the official reference story about Mobiliar’s Upgrade and Multitenant project available in 3 languages:

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...

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 to Guaranteed Restore Points

Fallback Strategy: Flashback to Guaranteed Restore PointsYou can attempt an Oracle database upgrade without having a fallback strategy. Of course you can. If you are brave enough. But I would recommend you this Fallback Strategy: Flashback to Guaranteed Restore Points. It is simple, easy, fast. And yes, it is an Enterprise Edition only feature. I have blogged about it in the past already but I realized it need some refresh.

Fallback Strategy: Flashback to Guaranteed Restore Points

One of the fastest way to revert from a big change (i.e. a database upgrade, an application upgrade, etc) is Flashback Database to a previously created Guaranted Restore Point.…

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...

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...

Hands-On Lab available: Upgrade to Oracle Database

Thanks to Roy – our Hands-On Lab “Upgrade, Migrate and Consolidate to Oracle Database 12.2” is now available for download from OTN.

Instructions and technical requirements are clearly documented on the OTN page as well. The lab is based on Virtual Box and can be easily imported upon download and unzip.

You can always access the lab via the blog’s top menu as well:

HOL Link on the Upgrade Blog

Hands-On Lab – Access via the Blog

Inside the lab you’ll find :

  • UPGR – an Oracle
Continue reading...

Non-CDB architecture still exists in Oracle Database 12.2.

I received a question whether the Non-CDB architecture still exists in Oracle Database 12.2.from 3 different colleagues in the past week:

“Does Oracle Database 12.2 still allow us to create non-CDB databases?”

And as I get this question at every customer-facing workshop these days as well before being able to talk about Single/Multitenant – I think it is important to clarify it. The statement is misleading. And I can’t blame anybody about misreading it.

Is the non-CDB architecture still available/supported in Oracle Database 12.2?

I did blog about it a while ago – but Google is not always your …

Continue reading...

Upgrade to Oracle Database 12.2 – New MOS Notes

Some very helpful MOS Notes got published regarding the upgrade to Oracle Database

Furthermore check regularly for Known Issues and Alerts – currently the note has no content but exists already:


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...


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...

SPFILE Parameter: max_pdbs – a must for Single Tenant

Sometimes my job has a aspect making me smile at the end of the day 😉

I sat together with Johannes Ahrends during a talk at the OUGN Conference on the boat from Oslo towards Kiel. And we were discussing afterwards why there’s no official way to limit the number of PDBs which will be essential for customers wanting to go the Single Tenant track. I had my Hands-On environment up and we played a bit in the break recognizing that a constraint on CONTAINER$ won’t be the correct solution as unplug/plug operations leave leftovers in it unless you …

Continue reading...

PDB unplug/plug/patch with PSUs or BPs

This question was posted on the blog a few days ago referring to my previous blog posts describing the two techniques to upgrade in an Oracle Multitenant environment:


We are planning to upgrade from to
The plan is to create new oracle home and
gradually moving PDB from to
Should I follow the same steps?
Is there any document for what we want to do?

And luckily there is a MOS Note out there describing the steps:

Continue reading...

DROP PLUGGABLE DATABASE – things you need to know

Directly after my DOAG (German Oracle User Group) Conference presentation about “How Single-/Multitenant will change a DBA’s life” Martin Bach (Enkitec) approached me and told me about his experiences with the DROP PLUGGABLE DATABASE command and future recoverability.

Martin discovered that once you issued the DROP PLUGGABLE DATABASE command you can’t reuse a previously taken backup of this particular PDB anymore and recover the PDB into this existing CDB. I wasn’t aware of this and I’m glad that Martin told me about it.

Actually only the meta information in the controlfile or the RMAN catalog will be deleted. But archive

Continue reading...

How to find out if a PSU has been applied? DBMS_QOPATCH

pflaster.jpgSince we change the PSU and BP patch numbering from Oracle Database to 12,1,0,2,160119 it is almost impossible to distinguish from the patch name only if you have applied a PSU or a BP.

In Oracle Database 12c there’s a package available which is very useful to query plenty of information about patches from within the database: DBMS_QOPATCH.

Here are a few helpful examples which I created by checking in our DBaaS Cloud database.

Which patches have been applied (or rolled back)?

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 20415564
        Action : APPLY
Continue reading...