New ways for backup, recovery and restore of Essbase Block Storage databases – part 1 by Bernhard Kinkel
By Georgiana Codrescu on Jun 14, 2011
Let’s start with the last point: If you wanted to backup just the data from your database, formerly you could use the Export utility that Essbase provides as an item in the database right-click menu in the Administration Services Console. This feature is still available, supporting both Block Storage (BSO) and Aggregate Storage (ASO) databases. But regarding usability, some limitations exist: for example, the focus on which data to export can be set only to Level0, Input Level or All data (the last two options are only available for BSO) – more detailed definitions are not possible. Also the ASCII format of the export file causes them to become rather large, maybe even larger than your Page and Index files.
Anyway, importing these files is quite simple, as this export can be (re-)loaded without any load rule, as long as the outline structure is the same – even if the database resides on another server. Also modifications are possible while using load rules in combination with an export file in column format.
But now the way to export data using a Calc Script promises more flexibility, smaller files and faster performance. However, this option is only available to BSO, as ASO cubes do not leverage Calc Scripts.
For example, in order to focus on even very detailed subsets of data, which is very usual in Calc Scripts, you can take advantage of common commands like FIX | ENDFIX and EXCLUDE | ENDEXCLUDE. In addition the new SET DATAEXPORTOPTIONS command provides more options to refine export content, formatting, and processing, including the possibility to export dynamically calculated values. You can also request statistics and an estimate of export time before actually exporting the data. The following syntax gives you an overview of the available settings:
DataExportLevel ALL | LEVEL0 | INPUT;
DataExportDynamicCalc ON | OFF;
DataExportNonExistingBlocks ON | OFF;
DataExportColFormat ON | OFF;
DataExportDimHeader ON | OFF;
DataExportRelationalFile ON | OFF;
DataExportOverwriteFile ON | OFF;
DataExportDryRun ON | OFF;
Looking at most of these options will probably already give you an idea on their use and functionality. For more detailed information about the SET DATAEXPORTOPTIONS command options, please see the available Oracle Essbase Online Documentation (rel. 18.104.22.168) or the Enterprise Performance Management System Documentation (including previous releases) on the Oracle Technology Network.
My example should focus on the binary export and import, as it provides faster export and load performance than export/import with ASCII files. Thus in the first section of my script I will use only two of the data export options, in order to export all data and to overwrite an eventually existing old export file with the new one. The subsequent syntax for the binary export itself is DATAEXPORT "Binfile" "fileName", where "Binfile" is the required keyword and "fileName" is the full pathname for the exported binfile. So the complete script reads:
DATAEXPORT "BinFile" "c:\Export\MyDB_expALL.bin";
Tip: Export file names can have more than 8 characters; the extension “.bin” is not mandatory.
The import of the binary file with the Calc Script uses the command DATAIMPORTBIN fileName;. In order to avoid potentially importing a wrong file or importing into a wrong database, each export file includes an outline timestamp, which the import by default checks. Just in case, this check should be bypassed, the command SET DATAIMPORTIGNORETIMESTAMP ON; could be placed before the DATAIMPORTBIN line. The import definition for the preceding export could look like the following:
SET DATAIMPORTIGNORETIMESTAMP ON;
After this rather new option for data export and import let’s turn to the new backup and restore option for complete databases provided in Administration Services Console starting with release 11. As well as or instead of the common strategies and methods used previously (like running a third party backup utility while the database is in read-only mode), this new feature provides an easy ad-hoc way to archive a database.
Select the Archive Database item from the right-click menu on the database node and in the subsequent window define the full path and name for the archive file, where the extension “.arc” is a recommendation from Oracle, but not mandatory.
The process could optionally be run as a background process, while Force archive would overwrite an existing file with the same name.
After starting the archive procedure, the database is set to read-only mode and a copy of the following files will be written to the archive file:
After this the database returns to read-write mode. However, not all files are backed up automatically using this procedure. The following table shows a list of files and file types that you would need to backup manually:
Tip: Also make a backup of the file essbase.bak_startup. This file is created after a successful start of the Essbase server (formerly this file was named just essabse.bak), as well as the essbase.bak file, which now has a different function: while the essbase.bak_startup is only created at the server start and no changes apply to this file until a next successful server start, the essbase.bak could be compared to the security file and updated manually or by using a MaxL command at any time. For a manual update in Administration Services Console under the respective Essbase server right-click Security, and select Update security backup file.
In MaxL run the command alter system sync security backup. Security files and the CFG-file reside in the ARBORPATH\bin directory, where you installed Essbase.
As the Archive option by default creates one large file, you have to make sure that the system you save your archive files to supports large files (e.g. in Windows NTFS). If you need smaller files, Essbase can be configured to create multiple files no larger than 2 GB by creating the SPLITARCHIVEFILE TRUE entry in the essbase.cfg file.
Restoring an archived database works as simply as the backup itself. First make sure that the database to be restored is stopped. Then from the right-click menu select Restore Database. Provide the required information about the archive file to be imported including the full path.
If the backed-up database used disk volumes, select Advanced. The database can be restored to the same disk volumes without any further definitions, or you define a new mapping for the volume names (e.g. “C” could be replaced by “F”), but you can neither change the number of volumes nor the space used on each volume compared to the original backed-up database. Select to restore in the background if desired, and click OK. The restore is done and confirmed in the Messages panel.
Tip: Usually, the same database would be restored that has been previously backed-up. But this doesn’t necessarily have to be the case. You can also use the restore feature to create a copy of your database (excluding the files mentioned above, which are not included in the archive file) or to overwrite another database. In both cases you must have an existing database to overwrite. From this “target” database select the Restore Database feature, but make sure to have checked Force Restore in the Restore Database dialog box.
Depending on the frequency of your archiving cycles, maybe the latest backup doesn’t restore the actual latest state of your database: following the backup, you might, for example, have run Dimension Build Rules or Data Load Rules, data loads from client interfaces or calculations. These would not be reflected in the restored database. In this case the new Transaction Logging and Replay option provides a good way to capture and replay post-backup transactions. Thus, a backed-up database can be recovered to the most recent state before the interruption occurred. This feature will be described in the second part of this article coming later this year.
Or – if you can’t wait – maybe you should learn how to use it as well as other important administration topics in our Essbase for System Administrators class; please refer also to the links provided below.
If you are also interested in other new features and smart enhancements in Essbase or Hyperion Planning stay tuned for coming articles or check our training courses and web presentations.
You can find general information about offerings for the Essbase and Planning curriculum or other Oracle-Hyperion products here; (please make sure to select your country/region at the top of this page) or in the OU Learning paths section, where Planning, Essbase and other Hyperion products can be found under the Fusion Middleware heading (again, please select the right country/region). Or drop me a note directly: firstname.lastname@example.org.
Bernhard Kinkel started working for Hyperion Solutions as a Presales Consultant and Consultant in 1998 and moved to Hyperion Education Services in 1999. He joined Oracle University in 2007 where he is a Principal Education Consultant. Based on these many years of working with Hyperion products he has detailed product knowledge across several versions. He delivers both classroom and live virtual courses. His areas of expertise are Oracle/Hyperion Essbase, Oracle Hyperion Planning and Hyperion Web Analysis.
All methods and features mentioned in this article must be considered and tested carefully related to your environment, processes and requirements. As a guidance please always refer to the available software documentation. This article does not recommend or advise any explicit action or change, hence the author cannot be held responsible for any consequences due to the use or implementation of these features.