Single-/Multitenant

Pitfalls: Connect to a PDB directly with ORACLE_PDB_SID

Posted on July 13, 2020 by Mike.Dietrich Single-/Multitenant 16

A few weeks ago I learned about the ability to connect directly into a PDB by using the environment variable ORACLE_PDB_SID. I blogged about it in Can you connect as SYSDBA without password into a PDB directly?. And today, I’d like to give you a bit more insights and warn you about the Pitfalls: Connect to a PDB directly with ORACLE_PDB_SID.

Pitfalls: Connect to a PDB directly with ORACLE_PDB_SID

Photo by Mauro Sbicego on Unsplash

How does it work?

When I wrote the blog post Can you connect as SYSDBA without password into a PDB directly? I haven’t dug into the technique behind the ability …

Transportable Tablespaces – Example and strange error with a PDB

Yesterday I was browsing around for a useful simple example to test Transportable Tablespaces. A colleague mailed with the other day with a strange error message. The attempt to import into a PDB in Oracle 19c failed. My first thought: Oh, this is simple. But I failed, too. And even worse, I couldn’t find a single useful note in MyOracle Support (MOS) for ORA-31640, ORA-27037, Linux-x86_64 Error: 2 with Additional information: 7. So I decided to summarize this in Transportable Tablespaces – Example and strange error with a PDB.

Transportable Tablespaces - Example and strange error with a PDB

Photo by Kerensa Pickett on Unsplash

A simple Transportable Tablespace

AutoUpgrade and Plug In to a CDB – with a single command (and video)

Posted on May 20, 2020 by Mike.Dietrich AutoUpgrade Single-/Multitenant 32

I have to confess, this feature is out and available for quite a while. But only unofficially. AutoUpgrade can upgrade and plug in a non-CDB into a CDB. But it lacked flexibility. And we didn’t test it a lot. Hence, you could find it documented in our Hands-On Lab instructions. And we did this exercise with you already in our sold-out labs at OOW 2019 as well. But since a week and a bit, it is officially supported: AutoUpgrade and Plug In to a CDB – with a single command (and video).

AutoUpgrade and Plug In to a CDB - with a single command (and video)

Photo by William Daigneault on Unsplash

Create

Can you connect as SYSDBA without password into a PDB directly?

Posted on May 7, 2020 by Mike.Dietrich Single-/Multitenant 24

I’m happy when I learn something new every day. And today, right before I wanted to turn my computer into “sleep”, I learned something which surprises me a lot. Have you ever asked the question: Can you connect as SYSDBA without password into a PDB directly? Of course with the usual way of “sqlplus / as sysdba”.

Photo by Jonas Verstuyft on Unsplash

Can you?

Our answer always was: No. You can’t. There is no bequeath connection available.

Today somebody asked a similar question internally. And the answer caught Roy, Daniel, Bill and myself by surprise. There is an …

Upgrade Testing with a Refreshable PDB – does this work?

Posted on May 5, 2020 by Mike.Dietrich Single-/Multitenant 4

Yesterday, Daniel pinged me to discuss an upgrade testing scenario I had evaluated briefly by myself a while ago. I can’t remember why I gave up. But Daniel had some great ideas and tried already a lot in our OCI cloud. We want to use a refreshable PDB. And keep it up to date, then upgrade it. The idea of this approach: Whereas a CLONE or RELOCATE uses the initial command as the marker, a REFRESH would allow me to be way more in synch. So the question is: Upgrade Testing with a Refreshable PDB – does this work?

Upgrade Testing with a Refreshable PDB - does this work?

Photo

Online Clone a PDB and synch with Oracle GoldenGate?

Posted on April 15, 2020 by Mike.Dietrich Single-/Multitenant 4

A few weeks ago I published two blog posts, one about Online Cloning and one about Relocate for PDBs. As in many cases, the blog posts got triggered by actual customer questions – and of course out of my own curiosity. In addition, we received the ultimate question: Can I Online Clone a PDB and synch with Oracle GoldenGate? And while my answer would have been “Of course!” a few doubts came up. But read below …

Online Clone a PDB and synch with Oracle GoldenGate?

Photo by Judith Prins on Unsplash

Online Clone versus Relocate of PDBs

I received very good ideas and feedbacks from you regarding …

Upgrade Testing – Online Clone a PDB to 19c

Posted on March 6, 2020 by Mike.Dietrich Single-/Multitenant Upgrade / Migration 10

In the past I wrote some articles about cloning PDBs to new environments. And I added a post about RELOCATE as well. But I realized that I have no specific blog post about a very typical scenario: Upgrade Testing – Online Clone a PDB to 19c.

Upgrade Testing - Online Clone a PDB to Oracle 19c

Photo by Jørgen Håland on Unsplash

My Setup

I have an Oracle 12.2.0.1 CDB with a PDB, and another Oracle 19.6.0 CDB. I will hot clone the PDB from 12.2.0.1 to the higher version CDB, and then upgrade the PDB.

Upgrade Testing - Online Clone a PDB to Oracle 19c

The motivation to do it this way is to allow testing for the application …

Oracle Multitenant: Be aware of the silent COMPATIBLE change

Posted on February 7, 2020 by Mike.Dietrich Single-/Multitenant 5

This topic is included in our Multitenant slides for a long time. But whenever I want to point somebody to the blog post, I realize that it’s not on the blog yet. Actually I discussed this topic recently during a customer visit again. It’s time to put it on the blog. When you use Oracle Multitenant: Be aware of the silent COMPATIBLE change.

Oracle Multitenant: Be aware of the silent COMPATIBLE change

Photo by Clint Patterson on Unsplash

Mixed version environments

When you work with Oracle Multitenant, the sooner or later you will have mixed version environments. This may be CDBs with Oracle 12.1.0.2 or Oracle 12.2.0.1 together with …

Upgrade PDBs with RELOCATE – How does it work?

Posted on January 30, 2020 by Mike.Dietrich Single-/Multitenant Upgrade / Migration 12

Today Roy and I received a question from a colleague about the RELOCATE feature in Multitenant. And I’d like to shed some light and this topic a bit more. Upgrade PDBs with RELOCATE – How does it work?

Upgrade PDBs with RELOCATE - How does it work?

Photo by Sebastian Pena Lambarri on Unsplash

RELOCATE and UPGRADE?

At first, it looks very promising to me. RELOCATE uses the HOT CLONE functionality of Multitenant but then relocates the PDB from one to another CDB. You need to fulfill a few important requirements, for instance:

Local Undo must be enabled in both CDBs
- This limits the technique to at least Oracle

Can you have more than 1 PDB with Standard Edition 2 (SE2)?

Posted on January 29, 2020 by Mike.Dietrich SE2 - Standard Edition 2 Single-/Multitenant 0

When we announced the 3-PDB-Support with Oracle Database 19c onward at OpenWorld 2019, I was very excited. According to the license documentation, this applies to Standard Edition 2 (SE2) as well. But does it work? Can you have more than 1 PDB with Standard Edition 2 (SE2)?

Can you have more than 1 PDB with Standard Edition 2 (SE2)?

Photo by Şahin Yeşilyaprak on Unsplash

The license documentation is pretty clear about 3 user-created PDBs:

https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87

Check with Oracle Database 19.5.0 SE2

It’s been a while since I installed SE2 instead of EE. But everything goes smoothly. You just need to select “Standard Edition 2” in the OUI. The image you …

Save Patching Downtime with Oracle Multitenant?

Posted on December 2, 2019 by Mike.Dietrich Best Practice Patch Recommendation Single-/Multitenant 4

At OOW 2019 we announced that you can have up to 3 user-created pluggable databases (PDBs) per container database (CDB) without having a Multitenant license. And in this blog post I’d like to demonstrate how you can save patching downtime with Oracle Multitenant.

The Concept

The idea this entire process settles on is very simple. When you create a fresh container database in the target (already patched!) environment, you don’t have to patch this one. You only have to relocate the PDB from the source into the target CDB. In addition, only datapatch needs to be executed. As the …

DBMS_PDB.DESCRIBE – Something to be aware of

Posted on October 28, 2019 by Mike.Dietrich Single-/Multitenant 2

When I wrote up my long blog post series about how to migrate to Multitenant, I came across an interesting behavior. I could create the XML manifest file to plugin without putting the database in read-only mode. But I learned afterwards that there are is a pitfall. Hence, this blog post will talk about DBMS_PDB.DESCRIBE – Something to be aware of.

DBMS_PDB.DESCRIBE - Something to be aware of

DBMS_PDB.DESCRIBE – why and when?

You will use the DBMS_PDB.DESCRIBE call for the sole purpose to create the so called XML manifest file. This file contains information about the non-CDB to plugin afterwards. You either generate it check …

3 PDBs included with Oracle 19c and desupport of non-CDBs with 20c

Posted on September 17, 2019 by Mike.Dietrich Single-/Multitenant 32

It’s Oracle Open World week in San Francisco. and it is time for announcements of course.

3 PDBs included with Oracle 19c and desupport of non-CDBs with 20c

Photo by Aaron Burden on Unsplash

What has been announced?

Today at OOW 2019, Jenny Tsai-Smith and Dominic Giles announced in their talk that the non-CDB architecture will be desupported from Oracle 20c on. But VERY IMPORTANT, from Oracle 19c on you can have 3 user-created pluggable databases of any type without a Multitenant license. As soon as you have need for 4 or more PDBs within one container database, you will need a Multitenant license.

License Guide

You can find the update regarding …

Database Migration from non-CDB to PDB – Migration with Data Pump

Posted on August 14, 2019 by Mike.Dietrich Data Pump Single-/Multitenant 6

You may have realized that there are a few techniques missing describing how to do a Database Migration from non-CDB to PDB – Migration with Data Pump is one of them. I will explain the most simple approach of going to Single- or Multitenant. It isn’t the coolest – and it isn’t very fast as soon as your database has a significant size. But it is not complex. And it allows you to move even from very old versions directly into an Oracle 19c PDB – regardless of patch levels or source and destination platform.

Database Migration from non-CDB to PDB – Migration with Data Pump

High Level Overview

Endianness change

Database Migration from non-CDB to PDB – The Minimal Downtime Challenge

Posted on August 13, 2019 by Mike.Dietrich Minimal Downtime Single-/Multitenant 4

There are several pitfalls when you plugin a non-CDB into a CDB environment. I’d like to highlight some of them – and show you potential workarounds as well. This is part of a series of blog posts to make your migration from non-CDB to PDB a bit smoother.

Database Migration from non-CDB to PDB – The Minimal Downtime Challenge

When we start to look at a migration from non-CDB to PDB for an important system, regardless of migrating on-prem or to the cloud, reducing the downtime is very important. In this blog post I’d like to highlight the different aspects …

Database Migration from non-CDB to PDB – The Fallback Challenge

Posted on August 5, 2019 by Mike.Dietrich Fallback Single-/Multitenant 2

Photo by Sebastian Grochowicz on Unsplash

Database Migration from non-CDB to PDB – The Fallback Challenge

As I work with customers on upgrades and migrations for a very long time, I know how important a proper fallback is.

When you attempt a migration from a non-CDB to PDB, you need to plan …

Database Migration from non-CDB to PDB – Various Pitfalls

Posted on August 2, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 20

Photo by timJ on Unsplash

Database Migration from non-CDB to PDB – Various Pitfalls

In all the previous blog posts of this series I tried to explain specific pitfalls, and how you can workaround them. This article is meant to collect the “leftovers”, the minor issues and pitfalls which you may not …

Database Migration from non-CDB to PDB – The Patch Level Pitfall

Posted on August 1, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 0

Photo by Piron Guillaume on Unsplash

Database Migration from non-CDB to PDB – The Patch Level Pitfall

When you consolidate on a larger scale, it is very likely that you have different patch levels in your database environments. But when you attempt to plugin a non-CDB into a CDB, you may see …

Database Migration from non-CDB to PDB – The Component Pitfall

Posted on July 31, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 4

Photo by Brett Jordan on Unsplash

The Component Pitfall

With component we mean the database component which you can find in DBA_REGISTRY – or CDB_REGISTRY. When Multitenant became available over 5 years ago, a decision had been made to make all options/components mandatory in a container database. This decision had to …

Database Migration from non-CDB to PDB – The Time Zone Pitfall

Posted on July 30, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 0

Photo by Allef Vinicius on Unsplash

Database Migration from non-CDB to PDB – The Time Zone Pitfall

Interestingly, there is no issue with different time zone settings within a single CDB. Your CDB$ROOT can be on DST V.32 whereas a PDB you plugin can be already on DST V.33. But only …

Database Migration from non-CDB to PDB – The COMPATIBLE pitfall

Posted on July 29, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 0

Database Migration from non-CDB to PDB - The COMPATIBLE pitfall

Database Migration from non-CDB to PDB – The COMPATIBLE pitfall

When you migrate your non-CDB to PDB, in most cases the COMPATIBLE setting of the non-CDB will be lower than the setting of the receiving CDB. But still in this case you may see warnings. I will explain how to deal with …

Database Migration from non-CDB to PDB – Typical Plugin Issues and Workarounds

Posted on July 29, 2019 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 0

In the previous blog posts I showed different approaches on how to migrate your database on a same Endianness platform into Multitenant. Whether you prefer to upgrade first or plugin first is up to you. I recommend upgrading first as this allows you a seamless fallback. But regardless of which approach you prefer, you may take care on potential pitfalls. Hence, this blog post is about Database Migration from non-CDB to PDB – Typical Plugin Issues and Workarounds. It may not be complete when I publish it and I may extend it later on. Let me know if you have …

Database Migration from non-CDB to PDB – Clone via NON$CDB, upgrade, convert

Posted on July 25, 2019 by Mike.Dietrich Single-/Multitenant 4

The third option for plugging in a non-CDB is called Database Migration from non-CDB to PDB – Clone via NON$CDB, upgrade, convert. As I showed already in this blog post a while ago, your source must be at least an Oracle 12.1 database. And this technique can be used also only for same-Endianness migrations but does not apply if you’d like to migrate from Big to Little Endian. If your source database matches already the receiving CDB, no upgrade is necessary.

Photo by Samuel Chan on Unsplash

High Level Overview

Endianness change possible:	No
Source database versions:	Oracle 12.1.0.2

Database Migration from non-CDB to PDB – Plug in, upgrade, convert

Posted on July 24, 2019 by Mike.Dietrich Single-/Multitenant 2

This is my next blog post about Database Migration from non-CDB to PDB – Plug in, upgrade, convert. But what is different from the previous one? And why is it necessary? Since Oracle Database 12.2.0.1 you can plugin a non-CDB at first, the upgrade and convert it. And I’ll show you this technique here.

Photo by Dan Freeman on Unsplash

High Level Overview

Endianness change possible:	No
Source database versions:	Oracle 12.2.0.1 or newer (or 12.1.0.2 when CDB has shared UNDO)
Characteristic:	Plugin into CDB first
Upgrade necessary:	Yes, after plugin
Downtime:	Plugin, copy (optional), upgrade and `noncdb_to_pdb.sql`
Minimal

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

Posted on July 23, 2019 by Mike.Dietrich Single-/Multitenant 4

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

Single-/Multitenant

How does it work?

A simple Transportable Tablespace

Create

Can you?

Online Clone versus Relocate of PDBs

My Setup

Mixed version environments

RELOCATE and UPGRADE?

Check with Oracle Database 19.5.0 SE2

The Concept

DBMS_PDB.DESCRIBE – why and when?

What has been announced?

License Guide

High Level Overview

Database Migration from non-CDB to PDB – The Minimal Downtime Challenge

Database Migration from non-CDB to PDB – The Fallback Challenge

Database Migration from non-CDB to PDB – Various Pitfalls

Database Migration from non-CDB to PDB – The Patch Level Pitfall

The Component Pitfall

Database Migration from non-CDB to PDB – The Time Zone Pitfall

Database Migration from non-CDB to PDB – The COMPATIBLE pitfall

High Level Overview

High Level Overview

High Level Overview

Archives

Categories

Upcoming Events

Upgrade @YouTube

Blog Stats

Subscribe via Email

Follow me on Twitter

Recent Posts