Want to hire me? For availability and scheduling please email info@arcepm.com

28 February 2017

The Compleat Idiot's Guide to PBCS No. 20 -- A lightweight and even better EPM cloud backup

Not better.  Best.  At least in the cloud.

Yr. Fthl., Hmbl., & Obt. Svt. has written how to back up on-premises Essbase and EPM systems.  But what about the cloud?  There is no MaxL to be found in PBCS or nor is there really any LCM utility.  Yes, Application Management does that in the cloud via the UI and yes, there is a single generation nightly backup via the maintenance window, but what about beyond then?  What if you want backups to go to your data center?  Or have multiple backups in the cloud itself?

Chris Rothermel has again come to the rescue with an approach that is – like just about everything in the cloud – better than what’s on offer in on-premises.  Yes, it’s sad that advance after advance seems to pass on-premises by but we must all adapt or perish as must Oracle.  Although I have not one single bit of proof, I think (I hope) that when the Big Release comes to on-premises EPM some time before Kscope17 it will bring some of the automation advances the EPM cloud has today.  Watch this space to see how right or wrong I am on that.  Edited to add that alas, no, probably not based on a conversation from yesterday.  Bummer but let’s hope my source is wrong.

As for this backup itself, it’s better or even best because its foundation is both simple and sophisticated.  What I mean by that is that Chris’ approach uses PBCS’ (EPBCS applies equally as does I think every other Oracle EPM cloud product) own Application Management functionality (for Luddite on-premises, think LCM) tools to define and create the backup and PBCS’ single utility, epmautomate, to do the EPM work (again, hidebound on-premises types, think the LCM utility as well as OS script commands to pull files onto an on-premises target).  

I really, really, really like Chris’ approach – better maybe than my own – and encourage you to take a good hard look at it and think about, if you’re an on-premises customer, how to meld it with my approach or Peter Nitchke’s.  The sky’s the limit.

For cloud, I can’t think of any other way than Chris’.

Enough of my blather, enjoy his excellent work below and again Chris, thank you so much for sharing your knowledge.

Timestamp and Keep Cloud Snapshots

The current cloud solution backs up your application nightly.  It archives everything created for the application including the data stored in the application into an LCM file called “Artifact Snapshot” and you get to set what time of day this job runs.  I happened to delete this “Artifact Snapshot” file one day and it re-appeared the next day.  From this I’ve determined that “Artifact Snapshot” is defined and controlled by Oracle when running the daily maintenance.  Every day at the same set time this job runs and creates a complete snapshot of your application.  The complete backup of your application is stored in a file called “Artifact Snapshot.”  There is only one file called “Artifact Snapshot” and there is only one recent copy of this file stored in the cloud.  
  • What if you want to keep more than one backup in the cloud?
  • What if you want the Snapshot as of right now instead of getting what was run at the scheduled daily maintenance time?
  • How do you enforce the number of copies retained and avoid storing a bunch of obsolete files?
  • What if you want to retain copies for a Snapshot other than “Artifact Snapshot”?
  • What if you have multiple applications to backup?
  • How can you schedule this?
  • What if I want to keep copies on both the customer owned machines and also on the cloud?

I’ve developed a batch script process that addresses these questions.  

Configure Parameters

This solution is easily modified for your environment.  Provide the appropriate epmautomate login credentials for URL, USER, PASSWORD, and DOMAIN.  These must be valid parameters that you’d use when using epmautomate login.  Similarly the SnapshotName must be a snapshot that can be found in PBCS.  For simplicity we’ll leave ‘Artifact Snapshot’ in place because that is the default snapshot used by the maintenance window and all cloud instances have this snapshot name.  The other variables for appname and env are ideally the name of your application and environment but you can name them whatever you like.  They are used in the script to help identify the backup files.  Finally the NumberOfBackups variable is a number which identifies how many backups for your application you wish to retain.
Fictitious Example:


Q:  What if you want to keep more than one backup in the cloud?
A:  Run the configured script.  It will download the snapshot, rename it, and upload it back to the cloud.  The timestamp placed on the snapshot represents the time the file was downloaded and renamed.  It is likely different than the time when the snapshot was actually created.

Q:  What if I want the Snapshot as of right now instead of getting what was run at the scheduled daily maintenance time?
A:  Run the configured script with an additional parameter to invoke the epmautomate exportsnapshot command.  The newly generated file is then downloaded, renamed, and uploaded back to the cloud.  The file uploaded back to the cloud has the extra parameter it was called with to differentiate it from a timed backup.  The timestamp reflected on this backup represents the time of the snapshot, not just the time the snapshot was downloaded.  In the example below I use the german word “jetzt” meaning now (at the present) to know that the timestamp of this file matches when the file was generated and not just when the file was downloaded.  Running the script with the extra parameter runs the extra EPMAutomate export command followed by the downloadfile command.  The word ‘jetzt’ was just used as an example.  You can use any word you like and know it will be appended to the zip filename.

Q:  How can I enforce the number of copies retained and avoid storing a bunch of obsolete files?
A:  For files downloaded renamed and stored on the Windows server this is very easy.  Cleaning up files stored in the cloud this is much trickier for several reasons.  The cloud does not have a rename feature, the cloud listfiles command does not show the date timestamp, and finally LCM files (zip files) are handled differently in the cloud and effectively disappear from view.  Other zip files will show just fine using the listfiles command but LCM files are not shown.  Since epmautomate listfiles command does not show you the list of LCM zip files we have to maintain a list separately.    For this reason a separate list of LCM files is kept to record what known LCM files are in the cloud.  The parameter NumberOfBackups is used to check this list of LCM files on the cloud.  If the number of files exceeds the threshold the oldest file(s) are deleted so only the desired number of most recent backups is kept.

Q:  What if I want to retain copies for a Snapshot other than “Artifact Snapshot”?
A:  Create that snapshot definition in the Migration section of the cloud.  Then update your batch file accordingly.  If for example I created a LCM export called “Rothermel Special” in the cloud and then I can update batch file with this new snapshot name.  

Q:  What if I have multiple applications to backup?
A:  Create separate batch files for each and just update the necessary parameters.  I recommend putting each application in its own folder to avoid a potential for mixing files.  Since the default snapshot which is downloaded is called “Application Snapshot” and it creates “Application Snapshot.zip” in your directory you could run into trouble with two parallel executions.  So either ensure the batch files are run in serial or put them in separate directories and invoke them in separate session windows.  

Q:  How can I schedule this?

Q:  What if I want to keep copies on both the customer owned machines and also on the cloud?
A:  My first solution for this backup process only stored the timestamp copies on the local machine.  The limitation of that is you had to have access to the machine to find the backups.  We realized it would be better to store the backup on the Cloud.  Since the epmautomate commands do not have the copy or rename capability this solution forces us to download the file from the cloud rename it locally and then upload the named file.  There’s no extra work in keeping the archive files on both the local machine and the cloud.  To enforce the number of backups limit we run a single command to delete excess files on the local machine.  I realize keeping files on both the local machine and the cloud is a bit like wearing both a belt and suspenders.  Either one will keep your pants up.  This solution is storing the files in both locations and you can modify it to only keep the files in the cloud or just keep the files on the local machine.

Solution Reference

The solution calls for additional scripts which are provided and also uses a Microsoft operating commands.  Once the behavior of these scripts or commands are understood on their own you can better understand how they are used in the larger solution.  The complete list of scripts called by the solution is provided.  Links referencing the documentation on native Microsoft OS commands are also provided below.

Supporting Scripts

  • timestamp.bat – A DOS script that stores timestamp data to variables
  • ea_deletefiles.bat – A DOS script that recursively calls epmautomate for a list of files
  • head.bat – A DOS script that acts like the Unix/Linux command with the same name used to show the first X number of non-blank records in a file

Native Microsoft OS Commands

  • sort – A native DOS command used to sort the contents of a file in either ascending or descending order (aka reverse order /r)
  • more – A native DOS command that can be used like the Unix tail function to when supplied with a number (+n)
  • command redirection operators (>) and (>>) to either create/overwrite (>) or append to a file (>>)  

Script Appendix


@echo off
REM This script taken from the following URL:
REM http://www.winnetmag.com/windowsscripting/article/articleid/9177/windowsscripting_9177.html
REM This is working on a US machine.  Time and date formats in other countries may differ.
REM Create the date and time elements.
for /f "tokens=1-7 delims=:/-, " %%i in ('echo exit^|cmd /q /k"prompt $d $t"') do (
  for /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^|date') do (
     set dow=%%i
     set %%a=%%j
     set %%b=%%k
     set %%c=%%l
     set hh=%%m
     set min=%%n
     set ss=%%o

REM Let's see the result.
echo %dow% %yy%-%mm%-%dd% @ %hh%:%min%:%ss%

REM We'll use these %variables% in other scripts for timestamp purposes


@echo off
REM ---------------------------------------------------------------------------
REM  ea_deletefiles.bat
REM  Author:      Chris Rothermel
REM  Date:        2017-01-12
REM  Usage:      ea_deletefiles <filename containing one filename per row>
REM  Purpose:   This script recursively calls the deletefile command for each
REM             record in the file provided.  There are other checks on the
REM             file such as checking it exists and checking to see if there are
REM             any records in the file.
REM             Input:  The input file should have no quotation marks and no trailing
REM             whitespace.  The record delimiter is a new row (<CR> <LF>).
REM             Pre-Req:  This script assumes an epmautomate session is already in use.
REM             Use the epmautomate login command to initiate a session.
REM ---------------------------------------------------------------------------

REM Reset %erorrlevel%
verify > nul

REM Check if a filename parameter has been provided
IF [%1]==[] goto :Usage
REM Check if the file exists
IF NOT EXIST "%1" goto :InvalidFile
REM   Check if the file is empty
for %%A in ("%1") do if %%~zA==0 goto EmptyFile

REM Recursively call deletefile for each record in the file
REM   Trim trim any left whitespace; beware whitespace on the right
REM   No quotes allowed in the records
REM   One filename per row
for /F "tokens=* usebackq delims= " %%a in (%1) do (
    call echo Deleting "%%a%%"
    call epmautomate deletefile "%%a%%")

REM * Successful Completion *
ECHO Requested epmautomate delete commands submitted
ECHO Verify and check for deletefile.log files
exit /b %errorlevel%

REM ---- Warnings and Errors ----
ECHO Warning:  No records found in the file provided

REM Use the special %0 variable to get the path to the current file.
REM Write %~n0 to get just the filename part.
ECHO You must have a session with EPM Automate active to use this.  Use the epmautomate login command.
exit /b %errorlevel%

ECHO Warning: File "%1" does not exist
exit /b 30

echo ERROR: %errorlevel%.
exit /b %errorlevel%



@echo off

if [%1] == [] goto usage
if [%2] == [] goto usage

call :print_head %1 %2
goto :eof

REM print_head
REM Prints the first non-blank %1 lines in the file %2.
setlocal EnableDelayedExpansion
set /a counter=0

for /f ^"usebackq^ eol^=^

^ delims^=^" %%a in (%2) do (
       if "!counter!"=="%1" goto :eof
       echo %%a
       set /a counter+=1

goto :eof

echo Usage: head.bat COUNT FILENAME

REM http://stackoverflow.com/questions/130116/windows-batch-commands-to-read-first-line-from-text-file


@echo off
REM ---------------------------------------------------------------------------
REM Purpose:  Keep snapshots in the cloud.  Find them in Migration/Snapshot.
REM           This process keeps a local file identifying the existing snapshots.
REM Usage 1:  Downloads the existing SnapshotName with timestamp
REM           C:\Oracle\EPM Automate\bin\cloudbkup_%appname%_%env%_snapshot
REM           -> maximus_prd_Artifact Snapshot_Fri 2017-01-06 @ 1016.zip
REM Usage 2:  Generates and downloads a new (current) snapshot with timestamp
REM           Supply any additional parameter when invoking the script
REM           C:\Oracle\EPM Automate\bin\cloudbkup_%appname%_%env%_snapshot <name>
REM           -> maximus_prd_Artifact Snapshot_Fri 2017-01-06 @ 1016 <name>.zip
REM Input:    Environment Variables, Snapshot Name, Number of backups to keep
REM Output:   Artifact Snapshot File (zip) with a unique timestamped name
REM ---------------------------------------------------------------------------
REM Author:   Chris.Rothermel
REM Date:     January 12, 2017
REM Note:     This code is based on the sample code provided in the EPMAutomate
REM           documentation and other code snippets found online.
REM ---------------------------------------------------------------------------
REM Pre-Requisites
REM    1.  This script requires the EPMAutomate utility to be installed
REM    2.  This script is written to be run in an MS Windows environment
REM    3.  This script calls 'timestamp.bat' which must be accessable
REM    4.  This script calls 'ea_delete_list_of_files.bat' which must exist
REM ---------------------------------------------------------------------------
REM Multiple Downloads
REM         It is recommended for this batch file to be in its own directory
REM         unique to the PBCS application to avoid any data mixup when
REM         when downloading snapshots that may have the same name.
REM ---------------------------------------------------------------------------
REM REM Environment Variables
REM Password can be the actual typed password or an encrypted password file.
REM If a file is used and is not in the same directory put the full path to the
REM file.
SET url=https://
SET user=
SET password=
SET appname=
SET env=
SET domain=
SET /A NumberOfBackups=10
SET SnapshotName=Artifact Snapshot
REM ---INPUT^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^--INPUT---

REM ASK --- Tony  
setlocal EnableExtensions
setlocal EnableDelayedExpansion
REM ASK --- Tony

REM Use a separate generic batch file to set system timestamp variables
REM Variables:  %dow% %yy% %mm% %dd% %hh% %min% %ss%
call timestamp.bat

rem EPM Automate Login
ECHO Logging into %appname% %env% %url%
call epmautomate login %user% %password% %url% %domain%

REM If the script is called without an additional parameter just get the existing snapshot
REM The existing parameter identifies this to generate a new export and append %1 to the name
IF [%1]==[] goto :GetExisting

REM This portion of the script calls exportsnapshot and generates a new snapshot for the
REM current date/timestamp.  It takes more time to create the new snapshot.  The timestamp
REM of the file accurately notes when the snapshot was generated.
REM     exportsnapshot (Oracle Doc)
REM     Repeats a previously performed export operation to create a snapshot of Migration
REM     content.  You can download the exported snapshot from the default location.
REM     Usage:  epmautomate exportsnapshot SNAPSHOT_NAME where
REM     SNAPSHOT_NAME is the name of an existing snapshot in Migration.  This snapshot is
REM     replaced by the new snapshot.
ECHO Generating snapshot:  %SnapshotName% %1
call epmautomate exportsnapshot "%SnapshotName%"

REM Download the newly generated file
call epmautomate downloadfile "%SnapshotName%"

REM Rename the downloaded artifact and append the timestamp to identify this as a NEW snapshot
echo .
ECHO Renaming "%SnapshotName%.zip" to "%appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow% %1.zip"
rename "%SnapshotName%.zip" "%appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow% %1.zip"

REM Uploading the renamed file to the cloud
echo Uploading "%appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow% %1.zip"
call epmautomate uploadfile "%appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow% %1.zip"

REM Sete the filename of this newly uploaded file to a variable for later use
REM Exclude the ".zip" extension for downloading and deleting these special EPM files.
ECHO SETTING tmp_filename to %appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow% %1
set tmp_filename=%appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow% %1

goto :KeepOnly

ECHO Getting existing snapshot: %SnapshotName%
REM This code block gets the existing SnapshotName and appends the timestamp.
REM The actual date the snapshot was taken could be different than the current date.  
call epmautomate downloadfile "%SnapshotName%"

REM Rename the downloaded artifact
echo .
ECHO Renaming "%SnapshotName%.zip" to "%appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow%.zip"
rename "%SnapshotName%.zip" "%appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow%.zip"

REM Uploading the renamed file to the cloud
echo Uploading "%appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow%.zip"
call epmautomate uploadfile "%appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow%.zip"

REM Set the filename of this newly uploaded file to a variable for later use

set tmp_filename=%appname%_%env%_%SnapshotName%_%yy%-%mm%-%dd% @ %hh%%min% %dow%
REM -----------------------------------------------------------------------------------------------------------------
REM -----------------------------------------------------------------------------------------------------------------

REM - Trim trailing white space
for /l %%a in (1,1,99) do if "!tmp_filename:~-1!"==" " set tmp_filename=!tmp_filename:~0,-1!

REM  Get the file that has the list of known snapshots for %appname%_%env%_%SnapshotName%
ECHO Downloading the known list of snapshots "%appname%_%env%_%SnapshotName%_filelist.txt"
CALL epmautomate downloadfile "%appname%_%env%_%SnapshotName%_filelist.txt"

REM - Add the new Snapshot filename to a managed list of files.  The file list must have each record on a separate row
REM   and no quotes may be used.
ECHO Adding "%tmp_filename%" to "%appname%_%env%_%SnapshotName%_filelist.txt"
ECHO %tmp_filename%>>"%appname%_%env%_%SnapshotName%_filelist.txt"

REM - 1.  Sort the list of files assuming the alphanumeric list of files is the same as the chronological list of files.
REM - It is very important for the date format to be in YYYY-MM-DD HHMM.  We do not know for certain if the
REM - lisfiles command lists the files by system date.
REM - 2.  Create a list of records exceeding %NumberOfBackups%
REM call sort "%appname%_%env%_%SnapshotName%.txt" /O "%appname%_%env%_%SnapshotName%_srt.txt"
REM  This command does both
ECHO Sorting "%appname%_%env%_%SnapshotName%_filelist.txt" and placing records in excess of %NumberOfBackups% into "%appname%_%env%_%SnapshotName%_filelist.del.tmp"
CALL sort /R "%appname%_%env%_%SnapshotName%_filelist.txt" | more +%NumberOfBackups% > "%appname%_%env%_%SnapshotName%_filelist.del.tmp"

REM  Delete any excess files by sending the .del.tmp files to ea_deletefiles.bat script for processing
ECHO Calling ea_deletefiles "%appname%_%env%_%SnapshotName%_filelist.del.tmp"
CALL ea_deletefiles "%appname%_%env%_%SnapshotName%_filelist.del.tmp"

ECHO Keeping only the most recent %NumberOfBackups% versions on the local machine
for /f "skip=%NumberOfBackups% eol=: delims=" %%F in ('dir /b /o-d /a-d "%appname%_%env%_%SnapshotName%*.*"') do @del "%%F"

REM  Sort "%appname%_%env%_%SnapshotName%_filelist.txt" from newest to oldest because we'll only keep the top newer files
CALL sort /r "%appname%_%env%_%SnapshotName%_filelist.txt" > "%appname%_%env%_%SnapshotName%_filelist.tmp"

REM  Keep only %NumberOfBackups% records in the file.  Use a separate batch script called head to do this.
CALL head %NumberOfBackups% "%appname%_%env%_%SnapshotName%_filelist.tmp" > "%appname%_%env%_%SnapshotName%_filelist.txt"

ECHO Uploading the updated list known list of snapshots "%appname%_%env%_%SnapshotName%_filelist.txt"
CALL epmautomate deletefile "%appname%_%env%_%SnapshotName%_filelist.txt"
CALL epmautomate uploadfile "%appname%_%env%_%SnapshotName%_filelist.txt"

REM Optionally Clean up excess files such as "%appname%_%env%_%SnapshotName%*.zip" and "%appname%_%env%_%SnapshotName%*.tmp"
REM Delete the deletefile.log files
REM del "%appname%_%env%_%SnapshotName%*.zip"
REM del "%appname%_%env%_%SnapshotName%*.tmp"
REM del "deletefile*.log"

ECHO logging out
call epmautomate logout
call timestamp.bat

echo Status:  Success %appname% %env% %SnapshotName% completed %1
exit /b %errorlevel%

echo Status:  Failed with error #%errorlevel%.
exit /b %errorlevel%

REM http://www.dostips.com/DtTipsStringManipulation.php#Snippets.TrimRightSubst

And that’s all

Not enough?  Just what do you want out of life, anyway?

You now have four different approaches to EPM backup.  Three are on-premises:  Essbase, Pete’s LCM way, and my LCM way.  This one is cloud-only and is thus doubly (cloud so ‘natch awesome and the code is awesome too) awesome.  Awesomeness aside, I think this subject has been covered more than adequately.  OTOH, I thought that about five years ago as well so only time will tell if it’s revisited.  

Chris, again, thank you so much for your work with the EPM community.

Be seeing you.