How to Take L0 Backups daily from the OCI Console  - a thought experiment

28th  July 2023

So Life is full of corner cases. You think this is such an outlandish scenario it can never happen. And then it happens.

One of our customers uses a third party application with some really heavy daily batch processing. It probably is not very well optimised but unfortunately being a third party product there is no way to change the batch processing to make it more efficient. What the batches do is they select the data into temporary tables, massages it, truncates the source table and reloads it back , for all the large tables. What this means effectively is 100% data churn daily. The alarming  thing about this is that the Level 1 backups are similarly sized to level zero backups.

So what is the problem you would say ? Well prior to moving to cloud they were taking daily level 0 backups to ensure consistent recovery times. By following a standard daily incremental weekly full setup a worst case restore could require one level zero and 6 level 1 backups each of the similar size. So it would take 7 times longer to recovery the database in the worst case that with Daily level 0's. 

The solution seems to be simple. Use a daily level zero backup. The problem is in Cloud if you want to leverage Oracle Managed backups from the console you have to go with a weekly level 0 /daily level 1 policy.


So in the immortal words of the highlander "There can be only one".

GAAAAAAAH ! So how do we fix this.


Option 1. Disable Oracle managed backups and switch to user backups using cron jobs or the bkup_api scripts from Oracle. 

Not good because one of the objectives of moving to cloud is to make things easier.


I proposed a solution that I have defined below as  Option2. Now we have not yet implemented this so if you try it do let me know how it goes.

Option2: Use a cronjob to change the backup configuration daily post backup such that the updated configuration resets the L0 day to tomorrow.

So we have 7 Configuration files. you can extract the current configuration using 

dbaascli database backup --dbname <value> --getConfig [--configFile <value>]

And reset the config using 

dbaascli database backup --dbname <value> --configure  --configFile <value>

By having a set of 7 configuration files with the Level 0 day ranging from 0 to 6 we can define 7 cronjob entries that will update the configuration daily to ensure that the next backup will be a level zero. Or a single entry that calculates which of the files to use based on the current day of the week.

And since everything is piped through the oracle managed backup system we can still see all the backups from the console.

We have in parallel requested an enhancement from Oracle to allow daily level zero backups as an Oracle managed backup recovery option. However the wheels of Oracle Development turn slowly.

Till then I can wait for my workaround to be approved by the Change board for testing in UAT.