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…

A few thoughts about OCM 12c upgrade

Yesterday I sat for the 12c OCM upgrade exam, which I mentioned in few blog posts before. The first step after checking your ID is of course signing the NDA, and thus you won't find much real information here.

This time I chose Utrecht as the place to take the exam. Not that I have any special preference, I took each of the exams in a different place so far. The only requirements were convenient time and location defined as 'somewhere in Europe'. But in the end, Utrecht turned out to be a good place. Oracle NL headquarters are easy accessible, it's a very new building, the lunch was good:-)
And the city is nice to see.

Regarding the exam, the usual important notes still hold true:

Arrive on time. It's a long day and you will have a lot of things to do.You will work hard the whole day. Get a good sleep before, be well rested.Review the exam topics well. Note that they may have change over time. There is for example an update as of January 1, 2016: Flex ASM was added…