Lost AWR snapshots reappear again in Oracle 12.2 in Multitenant

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

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

At 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. The upgrade runs fine. But…

Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2

Oracle Label Security (OLS) clean up in Oracle Database 11.2-12.2 is almost as simple and straight forward as removing OWM or APEX. You can do it online without downtime. But in older releases a relink operation did cause downtime. Oracle Label Security is an extra cost option. More information is available on oracle.com. Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2 Oracle Label Security is dependent on Oracle Database Vault (DV). In case DV is present you must remove it first if you plan to remove Oracle Label Security. Before you start removing anything from your database please…

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…

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

I did blog about this topic a while back. Upgrade PDBs – Everything At Once (Full CDB Upgrade) 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 ———-…

Hands-On Lab available: Upgrade to Oracle Database 12.2.0.1

Thanks to Roy – our Hands-On Lab “Upgrade, Migrate and Consolidate to Oracle Database 12.2” is now available for download from OTN. Download Hands-On Lab “Upgrade, Migrate and Consolidate to Oracle Database 12.2” Download the Hands-On Lab Instructions as PDF Instructions and technical requirements are clearly documented on the OTN page as well. The lab is based on Virtual Box and can be easily imported upon download and unzip. You can always access the lab via the blog’s top menu as well: Inside the lab you’ll find : UPGR – an Oracle 11.2.0.4 database ready to be upgraded to Oracle…

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…

Upgrade to Oracle Database 12.2 – New MOS Notes

Some very helpful MOS Notes got published regarding the upgrade to Oracle Database 12.2.0.1: MOS Note: 2173141.1 Complete Checklist for Manual Upgrades to non-CDB Oracle Database 12.2 MOS Note: 2173144.1 Complete Checklist for Manual Upgrade for Multitenant Architecture Oracle Databases from 12.1. to 12.2. MOS Note: 2189854.1 Complete Checklist for Upgrading to Oracle Database 12.2 using DBUA Furthermore check regularly for Known Issues and Alerts – currently the note has no content but exists already: MOS Note: 2239820.1 12.2.0.1 Base Release – Availability and Known Issues Cheers –Mike

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   …

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 past 3-4…