‘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:

ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\\ORACLE\\TDB\\SAPDATA1\\TDB_1\\TDB.DATA1'

Obviously something needs to be done now. Let’s start up the ‘brtools’ user interface:

BR0651I BRTOOLS 7.00 (32)
BR0280I BRTOOLS time stamp: 2008-09-22 02.08.51
BR0656I Choice menu 1 - please make a selection
-------------------------------------------------------------------------------
BR*Tools main menu

1 = Instance management
2 – Space management
3 – Segment management
4 – Backup and database copy
5 – Restore and recovery
6 – Check and verification
7 – Database statistics
8 – Additional functions
9 – Exit program

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:

In that case option “5 – Restore and recovery” appears to be suitable.

The next menu is already providing every necessary action for the planned recovery:

BR0662I Enter your choice:
5
BR0280I BRTOOLS time stamp: 2008-09-22 02.09.45
BR0663I Your choice: '5'

BR0280I BRTOOLS time stamp: 2008-09-22 02.09.45
BR0656I Choice menu 11 – please make a selection
——————————————————————————-
Restore and recovery

1 = Complete database recovery
2 – Database point-in-time recovery
3 – Tablespace point-in-time recovery
4 – Whole database reset
5 – Restore of individual backup files
6 – Restore and application of archivelog files
7 – Disaster recovery
8 – Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:

Since I want to have my database back up to the very last action I just go for the default selection (marked with the ‘=’) by pressing ‘c’.

This gives me the following menu:

c
BR0280I BRTOOLS time stamp: 2008-09-22 02.11.53
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0280I BRTOOLS time stamp: 2008-09-22 02.11.53
BR0657I Input menu 34 - please check/enter input values
-------------------------------------------------------------------------------
BRRECOVER options for restore and recovery
 1 * Recovery type (type) ............. [complete]
 2 - BRRECOVER profile (profile) ...... [initTDB.sap]
 3 ~ BACKINT/Mount profile (parfile) .. []
 4 - Database user/password (user) .... [/]
 5 - Recovery interval (interval) ..... [30]
 6 - Confirmation mode (confirm) ...... [yes]
 7 - Scrolling line count (scroll) .... [20]
 8 - Message language (language) ...... [E]
 9 - BRRECOVER command line (command) . [-p initTDB.sap -t complete -i 30 -s 20 -l E]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:

Basically I don’t need to change anything here, since I did not move my backup file after the backup had been taken. So, again I enter ‘c’.

The next message tells me that now another program of the BR*Tools will be started and I confirm this again with ‘c’:

BR0280I BRTOOLS time stamp: 2008-09-22 02.13.14
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0291I BRRECOVER will be started with options '-p initTDB.sap -t complete -i 30 -s 20 -l E'
BR0280I BRTOOLS time stamp: 2008-09-22 02.13.14
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
###############################################################################
BR0701I BRRECOVER 7.00 (32)
BR0705I Start of database recovery: vdywfbcs.crv 2008-09-22 02.14.10
BR0484I BRRECOVER log file: C:\\ORACLE\\TDB\\sapbackup\\vdywfbcs.crv
BR0280I BRRECOVER time stamp: 2008-09-22 02.14.11
BR0707I Recovery of database: TDB
BR0708I BRRECOVER action ID: vdywfbcs
BR0709I BRRECOVER function ID: crv
BR0710I Recovery type: complete
BR0280I BRRECOVER time stamp: 2008-09-22 02.14.11
BR0655I Control menu 101 - please decide how to proceed
-------------------------------------------------------------------------------
Complete database recovery main menu
 1 = Check the status of database files
 2 * Select database backup
 3 * Restore data files
 4 * Restore and apply incremental backup
 5 * Restore and apply archivelog files
 6 * Open database and post-processing
 7 * Exit program
 8 - Reset program status
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:

 

As described in the documentation, BRRECOVER is a kind of umbrella program, which would eventually start BRRESTORE, if necessary.

The actions of the main menu generally give the plot of the complete recovery process. I will just follow them step-by-step from top to bottom just by pressing ‘c’-ontinue all the time:

BR0662I Enter your choice:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.16.31
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0614I Database instance TDB is mounted
BR0750I Database instance TDB will be remounted now in mode 'IMMEDIATE'
BR0280I BRRECOVER time stamp: 2008-09-22 02.16.39
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.16.44
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
BR0280I BRRECOVER time stamp: 2008-09-22 02.16.44
BR0307I Shutting down database instance TDB ...
BR0280I BRRECOVER time stamp: 2008-09-22 02.16.48
BR0308I Shutdown of database instance TDB successful
BR0280I BRRECOVER time stamp: 2008-09-22 02.16.48
BR0330I Starting and mounting database instance TDB ...
BR0280I BRRECOVER time stamp: 2008-09-22 02.16.54
BR0331I Start and mount of database instance TDB successful
BR0755I Checking the status of database files for instance TDB...
BR0758I Data file 'C:\\ORACLE\\TDB\\SAPDATA1\\TDB_1\\TDB.DATA1' not found - it will be restored/recreated and recovered
BR0280I BRRECOVER time stamp: 2008-09-22 02.16.56
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

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.

BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
BR0280I BRRECOVER time stamp: 2008-09-22 02.19.10
BR0655I Control menu 101 - please decide how to proceed
-------------------------------------------------------------------------------
Complete database recovery main menu
 1 + Check the status of database files
 2 = Select database backup
 3 * Restore data files
 4 * Restore and apply incremental backup
 5 * Restore and apply archivelog files
 6 * Open database and post-processing
 7 * Exit program
 8 - Reset program status
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.19.21
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0699I Reading log file C:\\ORACLE\\TDB\\sapbackup\\backTDB.log ...
BR0280I BRRECOVER time stamp: 2008-09-22 02.19.21
BR0659I List menu 102 + please select one or more entries
-------------------------------------------------------------------------------
Database backups for complete database recovery
Pos.  Log           Start                Type        Mode        Device     Rc
  1 = bdywezva.fnd  2008-09-22 01.59.34  online      full        disk        1
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your selection:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.19.27
BR0663I Your selection: 'c'
BR0259I Program execution will be continued...

BR0699I Reading log file C:\\ORACLE\\TDB\\sapbackup\\bdywezva.fnd …

BR0772I Checking the availability of archivelog files for database instance TDB ...
BR0699I Reading log file C:\\ORACLE\\TDB\\saparch\\archTDB.log for device type 'disk'...

BR0699I Reading log file C:\\ORACLE\\TDB\\saparch\\archTDB.log for device type ‘tape/backint/rman’…

BR0699I Reading log file C:\\ORACLE\\TDB\\saparch\\archTDB.log for device type 'stage'...
BR0280I BRRECOVER time stamp: 2008-09-22 02.19.27
BR0655I Control menu 101 - please decide how to proceed
-------------------------------------------------------------------------------
Complete database recovery main menu
 1 + Check the status of database files
 2 + Select database backup
 3 = Restore data files
 4 # Restore and apply incremental backup
 5 * Restore and apply archivelog files
 6 * Open database and post-processing
 7 * Exit program
 8 - Reset program status
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.21.08
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0614I Database instance TDB is mounted
BR0280I BRRECOVER time stamp: 2008-09-22 02.21.15
BR0657I Input menu 111 - please check/enter input values
-------------------------------------------------------------------------------
BRRESTORE main options for restore of database files
 1 - BRRESTORE profile (profile) ........ [initTDB.sap]
 2 - BRBACKUP backup run (backup) ....... [bdywezva.fnd]
 3 - Fill-up previous restores (fillup) . [no]
 4 - Restore device type (device) ....... [disk]
 5 # BACKINT/Mount profile (parfile) .... []
 6 - Database user/password (user) ...... [/]
 7 - Files for restore (mode) ........... [4]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.21.18
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0280I BRRECOVER time stamp: 2008-09-22 02.21.18
BR0657I Input menu 112 – please check/enter input values
——————————————————————————-
Additional BRRESTORE options for restore of database files

 1 - Confirmation mode (confirm) ...... [yes]
 2 - Query mode (query) ............... [no]
 3 - Compression mode (compress) ...... [no]
 4 - Parallel execution (execute) ..... [0]
 5 - Additional output (output) ....... [no]
 6 - Message language (language) ...... [E]
 7 - BRRESTORE command line (command) . [-p initTDB.sap -b bdywezva.fnd -d disk -m 4 -k no -e 0 -l E -i 30]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.21.21
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0291I BRRESTORE will be started with options '-p initTDB.sap -b bdywezva.fnd -d disk -m 4 -k no -e 0 -l E -i 30'
BR0280I BRRECOVER time stamp: 2008-09-22 02.21.21
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.21.22
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
===============================================================================
 BR0401I BRRESTORE 7.00 (32)
BR0405I Start of file restore: rdywfbti.rsb 2008-09-22 02.21.22
BR0484I BRRESTORE log file: C:\\ORACLE\\TDB\\sapbackup\\rdywfbti.rsb
BR0457I Probably the database must be recovered due to partial restore
BR0280I BRRESTORE time stamp: 2008-09-22 02.21.26
BR0407I Restore of database: TDB
BR0408I BRRESTORE action ID: rdywfbti
BR0409I BRRESTORE function ID: rsb
BR0449I Restore mode: partial
BR0411I Database file for restore: C:\\ORACLE\\TDB\\SAPDATA1\\TDB_1\\TDB.DATA1
BR0419I Files will be restored from backup: bdywezva.fnd 2008-09-22 01.59.34
BR0416I 1 file found to restore, size 8.789 MB
BR1398I Files will be decompressed by RMAN
BR0421I Restore device type: disk
BR0420I Files will be restored from directory: C:\\ORACLE\\TDB\\sapbackup\\bdywezva
BR0280I BRRESTORE time stamp: 2008-09-22 02.21.26
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRRESTORE:
c
BR0280I BRRESTORE time stamp: 2008-09-22 02.21.29
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
BR0351I Restoring C:\\ORACLE\\TDB\\SAPDATA1\\TDB_1\\TDB.DATA1
BR0355I from C:\\ORACLE\\TDB\\sapbackup\\bdywezva\\TDB.DATA1.S ...
#FILE..... C:\\ORACLE\\TDB\\SAPDATA1\\TDB_1\\TDB.DATA1
#RESTORED. C:\\ORACLE\\TDB\\sapbackup\\bdywezva\\TDB.DATA1.S  #1/1
BR0280I BRRESTORE time stamp: 2008-09-22 02.22.03
BR0418I 1 of 1 file restored - 8.789 MB of 8.789 MB done
BR0204I Percentage done: 100.00%, estimated end time: 2:22
BR0001I **************************************************
BR0406I End of file restore: rdywfbti.rsb 2008-09-22 02.22.03
BR0280I BRRESTORE time stamp: 2008-09-22 02.22.03
BR0402I BRRESTORE completed successfully
===============================================================================
BR0292I Execution of BRRESTORE finished with return code 0
BR0280I BRRECOVER time stamp: 2008-09-22 02.22.03
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRRECOVER:

 

So, the datafile has been correctly restored, what’s missing now is the recovery of the last changes. I start this by (you guess it): pressing ‘c’.

BR0662I Enter your choice:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.23.44
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0614I Database instance TDB is mounted
BR0750I Database instance TDB will be remounted now in mode ‘IMMEDIATE’

BR0280I BRRECOVER time stamp: 2008-09-22 02.23.51
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.23.53
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0280I BRRECOVER time stamp: 2008-09-22 02.23.53
BR0307I Shutting down database instance TDB …

BR0280I BRRECOVER time stamp: 2008-09-22 02.23.57
BR0308I Shutdown of database instance TDB successful

BR0370I Directory C:\\ORACLE\\TDB\\sapbackup\\vdywfbcs created

BR0202I Saving C:\\ORACLE\\TDB\\SAPDATA1\\CONTROL01.CTL
BR0203I to C:\\ORACLE\\TDB\\sapbackup\\vdywfbcs\\CONTROL01.CTL …

BR0280I BRRECOVER time stamp: 2008-09-22 02.23.58
BR0330I Starting and mounting database instance TDB …

BR0280I BRRECOVER time stamp: 2008-09-22 02.24.11
BR0331I Start and mount of database instance TDB successful

BR0776I First redo log file sequence to apply for database instance TDB: 68

BR0777I Last redo log file sequence to apply for database instance TDB: 70

BR0699I Reading log file C:\\ORACLE\\TDB\\saparch\\archTDB.log for device type ‘disk’…

BR0699I Reading log file C:\\ORACLE\\TDB\\saparch\\archTDB.log for device type ‘tape/backint/rman’…

BR0699I Reading log file C:\\ORACLE\\TDB\\saparch\\archTDB.log for device type ‘stage’…

BR0280I BRRECOVER time stamp: 2008-09-22 02.24.15
BR0660I List display 113 – no selection possible
——————————————————————————-
Archivelog files to apply of database instance TDB

Pos.  Sequence   Status    Apply   From    Disk    Tape    Util   Rman   Stage

1 –       68   in_redo    yes    redo    ….    ….    ….   ….   ….
2 –       69   in_redo    yes    redo    ….    ….    ….   ….   ….
3 –       70   in_redo    yes    redo    ….    ….    ….   ….   ….

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:

 

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’.

BR0280I BRRECOVER time stamp: 2008-09-22 02.26.55
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0280I BRRECOVER time stamp: 2008-09-22 02.26.55
BR0657I Input menu 117 – please check/enter input values
——————————————————————————-
Applying archivelog files of database instance TDB

1 – First sequence number (first_seq) .. [68]
2 – Last sequence number (last_seq) …. [70]
3 # Use backup control file (back_ctl) . [no]
4 ~ Parallel recovery (degree) ……… []

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0783I Archivelog files with sequence number 68-70 of database instance TDB will be applied now

BR0280I BRRECOVER time stamp: 2008-09-22 02.27.15
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.28.04
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0280I BRRECOVER time stamp: 2008-09-22 02.28.04
BR0336I Applying offline redo log file C:\\ORACLE\\TDB\\ORIGLOG\\REDO03O.LOG …
BR0336I Applying offline redo log file C:\\ORACLE\\TDB\\ORIGLOG\\REDO04O.LOG …

BR0280I BRRECOVER time stamp: 2008-09-22 02.28.14
BR0784I Media recovery completed

BR0280I BRRECOVER time stamp: 2008-09-22 02.28.14
BR0256I Enter ‘c[ont]’ to continue, ‘s[top]’ to cancel BRRECOVER:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.29.09
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0280I BRRECOVER time stamp: 2008-09-22 02.29.09
BR0655I Control menu 101 – please decide how to proceed
——————————————————————————-
Complete database recovery main menu

1 + Check the status of database files
2 + Select database backup
3 + Restore data files
4 # Restore and apply incremental backup
5 + Restore and apply archivelog files
6 = Open database and post-processing
7 * Exit program
8 – Reset program status

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:

 

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”.

BR0662I Enter your choice:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.32.02
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0614I Database instance TDB is mounted
BR0064I Database instance TDB will be shut down now in mode 'IMMEDIATE'

BR0280I BRRECOVER time stamp: 2008-09-22 02.32.09
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.32.10
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0280I BRRECOVER time stamp: 2008-09-22 02.32.10
BR0307I Shutting down database instance TDB …

BR0280I BRRECOVER time stamp: 2008-09-22 02.32.13
BR0308I Shutdown of database instance TDB successful

BR0280I BRRECOVER time stamp: 2008-09-22 02.32.13
BR0657I Input menu 135 – please check/enter input values
——————————————————————————-
Options for opening database instance TDB

1 ~ Reset logs option (reset_logs) .. []
2 * Open database command (command) . [alter database open]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRRECOVER time stamp: 2008-09-22 02.32.40
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

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.32.46
BR0304I Starting and opening database instance TDB …

BR0280I BRRECOVER time stamp: 2008-09-22 02.33.04
BR0305I Start and open of database instance TDB successful

BR0280I BRRECOVER time stamp: 2008-09-22 02.33.15
BR0319I Control file copy created: C:\\ORACLE\\TDB\\sapbackup\\vdywfbcs\\CNTRLTDB.NEW 7094272

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

BR0280I BRRECOVER time stamp: 2008-09-22 02.33.16
BR0714I Complete database recovery completed

BR0280I BRRECOVER time stamp: 2008-09-22 02.33.16
BR0655I Control menu 101 – please decide how to proceed
——————————————————————————-
Complete database recovery main menu

1 + Check the status of database files
2 + Select database backup
3 + Restore data files
4 # Restore and apply incremental backup
5 + Restore and apply archivelog files
6 + Open database and post-processing
7 = Exit program
8 – Reset program status

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:

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:

We all hate the BR*TOOLS, don’t we?

Point-in-time-recovery considered harmful

Some hints to make handling of oracle datablock corruptions more efficient

BR*Tools for Oracle DBA

SAP Note 842240 FAQ: Backup strategy of large and highly-available databases

SAP Note 23070 Backup and Recovery: Basic Concepts

I hope you like this episode of “Mind the gap”.

Best regards,

Lars

Questions to SAP Support: “How to move MaxDB volumes?”

Unlike Oracle, MaxDB does not have a CONTROLFILE to store the location of its files.
Instead, all file locations are stored as common parameters.
A quick look at the parameters section of the KNLDIAG reveals this:

--------------------------------------------------------------------------------
Date       Time       TID(hex) Typ MsgID Label    Message-Text
--------------------------------------------------------------------------------
2008-09-10 19:14:17      0x7A4     20234 RTE      Dump of all kernel parameters start
2008-09-10 19:14:17      0x7A4     20207 RTE      Using mode NORMAL for data volume 1
2008-09-10 19:14:17      0x7A4     20207 RTE      Using mode NORMAL for data volume 2
[...]
2008-09-10 19:14:17      0x7A4     20235 RTE       DATA_VOLUME_GROUPS=1
2008-09-10 19:14:17      0x7A4     20235 RTE       DATA_VOLUME_MODE_0001=NORMAL
2008-09-10 19:14:17      0x7A4     20235 RTE       DATA_VOLUME_MODE_0002=NORMAL
2008-09-10 19:14:17      0x7A4     20235 RTE       DATA_VOLUME_NAME_0001=C:\sapdb\db760\devspaces\DAT_0001
2008-09-10 19:14:17      0x7A4     20235 RTE       DATA_VOLUME_NAME_0002=C:\sapdb\db760\devspaces\DAT_0002
2008-09-10 19:14:17      0x7A4     20235 RTE       DATA_VOLUME_SIZE_0001=6400
2008-09-10 19:14:17      0x7A4     20235 RTE       DATA_VOLUME_SIZE_0002=6400
2008-09-10 19:14:17      0x7A4     20235 RTE       DATA_VOLUME_TYPE_0001=F
2008-09-10 19:14:17      0x7A4     20235 RTE       DATA_VOLUME_TYPE_0002=F
2008-09-10 19:14:17      0x7A4     20235 RTE       DATE_TIME_FORMAT=INTERNAL
[...]
2008-09-10 19:14:17      0x7A4     20235 RTE       LOG_BACKUP_TO_PIPE=NO
2008-09-10 19:14:17      0x7A4     20235 RTE       LOG_IO_BLOCK_COUNT=4
2008-09-10 19:14:17      0x7A4     20235 RTE       LOG_IO_QUEUE=1000
2008-09-10 19:14:17      0x7A4     20235 RTE       LOG_MIRRORED=NO
2008-09-10 19:14:17      0x7A4     20235 RTE       LOG_QUEUE_COUNT=0
2008-09-10 19:14:17      0x7A4     20235 RTE       LOG_SEGMENT_SIZE=246
2008-09-10 19:14:17      0x7A4     20235 RTE       LOG_VOLUME_NAME_001=C:\sapdb\db760\devspaces\LOG_001
2008-09-10 19:14:17      0x7A4     20235 RTE       LOG_VOLUME_SIZE_001=1280
2008-09-10 19:14:17      0x7A4     20235 RTE       LOG_VOLUME_TYPE_001=F
2008-09-10 19:14:17      0x7A4     20235 RTE       LRU_FOR_SCAN=NO
[...]

That given, moving a volume is pretty easy.
It’s done in four steps:

  1. Stop the database (db_offline)
  2. Copy the volume you want to move to the new location
    (DON’T just MOVE it, but COPY it in case something goes wrong!)
  3. Use the DBMGUI to change the volume parameters:
    “Configuration”
    -> “Volumes”
    -> double click on the volume you want to move
    -> when the database is offline the text field “Device/Path” is editable
    -> enter the new path.
    Repeat this for all volumes you want to move.
  4. Startup the database (db_online)

 If the database starts up without any error it’s safe now to delete the files in their old location.

Should anything go wrong after you’ve changed the parameters you can use

dbmcli -U c param_restore 1

to get back the settings from before the change and start the database
with the files in the old location.

Of course, this is also possible via the command line tool dbmcli, but as this has no advantages and could lead to more mistakes I just present the DBMGUI procedure here.

best regards,
Lars