Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

SAP MaxDB Standby System (Recovery from Log Backup)

This page provides information about how to setup a Standby System with Recovery from Log Backup


The current versions of SAP DB/MaxDB (7.3 - 7.9) allow the setup of Standby System by recovering from log backups created by the primary database.

However there is no build-in functionality for the transfer of the redo log backups from the primary to the standby server. There is also no option to automatically apply the redo log backups (once these are available on the standby server). Moreover there is no functionality to easy switch over from the primary to the standby server in case of a failure.

Every step has to be done manually and there is no connection between the primary and the standby database, so in case the standby database stops the recovery for any reason, there won't be any warnings in the monitoring of the primary database.

Anyhow, despite of the mentioned shortcommings a SAP MaxDB standby database still provides a fallback system that can be brought online very quickly (compared to a full database recovery based on the latest backups).

Notice: the mentioned constraints do only apply to the out-of-the-box functionality of SAP MaxDB. By using third party tools (like the one of Libelle, DBShadow for MaxDB Databases) or cluster/storage solutions there are much more advanced standby features available.

See also information in SAP MaxDB documentation about Standby System in Standby Database.

Scripts for automating standby database handling

To run a standby database there are several tasks that need to be performed:

Setup a standby database

The SAP MaxDB software on the standby server needs to be on the exact same version as it is on the primary server.
Everytime the software is patched or upgraded on the primary server, this is also necessary on the standby server.

As soon as the software is installed on the standby server, the standby database has to be created and a complete data backup of the primary database should be restored to this database. It's not necessary that the standby database has the same name or the same volume layout as the primary database.
It is however important that the standby databases data area is able to contain the whole data area of the primary database.
Also, it is essential that the standby database is never set to ONLINE operational state as long as log files should be applyable.
Once the standby database was set to ONLINE (via db_online/db_warm) no further logs can be applied!
Since the recovery wizards of the database tools (for example daatabase studio) are designed to perform desaster recoveries most efficiently, make sure not to click the buttons to set the database ONLINE after the recovery!

Shipping of the redo log backups

Once the standby databases data area containt a complete data backup of the primary database, the standby database can be updated by recovering log backups of the primary database.
This is only possible if all redo log backups that had been created after the initial data backup had been done are applied in the order the backups where created.
If any log is missing in the sequence no further logs can be applied to the standby database.

In the SCN SAP MaxDB forum users posted script based solutions for shipping and applying log backups for Unix and Micrososft Windows platforms. The credits for these scripts go to Daniel Rajmanovich (Unix script) and Oliver Johnston (Microsoft Windows script). Attention: These scripts are NOT an SAP solution! You can use these scripts on your own risk only, no support is provided in case of errors using these scripts. 

Both scripts have been adapted to make use of the XUSER logon mechanism (dbmcli -U w/c) instead of specifying the logon credentials explicitly for each dbmcli-call (dbmcli -n <computer_name> -d <database_name> -u <dbm_operator>,<password>). This improves security and makes the script less dependend to the databases they are used for. Anyhow, to make this work, the XUSER data have to be setup appropriately for the os-user(s) that run the scripts.

Unix script

Well here it is, it consist in 2 files: restore.txt and restore_script.
It only restores 1 log backup on each run.
So you probably add it to a cron. It was made in Solaris 5.10.

the logbackups should have 666 rights on it so prdadm can delete them after restore.

Restore.txt has the commands that the dbmcli should execute once inside the dbmcli.

Contents of restore.txt

db_admin
db_connect
recover_start Autologbackup LOG 1
recover_replace Autologbackup /sapdb/PRD/logbackups/logbackupPRD 2
recover_cancel

The restore_script has the logic for changing restore.txt before executing it.

#!/bin/bash
IND=`ls /sapdb/PRD/logbackups/logbackupPRD* | cut -d. -f2 | sort -r | tail +2 | sort -r`
for i in $IND; do
ANT=`echo $i - 1 | bc`
POS=`echo $i + 1 | bc`
sed '/recover_start/s/'$ANT'/'$i'/g' /home/prdadm/restore.txt | sed '/recover_replace/s/'$i'/'$POS'/g' > /tmp/restore.$i.tmp
DIF=`diff /home/prdadm/restore.txt /tmp/restore.$i.tmp | wc -l`
if $DIF -eq 0 ; then
 echo "Error"
 exit 1
  else
 cp /tmp/restore.$i.tmp /home/prdadm/restore.txt
 dbmcli -U c -i /home/prdadm/restore.txt
 if $? -eq 0 ; then
  rm /sapdb/PRD/logbackups/logbackupPRD.$i
  rm /tmp/restore.$i.tmp
 else
  echo "Error"
  exit 1
 fi
fi
done
exit 0

 Micrososft Windows script

Script 1  (Standby.bat) - Top level script to call the others and delete any previous import script file.

del import_script.txt /f /q
call Recovery_Builder.bat
sleep 5
call Recovery_Apply.bat

Script 2  (Recovery_Builder.bat) - Sets DB to Admin mode and then builds the import list and exports to script file.

rem %%a = Logfile Name
rem %%b = log file number
rem %%j = Page number
Set LiveDB=xxxxxx
dbmcli.exe -U w_target db_admin
for /f "usebackq tokens=1,2,3,4 delims= " %%a in
  (`"dbmcli.exe -U w_target db_restartinfo |findstr /c:"Used LOG Page""`) do (set current_page=%%d)
for /f "usebackq tokens=1,2 delims=." %%a in (`"dir au*.* /b /o:e"`) do (
for /f "usebackq tokens=1,2,3,4 delims= " %%g in
  (`"dbmcli.exe -U w_source medium_label Auto_log_Backup %%b |findstr /c:"Last LOG Page""`)
  do (call :find_backup_page %%j %%a %%b)
)
:exit_loop
for /f "usebackq tokens=1,2 delims=." %%a in (`"dir au*.* /b /o:e"`) do (
if %%b GTR %first_file% (
echo recover_replace Auto_log_Backup "Autolog" %%b >> import_script.txt))
goto end
:find_backup_page
set backup_page=%1
if %current_page% EQU %1 (
echo db_connect >> import_script.txt
echo db_admin >> import_script.txt
echo recover_start Auto_log_Backup LOG %3 >> import_script.txt
set first_file=%3
if %current_page% GTR %1 goto exit_loop
)
:end

Script 3 (Recovery_Apply.bat) - Imports the generated script and then sets the DB back to offline.

dbmcli.exe -U w_target -i "import_script.txt"

Failover from the primary to the standby database

To perform a switch (or failover) from the primary database to the standby database, it's necessary that all remaining log entries since the last applied log backup are transfered to the standby database and applied there.
To do so, just create a log backup (which is also possible when the data area is not accessible anymore and the database can only started up to admin state) and make it available on the standby server. After the recovery of this last log backup the standby database can be opened.
Finally the applications that should access the standby database need to have the connection data so that the new server is accessed (e.g. change of hostname and database name).
If the application use the XUSER mechanism to logon to the database, only the XUSER data need to be changed to redirect the application to the standby database.

See also

Hot Standby System (With Shared Log Area)

 

 

5 Comments

  1. Guest

    Good Article, Found my answer. Thanks --Prince.

  2. Former Member

    Hi , can you tell us what is to be edited if we want to use this script?.

    In my scenario, we have log archives at dr site and want to restore.

    Better if we can delete restored logs, tell us how to use it in crontab job.

     

    Regards,

    Rohit

  3. Former Member

    In addition to my last comment,

    You did not mention what if some logs are already restored manually then how can restore rest of the logs.

    You can also add "check" from dbm.knl of standby server, that if logs are already restore then ignore.

    Awaiting for your response.

    Regards,

    Rohit

     

     

  4. Hi Rohit, please use the forum to discuss this issue with community. These scripts are not an SAP solution, so please discuss this with other users. Regards Birgit

    http://scn.sap.com/community/maxdb/content

  5. Former Member

    Hi,

    Thanks for the response.

    I have logged new discussion , please refer link below for quick response.

    http://scn.sap.com/thread/3956047