In the previous blog posts I showed different approaches on how to migrate your database on a same Endianness platform into Multitenant. Whether you prefer to upgrade first or plugin first is up to you. I recommend upgrading first as this allows you a seamless fallback. But regardless of which approach you prefer, you may take care on potential pitfalls. Hence, this blog post is about Database Migration from non-CDB to PDB – Typical Plugin Issues and Workarounds. It may not be complete when I publish it and I may extend it later on. Let me know if you have …Continue reading...
The third option for plugging in a non-CDB is called Database Migration from non-CDB to PDB – Clone via NON$CDB, upgrade, convert. As I showed already in this blog post a while ago, your source must be at least an Oracle 12.1 database. And this technique can be used also only for same-Endianness migrations but does not apply if you’d like to migrate from Big to Little Endian. If your source database matches already the receiving CDB, no upgrade is necessary.
High Level Overview
|Endianness change possible:
|Source database versions:||Oracle 126.96.36.199|
This is my next blog post about Database Migration from non-CDB to PDB – Plug in, upgrade, convert. But what is different from the previous one? And why is it necessary? Since Oracle Database 188.8.131.52 you can plugin a non-CDB at first, the upgrade and convert it. And I’ll show you this technique here.
High Level Overview
|Endianness change possible:||No|
|Source database versions:||Oracle 184.108.40.206 or newer (or 220.127.116.11 when CDB has shared UNDO)|
|Characteristic:||Plugin into CDB first|
|Upgrade necessary:||Yes, after plugin|
|Downtime:||Plugin, copy (optional), upgrade and
As first blog post of this series about Database Migration from non-CDB to PDB – Upgrade, plug in, convert I will explain the most straight forward approach of going to Single- or Multitenant. But you have other options as well. which I’ll showcase in the other blog posts. I demonstrated this technique on the blog already a while ago. But since Oracle 18.104.22.168, there are a few other options available.
High Level Overview
|Endianness change possible:||No|
|Source database versions:||Oracle 22.214.171.124 or newer|
|Characteristic:||Upgrade non-CDB first|
|Upgrade necessary:||Yes, before plugin|
||Upgrade, plugin, copy (optional) and|
Today I’ll start a blog post series of several “how to” articles about database migration from non-CDB to PDB – Overiew. You’ll find a quick introduction below. And of course, the links to all the other related blog posts.
Database Migration from non-CDB to PDB – Overview
When you plan to move from non-CDB, the Oracle term for regular database deployments, to a Pluggable Database (PDB), you will migrate your database. It will become a PDB and run as part of a Container Database (CDB). This is a well known concept for several years now called Oracle Multitenant. If you …Continue reading...
Sometimes it is necessary to warn you about known pitfalls to avoid frustration. In this particular case I decided not to blog about it simply because I thought this won’t happen to too many other people. Well, yesterday my good friend Philippe Fierens dropped me a message about an issue he ran into with a Transportable Tablespace PDB Migration and Local Undo. And I immediately knew what caused him trouble – and I regret that I didn’t blog about it (sorry Philippe!). We’ve seen the same problem with a large ExaCC migration project …Continue reading...
Yesterday I wrote about how to adjust the time zone setting in the
PDB$SEED as by default the time zone scripts won’t touch the
PDB$SEED when you execute them. And in addition, MOS Note:1509653.1 tells you, that the
PDB$SEED can’t be adjusted. But this leads to a weird mix of time zone settings across a Multitenant deployment. Which I’d guess is not desired. Following a tweet reply by Marco Mischke I realized: I explained how to patch the PDB$SEED – but I didn’t explain how to patch all PDBs with the a new time zone file?
I usually create my container databases for testing with the
CUSTOM option of DBCA. That allows me to specify exactly the components I’d like to have – or not. But I realized two things during the
CUSTOM creation: It takes very long. And in the
alert.log I see resize operations especially with the
PDB$SEED during dictionary view creation. I see heap warnings. And recompilations. I did ask myself how to speed up Multitenant
CUSTOM database creation.
PDB$SEED is the – I call it – “blueprint” pluggable database. It’s sole purpose is to allow you fast provisioning of …
I posted several blog posts about Unified Auditing in the past. And recently a large customer in the UK alerted me about their upgrades with PDBs from 12.1 to 12.2 taking much longer when Unified Auditing policies were enabled. It looks like as if Unified Auditing policies can slow down the upgrade.
We did some further investigations. See the results below.
You find a good number of blog posts explaining how to migrate to Unified Auditing, the performance tweaks in Oracle 126.96.36.199 and some other things:Continue reading...
We had several discussions on the internal mailing lists the other day about cloning with Pluggable Databases in Oracle 18c (or 188.8.131.52). And I blogged about this topic a while ago but realized I may need to refresh this a bit.
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...
There’s been silence for a while – but today I’d like to show you how to unplug a 184.108.40.206 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 220.127.116.11 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).
It is a Single Tenant environment with one PDB. If you need for information about how to …Continue reading...
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 18.104.22.168. 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...
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.
Please see also: Upgrade Testing – Online Clone a PDB to Oracle 19c
Cloning a PDB from Oracle 12.1 to Oracle 12.2
You can clone a non-CDB and make it a PDB but …Continue reading...
Since Oracle Database 22.214.171.124 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...
What 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.
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:
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...
Since 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
Actually 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 …
At the moment I work with one of our reference customers, Swiss Mobiliar Insurance, on their Multitenant upgrade to Oracle Database 126.96.36.199. 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 188.8.131.52.BP170117, destination version is Oracle Database 184.108.40.206.RU170718. …Continue reading...
What is noncdb_to_pdb.sql?
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...
There is a fancy new command to unplug a PDB in Oracle Database 220.127.116.11:
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO 'pdb1.pdb';
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 CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SQL> create pluggable… Continue reading...
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 CON_ID CON_NAME OPEN MODE RESTRICTED ----------… Continue reading...
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...SQL> COLUMN NAME FORMAT A8 SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID; NAME CON_ID DBID CON_UID GUID --------
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 18.104.22.168 (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...
Yesterday I received an interesting customer question:
“Do we have to gather fixed objects stats inside a PDB as well or only in the CDB$ROOT?”
The customer told me he couldn’t find anything in the documentation. Neither could I. And I did check also the usual suspects this morning “Best Practices for Gathering Statistics” – but it does not say anything about PDBs and CDBs.
Therefore I did a short test:
- Create a PDB in my Oracle 22.214.171.124 CDB:
SQL> create pluggable database PDB3 admin user adm identified by adm file_name_convert=( '/u02/oradata/CDB1/pdbseed', '/u02/oradata/CDB1/pdb3');
- Open the PDB:
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 126.96.36.199.1 to 188.8.131.52.5. The plan is to create new 184.108.40.206.5 oracle home and gradually moving PDB from 220.127.116.11.1 to 18.104.22.168.5. 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...
Why do I need to upgrade (or downgrade) a Pluggable Database (PDB) once I unplug it from version A and replug into version B?
This question is one of the most often asked questions in our workshops when Roy and I present Upgrades and Oracle Multitenant.
If you take a look into the documentation you’ll find:
But unfortunately this is not 100% correct for every object.
When you do a simple query for basic dictionary structures such as OBJ$ you’ll easily find out that a PDB has its own Data Dictionary. Some things are really linked …Continue reading...