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…
Category Archives: Single-/Multitenant
Be aware of database options selections for PDBs in DBCA
I did blog about several things-to-know with DBCA (Database Configuration Assistant) in the past. And one issue came up in our internal mailing list the other day. Be aware of database options selections for PDBs in DBCA. Missing one or the other “click” may bring you in trouble later on. I blogged about this already a while ago but this was only part of a larger post: Create Multitenant Databases with DBCA – Things to Know. Be aware of database options selections for PDBs in DBCA When you create a custom container database with DBCA you will see this screen….
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…
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…
Can you simply switch from SE2 to EE with Oracle Multitenant
This question is coming up from time to time – and currently not covered in the official Oracle documentation as far as I know: “Can you simply switch from SE2 to EE with Oracle Multitenant?” Yes, you can! First of all, the switch is possible and tested. But you’ll have to satisfy a few requirements: Both database versions must be the same Both databases must have the same character set (in Oracle Database 12.1) That’s it. Simply unplug your Standard Edition 2 PDB and plug it into your Enterprise Edition CDB. In addition you may have to run several component…
Why does the upgrade of PDB$SEED always take longer?
Upgrading the PDB$SEED takes always longer than any other container. What is the reason for an upgrade of the PDB$SEED taking so much longer in a real world environment? How does an upgrade with Oracle Multitenant work? I have written several blog posts in the past about the different techniques to upgrade an Oracle Multitenant environment: Upgrade Everything At Once Upgrade via Unplug/Plug/Upgrade The interesting case happens in the “Everything At Once“. The PDB$SEED takes significantly longer to upgrade than any other container. Upgrading the PDB$SEED takes always longer? Quick recap: The PDB$SEED is the (I call it) blueprint PDB…
Uninstalling OWM from a Multitenant container database
Caution Let me put a warning first: The intention of this blog post is NOT to recommend to uninstall anything just “because”. It is meant only as a guidance and explanation how to uninstall a component in an Oracle Multitenant container database deployment correctly without braking anything. And as this is not documented yet clearly, I bring up this blog post. Oracle Workspace Manager Oracle Workspace Manager (OWM) is a pretty cool feature of the Oracle Database. It does not require an extra license. OWM is meant to enable application developers and DBAs to manage current, proposed and historical versions…
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…
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 newest…
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…
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 …
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…