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?
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)
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.
