Single-/Multitenant

Can I restart a failed Multitenant Upgrade as well?

A while back I did blog about the new -R option of the parallel upgrade tool catctl.pl in Oracle Database 12.2.

And in case you will do a real Multitenant upgrade and fail – as it happened to me today due to “no space left on device” (no audit files could be written anymore) I tried the -R option as well based on Joe’s (our lead catctl.pl developer) recommendation:

$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -R -l /home/oracle/mike2 catupgrd.sql

Argument list for [catctl.pl]
Run in                c = 0
Do not run in         
Continue reading...

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

I did blog about this topic a while back.

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

SGA_MIN_SIZE in Oracle Database 12.2

I did summarize all new init.ora/spfile parameters in Oracle Database 12.2.0.1 a while ago:

And one caught Roy’s and my attention: SGA_MIN_SIZE.

When we present this new parameter as part of the Multitenant slides in our workshops, some people ask themselves:
“What happens if I oversubscribe such a parameter?” For example, if SGA_TARGET=4G and I have 10 PDBs and define in each of them SGA_MIN_SIZE=500M. Will the 9th and 10th PDB not startup anymore? Or will the 7th PDB already in trouble as the CDB$ROOT will need some SGA portion as …

Continue reading...

Upgrade to Oracle Database 12.2 and Converting it into an 12.2 PDB (plugin)

2 customers requested a bit more explanation on how to:
Convert and Plugin an Oracle 12.1 non-CDB database into an Oracle 12.2 container database

Cool, I like that 🙂 I will do this right away and use the approach to Upgrade first, then Plugin afterwards.

Upgrading an Oracle 12.1.0.2 database to Oracle 12.2.0.1

First the preupgrade.jar must be executed – please download always the most recent version from MOS Note:884522.1 as the version we publish is usually newer including important improvements (and fixes) than the version you’ll get with the release drop. We are working on including always the …

Continue reading...

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 …

Continue reading...

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

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 …

Continue reading...

APEX is in CDB$ROOT again – Journey to the Cloud VII

Well … it’s been a while … but I would like to continue my journey to the cloud …

What happened so far on my Journey to the Cloud?

DBaaS Oracle Cloud

Continue reading...

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 …

Continue reading...

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

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:

Continue reading...

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

Continue reading...

Why you should remove APEX from the CDB$ROOT

Upgrade Blog posts about Oracle Application Express:


Oracle APEX (Application Express) is great piece of software. But it gets installed by default into the container database’s CDB$ROOT unless you’d customized your CDB creation via scripts in Oracle 12.1.0.2. See:

on how to customize a Single/Multitenant Database with less options.

But that is not the topic I …

Continue reading...

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

Continue reading...

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 …

Continue reading...

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/

Unplug Plug Upgrade PDB Oracle Mutitenant

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 …

Continue reading...

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 …

Continue reading...

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 …

Continue reading...

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 …

Continue reading...

Oracle In-Memory Advisor with Oracle Multitenant? Issues?

Do you want to use the Oracle In-Memory Advisor in an Oracle Multitenant environment?

This is possible but with the current version it will require a workaround. The next available version of the IM Advisor is designated to support it without any workarounds.

At the moment the IM Advisor does not install into the CDB$ROOT container. But it can be installed into any PDB.

  • Create the user IMADVISOR in the PDB as local user first
  • No objects must be placed into the IMADVISOR schema but the user must have a default and a temporary tablespace
  • Install the
Continue reading...

Oracle Multitenant: New SQL Container Clause

Tiny little enhancement in Oracle Database 12.1.0.2:
The new CONTAINER clause to access data from different containers within one SQL statement. This may be very helpful, especially in case of schema consolidation. Similar things could have be done in Oracle 12.1.0.1 already by using database links – but resulting in way more complicated SQLs.

This is the new clause:

SELECT ename FROM CONTAINERS(scott.emp) WHERE CON_ID IN (45, 49);

See the documentation for more info about it.

–Mike…

Continue reading...

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 …

Continue reading...

Non-CDB architecture of Oracle databases is DEPRECATED since Oracle Database 12.1.0.2

Please find an updated blog post here:

as the text in the Database Upgrade Guide has been changed as well.


non-CDB deprecated in Oracle 12.1.0.2

What does this mean?

Deprecation first of all does not mean “desupported”. It means in this case that you of course can have still stand-alone Oracle databases as you know the architecture from previous releases. But you can also have a single-tenant deployment (a CDB with one PDB – no Multitenant license required) or multitenant databases (a CDB with up to 252 PDBs – Multitenant Option license required).…

Continue reading...

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

Continue reading...

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 …

Continue reading...