Backing Up Data and Recovering Databases

This chapter tells how to recover a database, how to back up a database, and it describes other ways that Hyperion Essbase protects data.

This chapter includes the following sections:

If you are migrating from a previous release of Hyperion Essbase, see the Hyperion Essbase Start Here booklet.

Backing Up a Database

A key part of a database maintenance routine is making regular backups of Hyperion Essbase data. The most common method of backing up data is archiving. Archiving makes copies of Hyperion Essbase data files exactly as they exist on the system. You can also back up data by exporting it. Exporting makes a copy of data in an ASCII text format. This section describes both backup methods.

Archiving Data

To archive data, use the Hyperion Essbase BEGINARCHIVE and ENDARCHIVE commands (or alter database begin archive in MaxL) along with a third-party backup utility of your choice. The BEGINARCHIVE command places the database in read-only mode and provides a list of files to back up. Users can remain logged in, but they are not able to modify data during the archive procedure.

Note:

If you back up your data without using BEGINARCHIVE, make sure that all Hyperion Essbase applications are closed and that all users are logged out during the backup procedure. Otherwise, you risk corrupting the database.

The file list generated by BEGINARCHIVE includes linked reporting object files and partitioning database files, if applicable. For more information on Linked Reporting Objects, see Linking Objects to Hyperion Essbase Data. For information on partitioning, see Designing Partitioned Applications.

To archive data:

Issue the BEGINARCHIVE command in ESSCMD, or use alter database begin archive in MaxL. This performs the following tasks:
- Commits any modified data to disk.
- Switches the database to read-only mode.
- Reopens the database files in shared, read-only mode.
- Creates a file containing a list of files that need to be backed up. By default, the file is called archive.lst. See the BEGINARCHIVE command in the online Technical Reference in the DOCS directory for more information on archive.lst.
  If a user tries to modify data during the archive procedure, an error message informs the user that data is in read-only mode for archiving. No user can update the database until the ENDARCHIVE command is issued. (See step 3.)
  
  Read-only mode persists until the ENDARCHIVE command is issued, even if the database is shut down or restarted.
Use a third-party backup utility to back up the files listed in archive.lst. These files are described in Recovering from a Crashed Database.
Be sure to back up data on every disk volume Hyperion Essbase uses. For information about data storage on multiple volumes, see Introducing the Hyperion Essbase Kernel.
Complete the archiving process by issuing the ENDARCHIVE command in ESSCMD or alter database end archive in MaxL to achieve the following:
- Puts the database into read-write mode.
- Re-opens database files in exclusive, read-write mode.

Note:

You must end archiving in order to put the database back into read-write mode, even if you shut down and restart the database. Neither ENDARCHIVE nor alter database end archive restarts the database.

If you try to cancel the archive after issuing BEGINARCHIVE and receive a "can't cancel" message, most likely the system is in the final stage of writing items to the drive and has reached the point where the operation cannot be cancelled.

Exporting Data

You can back up data by exporting instead of archiving. The EXPORT utility copies data to an ASCII text file that you specify; it does not compress data. The exported file contains data only and does not include control, outline, or security information. The maximum size of the exported file is 2 gigabytes.

Archiving is the recommended method of backing up data. However, you may use the EXPORT utility when performing any of the following tasks:

Transferring data across platforms
Backing up only a certain portion of the data; for example, level 0 blocks
Creating an exported file in text format rather than binary format

Note:

You can also export data by creating reports. For more information, see Copying Data Subsets and Exporting Data to Other Programs.

Using the EXPORT Utility

To export data, select Database > Export in Hyperion Essbase Application Manager, use the export statement in MaxL, or issue EXPORT in ESSCMD. For ESSCMD syntax information, see the online Technical Reference in the DOCS directory.

You can use the EXPORT utility to specify the amount of data to export:

All data
Level 0 blocks only (blocks containing only level 0 sparse member combinations)
Data from input blocks only (blocks containing data from a data load)

You can choose to export data in a columnar or non-columnar format. If you are going to load the exported data into a relational database, you might want to export the data in columns. In this format, all member names are shown in double quotation marks and repeated on each row. This format allows the exported data more structure that can be used for further data processing by applications other than Essbase tools.

To improve export performance, you can now export data in parallel to a specified number of files, using the export statement in MaxL or the PAREXPORT command in ESSCMD.

For MaxL or ESSCMD syntax information, see the online Technical Reference in the DOCS directory.

Reloading Exported Data

When you reload data that has been exported, Hyperion Essbase marks the data as input data. If you reload data exported from level 0 blocks or from input blocks, you must recalculate the database after reloading. When you recalculate the database, Hyperion Essbase recalculates every data block.

If you export all data in a database and then reload, Hyperion Essbase marks all blocks in the database as input blocks. Consequently, if you try to clear data by selecting Database > Clear Data > Non-Input Blocks from Hyperion Essbase Application Manager, no data is cleared because the database contains no non-input blocks.

Note: To clean the status of the blocks in a database after exporting all data and reloading, you can run the following calc script:

Set ClearUpdateStatus Only; Calc All;

When reloading data files that were exported in parallel into the database, it is highly recommended to load the data files according to the numbering order. Because each export file contains information on a disjointed portion of the database, loading them in the correct order ensures a sequential write order of the database blocks. This will give you optimum performance.

Recovering from a Crashed Database

After a server interruption such as a crash, Hyperion Essbase recovers your database, rolling back all transactions that were active when the interruption occurred. Hyperion Essbase also recovers and consolidates free fragments (unused addressable units in the data blocks).

Hyperion Essbase recovers data as soon as the server is started after a server interruption. Recovery consists of the following phases:

Transaction recovery rolls back all transactions that were active when the interruption occurred.
Index file recovery truncates files to their previously committed sizes.
Data freespace recovery rebuilds the data freespace tables. The size of the index determines the duration of this phase.

Only a media failure (faulty disk, disk failure, or head crash) requires you to restore from archived files. See Archiving Data.

CAUTION: Do not move, copy, modify, or delete any of the following files: ESSxxxxx.IND, ESSxxxxx.PAG, databasename.IND, databasename.ESM, databasename.TCT. Doing so can result in data corruption.

What to Expect If a Server Interruption Occurs

The following table lists types of server interruptions and their results:

*Table 50-1: Hyperion Essbase Recovery Handling*
Type of Interruption	Result
Power loss on server machine Operating system crash Server stopped with Ctrl + C keys	Server stops. When you restart the server, Hyperion Essbase recovers the database.
Operation cannot complete due to system limitations Memory shortage Out of disk space Application stopped with Ctrl+C keys	Hyperion Essbase performs fatal error handling. You may need to allocate more memory or disk space and restart the server.
Server crash	Hyperion Essbase Exception Manager creates error log file of type.XCP. Server stops. When you restart the server, Hyperion Essbase recovers the database.

Table 50-2 shows what you need to do if a server interruption occurs during a transaction. How Hyperion Essbase recovers from an interruption depends on the transaction isolation level setting (committed or uncommitted access). See Ensuring Data Integrity for more information.

*Table 50-2: Recovery Procedures for Server Requests*
Type of Lock	Recommended Action
Lock (for spreadsheet update)	Issue the lock command again.
Send (spreadsheet update)	If Hyperion Essbase issues an error, repeat the last send operation.
Calculation	Repeat the last calculation. See Calculating Data.
Data load	Repeat the last data load (see Performing a Data Load) or load the error log file (see Debugging and Optimizing Data Loads).
Arithmetic data load (adding to or subtracting from values in the database)	Look in the application log file to ascertain the last row for which Hyperion Essbase logged a completion message. Repeat the data load from the row that immediately follows the last row for which Hyperion Essbase logged the completion message. See Performing a Data Load and Debugging and Optimizing Data Loads.
Restructure	Repeat the last operation that caused a restructure. Check for temporary files and delete them.

Note: If the UPDATECALC parameter is set to FALSE, Hyperion Essbase recalculates the entire database if an interruption occurs during a calculation. (The default value for the parameter is TRUE.) For more information on UPDATECALC, see the online Technical Reference in the DOCS directory.

Spreadsheet Update Logging

For extra protection against data loss and for spreadsheet audit information, Hyperion Essbase provides a spreadsheet update logging facility. Enable this facility by using the SSAUDIT or SSAUDITR parameter in the ESSBASE.CFG server file. For information on the ESSBASE.CFG file, see the online Technical Reference in the DOCS directory.

SSAUDITR clears update log files automatically after archiving the data, when you issue the ENDARCHIVE command. SSAUDIT does not remove log files after archiving them. See Archiving Data for information on the ENDARCHIVE command.

You can specify SSAUDIT for all databases on the server or for individual databases. See the online Technical Reference in the DOCS directory for syntax information.

Hyperion Essbase handles recovery under normal situations. However, sometimes you may want to load the spreadsheet update log file manually. For example, if you have restored data from a recent backup and do not want to lose changes made since the backup was made or you experience a media failure, you can recover transactions from the update log file. To do so, use the Hyperion Essbase command-line facility, ESSCMD, from the operating system prompt.

The following ESSCMD command sequence loads the update log file:

LOGIN hostnode username password 
SELECT application_name database_name 
LOADDATA 3 filepath:application_name.ATX 
EXIT

To simplify the process of loading the update log file, prepare a batch file as described in Performing Interactive and Batch Operations Using ESSCMD

Note: When SSAUDIT or SSAUDITR is specified, Hyperion Essbase logs spreadsheet update transactions chronologically. Hyperion Essbase uses two files:

audit_database_name.ATX stores each update transaction as a unit.

audit_database_name.ALG contains historical information: date, time, size, and starting row of each transaction.

The spreadsheet update log can get quite large; even if you are using SSAUDITR, Hyperion Essbase clears the log only after you archive data. If spreadsheet update activities are frequent in your application, you may want to manually delete the log file periodically.

When a database is started after a shutdown, if spreadsheet logging is enabled, Hyperion Essbase writes the following message to the database log file:

Starting Spreadsheet Log 
volumename\application_directory\application_name\ 
database_name\database_name.atx For Database database_name

For example:

Starting Spreadsheet Log \ESSBASE\app\app1\sample\sample.atx 
for database sample

To ensure successful spreadsheet update logging, stop and restart your application after either of the following:

Initiating any operation that causes a restructure. See Database Restructuring.
Running any of the following database commands:
CREATEAPP
CREATEDB
COPYDB
RENAMEDB

Hyperion Essbase ensures that if you enable spreadsheet logging, updates cannot take place without being logged. If Hyperion Essbase cannot write to the update log for any reason, Hyperion Essbase stops the transaction and issues an error message.

SSAUDIT and SSAUDITR are available only from the ESSBASE.CFG file.