An initiative of Mobiliar IT

Oracle Multitenant 12.2 – the big challenge of PDB hot cloning in MAA environments

One of Oracle’s key features introduced with Multitenant 12.2 is the possibility to clone pluggable databases while they are open for read / write operations. This is really a great feature – and as long as you work in a standalone container database it works perfect. In this blog entry I will tell you the problems that you might probably get in MAA environments.

The key message for the usage of Oracle Multitenant is “consolidation”. Better consolidation means resource saving and finally cost saving.

Because of that, Mobiliar decided to use Oracle MT in the future and take advantage of saving resources.

In our case, the usage of Oracle MT means also, that we will put a lot of different business applications in the same database. So it quickly became clear that we had to use physical standby databases to guarantee the required availability.

As a MT reference customer we could try the new features already in the beta release.
We realized, that the usage of online cloning in MAA environments is a BIG PROBLEM!.

Let’s see what I mean:

You have two container databases in a physical standby configuration and start a hot clone from the primary database:

SQL> create pluggable database pdb02 from pdb1;

Pluggable database created.

SQL> alter pluggable database pdb02 open;

Pluggable database altered

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE 

---------- ------------------------------ ----------

         2 PDB$SEED                       READ ONLY

         3 MS_TEMPLATE_DO10MM90           READ WRITE

         4 PDB02                          READ WRITE

         6 PDB1                           READ WRITE

 

So on first sight, everything looks good. But if you check the alert log of the standby db,
you will find errors and realize that your standby database is corrupt and has to be recreated. 😦

Recovery created pluggable database PDB02
PDB02(4):File copy for ts-SYSTEM skipped since source is in r/w mode
2017-03-15T10:14:31.330319+01:00
PDB02(4):Errors in file /oradata/u00/app/oracle/diag/rdbms/do10mm98_01/do10mm98/trace/do10mm98_pr00_2938.trc:
ORA-01565: error in identifying file '+DATAFS'
ORA-17503: ksfdopn:2 Failed to open file +DATAFS
ORA-15045: ASM file name '+DATAFS' is not in reference form
PDB02(4):Recovery was unable to create the file as:
PDB02(4):'+DATAFS'
MRP0: Background Media Recovery terminated with error 1274
2017-03-15T10:14:31.364720+01:00
Errors in file /oradata/u00/app/oracle/diag/rdbms/do10mm98_01/do10mm98/trace/do10mm98_pr00_2938.trc:
ORA-01274: cannot add data file that was originally created as '+DATAFS/DO10MM99_01/4AC25F1C7F721BA3E0534EC10B0A18D3/DATAFILE/system.3065.938686469'
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
2017-03-15T10:14:31.531186+01:00
Errors in file /oradata/u00/app/oracle/diag/rdbms/do10mm98_01/do10mm98/trace/do10mm98_m000_8847.trc:
ORA-01110: data file 1: '+DATAFS/DO10MM98_01/DATAFILE/system.1693.938075093'
Recovery stopped due to failure in applying recovery marker (opcode 17.34).
Datafiles are recovered to a consistent state at change 112084723719 but controlfile could be ahead of datafiles.
2017-03-15T10:14:31.561940+01:00
Errors in file /oradata/u00/app/oracle/diag/rdbms/do10mm98_01/do10mm98/trace/do10mm98_pr00_2938.trc:
ORA-01274: cannot add data file that was originally created as '+DATAFS/DO10MM99_01/4AC25F1C7F721BA3E0534EC10B0A18D3/DATAFILE/system.3065.938686469'
2017-03-15T10:14:31.663943+01:00
MRP0: Background Media Recovery process shutdown (do10mm98)
2017-03-15T10:14:31.699339+01:00
Errors in file /oradata/u00/app/oracle/diag/rdbms/do10mm98_01/do10mm98/trace/do10mm98_m000_8847.trc:
ORA-01110: data file 13: '+DATAFS/DO10MM99_01/4AC25F1C7F721BA3E0534EC10B0A18D3/DATAFILE/system.3065.938686469'
Checker run found 1 new persistent data failures

But why does this happen?

The problem is, that Oracle has to manage two different redo streams: the redo, that is generated during the hot clone of the PDB, and the normal redo transport to the standby database.

Fact is: without manual tasks of the DBA read only clones are still only possible in data guard configurations!

Workaround

There is a note in Oracle support:

(1916648.1. Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant)

In this note you’ll find a step-by-step manual about the tasks that you have to follow in order to add the new created PDB on the standby site without corrupting the database. That works, but it’s more or less impossible to automize that task.

That was a big problem for us, because we implemented a DBAAS that will create and duplicate PDB’s automatically by using a REST API. So we needed a solution, that could be implemented in this automatization process.

So a few weeks ago the Multitenant product manager told me, that a new workaround exists.
This should make it possible to use it in a shell script. So of course I set up a new test environment J

The tasks that must be followed to add the new PDB on the standby database are still the same as described in MOS 1916648.1. But now Oracle does the most work for you.
Certain requirements must be fulfilled:

  • You need a database link from the standby to the primary database. Via this link, Oracle will copy the data files. You can’t create a database link on a standby database, so you have to create it on the primary database – and the redo transfer will do the rest for you.
sql> create db link my_copy_link connect to system identified by xxx using ’prim_db’;
  • Test connection from standby to primary database
sql> select * from dual@my_copy_link;

So here I ran into a big problem: How can I use a DB Link on a physical standby CDB that is only mounted? In order to use a db link, the CDB has to be opened in read only. To do that, you need active DG licensing. So I talked to the product management again and got the following answer:

 

“Because these operations require init parameters to be set and the physical standby to be  opened read only, licensing and feature usage tracking have been modified not to incur Active Data Guard licensing costs/usage tracking when the CDB$ROOT standby is open read only. ADG licensing will only occur and be tracked when a PDB on the standby is opened read only. Licensing documentation will be updated to reflect this change.”

 

  • So now we have a db link that works, because we can open the db in read only mode.
    The second thing that we have to do is to tell Oracle that it should use this link to copy the files from primary to standby database. This happens with an additional init.ora parameter:

standby_pdb_source_file_dblink

To get this parameter you must install the interim patch:

21252050: CREATE PDB FROM .XML DOES NOT PROPAGATE PDB FILES TO STANDB

After the patch is applied on the oracle home you can save the db link in the spfile

sql> alter system set standby_pdb_source_file_dblink=’my_copy_link’;

So the db link has to be created once in every CDB and afterwards you are ready to make hot clones in MAA environments without corrupting the standby database.

Step by Step Manual:

-- Create a temporary hot clone image TEMP from SOURCE without redo apply on standby

sql> create pluggable database TEMP from SOURCE standbys=none;

-- Open the cloned image to ‘instantiate’ the clone

sql> alter pluggable database TEMP open;

-- Need to close and open TEMP read only

sql> alter pluggable database TEMP close;

sql> alter pluggable database TEMP open read only;

-- Create the definite Database with the correct name and drop the temporary pdb TEMP

sql> create pluggable database MYPDB from TEMP;

sql> alter pluggable database MYPDB open;

sql> close pluggable database TEMP;

sql> drop pluggable database TEMP including datafiles;

Summary

This workaround might still not be THE favorite solution. But because Oracle will manage the copy of the data files between primary and standby CDB, we now at least have a solution that works in our automatized DBAAS environment.

So thanks to Oracle and especially to the MT product management for supporting us in solving this problem. I will update the post, if something will change about the procedure to create hot clones in MAA environments.


				

2 Responses to “Oracle Multitenant 12.2 – the big challenge of PDB hot cloning in MAA environments”

  1. Peter

    Where can I found the refreshed ADG license guide? You are sure that the ADG usage is only triggered, if you only open the root container ro and leave the PDBs closed. I can’t belive that! But it would be nice..Are there other requirements has to meet for that exception?

    Like

    Reply
    • Alain Fuhrer

      Hi Peter
      I asked again John McHugh from Oracle. He is the Senior Principal Product Manager – Oracle Multitenant and i get the following answer from him

      “FAQ:

      What are the licensing implications when opening a Physical Standby read-only for Multitenant PDB automated plug-in or cloning file copy operations to the standby?
      Customers are allowed to open the Physical Standby CDB$ROOT read-only without an Active Data Guard license in order to use the automated file copy functionality for PDB cloning and plug-in operations where the PDB files need to be copied to the standby. The Active DataGuard license is required when a PDB on the Physical Standby is opened read only. This is tracked in db_feature_usage_statistics.”

      At the moment i get no official link from him to this FAQ or to some MOS Notes. I will do some tests in the next weeks and then i verify that nothing is tracked in the b_feature_usage_statistics.
      I will keep you up to date with new informations

      Best Regards
      Alain

      Like

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Basic HTML is allowed. Your email address will not be published.

Subscribe to this comment feed via RSS

%d bloggers like this: