In my mini-series “Mind the gap” I will try to shed some light on where the little or big differences between MaxDB and Oracle databases are and what to keep in mind when working with them.
Today I take a look on how databases can be backed up and restored once there is important data in it.
Since the topic is rather wide and includes many aspects, I split this “Mind the gap” episodes into 2 parts.
Backup and Recovery – here we go
Before I describe the different approaches both Oracle and MaxDB development have taken to make Backup and Recovery possible, let us take a step back and think about what backup and recovery is for.
Backup? What for?
Plain and simple: A backup/recovery should protect us from the permanent loss of data.
Don’t misinterpret ‘protect’ here as ‘prevent’. Recovery is usually done, after the data loss already happened.
This data loss may be caused by a crash of the database instance, a user error (changed/deleted the wrong data), database corruptions etc.
Regardless what caused the data loss – a recovery should ‘undo’ it and bring the database to a state as if the data loss had never happened.
How is this possible without a wand and magic power? Simply by having a copy of the database that is not damaged – a backup.
Obviously it’s not an option to copy the whole database for each and every little change of data. Therefore databases usually write out a stream of data about what changes happened to the database – the redo log information. As the name suggest, it contains (among other things) the information to perform all changes to the database again – to re-do the changes.
“If we have a copy of an initial state of the database and we apply all the changes that happened meanwhile we’re able to get back all of our data!” – This is the very idea that is the basis to the recovery mechanisms of most DBMS around, and so for Oracle and MaxDB.
That said some consequences are obvious from it, but quite often overlooked:
- The recovery process should bring the database back into a consistent state, not just single tables, files or whatsoever.
- If one of the redo log files is missing, the recovery cannot proceed, since the redo log contains the changes in the same sequence as they occurred in the database. There is no way to skip one of these and still getting a consistent database from it (Ok, internally the DBMS can actually decide if a specific series of actions might have an impact to the final state and possibly omit these changes – but to be able to tell this, still all information are necessary).
- If the database is broken and the backup is broken – then there is no technical way to fix this without loosing data.
So how can we actually do backups then, if these are that important?
How MaxDB does it
The MaxDB database provides several functions to perform backups of the data stored in it. In fact, it is quite simple to get a MaxDB backup:
- Tell the database what kind of backup should be written where to.
- Start the backup
- That’s it.
Let’s get a bit more into the details of these steps:
1. Tell the database what kind of backup should be written where to
This step is a synonym for “define a backup medium”.
A MaxDB backup medium contains all information that is necessary to start a backup:
- What kind of backup should be performed? (full data backup, incremental data backup, log backup)
- How should the data be saved? (into a file, a pipe, to a tape or to a third party backup system)
- Should the backup be written to one target or to several targets (like several tapes) in parallel?
Each backup medium has its own unique name that is used to perform the backup. That way we have to define all those things only once and all backups that we make will use the settings.
Here’s how to define a backup medium in DBMGui:
Open the “Backup Medium” panel on the left and either double click on one of the existing definitions or click on the “New Medium” icon in the icon bar (the icons with the yellow *).
Then you get a window like this one:
Here you specify the type of backup, the target device, the backup tool (if you use something like networker or backint) and the targetdevice or file.
Attention: the full path to the target has to be provided! If you e.g. want to make databackups into a file directory than it’s not enough to write the directoy name here. You have to give also the filename that should be used for the backup (it is not taken from the Name-field above!).
This has an important consequence as well: if you make your data backups into a filesystem folder, than you have to take care of saving the file to somewhere else yourself.
MaxDB will not just overwrite the file at the next backup (wouldn’t be a good idea, would it?) and MaxDB won’t create subfolders to store the data there.
2. Start the backup
Once these backup mediums are defined, we can simply call the Backup-Wizard from DBM Gui/DBA Studio and start a backup.
Choose the backup type:
Select the medium definition to use:
Review the backup settings and start the backup:
During the backup you see a progress bar and you get a summary once the backup is finished:
Attention: MaxDB will not perform LOG-Backups until a complete DATA-backup is done; just because these LOG-Backups could never be used for anything then. This state – also called HISTLOST – is also printed out as a warning in the DBMGUI, so that you shouldn’t miss this important information.
As soon as the DATA-Backup has been done sucessfully LOG-Backups can be done either manually or automatically.
Each Backup the database takes (even the unsucesfull ones) is registered in the backup history. Therefore, in case you need to recover, the database already “knows” what backups are available for recovery.
After that quick walk-through let’s discuss some common questions.
So I only get one file for my whole database?
If you’re not doing parallel backups: Yes!
MaxDB does not perform file copies of the data volumes to backup the database. Instead, MaxDB reads all pages that belong to a specific consistent state of the database (savepoint) and writes all these pages into the backup file.
That way only those pages are saved that contain the current data and this backup can be used to recover the database into a consistent state again (with or without the redo log backups).
And since all pages are read by the MaxDB Kernel during the backup, corruptions are very likely found already during the backup.
No corrupt database will get backed up unnoticed here.
This is a big difference to the standard backup setup for Oracle databases in SAP environments.
Most often Oracle databases are switched to a special online backup mode so that the BR*Tools (I’ll cover that in detail in part 2 of this blog) can use os commands like cp, dd, etc. to copy the datafiles. Each and every bit of the datafiles will be saved – including emtpy/unused blocks. To recover from such a backup the redo logs are necessary – without them you will loose data. In addition, you don’t get a block consistency check while doing the backup – so it can happen (and it does happen a lot!) that you end up with many backups but all of them contain corruptions …
Of course, there are better options available for Oracle as well – I’ll cover them in part 2!
What about performance?
As said above, MaxDB reads all pages for the backup by the database kernel. Therefore, it uses the same I/O optimized access methods as the database usually does (e.g. DirectIO). In order to reduce the impact of the backup-I/O to the normal use of the database MaxDB uses separate I/O-Worker threads for the backup – one for each datavolume there is. This design guarantees that the backup speed is usually only limited by how quick the target device can store the data.
Backup times of MaxDB databases larger than 1 TB of less than 1:25 hours while the SAP is up and running on full steam are documented.
Does the MaxDB support compressing database backups?
Currently there is no such feature implemented, but it is planned to integrate it into the backup function.´
As seen in several discussions in the MaxDB forum (like MaxDB backup to file and deduplication) MaxDB backups are currently also not suited for de-duplication systems, but it can still be an option to use tools like zip on a backup file.
Do I have to use DBMGui for backup and recovery?
No, of course you can also use the DBA Studio – just kidding. Of course, you can use also the command line but you don’t get any more options there. Compared to the situation with Oracle and the BR*Tools, with MaxDB it is not easier, quicker, safer or more powerful to use the command line.
Instead it is highly recommended to use the DBMGui as it is designed to help you as much as possible with the tasks of backup and recovery and you have always an easy access to all the log files in case anything does not run as smoothly.
Can I use the backups for a system copy?
Yes, in fact this is the standard procedure to create homogenous system copies with MaxDB. Since all parameters are also stored in the backup file you can literally just use the backup file to create a new db instance on a different host and restore the database there. Since the data pages are stored in the backup and not the data volume files it’s even possible, without any effort, to change the disk layout (say to move from files to raw devices or to use bigger volume sizes).
The cheat sheet ------------- 8< ------------ cut here ------------ 8< ------------
Backups are done page-wise – the volumes are not just copied.
Backups are done while the database is online and SAP is up and running.
Every complete data backup contains consistent state of the database and can be used for recovery even when no log backup is available.
Block consistency is automatically checked during the backup.
DBMGui is used as the tool for Backup and Recovery – command line is usually not used (although possible)
SAP Support notes to know:
Documentation to read:
---------------------- 8< ------------ cut here ------------ 8< --------------------
Other Backup/recovery related blogs:
Questions to SAP Support: “Is my MaxDB recovery still running?”
Point-in-time-recovery considered harmful