Recover SAP IQ and SAP MaxDB databases to a new location

Both of these procedures require you to customize and run a config file.

Depending on how you protected the database, you need the procedure for:

Recover a volume-level backup image to a new target

The following sections include information on recovering a volume-level backup image to a new target.

You can also recover using instant mount and migrate of a database to a new target.

Required script

Before you can mount a database to a new target, you must prepare a script. The script is at /act/custom_apps/<database type>/restore.

SAP IQ

Script name: act_sybase_lvm_customdb_recovery.sh

Script location: /act/custom_apps/sybaseiq/restore

Script

# ./act_sybase_lvm_customdb_recovery.sh OSUSER=sybaseiq TARGET_MNT=/bkp/iqmnh
SRC_DB_DBA_USER=act SRC_DB_DBA_PWD=passwd SYBIQ_HOME=/home/sybaseiq VERSION=IQ-16_1
ACT_NAME=<database name> CATALOG_DB_FILE=/data1/<database name>/<database name>.db LOG_BKP_MNTPT=/bkp/iqmnh_archivelog

Arguments to the script

OSUSER = <SYBASE IQ OSUSER name>
ACT_NAME = <SYBASE IQ Source Database name>
TARGET_MNT = <Mount point specified during mount>
LOG_BKP_MNTPT = <Archive Log backup mount point name>
SYBIQ_HOME = <SYBASE IQ Home Location>
SRC_DB_DBA_USER = <Utility Database user name>
SRC_DB_DBA_PWD = <Utility Database password>
VERSION = <SYBASE IQ Home version>
CATALOG_DB_FILE = <Catalog database file location>

Connect to the IQ database and confirm databases are recovered and online.

dbisql -c "uid=<username>;pwd=<password>;eng=<engine name>;dbn=<database name>;" -nogui

SAP MaxDB

Script name: /var/act/scripts/ACT_MAXDB_lvmRestore_newTarget.conf

Script location: /act/custom_apps/maxdb/restore

Arguments to the script

OSUSER = <Target MAXDB OS User>
SRC_DBSID = <Source MAXDB SID of the source database to restore from>
TARGET_DB_USER = <Target MAXDB Instance DBM username and password> </br>
  These must be the same as source DBM username and password. You can
  change the password after the restore operation is finished.
TARGET_DBUSER_PASSWD = <Target MAXDB Instance DBM username and password><br>
  These must be the same as source DBM username and password or restore 
  and recovery fails. You can change the password after the restore
  operation is finished.
TARGET_SERVER_NAME = <Target MAXDB SID to restore>
TARGET_MNT_PNT = <This is the value that you use in step 3>
UNTIL_TIME = Recovery Time in the format: YYYY-MM-DD HH24:MI:SS
MANIFEST_FILE_LOC = <`cat /var/act/log/UDSAgent.log | grep <JOBID> | grep -i "Manifest_File_" | grep "/act/tmpdata/" | awk -F"disk at " '{print $2}'`>
JobID = <`cat /var/act/log/UDSAgent.log | grep -w <TARGET_MNT_PNT> | grep "GEN-INFO" | tail -1 | cut -d']' -f2 | cut -d' ' -f2`>
LOG_BKP_MNTPT = <`df -h | grep <TARGET_MNT_PNT> | grep "_archivelog" | awk '{print $NF}''>
BEGIN_TIME = <`cat /var/act/log/UDSAgent.log | grep "BEGIN_TIME" | grep -w <TARGET_MNT_PNT> | awk -F"BEGIN_TIME=" '{ print $2 }' | cut -d' ' -f1-2 | cut -d'"' -f2'>
SRC_DB_VERSION = <`dbmcli -d <SRC_DBSID> dbm_version | grep "VERSION" | awk -F"= " '{print $2}'.>

Procedure

To restore a volume-level backup image to a new target, follow these steps:

  1. From the App Manager Applications list, right-click the protected database and select Access. You can use the Managed Backup Plan status filter to show only protected databases.

  2. Select the latest snapshot to recover, and choose Mount.

  3. Provide a target mount point under mount location, for example, /dmpRstNew. This is used as the variable TARGET_MNT_PNT.

    The database backup is mounted under /dmpRstNew (TARGET_MNT_PNT) and the log backup is mounted under /dmpRstNew_archivelog (LOG_BKP_MNTPT).

  4. Log into the database server as root. Change the directory to /act/custom_apps/<database type>/restore.

  5. Run the script.

    SAP IQ

    /act/custom_apps/sybaseiq/restore/act_sybase_lvm_customdb_recovery.sh

    SAP MaxDB

    /act/custom_apps/maxdb/restore/ACT_MAXDB_lvmRestore_newTarget.sh

    Connect to the MaxDB instance and confirm that the databases are recovered and online:

    dbmcli -d <TARGET_SERVER_NAME> -u
<TARGET_DB_USER>,<TARGET_DBUSER_PASSWD>
db_state

  6. Unmount the mounted snapshot image.

    SAP MaxDB only. After the database is restored, take a dummy or dump database backup to generate a backup history point in backup_history_list, which is required to take log backups. For example, the following:

    dbmcli -d <DB> >MAXDB_KEY -uUTL -c backup_start <TEMPLATE_NAME> DATA AUTOIGNORE

    Without this, future volume level backups fail with the error fail to trigger autolog backup template.

Change the password

In some cases, you may need to change the database password.

SAP IQ

SAP MaxDB

  1. To change the password, log into the target db as DBM user:

    dbmcli -d TARGET_SERVER_NAME -u TARGET_DB_USER,TARGET_DBUSER_PASSWD user_changepwd dbm

For example, for DBM user changing the password from abcdef to fedcba, log in:

dbmcli -d glxn -u dbm,abcdef

Run this command:

user_changepwd dbm abcdef

  1. Log in again with the new password:

    dbmcli -d glxn -u dbm,fedcba

Recover a Full+Incremental backup image to a new target

The following sections include information on recovering a Full+Incremental backup image to a new target.

Required script

Before you can mount a database to a new target, you must prepare a script.

IBM Db2

Script name: ACT_DB2_dumprestore_newTarget.sh

Script location: /act/custom_apps/db2/dump/

Script:

#/act/custom_apps/db2/dump/ACT_DB2_dumprestore_newTarget.sh
SOURCE_INSTANCE=db2inst1 TARGET_MNT=/db2mnp DB_LIST=NZL,IND ARCHIVELOG_MNT=/db2mnp_archivelog SOURCE_LOGARCHMETH1=/db2logbackup UNTIL_TIME=2019-11-13-09.37.41.000000
ACT_JOBNAME=Job_123456

Arguments to the script:

SOURCE_INSTANCE = <Db2 Instance name>
DB_LIST= <Comma-separated database list to restore>
TARGET_MNT = <Mount point specified during mount>
ARCHIVELOG_MNT= <Archive Log backup mount point name>
UNTIL_TIME = <Recovery Time (Format: "YYYY-MM-DD-HH.MI.SS")>
SOURCE_LOGARCHMETH1= <Db2 Source database archivelog location, must be local disk>
ACT_JOBNAME= <standard mount jobname>

Connect to the Db2 instance and confirm that the databases are recovered and online.

db2 connect to <dbname>
db2 select db_status FROM SYSIBMADM.SNAPDB

PostgreSQL

Script name: ACT_POSTGRESQL_dumprestore_newTarget.sh

Script location: /act/custom_apps/postgresql/dump/

Script: /act/custom_apps/postgresql/dump/ACT_POSTGRESQL_dumprestore_newTarget.sh OSUSER <postgres_osuser> BASEDIR=<postgres_home> PORT=<postgres_port> DB_LIST=<comma separated db list> DUMPBKPLOC=<mountpoint name> [ DBUSER=<db_user> DBPASSWD=<db_password> ]

Arguments to the script 

OSUSER = <PostgreSQL instance OS user>
BASEDIR = <PostgreSQL software homelocation>
PORT = <Target PostgreSQL instance portnumber>
DB_LIST = <Comma separated database list, which need to restore>
DUMPBKPLOC = <Mountpoint provided during mount>
DBUSER = <PostgreSQL database username>
DBPASSWD = <PostgreSQL database user password>

Example

/act/custom_apps/postgresql/dump/ACT_POSTGRESQL_dumprestore_newTarget.sh
OSUSER=postgres BASEDIR=/home/postgres/postgresql_home_11.0 PORT=5434
DB_LIST=actdb,test1 DUMPBKPLOC=/smpMnt

Connect to the PostgreSQL instance and check if the database was recovered.

[postgres@secondary.postgres /home/postgres]$ psql -p5434 -upostgres -dpostgres
psql (11.0)
Type "help" for help.

postgres=# \l

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   | Ctype    |   Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 actdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                                                               postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                                                               postgres=CTc/postgres

 test1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

(6 rows)

SAP ASE

Script name: ACT_SYBASE_dumprestore_newTarget.sh

Script location: /act/custom_apps/sybase/dump

Script: 

#/act/custom_apps/sybase/dump/ACT_SYBASE_dumprestore_newTarget.sh SYBOSUSER=sybase
SRC_SYBASE_SQLD=/home/sybase/Sybase16Home/OCS-16_0 TARGET_DB_USER=sa
TARGET_DBUSER_PASSWD=sybase ACT_NAME=ASE1 TARGET_MNT_PNT=/dmpRstNew SRC_DBNAME=CU1
UNTIL_TIME="2019-10-22 22:13:40" BEGIN_TIME="2019-10-22 19:07:00"
LOG_BKP_MNTPT=/dmpRstNew_archivelog SRC_PAGE_SIZE=2048

Arguments to the script:

SYBOSUSER = <Target ASE OS user>
SRC_SYBASE_SQLD = <Target ASE OCS location ($SYBASE/$SYBASE_OCS)>
TARGET_DB_USER = <Target ASE instance username>
TARGET_DBUSER_PASSWD = <Target ASE instance password>
ACT_NAME = <Target ASE server name>
TARGET_MNT_PNT = <Mount point specified during mount>
SRC_DBNAME = <Comma separated db list to restore>
BEGIN_TIME = <Dump backup begin time (Format: YYYY-MM-DD HH24:MI:SS)>
UNTIL_TIME = <Recovery time (Format: YYYY-MM-DD HH24:MI:SS)>
LOG_BKP_MNTPT = <Logbackup mount point name>
SRC_PAGE_SIZE = <Source database page_size>

Connect to the SAP ASE instance and confirm that the databases are recovered and online.

#isql -U<username> -P<password> -S<ASE server name>
sp_helpdb
go

SAP IQ

Script name: ACT_SYBASEIQ_dumprestore_newTarget.sh

Script location: /act/custom_apps/sybaseiq/dump

# /ACT_SYBASEIQ_dumprestore_newTarget.sh OSUSER=sybaseiq TARGET_MNT=/bkp/iqmnh
SRC_DB_DBA_USER=act SRC_DB_DBA_PWD=passwd SYBIQ_HOME=/home/sybaseiq VERSION=IQ-16_1
ACT_NAME=<database name> CATALOG_DB_FILE=/data1/<database name>/<database name>.db LOG_BKP_MNTPT=/bkp/iqmnh_archivelog

Arguments to the script:

OSUSER = <SYBASE IQ OSUSER name>
ACT_NAME=<SYBASE IQ Source Database name>
TARGET_MNT = <Mount point specified during mount>
LOG_BKP_MNTPT= <Archive Log backup mount point name>
SYBIQ_HOME=<SYBASE IQ Home Location >
SRC_DB_DBA_USER=<Utility Database user name>
SRC_DB_DBA_PWD= <Utility Database password >
VERSION=<SYBASE IQ Home version >
CATALOG_DB_FILE = <Catalog database file location >

Connect to the IQ database and confirm databases are recovered and online.

dbisql -c "uid=<username>;pwd=<password>;eng=<engine name>;dbn=<database name>;" -nogui

SAP MaxDB

Script name: ACT_Maxdb_dumpRestore_newTarget.conf

Script location: /act/custom_apps/maxdb/dump.

Arguments to the script:

OSUSER = <Target MAXDB OS User>
SRC_DBSID = <Source MAXDB SID of the source database to restore from>
TARGET_DB_USER = Target MAXDB Instance DBM username and password.</br>
These must be the same as source DBM username and password or recovery 
fails. You can change the password after restore is finished.
TARGET_DBUSER_PASSWD = Target MAXDB Instance DBM username and password. These must
be the same as source DBM username and password or restore and
recovery fails. You can change the password after the restore
is finished.
TARGET_SERVER_NAME = Target MAXDB SID to restore
DUMPBKPLOC = This is the value that you can use in step three in the restoring a traditional file-based Full+Incremental backup image to a new target</a>
DBADMIN_PWD = DBADMIN Password for the target database
UNTIL_TIME = Recovery time in the format: YYYY-MM-DD HH24:MI:SS
LOG_MNT_PNT = TargetDB log file mount point If target server directory structure
is different from source
DATA_MNT_PNT = TargetDB data file mount point If target server directory structure
 is different from source
MANIFEST_FILE_LOC = `cat /var/act/log/UDSAgent.log | grep <JOBID> | grep -i "Manifest_File_" | grep "/act/tmpdata/" | awk -F"disk at " '{print $2}'`
JobID = `cat /var/act/log/UDSAgent.log | grep -w <DUMPBKPLOC> | grep "GEN-INFO" | tail -1 | cut -d']' -f2 | cut -d' ' -f2`
DUMPBKPLOC = `df -h | grep <DUMPBKPLOC> | grep "_archivelog" | awk '{print $NF}'`
LOG_BKP_MNTPT = `df -h | grep -w <DUMPBKPLOC> | grep "_archivelog" | awk '{print $NF}'`
BEGIN_TIME = `cat /var/act/log/UDSAgent.log | grep "BEGIN_TIME" | grep -w <DUMPBKPLOC> | awk -F"BEGIN_TIME=" '{ print $2 }' | cut -d' ' -f1-2 | cut -d'"' -f2`
SRC_DB_VERSION = `dbmcli -d <SRC_DBSID> dbm_version | grep "VERSION" | awk -F"= " '{print $2}'`

Connect to the MaxDB instance and confirm that the databases are recovered and online:

dbmcli -d <TARGET_SERVER_NAME> -u
<TARGET_DB_USER>,<TARGET_DBUSER_PASSWD>
db_state

Procedure

To restore a file-based backup image to a new target, follow the instructions below:

  1. From the App Manager Applications list, right-click the protected database and select Access.

  2. Select the latest snapshot to recover and click Mount.

  3. Provide a mount point under mount location, for example: /mymountpoint.

    The database backup is mounted under /mymountpoint and the log backup is mounted under /mymountpoint_archivelog.

  4. Retrieve the JobID of the mount from /var/act/log/UDSAgent.log by running the following command:

    grep "mount -t " /var/act/log/UDSAgent.log  | grep -w "<mountPoint provided in step3>"|tail -1

    For example:

    # grep "mount -t " /var/act/log/UDSAgent.log  | grep -w "/db2mnp" |tail -1
2019-11-18 23:59:19.740 GEN-INFO  [22488] Job_0404207 Spawning cmd: mount -t ext4 /dev/act403764_DBDump_1574101677612/act_staging_vol /db2mnp 2>&1

  5. ARCHIVELOG_MNT is equal to _archivelog. Refer to step three.

  6. Login to the database server as root. On the server, change the directory to the following:

    cd /act/custom_apps/<database type>/dump

  7. Run the script from command line—as root.

  8. Unmount the mounted dump snapshot image.