I would like to gather here in this document some specific traits of the Container Databases of Oracle 12c (Multitenancy) – the traits the are not broadly known. The list will be extended when some interesting characteristic of CDBs/PDBs pops up.
RMAN Pluggable Database Recovery – Loss of system datafile from PDB
The Container Database and all other PDBs are unaffected by the loss of datafile in the specific PDB, but not when the datafile is a SYSTEM datafile of the PDB. Loss of a SYSTEM datafile of a PDB is as critical as loss of a SYSTEM datafile of CDB/non-CDB, i.e. this will may lead to unpredictable behaviour, mostly crash the entire CDB (i.e. all PDBs will be unavailable).
You can read more about it at Franck Pachot’s blog here:
The only possible approach at the moment is that you need to restore/recover this SYSTEM datafile of PDB in MOUNT state of CDB. This behaviour will be enhanced in future releases, i.e., loss of SYSTEM datafile of PDB will NOT crash the CDB or other PDBs.
Read more about it in the Note: RMAN Pluggable Database Backup and Recovery in a Multitenant Environment (Doc ID 1521005.1)
PDB parameters in Data Guard configuration:
In 18.104.22.168, the ALTER SYSTEM SCOPE=SPFILE is disallowed on standby database because the standby DB is not opened in read-write mode and as such changes to the root’s PDB_SPFILE$ table are not allowed. In order to provide a way to solve the above limitation, there is a syntax enhancement in 22.214.171.124 to execute an ALTER SYSTEM SET statement with a DB_UNIQUE_NAME clause.
The DB_UNIQUE_NAME clause in the “ALTER SYSTEM SET/RESET …” is expected not be documented in 12.1.
This clause allows modification of the system parameter with SCOPE=SPFILE inside the PDB on the primary
database, but you can specify the db_unique_name of the standby database where the parameter value should be modified. The DML gets performed on the primary when you change the PDB parameter and it is propagated to standby database.
Querying User-Created Tables and Views Across All PDBs: (added 15.07.2016)
This feature is available starting with Oracle Database 12c Release 1 (126.96.36.199). The CONTAINERS clause enables you to query user-created tables and views across all PDBs in a CDB. This clause enables queries from the root to display data in tables or views that exist in all of the open PDBs in a CDB.
The following prerequisites must be met:
– The tables and views, or synonyms of them, specified in the CONTAINERS clause must exist in the root and in all PDBs.
– Each table and view specified in the CONTAINERS clause must be owned by the common user issuing the statement. When a synonym is specified in the CONTAINERS clause, the synonym must resolve to a table or a view owned by the common user issuing the statement.
To view data in multiple PDBs, ensure that the current container is the root.
Some live examples:
First let’s create a common user in both the CDB and PDB. We need to grant required privileges.
SYS@CDB> create user c##test_user identified by *********; User created. SYS@CDB> grant create session to c##test_user container=all; Grant succeeded. SYS@CDB> grant create table to c##test_user container=all; Grant succeeded. SYS@CDB> alter user c##test_user quota unlimited on test container=all; User altered. SYS@CDB> alter user C##TEST_USER default tablespace test container=all; User altered. SYS@CDB> select default_tablespace,con_id from cdb_users where username='C##TEST_USER'; DEFAULT_TABLESPACE CON_ID ------------------------------ ---------- TEST 1 TEST 3
Now we connect as C##TEST_USER to the CDB and we create table TEST with one row.
[email@example.com ~]$ sqlplus c##test_user/********* C##TEST_USER@CDB> create table test(message varchar(19)); Table created. C##TEST_USER@CDB> insert into test values('Message in CDB'); 1 row created. C##TEST_USER@CDB> commit; Commit complete. C##TEST_USER@CDB> select * from test; MESSAGE ------------------- Message in CDB
Now we connect as C##TEST_USER to the PDB instead and perform the same steps.
[firstname.lastname@example.org ~]$ sqlplus c##test_user/*********@PDB C##TEST_USER@PDB> create table test(message varchar(19)); Table created. C##TEST_USER@PDB> insert into test values('Message in PDB'); 1 row created. C##TEST_USER@PDB> commit; Commit complete. C##TEST_USER@PDB> select * from test; MESSAGE ------------------- Message in PDB
Let’s see if we can now query all the containers using containers() clause.
C##TEST_USER@CDB> select * from containers(TEST); MESSAGE CON_ID ------------------- ---------- Message in CDB 1 Message in PDB 3
Yup it works!!
What about PDB itself?
C##TEST_USER@PDB> select * from containers(TEST); MESSAGE CON_ID ------------------- ---------- Message in PDB 3
Not visible as expected, only local can be viewed.
Read more about it in the official documentation:
Flash Data Archive is available without license if no compression used
As found on Tim Hall’s oracle-base.com:
“In previous releases, flashback data archive used compression features from the advanced compression option, so it could only be used with enterprise edition and the advanced compression option. In Oracle 12c, the default when creating flashback data archives is no compression, so it is available for free in all editions of the database. This new default setting has also been back-ported to 188.8.131.52, making it free in all editions that can be patched to that level.”
You can read more about it on Tim’s page here:
Official doc “Oracle Database Licensing Information User Manual” states: “Basic Flashback Data Archive is in all editions. Optimization for Flashback Data Archive requires EE and the Advanced Compression option.”
Oracle Database Development Guide states also:
“You can enable optimization of data storage for history tables maintained by Flashback Data Archive by specifying OPTIMIZE DATA when creating or altering a Flashback Data Archive.”
OPTIMIZE DATA optimizes the storage of data in history tables by using any of these features:
– Advanced Row Compression
– Advanced LOB Compression
– Advanced LOB Deduplication
– Segment-level compression tiering
– Row-level compression tiering
The default is not to optimize the storage of data in history tables.
About the author
An Oracle technology geek and crazy long distance runner, DBA24 Owner
Senior Oracle DBA / Consultant / [OCP10g, OCP12c, OCE RAC 10g] / [experience: 9y+]
Currently working for Bluegarden (Oslo Norway) by Miratech Group
Past: Mastercard / Trevica by Britenet, Citi International PLC, PZU