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…

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…

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…

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

Install components in Multitenant ALWAYS with catcon.pl

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 catcon.pl, the perl driver meant to execute database scripts not only in…

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…

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

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…

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…

Can you have Oracle Multitenant in Oracle 12.1.0.2 SE2?

Please find¬†all our articles about Oracle Database Standard Edition 12.1.0.2 SE2:   Sept 17, 2015: SE2 – Some questions, some answers Sept 8, 2015: MOS Download for Oracle SE2 Sept 7, 2015: Support and Patches for Oracle 12.1.0.1 SE/SE1 Sept 3, 2015: Can you have Multitenant with Oracle SE2? Sept 1, 2015: Oracle Standard Edition SE2 available for download     Can you have Oracle Multitenant in Oracle Standard Edition 12.1.0.2 SE2? No, you can’t as Oracle Multitenant is a licensable option for Enterprise Edition (EE) databases only. But wait a second … You can do Oracle Single Tenant with…

Create a PDB directly from a stand-alone database?

The documentation offers a well hidden feature for migrating a database into the universe of Oracle Single-/Multitenant: Remote Cloning with the NON$CDB option. If you’ll read the documentation it doesn’t say much about this option, neither the requirements nor the exact syntax or an example: http://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686 Scroll down to the FROM clause: … FROM NON$CDB@dblink … this option will be able to plugin a stand-alone database and make it a pluggable database. Sounds interesting, let’s try it. Test 1 – Try to plugin an Oracle 11.2.0.4 database Well, the documentation doesn’t say anywhere anything about source release limitans. So I…

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…

Upgrade to 12c and Plugin – one fast way to move into the world of Oracle Multitenant

What is the best way to move into Oracle Multitenant (which includes Single Tenant with just one PDB) as well? This question came up on the blog, it’s part of the Multitenant chapter in our big slide deck but let me elaborate this a bit more in detail. Of course there are several ways to move a database into a Single/Multitenant environment such as Data Pump but one fact is for sure: You can’t convert a stand-alone database to become a container database (CDB$ROOT). A container database has to be built up from scratch as first step. Please use the…

New MOS Notes on Database Upgrades for 12c with or without Oracle Multitenant

Please find some new MyOracle Support (MOS) Notes about Database Upgrades to Oracle Database 12c, with or without Oracle Multitenant: MOS Note: 1503653.1 Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) . MOS Note: 1932762.1 Complete checklist for manual upgrade from 12.1.0.1 to 12.1.0.N (Full CDB Upgrade) . MOS Note:¬†1933391.1 Complete checklist for 12c R1 PDB upgrade (Upgrading single/multiple PDB) . MOS Note:¬†1932340.1 How to execute sql scripts in Multitenant environment (catcon.pl) . MOS Note:¬†1933011.1 Complete Checklist for DBUA Upgrade from 12.1.0.1 to 12.1.0.N . Credits for the CDB/PDB Notes¬†go to my colleague, Krishnakumar¬†Kunka¬†¬†ūüôā -Mike

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…

Remote Cloning of Pluggable Databases
in Oracle Database 12.1.0.1

Cloning of Pluggable Database in Oracle Multitenant is a great feature and very useful. It had just a tiny flaw in Oracle Database 12.1.0.1: Remote cloning from one to another CDB fails. This will work flawless beginning with the first patch set Oracle 12.1.0.2 – but if you want to use that feature still in Oracle 12.1.0.1 you need to apply at least PSU3 or a single patch¬†18898403. See the documentation: –Mike