Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c

PDB_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 As others have written a lot already about PDB_PLUG_IN_VIOLATIONS I won’t repeat it but rather share the links with you: https://martincarstenbach.wordpress.com/2016/06/29/tales-from-the-field-potential-reasons-for-pdb-plug-in-violations-part-1/ https://martincarstenbach.wordpress.com/2016/07/14/tales-from-the-field-potential-reasons-for-pdb-plug-in-violations-part-2/ https://blog.dbi-services.com/12c-multitenant-internals-pdb_plug_in_violations/…

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

There’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). It is a Single Tenant environment with one PDB. If you need for information about how to…

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…

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. Cloning a PDB from Oracle 12.1 to Oracle 12.2 You can clone a non-CDB and make it a PDB but you need to run noncdb_to_pdb.sql afterwards. When I wrote this blog post, Oracle…

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…

Create Multitenant databases with DBCA – Things to Know

When 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: Always create databases as customer databases Use your own templates in DBCA when you create databases Does DBCA execute datapatch in Oracle Database 12.2? Create Container databases with less options And there…

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 Pluggable Database

Since 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 Actually 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 Guaranteed…

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…