Unified Auditing policies can slow down the upgrade

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. Unified Auditing You find a good number of blog posts explaining how to migrate to Unified Auditing, the performance tweaks in Oracle 12.1.0.2 and some other things: Unified Auditing – Is it ON or OFF in Oracle 12.2.0.1? Unified…

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 12.2.0.1). 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…

Unplug a 12.2.0.1 PDB and plugin into 18c in the Cloud

There’s been silence for a while – but today I’d like to show you how to unplug a 12.2.0.1 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 12.2.0.1 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…

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

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…

Can you select a PDB’s character set?

Since Oracle Database 12.2.0.1 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 got…

Lost AWR snapshots reappear again in Oracle 12.2 in Multitenant

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

Fallback Strategy: Flashback Pluggable Database

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 Guaranteed…

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

At the moment I work with one of our reference customers, Swiss Mobiliar Insurance, on their Multitenant upgrade to Oracle Database 12.2.0.1. 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 12.1.0.2.BP170117, destination version is Oracle Database 12.2.0.1.RU170718. The upgrade runs fine. But…

noncdb_to_pdb.sql – why does it take so long?

What is noncdb_to_pdb.sql? 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…

Issue with PDB Archives in Oracle 12.2.0.1 in ASM

There is a fancy new command to unplug a PDB in Oracle Database 12.2.0.1: 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 database PDB1 admin user adm identified by adm 2 file_name_convert=(‘/u02/oradata/CDB2/pdbseed’,’/u02/oradata/CDB2/pdb1′); Pluggable…

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

I did blog about this topic a while back. Upgrade PDBs – Everything At Once (Full CDB Upgrade) 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 ———-…

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. Testcase I “borrowed” this test case from Jeannette’s SR: 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   …

Having fun with PDB LOCKDOWN PROFILES

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 12.1.0.2 (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 only to change the…

Gather Fixed Objects Stats in PDBs as well?

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 12.1.0.2 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: SQL> alter pluggable database…

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: Everything at Once One at a Time — via unplug/plug Question:  We are planning to upgrade from 12.1.0.2.1 to 12.1.0.2.5. The plan is to create new 12.1.0.2.5 oracle home and gradually moving PDB from 12.1.0.2.1 to 12.1.0.2.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: MOS Note:2102849.1 Unplug/Plug on different PSU environments on 12.1.0.2…

Why does a PDB require an upgrade?

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 into the PDB only –…

Recent News about Pluggable Databases – Oracle Multitenant

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: https://mikedietrichde.com/2014/08/05/upgrade-pdbs-one-at-a-time-unplugplug/ 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…

Create Container Databases (CDB) with less options – it’s now supported in Oracle 12.1.0.2

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…

Applying a PSU or BP to a Single-/Multitenant Environment

I have already explained in broad details a while ago how to: Upgrade a Multitenant Environment – Everything at Once https://mikedietrichde.com/2014/08/06/upgrade-pdbs-everything-at-once-full-cdb-upgrade/ Upgrade in a Single-/Multitenant Environment – One/Many at a time or also known as unplug/plug/upgrade strategy https://mikedietrichde.com/2014/08/05/upgrade-pdbs-one-at-a-time-unplugplug/ 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 PSU…

Automatic Maintenance Jobs in every PDB?
New SPM Evolve Advisor Task in Oracle 12.1.0.2

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…

Upgrade PDBs – Everything At Once (Full CDB Upgrade)

As referred to it before there are two techniques to upgrade an Oracle Multitenant environment: Everything at Once One (or many) at a Time — via unplug/plug 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 “hit” all of…

Upgrade PDBs – One at a Time via unplug/plug/upgrade

Basically there are two techniques to upgrade an Oracle Multitenant environment:Everything at Once and One (or many) at a Time. This blog post describes the case how to Upgrade PDBs – One at a Time via unplug/plug/upgrade. I will describe all necessary steps. During some presentations, discussions etc people were left with the impression that it will be a very simple approach to unplug one or many PDBs from a CDB in lets say Oracle 12.1.0.1 and plug it into an Oracle 12.1.0.2 Container Database. Bingo, upgraded! Well, unfortunately this is not true. In fact it is completely wrong. If…

More than one PDB in the same directory?

Can you create more than one pluggable database (PDB) within the same directory? And how does the file naming work? Considering the fact each PDB’s SYSTEM tablespace will be named system01.dbf by default the question is not trivial. This question got asked by a customer during one of the workshops in Switzerland last week. And the solution is straight forward. Thanks to Roy for trying it out yesterday at 170 km/h on our way back from Stuttgart 🙂 Thanks 🙂 -Mike Additional information: Within ASM with OMF the file structure looks like this: select con_id, substr(file_name,1,90),tablespace_name from cdb_data_files order by…

Starting up 252 PDBs automatically?

In my recent posting I have explained the startup of many PDBs at the same time. But once you startup the container database CDB$ROOT the PDBs will stay in MOUNT status. So how do you start them during CDB$ROOT startup (or immediately afterwards) in an automatic fashion? A startup trigger will do this job. CREATE OR REPLACE TRIGGER startup_all_pdbs AFTER STARTUP ON DATABASE BEGIN EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’; END; / And of course you can use the EXCEPT command option to exclude one or more PDBs from the automatic startup. CREATE OR REPLACE TRIGGER startup_all_pdbs_except_a_few AFTER STARTUP…