Automating Backup & Recovery for Oracle EPM Cloud [Tutorial]

What happens when you have data loss or a devastating change to your Oracle Cloud application? Is there an undo button or safety net to help you easily bounce back?

We want to proactively help you with this topic, whether it's preventing minor and major data loss, being prepared in case of application corruption, or you simply want an undo for your application changes. You also may want to surgically undo for a specific application artifact like a form or setting, or a specific slice of data back several weeks. Can you call Oracle for help, or should you do it yourself?

The answer is – Yes, of course, Oracle can provide you with help. But, at this point in time (if you’re without your own disaster recovery strategy in place) your choices are limited to one solution. Oracle uses only the prior night's back up to restore. There is no option at this point to surgically restore data in scenarios of major or minor (partial) data loss. This will get your application restored from last night's back up for the entire application. So, it’s important to know how to do it yourself.

It’s also important to understand how Oracle's EPM Automate can help with disaster recovery (DR) scenarios, business application continuity, data loads, as well as application backup and recovery (including major and minor data loss).

For a more thought-out disaster recovery strategy and implementation for your business and application continuity, you can build your own backups, archive, as well as recovery process and automation scripts.

In this blog post, we’ll look at the aspects of a disaster recovery strategy. You’ll also have real-world client code examples to start using and understanding EPM Automate and Windows PowerShell, as well as Windows Task Scheduler. This includes basic PowerShell code like login, logout, error handling (custom and out-of-box), and dynamic operating folders for running EPM Automate in parallel.

Oracle Cloud Backup and Recovery Strategy

There is an Oracle Doc ID 2165323.1 on EPM Cloud Services Disaster Recovery (DR) and Backup Clarification that says administrators are responsible for backing up the application snapshots to a server and recovery applications and data.

So, we should have a strategy and a plan in place for such scenarios. Oracle also says Application Snapshots are not created if it has not been in use for 14 days. “Artifact Snapshot” is the name of the application snapshot which is taken daily during the maintenance windows. This contains the entire application and data. The download is called Artifact Snapshot.zip. For more information on PBCS / EPBCS, see “What Happens During Daily Maintenance” in Doc ID 2409275.1. To access these My Oracle Support links, you’ll want to register with My Oracle Support.

To get started with your backup and recovery strategy, it’s important to always remember your goal as an administrator. The critical component of Application Backups and Disaster Recovery for EPM administrators is to protect your…

• Environment
• Systems
• Applications
• Dimensions
• Data
• Calculations
• Reports
• Security

Backing up, downloading, and archiving your entire PBCS application is important in a recovery scenario. The Oracle Artifact Snapshot backs up the BSO and ASO application and plan types with all artifacts, Data Management (DM), and Security. Both EPM Automate and PowerShell work together to streamline and automate this with basic scheduling tools like Task Scheduler. Some archive strategies, using PowerShell, can be used to download the snapshots and archive Daily, Weekly, Monthly, Quarterly, and Yearly backups of the application snapshot and level 0 data exports, which are heavy on disk space requirements. So, at a minimum for backups, we suggest downloading the application snapshot and backups every night — keeping them for a recommended 30 days or at least 14 days. You might want to keep events like year-end backups or quarterly reporting for longer, assuming that there is enough disk space.

Oracle provides sample code using Windows Batch Command and detailed documentation on their sample code which can give you ideas. This tutorial helps package Oracle code using PowerShell and EPM Automate commands into an approach and glue together all the pieces in a disaster recovery strategy.

This is all related to recent work I did for a client implementing PBCS. These are also relevant to…

• Enterprise Planning and Budgeting Cloud Service (EPBCS)
• Oracle Strategic Workforce Planning Cloud Service (SWP)
• Financial Consolidation and Close Cloud Service (FCCS)
• Profitability and Cost Management Cloud Service (PCMCS)
• Account Reconciliation Cloud Service (ARCS)
• Enterprise Data Management Cloud Service (EDMCS)

Oracle Cloud's EPM Automate utility does not currently have commands for Oracle Enterprise Performance Reporting Cloud (EPRCS).

Recovery Point Objective

What is your SLA for restore point time for business continuity for your application? This will depend on the business you are in and the expectations of up-time for your business application.

Out of the box, Oracle’s Nightly Application snapshot has a recovery point objective that is 24 hours. Using methods below, we can make this recovery point within hours in the sense of capturing data and preventing data loss, while not locking out users or removing them from the system while doing it. This is done with the DataExport business rule command — not with the PBCS Data Export method. We can also capture other frequently changing artifacts like metadata. This can all be triggered together to export as frequently as you like on a schedule in Task Scheduler.

For applications like PBCS for Financial planning or an FCCS for accounting close, then you might be fine with nightly backups or twice a day. This is using Window Task Scheduler.

Weekly and daily operational analytical cube would be a case when your business application requires restore points intraday. We would typically archive the application snapshot (backup) to the server and recommend 2-3 data (and possibly metadata) backups throughout the day. I’ll go into more detail in a future blog post.

Application Snapshot and Recovery

In PBCS, application snapshots are backups of the application — it includes components of the application that are available in the Migration tile in PBCS on the web interface. In the Migration section for Planning, the Essbase Data components are the Essbase BSO data files called .pag, Index files .ind, and metadata .otl files for the Essbase BSO cube. For the Essbase ASO cube, there are the .Dat for the data files and the otl file and other files. These are the application components used to backup and restore the entire application for all the plan types. Bear in mind, the data and metadata need to be the same at all levels. If the data and metadata are not the same when restoring, then not all the data will be reloaded, and unfortunately, there won't be an error message or anything to tell you the restore didn't work — besides reviewing the data in a Smart View retrieve sheet (less than ideal – I know).

Recovery Options

The backup and restoration process are setup to help administrators with their own requirements to create their own automation process. This blog post provides a high-level blueprint on how to create backups and restore data relating to PBCS. All these topics will be explored in more detail in subsequent blog posts.

• EPM Automate & PowerShell
• Application Backups and Recovery
• Application Migrations
• Data Backups and Recovery
• Audit
• Major Data Loss
• Minor Data Loss

Tools and Methods

The tools and methods of the trade are customizable and configurable for each business. We’ll go more in-depth into the tools of these tools in future blog posts.

These include…

• Full application restore from a backup is the easiest and most straightforward, but all data and metadata must be restored together and must be exactly the same. There will not be error messages while importing, so please be careful and check the data in Smart View. The size of the backup zip can be large and time consuming to download, upload, and import the application snapshot (i.e. time-consuming to restore everything).
  Pro Tip: Uploading snapshot zip files of greater than 1 GB or so will require EPM Automate to upload and not through the Migration tile on the PBCS web interface.
  
  
• Smart View and Excel is of course the first option for most people in Finance. This option will work for small sets of data, and it of course allows detailed audit when turned on.
  
  
• FDMEE (aka Data Management) is used to reload the data and review the data load in the Workbench and Data Mappings. This works when the data to reload is only from the source system and not user inputted data.
  
  
• You can enable Audit for all types including data, so it can capture what data was changed by whom and when.
  
  
• Major data loss and recovery can be done by restoring the snapshot as a whole from a point in time, restoring from a backup of a data export file with specific data “carved out,” or reloading using Data Management.
  
  
• Minor Data Loss is recommended to restore using Smart View, to restore from a backup of a data export file with specific data “carved out,” or to reload using Data Management. Minor data loss is only for when specific data is required to be restored — like for a specific month or entity. All other data from the original data export can be deleted from the import file to restore only this specific data. I recommend the DataExport command to export the data and I recommend Importing using the PBCS Import function in the Application Overview tile. Choose the Essbase format, instead of the Planning Format. This option will be my next blog post so please stay tuned. 
  
  

Introducing EPM Automate and PowerShell

For the purposes of this blog post, I’ll briefly walk you through EPM Automate for PBCS with Windows PowerShell. In the next set of blog posts, we’ll take a more in-depth look into these tools.

EPM Automate is an Oracle automation utility tool for automating and scheduling repeatable tasks for PBCS and other Oracle Cloud applications. These are “wrapped” around PowerShell and are scheduled using Windows Task Scheduler.

PowerShell can be written in PowerShell ISE to run within the framework or Notepad++ as a good text editing tool.

EPM Automate is installed (which we’ll cover in another post). This is version 18.06.36 — the latest and greatest in Production (as of July 2018).

This is Notepad++ which is often used to write code. You can download it from http://notepad-plus-plus.org/ and compare code (using a Compare Plug-in from Source Forge open source platform). It's also helpful to use for formatting and writing code.

Windows PowerShell is a task automation and scripting language tool from Microsoft. The PowerShell is a command-line shell for the scripting language, it can execute four kinds of named commands, including…

• Cmdlets – These are native commands in the PowerShell language that are specific built-in functions and commands. PowerShell script is then constructed for use around a set of code and commands to automate and streamline a process.
• PowerShell functions – These are custom code with functions within PowerShell script.
• Call other executable programs like EPM Automate
• PowerShell custom script file with .ps1 extension and PowerShell script module with .psm1 extension

Logging

There is a lot of recommended logging messages and errors for PowerShell, but let's keep this simple.

• Use > to create the log file.
• Use >> to append to the file.

Below is a screenshot example used at several of my client implementations.

The log will be output to a log folder. 

This is the sample log.

Running EPM Automate in Parallel

Pro tip: Keep in mind with the EPM Automate Utility that it’s designed to run one automated process at a time. There are many use cases where we are required to load data via EPM Automate at the same time as automating calculations and automating pushing data from the BSO to ASO using Data Maps.

There is a way to do this with a little bit of code. When there is a requirement to run multiple EPM Automate processes at the same time, then there is a PowerShell trick for running “dynamic operating” folders, if you need to run and have multiple PBCS connections to the same or different environment in parallel using EPM Automate. I’ll go in more details with this in a later blog post.

Code: This operating folder function is stored in a library script. When called, this will create the operating folder and then the other function with move logs and files and delete the operating folder in a repeatable fashion. This is the function's code…

This deletes the folder and sends the log files, zip files, and other files to other folders. And then it will self-destruct. Pretty cool. 

Login

As a quick introduction to EPM Automate using the Windows PowerShell code, we can log into EPM Automate using the “EPMAutomate login” command. It will look like this…

• $(Get_TimeStamp) calls the function to time stamp the command on the same line.
• EPM Automate Login is a comment for the log file.
• This section for the pipe and the following code will let the EPM Automate command and message be in the same line for logging purposes. | Add-Content -Path $EPMAutomateLog -NoNewline
• "ErrorHandling " is calling the function of that name to run my custom handling code. See below for details.

Here's how it will look in the PowerShell ISE framework.

Time Stamp Logging

I recommend every key section of your automation script have…

• Comments
• Functions for repeatable code
• Global and local variables
• Pipes

The first step is time stamping the log file for every step.

Under this topic, the Get_TimeStamp is a function used for time stamping the beginning on the script.

Try / Catch Error Handling

Error handling is a critical component of an automation script like PowerShell. A script that runs correctly all the time won't require error handling. All automation scripts can't account for all issues and possible scenarios that can go wrong, because Murphy’s Law says, "Anything that can go wrong will go wrong."

In general, an exception breaks the normal flow of execution and executes a pre-registered exception handling.

There will be issues that occur at each EPM Automate step and PowerShell function, and we will want to address it. Therefore, error handling should be implemented in every piece of PowerShell code you create.

PowerShell should handle errors and then stop running the script in the current pipeline.

The Trap statement includes a list of statements to run with optional error types to be specified.

A script or command can have multiple Trap statements. Trap statements can appear anywhere in the script or command.

One common method is the “try and catch” method. The Try and Catch block is an exception-based method to throw an exception and handle it with a set of tasks if there is an exception — like terminating with an error message and stop all processes from move forward. This is to allow the administrator to fix and complete the resolution of the issue before moving forward in subsequent tasks.

Custom Error Handling

There are many other examples of error handling. I've created my own custom error handling procedure that I've become comfortable with — and it’s simple and straightforward to implement. It allows me to flag down issues and error with a 1 in the Last Status Code to be passed in Email, Logs, and to the status of the program within the Windows Event Log when done running for other programs to be notified of the success or failure if needed. Or when successful, allow the script to return a 0 (zero), then it will continue successfully to the next EPM Automate step.

This is placed inside functions and the main PowerShell script to catch any unsuccessful terminating errors that may come up inside a command.

The custom error handling code is…

The purpose of the script is to… 

• Catch the error on every step
• Log the error code in the log file
• End the file with duration and notes using variables
• End the failure email
• Delete the dynamic operating folder
• Exit with the failure status code

Log out

This logs out the service admin user from EPM Automate. It will have logged the ending of the file in the log file, and copy this to the log file.

Conclusion 

Disaster Recovery is provided by Oracle EPM Cloud, and we have augmented Oracle's capability at the application level to provide enhanced DR options with Microsoft Windows PowerShell framework. Oracle is focused on security and reliability with their data centers and providing the Daily Maintenance function for automatic nightly backups, but many of the DR processes and tasks are self-service, so we have enhanced this with our code, functions, and scripts.

Don't forget to download the code used in this tutorial!