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/12.1.0.2/dbs/UNNAMED00010' 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:
'/oradata2/CDB2SBY/pdbfrompdb/system01.dbf'
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'
ORA-283 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE...
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');
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01274: cannot add data file that was originally created as
'/oradata/CDB2/pdbfrompdbcopy/system01.dbf'
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.

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 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 slow.direct 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…

Filter and access predicates

More than just column projections When we look around for further pointers in the tree nodes, we find more pieces resembling the column projections we have seen so far. With some experimenting, we will find out that these are access predicates and filters.

First of all, the location of these pointers is not always the same, it seems that the value at 0x34 is some kind of flag, indicating whether filters and/or access predicates are present, and where. Or whether there is just one, or more of them.  It probably also indicates what other info is present, but I have no idea what info that would be or what each value means.
Resembling, but different The data we see as predicates are not columns; after all, a predicate is a condition, not a single column. But the structure is similar to what we have seen with columns, and if we follow pointers further, we eventually build a tree, and some of the leaves are indeed just column projections.
After some contemplation, we realize it's all t…