Single Tenant

How to speed up Multitenant CUSTOM database creation

Why has the PDB$SEED such a strange setup?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.

Background Information

The PDB$SEED is the – I call it – “blueprint” pluggable database. It’s sole purpose is to allow you fast provisioning of … [ Read more ]

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.

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 … [ Read more ]

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.… [ Read more ]

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

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 
[ Read more ]

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

[ Read more ]

Can you select a PDB’s character set?

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 … [ Read more ]

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 … [ Read more ]

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 … [ Read more ]

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 … [ Read more ]

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 … [ Read more ]

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 … [ Read more ]

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:

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:

[ Read more ]

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 [ Read more ]

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:

 

 

 

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 [ Read more ]

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 … [ Read more ]

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

I have already explained in broad details a while ago how to:

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 ]

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 … [ Read more ]

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:

Credits for the CDB/PDB Notes go to

[ Read more ]

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 … [ Read more ]

Upgrade PDBs – Everything At Once (Full CDB Upgrade)

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 ]

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 … [ Read more ]

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

Share this:
[ Read more ]