Skip to main content

Multitenant CDB views trick

In this post, let's look at the new dictionary views added by multitenant: the CDB views and how they actually work.
With the advent of Oracle 12c, and the multitenant database, one more layer of naming has been added. It is no longer enough to specify just the schema and table as a unique identifier, and that new required layer is, of course, the PDB.

Oracle has decided to keep things much the same for a user connecting to a PDB; although a DBA can see some differences (e.g. redo), a user (or an application) can be virtually oblivious to this new multi-tenancy functionality.

One of the consequences is that Oracle decided to have all DBA (plus ALL and USER) views show only the current PDB. So to show data from all PDBs a new family of views has been introduced; the CDB views. Generally speaking then, for any DBA view showing all objects in a PDB, there is an associated CDB view that displays this information globally, across all PDBs.

CDB$VIEW

In 12.1.0.1, Oracle introduced a function called CDB$VIEW and the query for CDB_OBJECTS looks like this:

SELECT ..., CON_ID FROM CDB$VIEW("SYS"."DBA_OBJECTS")

This special function works similar to GV$ views in that it runs a parallel query to query all the PDBs, and then union all the results together. It also adds CON_ID to show which PDB each row comes from.
It actually queries all the containers, including CDB$ROOT and PDB$SEED:
select con_id, count(*) from cdb_data_files group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         2          2
         1          4
         4          3
         3          5

The function is not documented, and although you can try using it, you will run into strange behavior. If you use a non-global user, you can end up killing the server process:

select * from cdb$view("SCOTT"."EMP")
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xA] [PC:0xB83E308, kglhdgn()+120] [flags: 0x0, count: 1
kglhdgn()+120        signal   __sighandler()       7F5B34766C40 ?
kglrdti()+238        call     kglhdgn()            7F5B34766C40 ?
qcdlgbo()+5243       call     kglrdti()            7F5B34766C40 ?

And if you use global user (as you should), you run into another problem: the table must exist in all containers, including PDB$SEED.
So if it is not in CDB$ROOT, the query fails immediately:
select * from cdb$view("SYSTEM"."TEST1")
 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

But if you create this in root, you are still missing the PDB$SEED:
select * from cdb$view("SYSTEM"."TEST1")
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P001
ORA-00942: table or view does not exist

And to modify PDB$SEED, you have to open it read-write... which is scary and not something I would recommend!:
alter session set "_oracle_script"=TRUE;

alter pluggable database pdb$seed close immediate;
alter pluggable database pdb$seed open read write;
...
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open read only;
alter session set "_oracle_script"=FALSE;

Fortunately, Oracle is clever enough to run the PX slaves only for those that are really needed - so specifying CON_ID, you can get around the PDB$SEED issue.

select * from cdb$view("SYSTEM"."TEST1") where con_id in (3,4);
D CON_ID
- ----------
X          3
X          4

However, you still need the CDB$ROOT copy though, so that Oracle knows the table definition. And, of course, the table definition must be the same in all PDBs, otherwise you get errors about missing columns - or something more obtuse.
select * from cdb$view("SYSTEM"."TEST1") where con_id in (4)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-00904: "DUMMY": invalid identifier

CONTAINERS

12.1.0.2 changed this functionality, so that it now uses a CONTAINERS() clause:
SELECT ..., CON_ID FROM CONTAINERS("SYS"."DBA_OBJECTS")

The is very similar to the CDB$VIEW functionality, but the biggest 'improvement' is that it is now a documented and supported option, which you can safely use on your own tables. But note that there are still some quirks regarding the ownership, meaning it must be owned by the same common (=CDB level) user.

Also one noteworthy point is that the clause now excludes PDB$SEED by default. While this is usually a good thing (you don't want to bother with tables nobody can actually use), it can sting you when querying CDB_DATA_FILES. For example, it no longer shows all the files of a database, although there is a parameter for this; set exclude_seed_cdb_view: i.e:
select * from containers("SYSTEM"."TEST1")
SQL> select con_id, count(*) from cdb_data_files group by con_id;

select con_id, count(*) from cdb_data_files group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         1          4
         3          3
         4          3

As for other changes covered by what we have discussed above, not much is different. Using a non-global user is still prohibited, but at least there is a proper error message to report this now:

select * from containers("SCOTT"."EMP")
                                 *
ERROR at line 1:
ORA-00942: table or view does not exist

And because it does not query PDB$SEED, one does not need to use the CON_ID predicate to exclude the PDB$SEED from the query.

Comments

Popular posts from this blog

ORA-27048: skgfifi: file header information is invalid

I was asked to analyze a situation, when an attempt to recover a 11g (standby) database resulted in bunch of "ORA-27048: skgfifi: file header information is invalid" errors. I tried to reproduce the error on my test system, using different versions (EE, SE, 11.1.0.6, 11.1.0.7), but to no avail. Fortunately, I finally got to the failing system: SQL> recover standby database; ORA-00279: change 9614132 generated at 11/27/2009 17:59:06 needed for thread 1 ORA-00289: suggestion : /u01/flash_recovery_area/T1/archivelog/2009_11_27/o1_mf_1_208_%u_.arc ORA-27048: skgfifi: file header information is invalid ORA-27048: skgfifi: file header information is invalid ORA-27048: skgfifi: file header information is invalid ORA-27048: skgfifi: file header information is invalid ORA-27048: skgfifi: file header information is invalid ORA-27048: skgfifi: file header information is invalid ORA-00280: change 9614132 for thread 1 is in sequence #208 Interestingly, nothing interesting is written to

Multitenant and standby: recover from subsetting

In the previous post we learnt how to exclude a PDB (or a datafile) from the standby database recovery. Of course, that might not be the real end goal. We may just want to skip it for now, but have the standby continue to be up-to-date for every other PDB, and eventually include the new PDB as well. Again, standard Oracle pre-12c DBA knowledge is helpful here. These files are just missing datafiles and thus a backup can be used to restore them. The new 12c features add some quirks to this process, but the base is just sound backup and recovery. Backup So let's start with a proper backup: rman target=/ Recovery Manager: Release 12.1.0.2.0 - Production on Mon Nov 16 12:42:38 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. backup database; connected to target database: CDB2 (DBID=600824249) Starting backup at 16-NOV-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=193

Multitenant and standby: subsetting

In the previous post we looked at managing new PDBs added to a standby database, by copying the files to the DR server, as required. However, there is another possible approach, and that is to omit the PDB from the standby configuration altogether. There are two ways of achieving this: 1. Do it the old-school way. A long time before 12c arrived on the scene one could offline a datafile on the standby database to remove it. The same trick is used in TSPITR (tablespace point-in-time recovery), so that you don't need to restore and recover the entire database if you are only after some tablespaces. 2. 12.1.0.2 adds the option to automatically exclude the PDB from standby(s). And 12.2 adds the option to be more specific in case of multiple standbys. For the sake of curiosity I started by setting standby file management to manual again. What I found is that there was very little difference, and the steps to take are exactly the same - it’s just the error message that is slightly