Unified Auditing policies can slow down the upgrade

I posted several blog posts about Unified Auditing in the past. And recently a large customer in the UK alerted me about their upgrades with PDBs from 12.1 to 12.2 taking much longer when Unified Auditing policies were enabled. It looks like as if Unified Auditing policies can slow down the upgrade. We did some further investigations. See the results below. Unified Auditing You find a good number of blog posts explaining how to migrate to Unified Auditing, the performance tweaks in Oracle 12.1.0.2 and some other things: Unified Auditing – Is it ON or OFF in Oracle 12.2.0.1? Unified…

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

Oracle 18.3.0 Multitenant: Compatibility Check does not work

When 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. Interestingly this…

Drop a tablespace in a PDB with a Guaranteed Restore Point being active

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

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…

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…

Swiss Mobiliar Boosts Performance by Upgrading its Oracle Databases

You may have read already some stories about our successful collaboration with Switzerland’s most trusted insurer, Swiss Mobiliar Insurance on this blog. But today I can add an official reference story in 3 languages about how Swiss Mobiliar Boosts Performance by Upgrading its Oracle Databases. Reference Story: Mobiliar’s Upgrade Project And I’m very excited to link the official reference story about Mobiliar’s Upgrade and Multitenant project available in 3 languages: Swiss Mobiliar Boosts Performance by Upgrading its Oracle Databases (English) „Die Mobiliar“ steigert Performance durch Upgrades ihrer Oracle Datenbanken (German) La Mobilière améliore la performance en actualisant ses bases de…

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

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…

Fallback Strategy: Flashback to Guaranteed Restore Points

You can attempt an Oracle database upgrade without having a fallback strategy. Of course you can. If you are brave enough. But I would recommend you this Fallback Strategy: Flashback to Guaranteed Restore Points. It is simple, easy, fast. And yes, it is an Enterprise Edition only feature. I have blogged about it in the past already but I realized it need some refresh. Fallback Strategy: Flashback to Guaranteed Restore Points One of the fastest way to revert from a big change (i.e. a database upgrade, an application upgrade, etc) is Flashback Database to a previously created Guaranted Restore Point….

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…

Why EXCLUDE_SEED_CDB_VIEW is now an underscore in Oracle 12.2

I 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 not a big fan…

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…

Issue with PDB Archives in Oracle 12.2.0.1 in ASM

There is a fancy new command to unplug a PDB in Oracle Database 12.2.0.1: ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO ‘pdb1.pdb’; The nice thing with this command differing in the file ending of ‘pdb‘ instead of ‘xml‘ as you used it in Oracle 12.1 (and the ‘xml‘ option is still available of course): Instead of just creating an xml description file it zips everything together into a PDB archive. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ———- —————————— ———- ———- 2 PDB$SEED READ ONLY NO SQL> create pluggable database PDB1 admin user adm identified by adm 2 file_name_convert=(‘/u02/oradata/CDB2/pdbseed’,’/u02/oradata/CDB2/pdb1′); Pluggable…

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. Restarting a failed Database Upgrade with catctl.pl 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 C =…

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

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: New SPFILE parameters in Oracle Database 12.2.0.1 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 well? None…

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

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…

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 only to change the…

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

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: Everything at Once One at a Time — via unplug/plug 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: MOS Note:2102849.1 Unplug/Plug on different PSU environments on 12.1.0.2…

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…