To facilitate recovering a block storage database to a later time than a restored database, you must enable transaction logging. As transactions are executed, Essbase writes operational parameters to a log store. Users with the Essbase Administrator role can view a list of logged transactions and then replay them to recover the data that was not captured after a backed-up database is restored.
Table 7 lists the transactions, by category, that Essbase logs when transaction logging is enabled:
Table 7. List of Logged Transactions by Category
Category | Transaction |
|---|---|
Calculation |
|
Data load |
|
Lock and send | Update database cells with data from Smart View or Oracle Essbase Spreadsheet Add-in. |
The operational parameters that are logged for each transaction vary. Table 8 shows the parameters that are logged for three representative transactions:
Table 8. Examples of Parameters Logged for Specific Transactions
Transaction | Logged Parameters |
|---|---|
Execute a client-side calculation |
|
Import data from different sources to Essbase Server |
|
Update database cells with data from Smart View, or Oracle Essbase Spreadsheet Add-in |
|
Note: | “Replay” is a reserved word and cannot be used as the name of a block or aggregate storage database. If you named a database “replay” in the past, you must rename the database before enabling the transaction logging and replay feature. |
Transaction logging can be enabled at the server, application, or database level.
To enable transaction logging, you must specify a centralized location where Essbase can write transaction log stores. The location must be to an existing directory on Essbase Server.
Add the TRANSACTIONLOGLOCATION configuration setting to essbase.cfg. The syntax for the statement:
TRANSACTIONLOGLOCATION [appname [dbname]] LOGLOCATION NATIVE ENABLE | DISABLE
Note: | Do not change the NATIVE argument, which is a reserved field. For the log location, Oracle recommends using a physical disk other than the disk on which the ARBORPATH directory or disk volumes reside. |
In LOGLOCATION, Essbase creates subdirectories corresponding to the applications and databases for which you enable transaction logging, using the following structure:
LOGLOCATION/appname/dbname
For example, the following statement enables transaction logging for all databases associated with the Sample application:
TRANSACTIONLOGLOCATION Sample /Oracle/trlog NATIVE ENABLE
As transactions are logged for the Sample.Basic database, the log store is written to:
/Oracle/trlog/Sample/Basic
Depending on the number of databases for which you enable transaction logging, you might want to create more than one centralized transaction log location; however, Oracle recommends keeping the number of log locations to a minimum.
Additionally, by using multiple TRANSACTIONLOGLOCATION statements, you can enable transaction logging at a more global level and, at the same time, disable logging at a more granular level. In the essbase.cfg file, for the override to take effect, the more global enabling statement must precede the more granular disabling statement .
Consider the following examples:
The first statement enables transaction logging for all applications and their associated databases on Essbase Server; the second statement disables transaction logging for all databases associated with a specific application (Sample):
TRANSACTIONLOGLOCATION /Oracle/trlog NATIVE ENABLE
TRANSACTIONLOGLOCATION Sample /Oracle/trlog NATIVE DISABLE
The first statement enables transaction logging at the application level (Sample); the second statement disables transaction logging for a specific database (Basic) in the application:
TRANSACTIONLOGLOCATION Sample /Oracle/trlog NATIVE ENABLE
TRANSACTIONLOGLOCATION Sample Basic /Oracle/trlog NATIVE DISABLE
Note: | Before renaming or copying an application or database for which transaction logging is enabled, you must enable logging for the renamed or copied application or database and specify the same LOGLOCATION that is specified for the source application and database. |
To enable the replay of logged transactions, Essbase creates archive copies of data load and rules files in ARBORPATH/app/appname/dbname/Replay.
By default, Essbase archives only data load and rules files for client data loads.
To change the type of data to be archived, add the TRANSACTIONLOGDATALOADARCHIVE configuration setting to the essbase.cfg file. The syntax for the statement:
TRANSACTIONLOGDATALOADARCHIVE [appname [dbname]] [OPTION]
Valid values for the OPTION argument:
CLIENT—(Default) Archives data load and rules files for client data loads.
SERVER—Archives data load and rules files on the server and SQL-server data loads.
Caution!
Server data loads are replayed using the data load and rules files that are archived on the server in the Replay directory. Do not rename these files. Also, if the contents of the data load and rules files are changed before the replay operation, the modified data is used during replay. Therefore, the data in the recovered database will not be the same as the original data.
SERVER_CLIENT—Archives server and client data.
NONE—No data is archived.
If you select NONE and you use client data, Essbase cannot replay the data load. To recover transactions, you must manually load the client data before you replay the remaining transactions.
Caution!
If you use server or SQL data and the data and rules files are not archived in the Replay directory (for example, you did not use the SERVER or SERVER_CLIENT option), Essbase replays the data that is in the data source, which may or may not be the data that was originally loaded.
For example, the following statement archives server and client data for all databases on Essbase Server:
TRANSACTIONLOGDATALOADARCHIVE SERVER_CLIENT
An administrator can view the list of logged transactions and the information associated with each transaction. You can specify whether to list transactions that were logged after the following conditions:
A specified time
The time when the last replay request was executed or the time of the last restored backup (whichever occurred later)
To view logged transactions, use a tool:
| Tool | Topic | Location |
|---|---|---|
| Administration Services | Viewing Logged Transactions for Block Storage Databases | Oracle Essbase Administration Services Online Help |
| MaxL | query database | Oracle Essbase Technical Reference |
In Administration Services Console, use the Display Transactions command to view a list of transactions, as shown in Figure 3, List of Logged Transactions You can sort the list by the following attributes:
Sequence ID
Username
Start Time
End Time
Request Type
Although you can sort the list of transactions, you must replay transactions in the same order as the sequence ID.
When using the query database MaxL statement with the list transactions grammar, you can specify whether to display the list output in the MaxL Shell window (the default) or to write the list output to a specified file. The syntax for the statement:
query database appname.dbname list transactions [after TIME [[force] write to file FILE-NAME]]];
For example, the following statement displays, in the MaxL Shell window, the transactions in the Sample.Basic database that were logged after the last replay request was executed or after time of the last restored backup (whichever occurred later):
query database Sample.Basic list transactions;
Writing the output to a file is useful for processing the log information with other tools. For example, you can import the file contents into a relational database and write a tool to analyze the information.
When using the write to file grammar, the list output is written to a comma-separated file on the Essbase Server computer. Provide the full path to an existing directory on Essbase Server and the name of the output file. If only the output file name is provided, Essbase writes the file to ARBORPATH/app.
When writing to an existing output file, use force grammar to overwrite the file.
For example, the following statement writes Sample.Basic database transactions that were logged after November 20, 2009, at 12:20:00 to a CSV file in the Sample.Basic database directory:
query database Sample.Basic list transactions after '11_20_2009:12:20:00' write to file EPM_ORACLE_HOME/products/Essbase/EssbaseServer/app/Sample/Basic/listoutput.csv';
In replaying logged transactions, by default, Essbase uses the security settings of the user who originally performed the transaction. If that user no longer exists or if the user's user name is changed, the replay operation fails. To use the security settings of the administrator who performs the replay operation (either explicitly or if the original user's settings cannot be used), use the REPLAYSECURITYOPTION n configuration setting in essbase.cfg. Values for n and whose security settings they specify:
1—(default) The user who originally performed the transaction
2—The administrator performing the replay operation
3—The user who originally performed the transaction
If that user no longer exists or that user's user name was changed, the security settings of the administrator performing the replay operation are used.
Only an administrator can replay transactions. You can replay logged transactions based on these criteria:
Transactions logged after a specified time
Transactions logged after the last replay request was executed or after the time of the last restored backup (whichever occurred later)
Selectively, transactions based on a range of sequence IDs
Each logged transaction is assigned a sequence ID, indicating the order in which the transaction was performed. To ensure the integrity of the restored data after a replay, Essbase enforces the replay of transactions in the same order in which they were originally performed. For example, if a data load was followed by a calculation, replaying the calculation before the data load would produce incorrect results; the data load transaction must be replayed before the calculation transaction.
The order of sequence IDs is tracked across multiple replay commands. After a transaction is replayed, you can replay only transactions with a greater sequence ID. For example, after replaying transactions with sequence IDs of 1, 2, and 3, you can replay only transactions with a sequence ID of 4 or greater.
Transactions that are executed and logged after the restore operation are not replayed, unless you replay those transactions using their sequence IDs. After restoring a database, Oracle recommends that you finish replaying the transactions that were logged after the backup and before the restore and that are needed to fully recover the database; then you can continue executing new transactions.
Note: | You can skip replaying a transaction if you are sure that the transaction results are not required to recover the database. For example, if you performed two full data loads with identical data, you need only replay one of the data loads. |
To replay logged transactions, use a tool:
| Tool | Topic | Location |
|---|---|---|
| Administration Services | Replaying Logged Transactions for Block Storage Databases | Oracle Essbase Administration Services Online Help |
| MaxL | alter database | Oracle Essbase Technical Reference |
The replay functionality is the same whether you use Administration Services Console or MaxL.
To selectively replay transactions, enter a comma-separated string of sequence ID ranges. A range can consist of one or more transactions:
One transaction: n to n; for example, 1 to 1
Multiple transactions: x to y; for example, 20 to 100
In Administration Services Console, use the Replay Transactions command.
In MaxL, use the alter database statement with the replay transactions grammar. Syntax:
alter database appname.dbname replay transactions [after TIME] [using sequence_id_range COMMA-SEPARATED_RANGE];
For example, the following statement replays the transactions in the Sample.Basic database with sequence IDs 1–10 and 20–100:
alter database Sample.Basic replay transactions using sequence_id_range 1 to 10,20 to 100;
Periodically, you might want to remove the transaction log store and the files in the Replay directory to increase available disk space on Essbase Server.
Transaction log store: Oracle recommends removing the transaction log store for one database at a time. The log store is in a subdirectory under the log location specified by the TRANSACTIONLOGLOCATION configuration setting. For example, if the log location for the Sample.Basic database is /Oracle/trlog, delete the contents of /Oracle/trlog/Sample/Basic
See Enabling Transaction Logging.
Replay directory: After you have replayed transactions, the data and rules files associated with the replayed transactions can be removed from the ARBORPATH/app/appname/dbname/Replay directory (see Configuring Transaction Replay). You can delete all of the files in the Replay directory, or follow these guidelines for selectively removing files:
Remove the data and rules files in chronological order, from earliest to latest.
Do not remove data and rules files with a timestamp that is later than the timestamp of the most recent archive file.
Note: | Oracle recommends waiting until after several subsequent database backups before deleting files associated with transaction logging and replay. |
When logging and replaying transactions from partitioned databases, follow these guidelines:
Partition commands (for example, synchronization commands) are not logged and, therefore, cannot be replayed. When recovering a database, you must replay logged transactions and manually make the same partition changes in the correct chronological order.
When using partitioned databases or using the @XREF function in calculation scripts, you must selectively replay logged transactions in the correct chronological order between the source and target databases.
Note:
Back up and restore the source and target databases simultaneously. See Backing Up and Restoring Partitioned Databases.