Introduction

This blog will cover the scenario of creating a custom provisioning process for database refresh in SAP LaMa. A while back I did a similar blog about custom refresh of SAP ASE database. The approach is similar.

It’s a one-time configuration effort as once the custom refresh process is created it can be executed multiple times from LaMa. In addition, blueprints can be created during the first execution that allow a virtually one-click operation afterwards.

Before getting into the details, I would like to mention a couple of points regarding the refresh options in LaMa.

Option 1: Using storage based approach where a storage snapshot is taken of the source. AnyDB refresh (e.g. HANA, ASE, MS-SQL, DB2, Oracle etc) is supported out of the box when using this option. Prerequisite is the use of partner integrations that are included with LaMa Enterprise.

Option 2: Restore based approach where backup of the source is used for the refresh. HANA is supported out of the box. Other databases require a customization which is what I am covering in this blog

Option 3: Replication based approach. HANA is supported out of the box. For other databases, a custom refresh can be created to support other replication tools.

Note: Some diagrams may appear too small or blurry to view. In this case please click on the diagram to enlarge it.

Lab Environment

LaMa Enterprise 3.0 SP23 (recommendation is to use the latest SP)

PCA license installed on source and target

Source System: All in one ECC with Oracle 19c running on SuSE Linux Enterprise 12 SP5; Hostname: nm-ora-1; SID: PNM

Target System: Identical to source system; Hostname: nm-ora-2; SID: QNM

Shared NFS: Mounted on both hosts as /efs (where the source database backup files are stored; also used for transferring files from source to target e.g. CONTROL.SQL)

SAP Host Agent Patch 56 (latest recommended)

SAP Adaptive Extensions 1.0 Patch 69 (latest recommended)

Software Provisioning Manager 1.0 SP35 (latest recommended)

Disclaimer

This blog is published “AS IS”. Any software coding and/or code lines / strings (“Code”) included are only examples and are not intended to be used in any productive system environment. The Code is only intended to better explain and visualize the features of SAP Landscape Management. No effort has been made to make the code production quality (e.g. security, error handling, robustness, etc). If you use any of the code shown, you are doing it at your own risk.

Information in this blog could include technical inaccuracies or typographical errors. Changes may be periodically made.

Assumptions

It is assumed that your environment is already setup correctly for creating custom processes in LaMa. This blog does not go over all the configuration steps. If this is your first time using LaMa Automation Studio then I recommend that you start with something simple and then move on to more complex setups like custom provisioning. Please refer to this blog for other examples. If you are new to LaMa configuration for provisioning tasks then you could refer to this blog for guidance.

LaMa Flow

The high-level flow in LaMa of this custom Oracle database refresh is below.

1. Export configuration of the target system
2. Run orabrcopy on source system
3. Transfer files to NFS shared directory
4. Fence the target system
5. Perform brecover on target
6. Run control.sql file on target
7. Run software provisioning manager
8. Import configuration and execute PCA (post copy automation) on target
9. Unfence the target system

Note that the customization method described is not the only way. Other methods can be automated as well but the approach to creating the process in LaMa will be the same.

Prerequisites

On the source system do the following (you can change the directory paths but reflect them in the subsequent steps):

• Setup OraBRCopy.
  • Create directory /work/orabrcopy
  • Change permission to 777 (chmod 777 /work/orabrcopy)
  • Copy ORABRCOPY.SAR from the directory where SWPM is extracted. It should be in <SWPM directory>/COMMON/INSTALL/
  • Extract the SAR file in /work/orabrcopy
• Mount shared NFS as /efs
  • Mount options used in /etc/fstab: rw,hard,intr,rsize=32768,wsize=32768,timeo=14
  • Create subdirectories /efs/rman/<SID> and /efs/work with correct permissions (orasid for both source and target must have access so assign the right group permissions)
• Create /work/scripts directory
  • 755 permission (mandatory)
• Take online backup:
  • Edit /oracle/<SID>/sapprof/init<SID>.sap
  • Change the parameter backup_root_dir = /efs/rman/<SID>
  • brbackup -u / -m full -t online_cons -d disk
    • Or if compression and verify needed: brbackup –u / –m full –t online_cons –d disk –k yes –w
• Install latest host agent and adaptive extensions
• Install PCA license

On the target system, do the following:

• Ensure you have SWPM extracted and resides in the path /usr/sap/SWPM
• Mount shared NFS as /efs
  • Mount options used in /etc/fstab: rw,hard,intr,rsize=32768,wsize=32768,timeo=14
• Create /work/scripts directory
  • 755 permission (mandatory)
• Install latest host agent and adaptive extensions
• Install PCA license
• Ensure host is enabled for isolation in LaMa
• Have a good up to date backup of target just in case something goes wrong and you need to revert back to the original state

LaMa Configuration Steps

1. Create the action configuration files on source for orabrcopy (OS Level)
2. Create orabrcopy execution script on source (OS Level)
3. Create the action configuration files on target for recovery (OS Level)
4. Create recovery script on target  (OS Level)
5. Create the action configuration files on target for Dark Mode SWPM (OS Level)
6. Create Dark Mode SWPM execution script on target (OS Level)
7. Create Provider Definitions (LaMa)
8. Design Custom Provisioning (LaMa)
9. Assign custom provisioning process to instance (LaMa)

LaMa Configuration File — Source

We rely on the SAP Host Agent and therefore the configuration files are located in directory /usr/sap/hostctrl/exe/operations.d and end with “.conf”

Create file source_orabrcopy.conf

Username: ora$[SAP_LVM_SRC_SID:#tolower]
Name:source_orabrcopy
Description: ORABRCOPY
Command: /work/scripts/source_orabrcopy.sh $[SAP_LVM_SRC_SID] $[SAP_LVM_TARGET_SID] $[PARAM-PASS:#required]
ResultConverter: flat
Platform: Unix

Line 1: Specifies the username to execute the script. The SID for source is passed as a variable (i.e. PNM) and this is converted to lower case and “ora” prefixed to it. Therefore username will be “orapnm”. These variables are already available in LaMa and do not need to be prompted to enter them.

Line 2: Name of the operation

Line 3: Description

Line 4: The full path of the script and the arguments to be passed. PARAM-PASS is a custom parameter and will be used to enter the system password. LaMa will prompt for this and #required means it is mandatory

Line 5: No special handling is performed

Line 6: Only applicable to Unix/Linux hosts (will not be available on Windows hosts)

Refer to the LaMa guide section for detailed explanation of syntax

Set the right permissions:

• chown root:root <.conf file>
• chmod 755 <.conf file>

LaMa Configuration File (database recovery) — Target

Go to directory /usr/sap/hostctrl/exe/operations.d

Create the file target_recover.conf

Name: target_recover
Username: ora$[SAP_LVM_TARGET_SID:#tolower]
Command: /work/scripts/target_recover.sh $[SAP_LVM_SRC_SID] $[SAP_LVM_TARGET_SID]
ResultConverter: flat
Platform: Unix

Set the right permissions:

• chown root:root <.conf file>
• chmod 755 <.conf file>

The above configuration file will execute the script as orasid (oraqnm)

LaMa Configuration File (dark mode SWPM) — Target

Go to directory /usr/sap/hostctrl/exe/operations.d

Create the file DB_custom_swpm.conf

Name: DB_custom_swpm
Username: root
Command: /work/scripts/DB_custom_swpm.sh
ResultConverter: flat
Platform: Unix

Set the right permissions:

• chown root:root <.conf file>
• chmod 755 <.conf file>

The above configuration file will run the script as root. In the script, software provisioning manager  (SWPM) is called in dark mode. This means it is an unattended run of SWPM and we will not go through the usual dialog when running it. The section below describes how to set this up and then create the script.

We need to also create a configuration file for a dummy step to stop the default software provisioning manager from executing. This will just execute an echo command. The real software provisioning manager execution will be done as replacement to the restore backup step when designing custom provisioning.

Create the file custom_swpm_echo.conf

Name: custom_swpm_echo
Command: echo 'executing dummy SWPM'
ResultConverter: flat
Platform: Unix

Set the right permissions:

• chown root:root <.conf file>
• chmod 755 <.conf file>

Create Parameter-File for Dark Mode SWPM  — Target

Before we can run unattended swpm in a script we need to create the parameter-file. To do this run swpm in interactive mode and when you reach the summary screen do not proceed further.

My swpm was installed in /usr/sap/SWPM

cd /usr/sap/SWPM

./sapinst

• Login as root
• After login, select the SWPM option: Generic -> Oracle -> Refresh Database Content

• Choose Custom as I want to change the user from “oracle” to “orasid” which is what my installation has.

• On next screen, continue and accept default setting for profile.
• Fill in the DNS info as needed on next screen
• Next choose homogeneous system copy

• Enter the target DBSID on next screen
• Enter Database Server and Client versions on next screen
• On the screen for database user select orasid from the drop down

• Enter ABAP Schema and password in next screen
• Enter password details for database users on next screen
• Choose “Database already recovered” as the recovery method; You can also delete old users if you prefer

• Accept defaults on “Actions Before SAP System Start” (i.e. no check box ticked)
• Enter password for DDIC
• Decide on whether to cleanup operating system users or not

When you reach parameter “Review Parameters” screen — EXIT

At the command prompt on target host (after exit of the above), go into the latest log directory /tmp/sapinst_instdir/GENERIC/SYB/DBREFRESH/<log dir>/

Here you will find the file “inifile.params”. Copy this file to “QNM_inifile.params” (e.g. cp -p inifile.params QNM_inifile.params)

Copy the whole above directory structure from /tmp to a directory of your choice

e.g. cp -pr /tmp/sapinst_instdir /work

Script: source_orabrcopy.sh — Source

Below is a sample of the script for OraBRcopy. Put the scripts in /work/scripts of the source system

#!/bin/bash
# This will run on source as ora<sid>
SOURCE_SID=$1
TARGET_SID=$2
PASSWORD=$3
log_file=/tmp/custom_$TARGET_SID.log
orasid=`echo ora$SOURCE_SID | awk '{print tolower($0)}'`
path1=`eval echo "~$orasid"`
. $path1/.profile
rm -rf /efs/work/*
cd /work/orabrcopy
./ora_br_copy.sh -generateFiles -targetSid $TARGET_SID -password $PASSWORD -listenerPort 1521
cp CONTROL.SQL /efs/work
cp init$TARGET_SID.ora /efs/work
cp -r /oracle/$SOURCE_SID/sapbackup /efs/work
echo Script Completed >> $log_file
date >> $log_file

Script: DB_custom_swpm.sh — Target

Below is a sample of the script to launch the unattended swpm. Save it in /work/scripts of target system

#!/bin/sh
#Dark mode swpm for Oracle
#This is where post restore steps occur prior to PCA
SWPM_DIR=/usr/sap/SWPM
LOG_DIR=/tmp
LOG_FILE=lama_swpm_log
# Call sapinst
$SWPM_DIR/sapinst 'SAPINST_INPUT_PARAMETERS_URL=/work/sapinst_instdir/GENERIC/OR
A/DBREFRESH/inifile.params' 'SAPINST_EXECUTE_PRODUCT_ID=NW_DBRefreshOnly:GENERIC
.ORA.PD' 'SAPINST_SKIP_DIALOGS=true' 'SAPINST_SLP_MODE=true' 'SAPINST_START_GUI=
false' 'SAPINST_START_GUISERVER=false'
#
echo Script Completed >> $LOG_DIR/$LOG_FILE
date >> $LOG_DIR/$LOG_FILE

Script: target_recover.sh — Target

Save it in /work/scripts of target system

#!/bin/bash
# This will run on target as ora<sid>
SOURCE_SID=$1
TARGET_SID=$2
TARGET_DBSID=$TARGET_SID
backup_root_dir=/efs/rman/$SOURCE_SID
log_file=/tmp/recover_$TARGET_SID.log
shared_dir=/efs/work
orasid=`echo ora$TARGET_SID | awk '{print tolower($0)}'`
path1=`eval echo "~$orasid"`
. $path1/.profile
if [ -f "$log_file" ]; then
        rm $log_file
fi
DIR1=/oracle/$TARGET_DBSID/saparch
DIR2=/oracle/$TARGET_DBSID/oraarch
#Empty saparch directory
echo "Empty Dirs1" >> $log_file
if [ "$(ls -A $DIR1)" ]; then
     rm -rf /tmp/saparch
     cp -r $DIR1/* /tmp/saparch
     rm -rf $DIR1/*
fi
echo "Empty Dirs2" >> $log_file
#Empty oraarch directory
if [ "$(ls -A $DIR2)" ]; then
     rm -rf /tmp/oraarch
     cp -r $DIR2/* /tmp/oraarch
     rm -rf $DIR2/*
fi
#
#copy control file
echo "copy control file" >> $log_file
cp $shared_dir/CONTROL.SQL ~$orasid
#
#Save init.ora
echo "save init file" >> $log_file
cp /oracle/$TARGET_SID/19/dbs/init$TARGET_SID.ora /oracle/$TARGET_SID/19/dbs/init$TARGET_SID.ora_SAVED_REC
#
#Copy init.ora
echo "copy init file" >> $log_file
cp $shared_dir/init$TARGET_SID.ora /oracle/$TARGET_SID/19/dbs
#
#Copy sapbackup logs
echo "copy sapbackup file" >> $log_file
cp -r $shared_dir/sapbackup/* /oracle/$TARGET_SID/sapbackup/
#
#Rename backSID.log
echo "rename backSID file" >> $log_file
mv /oracle/$TARGET_SID/sapbackup/back$SOURCE_SID.log /oracle/$TARGET_SID/sapbackup/back$TARGET_SID.log
#
#Edit initSID.sap to give path of backup files
echo "edit initsid.sap file" >> $log_file
sed -i "/^backup_root_dir/cbackup_root_dir = ${backup_root_dir}" /oracle/$TARGET_SID/sapprof/init$TARGET_SID.sap
#
# Run brrecover
echo "run brrecover" >> $log_file
brrecover -u / -t reset -b last -f $TARGET_SID -c force
#
#Run the control.sql script
echo "run control.sql file" >> $log_file
cd $shared_dir
sqlplus /nolog <<EOF
CONNECT / AS SYSDBA
SHUTDOWN IMMEDIATE
@CONTROL.SQL
exit;
EOF

Provider Definition for source_orabrcopy

• Login to SAP LaMa and create a new provider definition as “Script Registered with Host Agent”
• Automation Studio -> Provder Definitions -> Create Provider
• Add the host nm-ora-1 (source) from the drop down and click “Retrieve Scripts”. The host agent fills the drop list.
• Choose the script (the configuration file) “source_orabrcopy” and give Name of “source_orabrcopy”. Also check mark Instance and Provisioning Replacement boxes. Click on Create Provider.

• Click on the newly created provider definition and go to Parameters tab. Click on “Add Parameter”

• Type is string and mark it as mandatory and secure

Provider Definition for target_recovery

Repeat the definition creation process for target_recovery on host nm-ora-2 (target). Below screen of already create provider definition. No parameters required.

Provider Definition for Custom SWPM

Repeat the definition creation process for custom swpm on host nm-ora-2 (target). Below screen of already create provider definition. No parameters required.

• We also need to create a dummy provider definition to replace the default software provisioning manager step.

Design Custom Provisioning Process

• Open Automation Studio -> Custom Provisioning and click on “Create”
• The custom provisioning has predefined steps. We need to replace some of these steps with our own custom steps.
  • Software Provisioning Manager needs to be replaced by our dummy step — custom_swpm_echo
  • Clone Volumes (Source System) needs to be replaced by our source_orabrcopy
  • Restore Backup (Target System) needs to be replaced by our target_recovery and DB_custom_swpm steps

• Note that the steps listed in automation studio are not necessarily in the right sequence. For example the software provisioning step shows at the top but is not the first step. Instead just focus on the steps that need to be replaced.
• Click on Software Provisioning Manager followed by click on “+”
  • Here we specify the provider definition “custom_swpm_echo” so it becomes a dummy step

• Don’t click on Create …. yet. We will do that once all replacement steps are added. The next step we replace is the “Clone Volumes (Source System)”. Since we are doing a database refresh using backup and restore we do not need to clone any storage volumes.
• If no volume is cloned on source then clone steps on target are irrelevant.
• Click on “Clone Volumes (Source System) and “+”
  • Choose source_orabrcopy as provider definition

• Now click on “Restore Backup (Target System) followed by “+”
  • Select target_recover as provider definition
• Press “+” again to add another step
  • Select DB_custom_swpm
• We have added two replacement steps here

• Now click on “Create Custom Provisioning Process”

Assign Custom Provisioning Process to Instance

We now need to assign this custom provisioning process to the instance which should use this process rather than the default process.

• Open the LaMa Configuration screen. Configuration -> Systems
• Find the source system PNM and click edit. Navigate to Provisioning & RFC tab. Select the above custom provisioning process from the drop down and check mark the box. Click save.

• Repeat the above for the target system QNM. Also ensure source system is selected from the drop down.

• Verify that you now see 2 systems referencing the custom provisioning process.
• Automation Studio -> Custom Provisioning

• Select defaults

• Enter system password