‘Mind the gap…’ (#3 – Backup/Restore – Part 2)

Before starting with this blog, I’ve to apologize for being so late with this issue of “Mind the Gap”. Since the last part of this series (‘Mind the gap…’ (#3 – Backup/Restore – Part 1) ) I’ve been too busy to write such a lengthily topic and other tasks appeared in a bit unplanned fashion.

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.

One of these short-term tasks is that I’m currently in Brazil, visiting the colleagues of the very new GSC Sao Leopoldo and provide some training and coaching there. Maybe I’ll get the chance to write something about this as well – but looking to the delay of this blog I won’t promise anything …

In the last episode of ‘Mind the Gap’ I described the backup and restore option of MaxDB.

So today it’s about the options you have to save your precious data when you use Oracle.

Actually it’s pretty difficult to write a comprehensive and comparative blog on this topic because there are so many different options to backup Oracle databases – one could write a book about it. And indeed, a quick check on Amazon gives about 232 titles for “Oracle + Backup”.

Therefore what I’m going to do is to describe, what I’ve seen most often on SAP customer systems.

Usually customers employ the BR*Tools (‘BR’ stands for Backup/Recovery) to administer the Oracle DBs of their SAP systems (at least that’s what SAP recommends them to do).

The BR*Tools are a set of programs that cover several administration scenarios and that ate highly integrated into the SAP CCMS. The covered scenarios do not only include backup and recovery but also maintenance of database statistics, consistency checks, space management, system copies and a lot more.

For the backup/restore part there are 5 programs that play a role:

brbackup – performs backups of the database data files, control files and settings

brrecover – performs the recovery of the database or parts of it. If necessary it automatically employs brrestore to get back old backups of particular data files

brrestore – reads files out of backups and restores them to a location in the file system.

brarchive – performs backups and afterwards deletion of archive log files (backed up redo log files). The deletion of the files is important to avoid an archiver stuck of the Oracle database and thus a production halt.

brtools – this is the menu based user interface to call the other programs without having to know about the command line syntax.

You may have wondered about the word “scenario” in the above paragraph.
What do I mean by that? Very unlike the functional oriented backup tools of MaxDB, the BR*Tools do not just provide single functions that the DBA has to know in which order to employ in what situation. Instead the BR*Tools offer a sort of ‘action-sequences’ for different situation.

Maybe an example makes this concept easier to grasp:
Suppose you’ve lost one data file of a SAP application tablespace. Usually you would have to figure out, what data file is affected, look up the most current backup, find out what archive logs are necessary to recover from this backup to the most recent change in the database and finally – perform all these steps.

When using the BR*Tools you simply move step-by-step through the menus for backup.

In my test database I voluntarily renamed a file “C:\\ORACLE\\TDB\\SAPDATA1\\TDB_1\\TDB.DATA1” so that it appears missing to the database. A STARTUP of the instance therefore clearly leads to this message:

As we see BRRECOVER has figured out what file is missing and will take the necessary actions to fix this.

The very next step is to tell BRRECOVER which backup it should use for this. The default selection here is of course the most current one, as this would shorten the recover time immensely. Anyhow, you might also choose a different one, if you feel you need to.

Here we see that BRRECOVER has figured out correctly, that the last changes are still available in the online redolog files and thus no restore of archive files is necessary. This saves some time and I can continue by just pressing ‘c’.

So, without having to think too much about what would be necessary to do for the recovery of the database, the BR*Tools already did all of the work for me. What’s left is, to open the database again. So a final ‘c’ to start menu point “6 = Open database and post-processing”.

BR0280I BRRECOVER time stamp: 2008-09-22 02.32.40
BR0329I Database instance TDB will be opened now
BR0787I No more archivelog files can be applied after database has been opened
BR0675I Do you want to perform this action?
BR0676I Enter ‘y[es]/c[ont]’ to perform the action, ‘n[o]’ to skip it, ‘s[top]’ to abort:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.32.46
BR0257I Your reply: ‘c’
BR0677I The action will be performed…
BR0259I Program execution will be continued…

BR0280I BRRECOVER time stamp: 2008-09-22 02.33.15
BR0595I Selecting indexes with NOLOGGING created during or after the selected backup bdywezva.fnd 2008-09-22 01.59.34 …
BR0285I This function can take several seconds/minutes – be patient…

BR0280I BRRECOVER time stamp: 2008-09-22 02.33.16
BR0596I No indexes found with NOLOGGING created during or after the selected backup bdywezva.fnd 2008-09-22 01.59.34

Even with this final step BRRECOVER did some more work for me.
It warned me about that opening the database will nullify the option to apply further archive logs, it offered the option to use the RESETLOGS option and it also took care of UNUSABLE indexes that might have been present due to the usage of the NOLOGGING option (which is particular often the case with BI-systems).

As you may have noticed, both the MaxDB DBMGUI as well as the BR*Tools take much of the complexity of database backup and recovery away from the DBAs.
Even if you don’t know the commands or the overall process of a database recovery, you can simply use the tools supplied by SAP to easily get back to the latest state of your database (which might be the most important scenario, although not the most often used one).

Where MaxDB just don’t have that many options to setup a backup and recovery system, Oracle with the BR*Tools offer several different setup possibilities that may even overlap each other (e.g. RMAN or user-managed backups).

That makes the BR*Tools much more flexible and adaptable to special setups, but as so often, with flexibility there comes complexity.
And there are many users that struggle in initially setting up the BR*Tools by configuring correctly its init<SID>.sap configuration file.

If simplicity and secureness are the most important features of a backup/recovery tool, than the MaxDB tools are in advantage, as you immediately recognize if your setup (e.g. defining the backup templates a.k.a. mediums) are working correctly.
With the BR*Tools you may have to run a few tests until you’re completely sure they work the way you want them to.

To find out more about the BR*Tools and the different topics of backup and recovery with SAP Oracle databases check these links: