Using Transaction Logging to Recover Post-Archived Essbase data
- by Keith Rosenthal
Data recovery is typically performed by restoring data from an archive. Data added or removed since the last archive took place can also be recovered by enabling transaction logging in Essbase. Transaction logging works by writing transactions to a log store. The information in the log store can then be recovered by replaying the log store entries in sequence since the last archive took place. The following information is recorded within a transaction log entry:
Sequence ID
Username
Start Time
End Time
Request Type
A request type can be one of the following categories:
Calculations, including the default calculation as well as both server and client side calculations
Data loads, including data imports as well as data loaded using a load rule
Data clears as well as outline resets
Locking and sending data from SmartView and the Spreadsheet Add-In. Changes from Planning web forms are also tracked since a lock and send operation occurs during this process.
You can use the Display Transactions command in the EAS console or the query database MAXL command to view the transaction log entries.
Enabling Transaction Logging
Transaction logging can be enabled at the Essbase server, application or database level by adding the TRANSACTIONLOGLOCATION essbase.cfg setting. The following is the TRANSACTIONLOGLOCATION syntax:
TRANSACTIONLOGLOCATION [appname [dbname]] LOGLOCATION NATIVE ENABLE | DISABLE
Note that you can have multiple TRANSACTIONLOGLOCATION entries in the essbase.cfg file. For example:
TRANSACTIONLOGLOCATION Hyperion/trlog NATIVE ENABLE
TRANSACTIONLOGLOCATION Sample Hyperion/trlog NATIVE DISABLE
The first statement will enable transaction logging for all Essbase applications, and the second statement will disable transaction logging for the Sample application. As a result, transaction logging will be enabled for all applications except the Sample application.
A location on a physical disk other than the disk where ARBORPATH or the disk files reside is recommended to optimize overall Essbase performance.
Configuring Transaction Log Replay
Although transaction log entries are stored based on the LOGLOCATION parameter of the TRANSACTIONLOGLOCATION essbase.cfg setting, copies of data load and rules files are stored in the ARBORPATH/app/appname/dbname/Replay directory to optimize the performance of replaying logged transactions. The default is to archive client data loads, but this configuration setting can be used to archive server data loads (including SQL server data loads) or both client and server data loads.
To change the type of data to be archived, add the TRANSACTIONLOGDATALOADARCHIVE configuration setting to the essbase.cfg file. Note that you can have multiple TRANSACTIONLOGDATALOADARCHIVE entries in the essbase.cfg file to adjust settings for individual applications and databases.
Replaying the Transaction Log and Transaction Log Security Considerations
To replay the transactions, use either the Replay Transactions command in the EAS console or the alter database MAXL command using the replay transactions grammar. Transactions can be replayed either after a specified log time or using a range of transaction sequence IDs.
The default when replaying transactions is to use the security settings of the user who originally performed the transaction. However, if that user no longer exists or that user's username was changed, the replay operation will fail.
Instead of using the default security setting, add the REPLAYSECURITYOPTION essbase.cfg setting to use the security settings of the administrator who performs the replay operation. REPLAYSECURITYOPTION 2 will explicitly use the security settings of the administrator performing the replay operation. REPLAYSECURITYOPTION 3 will use the administrator security settings if the original user’s security settings cannot be used.
Removing Transaction Logs and Archived Replay Data Load and Rules Files
Transaction logs and archived replay data load and rules files are not automatically removed and are only removed manually. Since these files can consume a considerable amount of space, the files should be removed on a periodic basis. The transaction logs should be removed one database at a time instead of all databases simultaneously. The data load and rules files associated with the replayed transactions should be removed in chronological order from earliest to latest. In addition, do not remove any data load and rules files with a timestamp later than the timestamp of the most recent archive file.
Partitioned Database Considerations
For partitioned databases, partition commands such as synchronization commands cannot be replayed. When recovering data, the partition changes must be replayed manually and logged transactions must be replayed in the correct chronological order.
If the partitioned database includes any @XREF commands in the calc script, the logged transactions must be selectively replayed in the correct chronological order between the source and target databases.
References
For additional information, please see the Oracle EPM System Backup and Recovery Guide. For EPM 11.1.2.2, the link is
http://docs.oracle.com/cd/E17236_01/epm.1112/epm_backup_recovery_1112200.pdf
