Single-/Multitenant

Database Migration from non-CDB to PDB – Upgrade, plug in, convert

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 12.2.0.1, there are a few other options available.

Database Migration from non-CDB to PDB - Upgrade, plug in, convert

High Level Overview

Endianness change possible: No
Source database versions: Oracle 11.2.0.4 or newer
Characteristic: Upgrade non-CDB first
Upgrade necessary: Yes, before plugin
Downtime:
Upgrade, plugin, copy (optional) and
Continue reading...

Database Migration from non-CDB to PDB – Overview

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

Transportable Tablespace PDB Migration and Local Undo

Transportable Tablespace PDB Migration and Local Undo

Photo by Steve Johnson on Unsplash

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

Why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE

Why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE

There usually a philosophic battle happening when it comes to underscore parameters. The official statement is more or less: It’s an underscore, hence it is undocumented and you shouldn’t touch it unless Oracle Support advises you to do so. I agree in many cases. But in this specific one, I don’t. And in this post I will explain why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE in all your singe- and Multitenant environments. Always!

A bit of history

At first, a little bit of a history excursion. This parameter wasn’t an underscore in Oracle 12.1. You can find it as EXCLUDE_SEED_CDB_VIEW in 12.1.0.1 …

Continue reading...

How to patch all PDBs with the a new time zone file?

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?

How to patch all PDBs with the a new time zone file?

Photo by Laureen

Continue reading...

Does the PDB$SEED get “time zone” patched or not?

Today I received a question from a very experienced Oracle ACS engineer whether the PDB$SEED get time zone patched or not when a time zone patch gets applied. I’d say spontaneously “Of course, it does” but after thinking for a few seconds I started having doubts. Question is: Does the PDB$SEED get “time zone” patched or not?

Does the PDB$SEED get "time zone" patched or not?

Photo by Luis Cortes on Unsplash

Time zone scripts are in ?/rdbms/admin since Oracle 18c

First of all, since Oracle Database 18c the scripts to adjust time zone settings of the database are in ?/rdbms/admin. If you did our Hands-On Lab already …

Continue reading...

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 …

Continue reading...

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 …

Continue reading...

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

Continue reading...

Creating CDBs and non-CDBs with less options

Creating CDBs and non-CDBs with less optionsI wanted to write up this blog post a long time ago. When Helen Vanderheide did ask me a few weeks ago if she can omit catoctk.sql during the creation of the database, I realized that I should update my previous blog posts about Creating CDBs and non-CDBs with less options. And of course, the following is usable with non-CDBs as well.

You’ll find an example and especially the component dependency matrix below as well as many links to related blog posts at the end. If you don’t want to read all this, just scroll down to the Summary

Continue reading...

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
	 
Continue reading...

Be aware of database options selections for PDBs in DBCA

I did blog about several things-to-know with DBCA (Database Configuration Assistant) in the past. And one issue came up in our internal mailing list the other day. Be aware of database options selections for PDBs in DBCA. Missing one or the other “click” may bring you in trouble later on. I blogged about this already a while ago but this was only part of a larger post: Create Multitenant Databases with DBCA – Things to Know.

Be aware of database options selections for PDBs in DBCA

When you create a custom container database with DBCA you will see this …

Continue reading...

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

Continue reading...

Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c

Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18cPDB_PLUG_IN_VIOLATIONS is a strange view in the Oracle database universe. It does not follow Oracle naming conventions – and it has too many underscores in its name. Furthermore I get always annoyed by the fact that it doesn’t get purged automatically. In addition it accumulates a massive number of useless information over time. But in Oracle Database 18c there’s a least some relief: A new call gets introduced which allows purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c.

PDB_PLUG_IN_VIOLATIONS

Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c

As others have written a lot already about PDB_PLUG_IN_VIOLATIONS I won’t repeat it but rather share the links with you:

Continue reading...

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

Unplug a 12.2.0.1 PDB and plugin into 18c in the CloudThere’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).

Unplug a 12.2.0.1 PDB and plugin into 18c inthe Cloud

It is a Single Tenant environment with one PDB. If you need for information about how to …

Continue reading...

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 
Continue reading...

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.

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

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 …

Continue reading...

Create Multitenant databases with DBCA – Things to Know

Create Multitenant databases with DBCA - Things to KnowWhen you create Multitenant databases with DBCA there are some Things to Know. And in Oracle Database 12.2.0.1’s Database Creation Assistant (DBCA) there is a common pitfall in the “Options” screen. It was grayed out in the previous release but when you choose to create a “custom” database you can select the options/components now. Be careful …

Previous posts

I did blog several times about the DBCA already:

And …

Continue reading...

Lost AWR snapshots reappear again in Oracle 12.2 in Multitenant

Lost AWR snapshots reappear again in Oracle 12.2 in MultitenantWhat 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 
Continue reading...

Fallback Strategy: Flashback Pluggable Database

Fallback Strategy: Flashback Pluggable Database in Oracle 12.2Since 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

Fallback Strategy: Flashback Pluggable Database in Oracle 12.2Actually 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

Continue reading...

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

ORA-44787 - Don't mess with the Default Oracle ServiceAt 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. …

Continue reading...

Why EXCLUDE_SEED_CDB_VIEW is now an underscore in Oracle 12.2

Hiding information is not good - and exclude_seed_cdb_view is now an underscore in Oracle 12.2I have received several questions from customers in the past weeks using Oracle Multitenant about the parameter exclude_seed_cdb_view.It magically disappeared and morphed into _exclude_seed_cdb_view since Oracle Database 12.2.0.1. To be fair, the exclude_seed_cdb_view is listed in the list of obsolete parameters in Oracle Database 12.2.0.1.

What is EXCLUDE_SEED_CDB_VIEW?

This parameter hides everything belonging to the PDB$SEED from the usual queries. For instance you ran a query against CDB_DATA_FILES but you won’t see the data files belonging to PDB$SEED in the result set. Whether this is good or bad, at least RMAN (Recovery Manager) worked always correctly.

But I’m …

Continue reading...

Can you simply switch from SE2 to EE with Oracle Multitenant

Oracle MultitenantThis question is coming up from time to time – and currently not covered in the official Oracle documentation as far as I know: “Can you simply switch from SE2 to EE with Oracle Multitenant?”

Yes, you can!

First of all, the switch is possible and tested. But you’ll have to satisfy a few requirements:

  • Both database versions must be the same
  • Both databases must have the same character set (in Oracle Database 12.1)

That’s it. Simply unplug your Standard Edition 2 PDB and plug it into your Enterprise Edition CDB.

In addition you may have to run several component …

Continue reading...

Why does the upgrade of PDB$SEED always take longer?

Upgrading the PDB$SEED takes always longer than any other container.
What is the reason for an upgrade of the PDB$SEED taking so much longer in a real world environment?

How does an upgrade with Oracle Multitenant work?

Upgrading the PDB$SEED takes always longerI have written several blog posts in the past about the different techniques to upgrade an Oracle Multitenant environment:

  1. Upgrade Everything At Once
  2. Upgrade via Unplug/Plug/Upgrade

The interesting case happens in the “Everything At Once“. The PDB$SEED takes significantly longer to upgrade than any other container.

Upgrading the PDB$SEED takes always longer?

Quick recap: The PDB$SEED is the (I call it) blueprint

Continue reading...