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 … [ Read more ]
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 … [ Read more ]
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 18.104.22.168 and some other things:[ Read more ]
We had several discussions on the internal mailing lists the other day about cloning with Pluggable Databases in Oracle 18c (or 22.214.171.124). 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 … [ Read more ]
There’s been silence for a while – but today I’d like to show you how to unplug a 126.96.36.199 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 188.8.131.52 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 … [ Read more ]
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 184.108.40.206. 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… [ Read more ]
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 … [ Read more ]
Since Oracle Database 220.127.116.11 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 … [ Read more ]
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… [ Read more ]
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 … [ Read more ]
At the moment I work with one of our reference customers, Swiss Mobiliar Insurance, on their Multitenant upgrade to Oracle Database 18.104.22.168. 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 22.214.171.124.BP170117, destination version is Oracle Database 126.96.36.199.RU170718. … [ Read more ]
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 … [ Read more ]
There is a fancy new command to unplug a PDB in Oracle Database 188.8.131.52:
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… [ Read more ]
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 ----------… [ Read more ]
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:
… [ Read more ]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 184.108.40.206 (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 … [ Read more ]
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 220.127.116.11 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 18.104.22.168.1 to 22.214.171.124.5. The plan is to create new 126.96.36.199.5 oracle home and gradually moving PDB from 188.8.131.52.1 to 184.108.40.206.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:[ Read more ]
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 … [ Read more ]
Three recent lessons about PDBs in the Oracle Single/Multitenant space you should be aware of.
And thanks to my teammates and the Multitenant PMs for bringing this into our radar.
Unplug/plug – don’t forget to DROP your PDB
I’ve had to add a single line to my previous blog post about the upgrade solution Unplug/Plug/Upgrade for PDBs:
You’ll have to DROP your PDB after you have unplugged it as otherwise the information will stay in the CDB’s dictionary where you have unplugged it from (a) forever and (b) during an subsequent upgrade of the entire source CDB. But the … [ Read more ]
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 220.127.116.11.
Create Container Databases (CDB) with less options – it’s now supported in Oracle 18.104.22.168
Almost all customers I have talked to in the … [ Read more ]
I have already explained in broad details a while ago how to:
- Upgrade a Multitenant Environment – Everything at Once
- Upgrade in a Single-/Multitenant Environment – One/Many at a time
or also known as unplug/plug/upgrade strategy
But one may miss the steps for applying a PSU (Patch Set Update) or BP (Bundled Patch) to a Single-/Multitenant Environment.
At first everything will work just the same if you choose the Everything-at-Once strategy as datapatch will adjust all the required things regardless of being executed in a stand-alone or a singe/Multitenant environment.
But what happens if you apply a … [ Read more ]
A customer checking out our slides from the OTN Tour in August 2014 asked me a finicky question the other day:
“According to the documentation the Automatic SQL Tuning Advisor maintenance task gets executed only within the CDB$ROOT, but not within each PDB – but the slides are not clear here. So what is the truth?”
Ok, that’s good question. In my understanding all tasks will get executed within each PDB – that’s why we recommend (based on experience) to break up the default maintenance windows when using Oracle Multitenant. Otherwise all PDBs will have the same maintenance windows, and … [ Read more ]
As referred to it before there are two techniques to upgrade an Oracle Multitenant environment:
In this post I will explain the method of “Everything At Once” and describe all the steps. The benefit of this approach is simplicity and ease of maintenance. In an upgrade workshop in Melbourne earlier this year a DBA from Germany came by in one of the breaks explaining that he takes care on over 100 developer databases – and it would ease his life a lot if he could … [ Read more ]