Skip to main content

Multitenant and standby databases

As you probably know, the Oracle 12c multitenant feature consists of a container database (CDB) and one or more pluggable databases (PDBs). Each PDB has a separate set of data files, however, a lot of other components are still shared.
The obvious one is the instance itself and, after all, that's a major selling point for this feature in the first place (i.e.; consolidation). But even at the database (=disk) level, a lot of things are common:

  • The dictionary is partially shared (linked) 
  • Redo logs contain changes for all PDBs and the CDB 
  • The undo is also shared (however, watch 12.2 for a possible change with this)

The nature of a shared (linked) dictionary and common redo logs imply that a standby (Data Guard) configuration is set up at the CDB level. One source CDB means one target CDB, with all PDBs going to the same single target CDB in a 1:1 relationship.

This problem is not completely new, having been introduced with transportable tablespaces in Oracle 8i – but the CDB is now potentially subject to more data movement than we have been used to before; PDBs can be created, unplugged, plugged, dropped.

In this post let's look at the more common, simple case, where you want the same operations to occur on the standby database as they did on the primary:

  • If you create a new PDB from PDB$SEED Oracle has all the information it needs and the standby will recover the database just fine.
  • If you create a new PDB as a clone of an existing (local) one, Oracle has the files it needs... but it won't copy it this for the standby, unless you are using the additional paid Advanced Data Guard option.
  • If you create new PDB from a different set of files (remote), you need to copy the files to the standby, too.

So let's try this and see:

1. From pdb$seed
First of all, one needs to realize that STANDBY_FILE_MANAGEMENT comes into play here - after all, it's just new datafiles being created:
create pluggable database pdbfromseed admin user pdbfromseeduser identified by oracle FILE_NAME_CONVERT=('/oradata/CDB2/pdbseed','/oradata/CDB2/pdbfromseed');

Recovery created pluggable database PDBFROMSEED
Wed Nov 11 20:48:20 2015
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /oradata2/CDB2SBY/pdbfromseed/system01.dbf from /oradata2/CDB2SBY/pdbseed/system01.dbf
File #10 added to control file as 'UNNAMED00010' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Not surprisingly, you can either manually rename the file or just set the parameter to auto (but that won't fix the current problem, it works for new operations only):
alter database rename  file '/u01/app/oracle/product/' to '/oradata2/CDB2SBY/pdbfromseed/system01.dbf';

Database altered.

alter system set standby_file_management=auto;

System altered.
And all is then well:
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /oradata2/CDB2SBY/pdbfromseed/sysaux01.dbf from /oradata2/CDB2SBY/pdbseed/sysaux01.dbf
Recovery created file /oradata2/CDB2SBY/pdbfromseed/sysaux01.dbf
Successfully added datafile 11 to media recovery
Datafile #11: '/oradata2/CDB2SBY/pdbfromseed/sysaux01.dbf'

2. So let's create a new PDB from an existing one:
create pluggable database pdbfrompdb from pdbfromseed file_name_convert=('/oradata/CDB2/pdbfromseed','/oradata/CDB2/pdbfrompdb'); 
Errors in file /oradata/diag/rdbms/cdb2sby/CDB2SBY/trace/CDB2SBY_pr00_7068.trc:
ORA-01565: error in identifying file '/oradata2/CDB2SBY/pdbfrompdb/system01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Recovery was unable to create the file as:
Errors with log /home/oracle/fra/CDB2SBY/archivelog/2015_11_11/o1_mf_1_763_c47c77xz_.arc
Wed Nov 11 21:07:27 2015
Media Recovery failed with error 1274
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.34).
Datafiles are recovered to a consistent state at change 2814907 but controlfile could be ahead of datafiles.
Wed Nov 11 21:07:27 2015
Errors in file /oradata/diag/rdbms/cdb2sby/CDB2SBY/trace/CDB2SBY_pr00_7068.trc:
ORA-00283: recovery session canceled due to errors
ORA-01274: cannot add data file that was originally created as '/oradata/CDB2/pdbfrompdb/system01.dbf'
So we need to copy the files:
oracle@localhost:/oradata2/CDB2SBY$ mkdir pdbfrompdb
oracle@localhost:/oradata2/CDB2SBY$ cp pdbfromseed/* pdbfrompdb/
And after restarting recovery, the standby is up again.

3. A similar thing will also happen when plugging in an unplugged PDB:
create pluggable database  pdbfrompdbcopy as clone using '/home/oracle/unplug_pdb.xml' file_name_convert=('/oradata/CDB2/pdbfrompdb','/oradata/CDB2/pdbfrompdbcopy');
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01274: cannot add data file that was originally created as
cp /oradata/CDB2/pdbfrompdb/* pdbfrompdbcopy/
Note that in #2 we could copy the files locally, because Oracle has the files of the cloned PDB (and ADG can thus do it easily, too). In this case, however, it's just some other PDB, so we have to copy the files from the PDBs source of origin.

Next time we will have a look at another option: omitting the files from standby.


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,,, 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 :
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 alert.log n…

Reading data from PGA and SGA

Overview For our investigation of execution plan as it is stored in memory, we need in the first place to be able to read the memory.

We have the options of
x$ksmmem, reading SGA using SQL. Personally I don't like it, it's cumbersome and SGA read: obviously reading SGA only; it's fast and easy to doread process memory: can read PGA, process stack - and since the processes do map the SGA, too, you can read it as well. Unfortunately ptrace sends signals to the processes and the process is paused when reading it, but so far all my reads were short and fast and the processes did not notice. Some OS configurations can prevent you from using ptrace (e.g. docker by default), google for CAP_SYS_PTRACE.gdb: using your favorite debugger, you can read memory as well. Useful when investigating. Direct SGA read I always considered direct SGA read of some dark magic, but the fundamentals are actually very easy. It still looks like sorcery when actually reading the Oracle in…

Reading execution plan from SGA and PGA - teaser

Some of you have seen my presentation about hidden parts of Oracle execution plans and how to access the plan in the memory directly and parse it. I presented it at OakTable World 2017  and it will be also presented in Wellington and Acukland this November.  You can download the presentation at

I realize that many people did not have the chance to attend... and that the slides need quite a lot of explaining, it's hard to understand this internals without more explanation.

Thus you can look forward to seeing a handful of blog posts, inspired by this presentation, and explain some of the aspects of this whole topic.

We will start with some basic tools; accessing SGA and PGA, which you may find useful for many other tasks, too. Then we will look at some details of the execution plan. This is not - and neither the presentation is - an exhaustive guide to the execution plan internals. It would be a multi-year project to t…