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!
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:
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;
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.