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&A

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?

A:  See the Automating Script Execution section of the EPM Automate Utility documentation.

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

Timestamp.bat

@echo off

REM This script taken from the following URL:

REM http://www.winnetmag.com/windowsscripting/article/articleid/9177/windowsscripting_9177.html

REM

REM This is working on a US machine.  Time and date formats in other countries may differ.

REM

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

ea_deletefiles.bat

@echo off

REM ---------------------------------------------------------------------------

REM  ea_deletefiles.bat

REM

REM  Author:      Chris Rothermel

REM

REM  Date:        2017-01-12

REM

REM  Usage:      ea_deletefiles <filename containing one filename per row>

REM

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

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

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

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%%")

IF %ERRORLEVEL% NEQ 0 goto :ERROR

REM * Successful Completion *

ECHO .

ECHO Requested epmautomate delete commands submitted

ECHO Verify and check for deletefile.log files

exit /b %errorlevel%

REM ---- Warnings and Errors ----

:EmptyFile

ECHO Warning:  No records found in the file provided

:Usage

REM Use the special %0 variable to get the path to the current file.

REM Write %~n0 to get just the filename part.

ECHO Usage: %~n0 FILENAME

ECHO You must have a session with EPM Automate active to use this.  Use the epmautomate login command.

exit /b %errorlevel%

:InvalidFile

ECHO Warning: File "%1" does not exist

exit /b 30

:ERROR

echo ERROR: %errorlevel%.

exit /b %errorlevel%

REM EOF

head.bat

@echo off

if [%1] == [] goto usage

if [%2] == [] goto usage

call :print_head %1 %2

goto :eof

REM

REM print_head

REM Prints the first non-blank %1 lines in the file %2.

REM

:print_head

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

:usage

echo Usage: head.bat COUNT FILENAME

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

Cloud_Backup_App_Env.bat

@echo off

REM ---------------------------------------------------------------------------

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

REM Usage 1:  Downloads the existing SnapshotName with timestamp

REM

REM           C:\Oracle\EPM Automate\bin\cloudbkup_%appname%_%env%_snapshot

REM           -> maximus_prd_Artifact Snapshot_Fri 2017-01-06 @ 1016.zip

REM

REM Usage 2:  Generates and downloads a new (current) snapshot with timestamp

REM           Supply any additional parameter when invoking the script

REM

REM           C:\Oracle\EPM Automate\bin\cloudbkup_%appname%_%env%_snapshot <name>

REM           -> maximus_prd_Artifact Snapshot_Fri 2017-01-06 @ 1016 <name>.zip

REM

REM Input:    Environment Variables, Snapshot Name, Number of backups to keep

REM

REM Output:   Artifact Snapshot File (zip) with a unique timestamped name

REM

REM ---------------------------------------------------------------------------

REM   

REM Author:   Chris.Rothermel

REM

REM Date:     January 12, 2017

REM

REM Note:     This code is based on the sample code provided in the EPMAutomate

REM           documentation and other code snippets found online.

REM

REM ---------------------------------------------------------------------------

REM Pre-Requisites

REM  

REM    1.  This script requires the EPMAutomate utility to be installed

REM  

REM    2.  This script is written to be run in an MS Windows environment

REM

REM    3.  This script calls 'timestamp.bat' which must be accessable

REM

REM    4.  This script calls 'ea_delete_list_of_files.bat' which must exist

REM

REM ---------------------------------------------------------------------------

REM  

REM Multiple Downloads

REM  

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 REM Environment Variables

REM

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.

REM  

REM ---INPUT--INPUT--INPUT--INPUT--INPUT-*-INPUT--INPUT--INPUT--INPUT--INPUT---

REM

SET url=https://

SET user=

SET password=

SET appname=

SET env=

SET domain=

SET /A NumberOfBackups=10

SET SnapshotName=Artifact Snapshot

REM

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

ECHO OFF

ECHO .

rem EPM Automate Login

ECHO Logging into %appname% %env% %url%

call epmautomate login %user% %password% %url% %domain%

IF %ERRORLEVEL% NEQ 0 goto :ERROR

ECHO .

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

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

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.

REM  

ECHO Generating snapshot:  %SnapshotName% %1

call epmautomate exportsnapshot "%SnapshotName%"

IF %ERRORLEVEL% NEQ 0 goto :ERROR

REM Download the newly generated file

call epmautomate downloadfile "%SnapshotName%"

IF %ERRORLEVEL% NEQ 0 goto :ERROR

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"

ECHO .

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

:GetExisting

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%"

IF %ERRORLEVEL% NEQ 0 goto :ERROR

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"

IF %ERRORLEVEL% NEQ 0 goto :ERROR

ECHO .

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"

IF %ERRORLEVEL% NEQ 0 goto :ERROR

ECHO .

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

:KeepOnly

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"

ECHO .

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"

ECHO .

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"

ECHO .

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 .

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"

REM

ECHO .

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"

:logout

ECHO .

ECHO logging out

call epmautomate logout

IF %ERRORLEVEL% NEQ 0 goto :ERROR

call timestamp.bat

GOTO EOF

:EOF

ECHO .

echo Status:  Success %appname% %env% %SnapshotName% completed %1

exit /b %errorlevel%

:ERROR

ECHO !

echo Status:  Failed with error #%errorlevel%.

exit /b %errorlevel%

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

REM

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.