Oracle Customer Engineering & Advocacy Lab (CEAL) Blog covers Oracle Analytics Cloud, Oracle Analytics Server and...

Using Transaction Logging to Recover Post-Archived Essbase data

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:


Note that you can have multiple TRANSACTIONLOGLOCATION entries in the essbase.cfg file.  For example:



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.


For additional information, please see the Oracle EPM System Backup and Recovery Guide.  For EPM, the link is


Join the discussion

Comments ( 4 )
  • Leonid Friday, November 30, 2012

    Are there any possibilities to reread transaction log file after replay transactions?

    I mean imagine the situation:

    1. I do it on a standy site

    2. I restored from an archive file

    3. Replayed transactions

    4. Then on a primary site happened a new transaction

    5. This transaction was moved to the standby site

    And now, is it possible to replay this transaction without restoring database from an arc file and replaying old transactions once more?

  • er77 Friday, November 30, 2012

    Do you know any production env with using Transaction Logging ? Is it real case world ?

  • Keith Rosenthal Wednesday, December 12, 2012

    You do not need to restore the database from an archive file in order to replay transactions. Transactions can be replayed based on either the transaction sequence ID or transaction time and are not dependent on rostoring the database from an archive.

  • Keith Rosenthal Wednesday, December 12, 2012

    We have customers that are using transaction logging in a production environment and have found this functionality to be very useful

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.