Single Tenant ist unnütze Komplexität? / Single Tenant is only unnecessary complexity? - Remote snapshot clone

Some people may wait for the second part of my three blog posts I am writing about Single Tenancy and why (some / a lot / most) people do think it is unnecessary complexity or - even worser - it is unnecessary at all.

What you do need to use the scripts as they are can be found at my first blog entry.

Case 2 - Remote snapshot clone of a PDB:

Snapshoting databases using a database link does have a lot of use cases.
The following two of them I do like most:
"Fast Provisioning", e.g. of nightly builds to testers or developers and application upgrades.

For remote snapshot pdb clones you either use file systems with snapshot technology included (like ACFS or ZFS) or you use traditional file systems (then you need to set CLONE_DB=true at your spfile and restart the database).

Well, how do you provision a nightly build today in 5 minutes to your testers and developers? On 40 machines? RMAN clone? Datapump export and import? What about the disk space you need for that? Like to spend money on it? Then tell me which shares I should buy before you throw the money out of the window. 😉

Or how do you make an application upgrade? With a non CDB database you can use offline backup the database, online backup but note down the last SCN, configure flashback database, ... And how long does it need to return to the before-upgrade-image if something goes wrong with the application upgrade?

Guys, years ago I was an app consultant and I had to go back with an installation of an application three times (at the same customer), because the business people did find some issues they haven't seen (tested) on the test system before. It took me hours and a lot nerves to restore everything to the right point-in-time and to setup the standby environments later on again...And if you do this three times in 4 months... 😟

So, the trick you will use for all of these uses cases is in the future - remote snapshot clone. Fast, reliable, you don't need to touch the "production", because all changes are done in your "new" snapshoted PDB.

What are the steps you need to do?

1. Create a common user and grant him rights, open the PDB you want to make a snapshot from read only (if you don't use a snapshot capable file system);
2. Create a database link to the PDB you want to clone from the CDB$ROOT you want the PDB cloned to.
3. Create the snapshot clone remote at the second CDB.
4. Open the new PDB read write
5. Make your application upgrade or develop or test
1. if it is sucessful / if you need to keep the PDB - "alter pluggable database <xyz> materialize;"
2. if it is not successful or before you create another nightly build - drop the new PDB and open the original source PDB read write again

Script 1 to run at the source CDB$ROOT container prepares the source PDB to be snapshoted.
Script 2 to run at the target CDB$ROOT container where the snapshot clone should be created

Some notes to point 6: If you have dropped the snapshot clone PDB the source PDB can't be open read write. The error message you get is not telling you the truth, because the PDB files are on OS level also set to READ. So what you do need to do is to change the permission on OS level for the database files. So the right command will look like

sudo chmod 640 /u01/app/oracle/oradata/XFEE1/77F0ADE89C820516E0534738A8C0802B/datafile/*

After you have made these changes, the source PDB can be opened read write again. It's part of the script 2, but as the folder names are different on different databases, I have made a comment out of it.

One last comment: I didn't tested the scripts as I have thrown things out. If something does not work, please come back and leave a comment, so I can fix it.
Thanks a lot!


By the way - happy New Year 2019 everyone!