Ver.1.0
REVISION LIST
Document Name: Essbase Database Recovery
Version: 1.0
# Revision Date Revision Description Version Location in Document Revised by
1 22-08-2007 Initial Version 1.0 Entire Document Saravanan.S
Table of Contents
Backing Up Applications and Application Databases 3
Backing Up the Application in Hyperion Essbase 4
Backing Up the Relational Database 9
Restore Essbase Application 9
Restore Hyperion Planning Application 10
Backing Up Applications and Application Databases
You should back up your applications and application databases on a daily basis.
Specific instances of when you need to back up your applications and application databases include:
Before refreshing an existing application (Hyperion Planning prompts you to perform a backup first)
Before moving an application to a new server
Before upgrading an application
As your business dictates (for example, at key planning milestones)
Backing up an application and its related application databases is a three-part process, consisting of the following steps:
Backing up the application specifics in Hyperion Essbase
Backing up the relational databases
Backing up the required components of Hyperion Planning
Backing Up the Application in Hyperion Essbase
There are four pieces of information that must be backed up for an application in
Hyperion Essbase:
The actual application
The security file
The configuration settings
The backup file
Backup Strategy
Backup of the Hyperion servers should be taken daily (5 days a week). Full backups should be taken including SQL Server
Essbase databases can be backed up into an offline folder on the server using a ESSCMD script. Folder Structure: Drive:\Essbase_Auto_Backup
The backups should be stored in a folder in one of the server and the latest backup can be found in the same server. Folder Structure: Drive:\backup
The application specific files should be stored in a directory in the server the backup is stored. Folder Structure: Drive:\ BackUp\EssbaseBackup\
The files specific to Essbase Configuration should be stored in a folder ,in the server the backup is stored. Folder Structure: Drive:\BackUp\EssbaseBackup\cfg_bak_sec
The data files (all and level zero data) should be stored in a separate folder. Folder Structure: Drive:\ BackUp\EssbaseBackup\Lev0_All_Export
This backup directory should be part of System (OS) backup and hence should reside in back up tapes too.
The execution of the backup script follows the steps mentioned below:
Step 1.
Log out all the users from Essbase.
Step 2.
Unload the application Essbase Application and copy the application folder to the backup directory (Without unloading the application, the database files such as .esm, .otl, .pag, .db, .dbb, .ind files would not be copied to the backup).
Step 3.
Copy security, config and .bak files of Essbase
Step 4.
Load the application (Without loading the application, data export is not possible).
Step 5.
Once the application is loaded, the users will be able to logon to the same. Hence the script logs out all users and disables further logins.
Step 6.
Export both level zero data and data at all levels.
Step 7.
Enable login for all users.
Files to Backup
Current configuration shows the following databases are included in the script above:
File Stored where
essn.ind \\Hyperion\essbase\app\
essn.pag \\Hyperion\essbase\app\
Plan1.esm \\Hyperion\essbase\app\
Plan1.tct \\Hyperion\essbase\app\
Plan1.ind \\Hyperion\essbase\app\
Plan1.db \\Hyperion\essbase\app\
Essbase.sec \\Hyperion\essbase\bin
Essbase.bak \\Hyperion\essbase\bin
Essbase.cfg \\Hyperion\essbase\bin
All *.otl files \\Hyperion\essbase\app\
All *.rul files \\Hyperion\essbase\app\
All *.csc files \\Hyperion\essbase\app\
All *.rep files \\Hyperion\essbase\app\
Back up scripts
Begin Archive:
To put the database
LOGIN "Essbase server name" "super user name" " super user password";
LOGOUTALLUSERS "y"
DISABLELOGIN "Application Name"
BEGINARCHIVE "Application Name" "Database Name" "archive1.txt";
LOGOUT;
EXIT;
Backup script:
Renames the previous day’s backup and takes backup of all the necessary files.
cd DRIVE:\Essbase_Auto_Backup
cscript RenameAppendDate.vbs DRIVE:\Backup\EssbaseBackup
mkdir DRIVE:\Backup\EssbaseBackup
mkdir DRIVE:\Backup\EssbaseBackup\
mkdir DRIVE:\Backup\EssbaseBackup\cfg_bak_sec
mkdir DRIVE:\Backup\EssbaseBackup\Lev0_All_Export
cd DRIVE:\Hyperion\Essbase\bin
esscmd DRIVE:\Essbase_Auto_Backup\Unloadapp.txt
xcopy DRIVE:\Hyperion\Essbase\App\
xcopy DRIVE:\Hyperion\Essbase\bin\essbase.bak DRIVE:\Backup\EssbaseBackup\cfg_bak_sec\
xcopy DRIVE:\Hyperion\Essbase\bin\essbase.sec DRIVE:\Backup\EssbaseBackup\cfg_bak_sec\
xcopy DRIVE:\Hyperion\Essbase\bin\essbase.cfg DRIVE:\Backup\EssbaseBackup\cfg_bak_sec\
esscmd DRIVE:\Essbase_Auto_Backup\loadapp.txt
cd DRIVE:\Hyperion\Essbase\bin
esscmd DRIVE:\Essbase_Auto_Backup\beginarchive.txt
Esscmd DRIVE:\Essbase_Auto_Backup\Ess_Plan1_Lev0_Export.txt
Esscmd DRIVE:\Essbase_Auto_Backup\Ess_Plan1_All_Export.txt
esscmd DRIVE:\Essbase_Auto_Backup\endarchive.txt
cd DRIVE:\backup
End Archive:
Puts the database back into write mode, so that the users can save data to the database.
LOGIN " Essbase server name" "super user name" " super user password";";
ENDARCHIVE "Application Name" "Database Name" "archive1";
ENABLELOGIN "Application Name";
LOGOUT;
EXIT;
Load Application:
Loads the application
LOGIN "Essbase server name" "super user name" " super user password";
LOADAPP "Application Name";
LOGOUT;
EXIT;
Unload Application:
Unloads the application "Application Name", to restrict the users from editing data in the database.
LOGIN "Essbase server name" "super user name" " super user password";
LOGOUTALLUSERS "y"
UNLOADAPP "Application Name";
LOGOUT;
EXIT;
RenameAppendDate.vbs
Used to rename the previous day’s backup with the date and time when this day’s backup has started. Current day’s backup can be found in Drive:\backup\EssbaseBackup.
'Get the command-line arguments. The first argument is required.
If Wscript.Arguments.Count = 0 Then 'Show interactive help text
Wscript.Echo "The first argument is required"
Wscript.quit
End If
‘Check for the command-line arguments.
OriginalPathFileName = Wscript.Arguments (0)
If Wscript.Arguments.Count = 2 Then
NewBasePathFileName = Wscript.Arguments (1)
Else
NewBasePathFileName = OriginalPathFileName
End If
'Get the current date and format it into a string that looks like "_MMDDYYYY"
DateString = "_" & right ("0" & month (date ()),2) & right("0" & day(date()),2) & year(date()) & "_" _& Right("0" & hour(Time()),2) & right("0" & minute(Time()),2) & right("0" & second(Time()),2)
'Create a Shell object
Set WshShell = Wscript.CreateObject ("Wscript.Shell") '
'Build the CmdString
CmdString = "CMD /C move " & OriginalPathFileName & " " & NewBasePathFileName _
& DateString
'Run MOVE command
ReturnCode = WshShell.Run (CmdString, 1, TRUE) '0 = String found, 1 = string not found
Wscript.quit 'Exit the script
Backing Up the Relational Database
Different databases in the SQL Server should be backed up, to ensure continuity on the following application:
1. Hyperion Planning
SQL Server database should also include a Maintenance plan, which performs a backup to a file. The following databases should be configured with a maintenance plan. Also the plan perform a transaction log backup every 3 hours to the same location:
Database File Location of Backup
DB Maintenance Plan 1
(Hyperion Planning Application Database) Drive:\SQLBackUp\
HPSYSDATA (Hyperion Planning system Database) Drive:\SQLBackUp\ HPSYSDATA IN
Restore Essbase Application
Follow the steps below to restore the application and data.
1. Stop Essbase service
2. Copy all the files from the backup folder DRIVE:\BackUp\EssbaseBackup\
3. Copy the files from DRIVE:\BackUp\EssbaseBackup\cfg_bak_sec to DRIVE:\Hyperion\Essbase\Bin
4. After reloading all the files start the Essbase server.
5. During instances such as Data corruption or instances where in you need to load data, you may need to import the data from the data files found in DRIVE:\BackUp\EssbaseBackup\Lev0_All_Export. You may either import all data or import level zero data. Import procedure is as follows. The import procedures are as follows:
a. Logon to Essbase Application manager using the super user id and its password
b. Select the application “
c. Select “Database -> Export”
d. Select the data file from the latest backup folder DRIVE:\BackUp\ EssbaseBackup\ Lev0_All_Export).
e. If you wish to load “All Data”, select the file Plan1_All_Exp, else choose the file Plan1_Lev0_Exp.
f. After executing all the necessary calculation scripts, execute the test reports to check the data values present in the database.
Restore Hyperion Planning Application
1. Create UDLs
2. Register the UDLs in the admin console of Hyperion Planning found in DRIVE:\Program Files\Hyperion Solutions\Hyperion Planning\Server\HSXSysAdmin.msc. The DSN names should be same as the UDL names.
3. Open the Hyperion Planning system database (HPSysData) and open the table HSX_DATASOURCE. Insert a new row into the table. Add the product name (PLANNING), application name (
4. If Essbase application already exists and is working fine, stop with Refreshing Essbase through Hyperion Planning Desktop. In the event of Essbase failure, please continue with the following steps.
5. On the Essbase server, create an application named
6. Open Hyperion Planning and open the application. Use the Manage Database task to create the Essbase outline.
Note: If Hyperion Essbase outlines have been copied to the new application,perform a Cube Refresh. If Essbase outlines have not been copied to the new application in Hyperion Essbase, perform a Cube Create.
7. You need to run HSPsetupsupport utility in the folder DRIVE:\Program Files\Hyperion Solutions\Hyperion Planning\Utils, to display the new application on the Web.
8. To restore data, please follow the steps in “Restore Essbase Application”
Hi To All
ReplyDeleteWhat ever I am providing here is I am gathering the information from different sources and I am uploading here.
My idea is to help the hyperion community.please if you have any information please post here.
I want gather the information related to hyperion essbase.
Thanks
Koti Reddy
Hello Koti,
ReplyDeleteI have not done backup for the last ten years, very rusty, thanks to your note for the head start. In this case, the ".vbs" file is separate, all these is to be kick off with a ".bat" file?
Thank you in advance