Oracle PDBs in a Data Guard environment

Sven Illert -

Oracle introduced it’s container based database configuration with Oracle 12.1, which dates back to the year of 2013. So it’s ten years in the wild and the adoption process is still in progress as not all of my customers have migrated to the new architecture yet, even if is enforced with the upcoming 23c release. Although this architectural change has brought a variety of useful features that make the daily business for an DBA a lot easier, there are some problems in combination with Data Guard.

What works out of the box?

First let’s check what does work out of the box when using PDBs in the most easy fashion like in creating one from a PDB$SEED. Dropping and modifying a PDB is not that of a problem because those are operations that can easily be handled by the automatic standby file management when the standby database is in mounted state. Let’s have a look at the following configuration.

SQL> -- PRIMARY
SQL> show pdbs

   CON_ID    CON_NAME    OPEN MODE    RESTRICTED
_________ ___________ ____________ _____________
        2 PDB$SEED    READ ONLY    NO

SQL> show parameter standby_file_management
NAME                    TYPE   VALUE
----------------------- ------ -----
standby_file_management string AUTO

SQL> -- STANDBY
SQL> show pdbs

   CON_ID    CON_NAME    OPEN MODE    RESTRICTED
_________ ___________ ____________ _____________
        2 PDB$SEED    READ ONLY    NO

SQL> show parameter standby_file_management
NAME                    TYPE   VALUE
----------------------- ------ -----
standby_file_management string AUTO

Now let’s create a pluggable database and add some services for it to be able to connect. Also with a service managed by Grid Infrastructure, there’s no need to save the state of a pluggable database, since it will be opened when the service starts.

SQL> create pluggable database PDB01SI01 admin user pdb_admin identified by oracle;

Pluggable database PDB01SI01 created.

[oracle@vm101 ~]$ srvctl add service -db $ORACLE_UNQNAME -pdb PDB01SI01 -service SVC_PDB01SI01 -role primary
[oracle@vm101 ~]$ srvctl start service -db $ORACLE_UNQNAME -service SVC_PDB01SI01
[oracle@vm105 ~]$ srvctl add service -db $ORACLE_UNQNAME -pdb PDB01SI01 -service SVC_PDB01SI01 -role primary

As you would expect the PDB is created on the primary and standby site.

SQL> -- PRIMARY
SQL> show pdbs

   CON_ID     CON_NAME     OPEN MODE    RESTRICTED
_________ ____________ _____________ _____________
        2 PDB$SEED     READ ONLY     NO
        3 PDB01SI01    READ WRITE    NO

SQL> -- STANDBY
SQL> show pdbs

   CON_ID     CON_NAME    OPEN MODE    RESTRICTED
_________ ____________ ____________ _____________
        2 PDB$SEED     MOUNTED
        3 PDB01SI01    MOUNTED

For the sake of completeness of the test we should perform a switchover operation to make sure that everything is working as expected.

DGMGRL> show configuration;

...

SUCCESS   (status updated 42 seconds ago)

DGMGRL> switchover to si01v105;

...

Switchover succeeded, new primary is "si01v105"

SQL> -- NEW STANDBY
SQL> show pdbs;

   CON_ID     CON_NAME    OPEN MODE    RESTRICTED
_________ ____________ ____________ _____________
        2 PDB$SEED     MOUNTED
        3 PDB01SI01    MOUNTED

SQL> -- NEW PRIMARY
SQL> show pdbs

   CON_ID     CON_NAME     OPEN MODE    RESTRICTED
_________ ____________ _____________ _____________
        2 PDB$SEED     READ ONLY     NO
        3 PDB01SI01    READ WRITE    NO

OK, so Data Guard and multi tenant is really easy, isn’t it? It seems we could end here with this post, right?

Unplug, plug and switchover with failure

Of course I wouldn’t write about this topic if there weren’t problems buried somewhere. The majority of problems can be avoided when you have Active Data Guard licensed, because some of the operations need an open database.

To demonstrate one of the possible problems with PDB management let’s try to unplug a database and plug it into another container.

SQL> alter pluggable database PDB01SI01 close immediate;

Pluggable database PDB01SI01 altered

SQL> alter pluggable database PDB01SI01 unplug into '/tmp/PDB01SI01.xml';

Pluggable database PDB01SI01 altered.

SQL> drop pluggable database PDB01SI01 keep datafiles;

Pluggable database PDB01SI01 dropped.

[oracle@vm105 ~]$ srvctl remove service -db $ORACLE_UNQNAME -service SVC_PDB01SI01
[oracle@vm101 ~]$ srvctl remove service -db $ORACLE_UNQNAME -service SVC_PDB01SI01

Plug it into another CDB with a data guard database. With copy we can recreate the PDB after a failure.

SQL> create pluggable database PDB01SI02 using '/tmp/PDB01SI01.xml' copy;

Pluggable database PDB01SI02 created.

[oracle@vm105 ~]$ srvctl add service -db $ORACLE_UNQNAME -pdb PDB01SI02 -service SVC_PDB01SI02 -role primary
[oracle@vm105 ~]$ srvctl start service -db $ORACLE_UNQNAME -service SVC_PDB01SI02
[oracle@vm101 ~]$ srvctl add service -db $ORACLE_UNQNAME -pdb PDB01SI02 -service SVC_PDB01SI02 -role primary

Check PDBs in both databases.

SQL> -- PRIMARY
SQL> show pdbs;

   CON_ID     CON_NAME     OPEN MODE    RESTRICTED
_________ ____________ _____________ _____________
        2 PDB$SEED     READ ONLY     NO
        3 PDB01SI02    READ WRITE    NO

SQL> -- STANDBY
SQL> show pdbs

   CON_ID     CON_NAME    OPEN MODE    RESTRICTED
_________ ____________ ____________ _____________
        2 PDB$SEED     MOUNTED
        3 PDB01SI02    MOUNTED

Everything looks fine, so let’s switch, eh? Since this was a single instance configuration and it was running on the wrong node in a cluster, I relocated the DB first to the right node and wanted to check the Data Guard status. I discovered the following state.

DGMGRL> show configuration;

Configuration - si02

  Protection Mode: MaxPerformance
  Members:
  si02v105 - Primary database
    si02v100 - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 49 seconds ago)

The first action in this case was to check the alert log on the standby database.

2023-07-11 22:22:25.761000 +02:00
 Started logmerger process
PR00 (PID:1152038): Managed Standby Recovery starting Real Time Apply
Errors in file /u01/app/oracle/diag/rdbms/si02v100/SI02/trace/SI02_dbw0_1151696.trc:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form

OK, so what is datafile 8? Let’s check on the primary:

SQL> select file#, c.name, d.name PDB from v$datafile d, v$pdbs c where d.con_id=c.con_id and file#=8;

   FILE#          PDB                                                                              NAME
________ ____________ _________________________________________________________________________________
       8 PDB01SI02    +DATA/SI02V105/003BBAD8FC9C41AFE0636524100AA5EE/DATAFILE/system.340.1141942389

As we can see this file is part of the newly created PDB. At this point you should copy the datafiles from the source to the OMF destination for the standby database. We can use RMAN for that purpose on the standby location.

RMAN> connect target /
RMAN> recover standby database from service 'vm105/SI02V105';

This handles all the necessary copy, recover and rename stuff that’s needed to get the standby database running. Afterwards we have to reenable the database with the data guard broker.

DGMGRL> enable database si02v100;
Enabled.
DGMGRL> show configuration;

...

SUCCESS   (status updated 19 seconds ago)

OK, now let’s verify switchover works and possibly switch back. We have seen that it might be not that easy creating a PDB from an existing location. The reason is that the standby container must execute all the steps for the creation process equivalently to the primary. That might not be possible if, for example, the creation process refers to files that don’t exist on the standby site like in our case (the XML file refers to other filenames as on the standby, because of the usage of OMF).

DGMGRL> switchover to si02v100;
DGMGRL> switchover to si02v105;

Unplug, Plug and switchover with success

The previous attempt went with room for improvement. Let’s drop the pdb and try another method.

SQL> alter pluggable database PDB01SI02 close immediate;
SQL> drop pluggable database PDB01SI02 including datafiles;

Verify this on both sides and check data guard after some time using these commands.

SQL> show pdbs
DGMGRL> show configuration;
DGMGRL> switchover to si02v100;
DGMGRL> switchover to si02v105;

What if there was a way to enable the standby database to use the source pdb files on it’s own? Let’s see what we can find in the documentation … Oh! There’s a parameter called standby_pdb_source_file_directory. Maybe this will help us? Let’s try!

First we need to copy the data files of the source pdb to the standby machine.

$ for file in $(grep path /tmp/PDB01SI01.xml | cut -d'>' -f2 | cut -d'<' -f1); do asmcmd cp $file /u01/stage; done
copying +DATA/SI01V105/003BBAD8FC9C41AFE0636524100AA5EE/DATAFILE/system.336.1141938207 -> /u01/stage/system.336.1141938207
copying +DATA/SI01V105/003BBAD8FC9C41AFE0636524100AA5EE/DATAFILE/sysaux.337.1141938229 -> /u01/stage/sysaux.337.1141938229
copying +DATA/SI01V105/003BBAD8FC9C41AFE0636524100AA5EE/DATAFILE/undotbs1.338.1141938257 -> /u01/stage/undotbs1.338.1141938257
$ scp /u01/stage/* vm101:/u01/stage

Now let’s set the discovered parameter at the standby database and create the pluggable database again on the primary database.

SQL> -- STANDBY
SQL> alter system set standby_pdb_source_file_directory='/u01/stage';    

SQL> -- PRIMARY  
SQL> create pluggable database PDB01SI02 using '/tmp/PDB01SI01.xml' copy;

And now let’s have a look, what we see in the alert log of the standby database when we create the pluggable database the same way as in the previous example.

Recovery attempting to copy datafiles for pdb-PDB01SI02 from           source dir-/u01/stage
2023-07-13 20:40:16.429000 +02:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file +DATA/SI02V100/F27828A2C94754A9E0536624100AC310/DATAFILE/system.356.1142109589 from /u01/stage/system.336.1141938207
Successfully added datafile 11 to media recovery
Datafile #11: '+DATA/SI02V100/F27828A2C94754A9E0536624100AC310/DATAFILE/system.356.1142109589'
2023-07-13 20:40:43.943000 +02:00
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file +DATA/SI02V100/003BBAD8FC9C41AFE0636524100AA5EE/DATAFILE/sysaux.349.1142109617 from /u01/stage/sysaux.337.1141938229
Successfully added datafile 12 to media recovery
Datafile #12: '+DATA/SI02V100/003BBAD8FC9C41AFE0636524100AA5EE/DATAFILE/sysaux.349.1142109617'
2023-07-13 20:40:57.173000 +02:00
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file +DATA/SI02V100/003BBAD8FC9C41AFE0636524100AA5EE/DATAFILE/undotbs1.358.1142109645 from /u01/stage/undotbs1.338.1141938257
Successfully added datafile 13 to media recovery
Datafile #13: '+DATA/SI02V100/003BBAD8FC9C41AFE0636524100AA5EE/DATAFILE/undotbs1.358.1142109645'

This looks like we found a solution to the problem without recovery using RMAN. Hooray!

Unplug, Plug and switchover using PDB archive

But what about self-contained pdb archives? They become very handy if you want to copy everything in one single file.

SQL> alter pluggable database PDB01SI01 unplug into '/u01/stage/PDB01SI01.pdb';
SQL> drop pluggable database PDB01SI01 keep datafiles;
$ scp /u01/stage/PDB01SI01.pdb vm101:/u01/stage
SQL> create pluggable database PDB01SI02 using '/u01/stage/PDB01SI01.pdb' copy;

Let’s have a look into the standby alert log.

Recovery created pluggable database PDB01SI02
Recovery attempting to copy datafiles for pdb-PDB01SI02 from           source dir-/u01/stage
Errors in file /u01/app/oracle/diag/rdbms/si02v100/SI02/trace/SI02_pr00_1439299.trc:
ORA-01565: error in identifying file '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form
Recovery was unable to create the file as:
'+DATA'

Do we really have to unzip the files?

$ cd /u01/stage
$ unzip -q PDB01SI01.pdb

Let’s try again.

SQL> create pluggable database PDB01SI03 using '/u01/stage/PDB01SI01.pdb';

Let’s look into the alert log, if things got worse.

Recovery created pluggable database PDB01SI02
Recovery attempting to copy datafiles for pdb-PDB01SI02 from           source dir-/u01/stage
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1C8] [PC:0x978D0AF, kpdbfOMFFindFile()+287] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/si02v100/SI02/trace/SI02_pr00_1460589.trc  (incident=34045) (PDBNAME=CDB$ROOT):
ORA-07445: exception encountered: core dump [kpdbfOMFFindFile()+287] [SIGSEGV] [ADDR:0x1C8] [PC:0x978D0AF] [Address not mapped to object] []

Oops. So that does not seem to work. I have reproduced this with another database. What can we do? We could modify the XML description file to represent the extracted files. The archive remains extracted on the standby side in the same location, so that the datafiles are accessible.

$ cd /u01/stage
$ unzip -q PDB01SI01.pdb
$ mv u01/stage/PDB01SI01.xml .
$ sed "s#\+DATA/SI01V105/003BBAD8FC9C41AFE0636524100AA5EE/DATAFILE#/u01/stage#" -i PDB01SI01.xml
SQL> create pluggable database PDB01SI03 using '/u01/stage/PDB01SI01.xml' copy;

The outcome was quite disillusioning.

ORA-19505: failed to identify file "/u01/stage/system.339.1142110293"
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
19505. 00000 -  "failed to identify file \"%s\""
*Cause:    call to identify the file returned an error
*Action:   check additional messages, and check if the file exists.

So at the moment there seems to be no way to create a pluggable database from an archive. If you ever managed to create a standby PDB using an archive, let me know in the comments below.

Conclusion?

Above we have seen that there are several pitfalls when using PDBs in a Data Guard environment. One thing that is not described here but that obviously would also fail is creating a PDB using a network link. This would only succeed if you have Active Data Guard and the standby database can access the source pdb using the same host description specified in the db link as the primary database.

But is there a way to work around all that hassle that works in any way, independently of the process to create the PDB on the primary? Of course there is, but it need some work. The scheme would always be the same though.

The first step is to create the database with the standbys=none clause. This prevents the standby CDB to set the newly created standby PDB into recovery mode.

SQL> create pluggable database PDB01SI03 using '/u01/stage/PDB01SI01.pdb' standbys=none;
SQL> alter session set container=PDB01SI03;
SQL> select 'alter database datafile ' || file# || ' online;' from v$datafile;  -- Need that output later

Now on the standby database restore and recover the PDB, but disable Data Guard recovery before.

DGMGRL> edit database si03v105 set state = 'APPLY-OFF';

RMAN> run {
2> set newname for pluggable database PDB01SI03 to new;
6> restore pluggable database PDB01SI03 from service 'vm101/SI03V100';
7> switch datafile all;
8> switch tempfile all;
9> }
RMAN> recover database;

We need to enable recovery for the pdb, since it was disabled upon creation and online all datafiles that aren’t online now.

SQL> alter session set container=PDB01SI03;
SQL> alter pluggable database enable recovery;
SQL> alter database datafile 15 online;
SQL> alter database datafile 16 online;
SQL> alter database datafile 17 online;

Finally enable the recovery of the standby database again and that’s it!

DGMGRL> edit database si03v105 set state = 'APPLY-ON';