In This Section:
Understanding Isolation Levels
Understanding How Essbase Handles Transactions
Specifying Data Integrity Settings
Checking Structural and Data Integrity
Using VALIDATE to Check Integrity
The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases. Also see Comparison of Aggregate and Block Storage.
When a database is in read/write mode, Essbase considers every update request to the server (such as a data load, a calculation, or a statement in a calculation script) as a transaction. Essbase tracks information about transactions in a transaction control file (dbname.tct).
The transaction control file contains an entry for each transaction and tracks the current state of each transaction (Active, Committed, or Aborted).
Isolation levels determine how Essbase commits data to disk. When data is committed, it is taken from server memory and written to the database on disk. Essbase automatically commits data to disk. There are no explicit commands that users perform to commit data blocks. However, setting the isolation level for a database defines how Essbase automatically commits data blocks.
Essbase offers two isolation levels for transactions—committed access and uncommitted access (the default). You can optimize data integrity by using committed access.
For an explanation of access types, see Committed Access and Uncommitted Access.
Setting the isolation level to committed access may increase memory and time requirements for database restructure. |
Essbase issues write (exclusive) locks for blocks that are created, updated, or deleted, and issues read (shared) locks for blocks that should be accessed but not modified. By issuing the appropriate locks, Essbase ensures that data changed by one operation cannot be corrupted by a concurrent update.
This section discusses locks on data blocks, not locks on database artifacts. For information about locking and unlocking outlines and other artifacts, see Locking and Unlocking Artifacts.
Table 156 explains the lock types:
Table 156. Basic Lock Types
Table 157 shows the locks that Essbase issues for various types of operations.
Table 157. Locking by Higher-Level Functions
How Essbase handles locking depends on whether committed or uncommitted access is enabled.
Committed access provides a high level of data consistency because only one transaction at a time can update data blocks. Under committed access, Essbase allows transactions to hold read/write locks on all data blocks involved with the transaction until the transaction completes and commits. However, you can still allow read-only access to the last committed data values.
Essbase provides options that determine when locks are issued on data blocks:
Pre-image access (enabled by default). Pre-image access provides users read-only access to data blocks that are locked for the duration of a concurrent transaction. Users see the last committed data values for the locked data blocks.
Indefinite wait (the default). The transaction waits to acquire a lock on the required locked block.
Immediate access, or no wait. If a required block is locked by another transaction, Essbase displays a lock timeout message, and the transaction aborts.
A number of seconds that you specify. The transaction waits that number of seconds to acquire a lock on the required locked blocks. If the specified time runs out before the transaction acquires a lock, Essbase displays a lock timeout message, and the transaction aborts.
When pre-image access is enabled, you are not limited to read-only access to data blocks; if you need write access to locked blocks, the transaction waits for write access or times out, depending on the wait or timeout setting. The transaction gets immediate write access to data blocks that are not locked by another transaction.
If pre-image access is not enabled, and if you need read or write access to locked blocks, the transaction waits for write access or times out, depending on the wait or timeout setting.
Under committed access, note the following memory considerations:
Essbase retains redundant data until a transaction commits. Allow disk space for double the size of the database to accommodate redundant data.
Models with a large number of blocks may experience memory problems under committed access. Each lock (one lock per block) uses approximately 80 bytes of memory per calculation, and each lock is held in memory until the transaction is complete. There is a limit to the addressable memory space per process, and eventually models with a large number of blocks may hit this limit, causing the transaction to terminate. In such cases, consider using Uncommitted Access.
Under committed access, Essbase locks blocks for read and write access:
For read access, the lock remains until another transaction requests it, whether or not the transaction is complete. Other transactions can read the locked block, but none can alter it.
For write access, a transaction locks and holds the lock on each block that it modifies until the transaction completes.
Table 158 illustrates locking behavior under committed access when multiple transactions are contending for a lock on the same data. In this example, transaction Tx1 is running and transaction Tx2 is requesting access to the same data. (Note that access to locked blocks depends on what options are enabled. For a discussion of options, see Committed Access.)
Table 158. Locking Behavior Under Committed Access
For information on setting concurrency parameters, see Specifying Data Integrity Settings.
Occasionally, under committed access, a deadlock results when two transactions are locking or waiting for access to the same blocks, and neither transaction can complete under these conditions.
For example, if transaction Tx1 needs to update first data block B1 and then data block B2, it first locks B1 and then attempts to lock B2. Meanwhile, if transaction Tx2 needs to update first data block B2 and then block B1, Tx2 first locks B2 and then attempts to lock B1. Tx1 locked B1 and is waiting for B2, and Tx2 locked B2 and is waiting for B1.
Essbase transactions periodically perform deadlock detection while waiting to acquire a lock. If detected, Essbase issues an error message, and the transaction fails.
If you try to update a block that is locked by another user, Essbase behaves in these ways:
If wait is set to indefinite, the transaction waits to acquire the needed locks.
If wait is set to 0 (immediate), and if the required blocks are not immediately available, Essbase displays an error message, and the transaction fails.
If wait is set to a user-specified number of seconds, and the time has expired, Essbase displays an error message and aborts the transaction.
For information about how to set concurrency options, see Specifying Data Integrity Settings.
Under committed access, if the server crashes, Essbase rolls back all database updates by transactions that were in progress when the server stopped, ensuring that changes made by the aborted transactions are undone.
If a transaction is aborted because of a nonfatal error, all changes made by the transaction are rolled back.
With uncommitted access (enabled by default), the Essbase kernel allows transactions to hold read/write locks on a block-by-block basis; Essbase releases a block after it is updated but does not commit blocks until the transaction completes or until a specified limit (a “synchronization point”) has been reached. You can set this limit, as described below.
Concurrent users accessing the same data blocks might experience unexpected results under uncommitted access, because Essbase allows read-only access to data at its last commit point.
With uncommitted access, you can control when Essbase performs an explicit commit operation by specifying synchronization point parameters:
Commit Blocks (number of blocks modified before a synchronization point occurs). The default is 3,000.
If you set Commit Blocks to 0, the synchronization point occurs at the end of the transaction.
Commit Rows (number of rows to data load before a synchronization point occurs). The default is 0, which means that the synchronization point occurs at the end of the data load.
If either Commit Blocks or Commit Rows has a nonzero value, a synchronization point occurs when the first threshold is reached. For example, if Commit Blocks is 10 but Commit Rows is 0 and you load data, a synchronization point occurs after 10 blocks are updated. If Commit Blocks is 5 and Commit Rows is 5 and you load data, a synchronization point occurs after 5 rows are loaded or 5 blocks are updated, whichever happens first.
If a user-defined threshold is exceeded during an operation, Essbase issues a synchronization point to commit the data processed to that point. Essbase performs as many synchronization points as are necessary to complete the operation.
Essbase analyzes the value of Commit Blocks and Commit Rows during its analysis of feasibility for parallel calculation use. If Essbase finds the values set too low, it automatically increases them. |
For information about how to specify synchronization point parameters, see Specifying Data Integrity Settings.
Essbase retains redundant data to enforce transactional semantics. Allow disk space for double the size of the database to accommodate redundant data, particularly if both Commit Blocks and Commit Rows are set to 0. |
If your data cache is too small to hold the number of blocks specified in your Commit Blocks and Commit Rows settings, the blocks will be written to disk as soon as the caches become full, which will be before the transaction is committed.
Under uncommitted access, Essbase locks blocks for write access until Essbase finishes updating the block. Under committed access, Essbase holds locks until a transaction completes.
Table 159 illustrates locking behavior under uncommitted access when many transactions contend for a lock on the same data. In this example, transaction Tx1 is running and transaction Tx2 is requesting access to the same data.
With uncommitted access, blocks are released more frequently than with committed access, when all blocks are locked until the end of the transaction.
Under uncommitted access, if the server crashes, Essbase rolls back all database updates from the point of the last successful commit. Some updates from an aborted transaction may have committed. Whether transactions committed their updates the way users expected depends on the order in which overlapping transactions updated and committed data.
If a transaction is aborted because of a nonfatal error, Essbase commits only the data that the transaction finished processing before the abort of the transaction.
Consider the following issues when choosing an isolation level:
Database performance.
Uncommitted access always yields better database performance than committed access. When using uncommitted access, Essbase does not create locks that are held for the duration of a transaction but commits data based on short-term write locks.
Data consistency.
Committed access provides a higher level of data consistency than uncommitted access. Retrievals from a database are more consistent. Also, only one transaction at a time can update data blocks when the isolation level is set to committed access. This factor is important in databases where multiple transactions attempt to update the database simultaneously.
Data concurrency.
Uncommitted access provides better data concurrency than committed access. Blocks are released more frequently than during committed access. With committed access, deadlocks can occur.
Database rollbacks.
If a server crash or other server interruption occurs during active transactions, the Essbase kernel rolls back the transactions when the server is restarted. With committed access, rollbacks return the database to its state before transactions began. With uncommitted access, rollbacks may result in some data being committed and some data not being committed.
Essbase tracks transactions from start to finish, swapping data blocks in and out of memory as needed and committing data blocks when a transaction completes. The following list describes how Essbase handles a transaction: all list items apply to committed and uncommitted access (see Understanding Isolation Levels).
The OLAP engine notifies the Essbase kernel that a transaction is to begin.
The Essbase kernel locates the requested data. It passes the data, and some associated control information, to the OLAP engine. If you are using Spreadsheet Add-in, this data is displayed on the sheet.
If you are using Spreadsheet Add-in, when you modify data, you issue the Send command.
The Essbase kernel associates the transaction with an entry in its transaction control table.
After the operation is complete on the OLAP engine side, the OLAP engine notifies the Essbase kernel about the update, and the Essbase kernel updates internal data structures accordingly.
Steps 4–8 repeat as often as necessary to complete the operation.
The transaction ends. If Essbase encounters an error during transaction processing, it aborts the transaction. If no errors are encountered, Essbase commits the transaction. For differences in commit behavior under committed and uncommitted access, see Understanding Isolation Levels.
Essbase issues a message to notify the client that the transaction is complete; for example, “TOTAL CALC ELAPSED TIME...”
Under uncommitted access, it is possible to access uncommitted data when multiple transactions are active and are accessing the same data. Transaction results are unpredictable under uncommitted access.
Under uncommitted access, if you have defined a commit threshold, Essbase may need to break down one database operation into multiple synchronization points. See Uncommitted Access for information on commit thresholds.
You can specify isolation level, synchronization point parameters, and concurrency parameters using Administration Services, MaxL, or ESSCMD. Changes to isolation level settings take effect the next time there are no active transactions. For information about deciding which settings to choose, see Committed Access and Uncommitted Access.
To specify isolation level settings using ESSCMD, enter SETDBSTATEITEM 18 in ESSCMD and either follow the prompts or supply the required values on the command line.
Choose 1 (committed access) or 2 (uncommitted access, the default). Depending on which you specify, ESSCMD prompts you for other parameters (or you can supply the values on the command line).
If you choose 1 (committed access), ESSCMD prompts for the following information:
Pre-image access; Y (Yes) or N (No, the default). Pre-image access provides users read-only access to data blocks that are locked for the duration of a transaction. Users see the last committed data values for the locked data blocks.
Wait (in the Database Settings dialog box) or timeout (in ESSCMD): -1, 0, or n.
If you choose 2 (uncommitted access), ESSCMD prompts for the following values. See Uncommitted Access for explanations of these options.
You can also specify isolation level parameters (pre-image access and so on) by specifying parameters 19–22 on SETDBSTATEITEM. Enter SETDBSTATEITEM with no parameters; ESSCMD displays a list that includes each parameter by number, with a description.
Here is an example of using SETDBSTATEITEM to set an isolation level. This example enables committed access and pre-image access and specifies indefinite wait time.
SETDBSTATEITEM 18 "SAMPLE" "BASIC" "1" "Y" "-1"
To ensure data integrity, the Essbase kernel temporarily retains redundant (duplicate) information. To accommodate redundant information, allow disk space for double the size of the database.
Essbase maintains a file called dbname.esm, in which it stores crucial control information.
To validate database integrity and to check for database corruption, perform an action:
Perform a dense restructure. Because a dense restructure recreates all blocks within a database, this method verifies index nodes and cells for each block.
Export all levels of data from the database. Exporting an entire database accesses blocks and all data values across the entire database.
Use the ESSCMD VALIDATE command to check structural and data integrity. See Using VALIDATE to Check Integrity.
If errors occur during any of these checks, restore the database from backups. See the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide.
The VALIDATE command performs many structural and data integrity checks:
Verifies the structural integrity of free space information in the index.
Compares the data block key in the index page with the data block key in the corresponding data block.
The Essbase index contains an entry for every data block. For every read operation, VALIDATE automatically compares the index key in the index page with the index key in the corresponding data block and checks other header information in the block. If it encounters a mismatch, VALIDATE displays an error message and continues processing until it checks the entire database.
Restructures data blocks whose restructure was deferred with incremental restructuring.
Checks every block in the database to make sure each value is a valid floating point number.
As Essbase encounters mismatches, it records error messages in the VALIDATE error log. You can specify a file name for error logging; Essbase prompts you for this information if you do not provide it. The VALIDATE utility runs until it has checked the entire database.
You can use the VALIDATE command in ESSCMD to perform these structural integrity checks.
During index free space validation, the VALIDATE command verifies the structural integrity of free space information in the index. If integrity errors exist, Essbase records them in the VALIDATE log. The file that you specified on the VALIDATE command holds the error log.
If VALIDATE detects integrity errors regarding the index free space information, the database must be rebuilt. You can rebuild in three ways:
See Optimizing Database Restructuring and the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide.
Even if you do not use VALIDATE, Essbase automatically performs certain validity checking whenever a read operation is performed, to ensure that the index is synchronized with the data.
For every read operation, Essbase compares the data block key in the index page with the data block key in the corresponding data block and checks other header information in the block.
If Essbase encounters a mismatch, it displays an “Invalid block header” error message.
After a server interruption such as a crash, Essbase recovers a database, rolling back all transactions that were active when the interruption occurred. Recovery time depends on the index size. The larger the index, the longer it takes.
Essbase also recovers and consolidates free fragments (unused addressable units in the data blocks). However, free space recovery is the most time-consuming aspect of database recovery, so it is delayed by default. You must trigger free space recovery explicitly unless you have changed the default setting. See Free Space Recovery for the advantages and disadvantages of delaying free space recovery.
Essbase recovers data as soon as the server is started after a server interruption. Recovery phases:
Free space recovery is delayed until you trigger it, unless you have changed the default setting. |
A media failure (faulty disk, disk failure, or head crash) requires that you restore data from backups. See the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide.
Do not move, copy, modify, or delete the following files: essxxxxx.ind, essxxxxx.pag, dbname.ind, dbname.esm, dbname.tct. Doing so can result in data corruption. |
The Essbase kernel uses fatal error handling to display appropriate messages and to shut down the server, depending on the error encountered. For an explanation of how fatal error handling works, see Understanding Fatal Error Handling.
For information about how transactions are rolled back after a crash, see Committed Versus Uncommitted Access.
Database recovery occurs when you load an application that has just crashed or terminated abnormally. Essbase does not perform free space recovery automatically, because it is the most expensive part of database recovery. You must either trigger free space recovery explicitly or change the default setting so that Essbase will recover free space automatically.
All database functions run normally whether you recover free space or not. When you recover free space, you can reuse disk space marked as free in the data files. Keep in mind that free space recovery is time-consuming, so you might delay it until a better time.
You should, however, perform free space recovery as soon as possible to take advantage of the free space in the data files and to ensure that the database has not been corrupted. Also, if a database crashes repeatedly, and you do not run free space recovery, data files can become unnecessarily large.
To trigger free space recovery, use the MaxL alter database command. For example:
alter database DBS-NAME recover freespace
To change the default behavior for free space recovery, change the DELAYEDRECOVERY configuration setting to FALSE. See the “Configuration Settings” section of the Technical Reference.
To get information about free space recovery, use the GETDBSTATS command. GETDBSTATS provides the following information about free space recovery:
Free Space is Recoverable : true/false
Estimated Bytes of Recoverable Free Space : nnnTable 160 lists types of Essbase Server interruptions and their results:
Table 160. Essbase Recovery Handling
Table 161 shows what you must do if a server interruption occurs during a transaction. How Essbase recovers from an interruption depends on the transaction isolation level setting (committed or uncommitted access)—see Rollback with Committed Access and Rollback with Uncommitted Access.
Table 161. Recovery Procedures for Server Requests
If Essbase issues an error, repeat the last send operation. If the spreadsheet has been lost or does not exist, and if you are using SSAUDIT spreadsheet logging, reload the dbname.atx file. See How to Use Spreadsheet Update Logging. | |
Check the server and application logs to see where the calculation left off. See Viewing the Essbase Server and Application Logs. Decide whether to start the calculation over. Repeat the last calculation. | |
| |
Arithmetic data load (adding to or subtracting from values in the database) | If the database is set to committed access, reload the data. (The transaction has been rolled back.) If the database is set to uncommitted access, some of the data loaded, so if you reload all of the data, you receive incorrect results for the data values that loaded twice. Therefore, perform the following actions: |
The restructure is not complete. Delete the temporary restructure files: .pan, .inn, and .otn. Repeat the last operation that caused a restructure. |
For extra protection against data loss and for spreadsheet audit information, Essbase provides spreadsheet update logging, which you enable by using the SSAUDIT or SSAUDITR parameter in the essbase.cfg file on the server. You can specify SSAUDIT for all databases on the server or for individual databases. See the Oracle Essbase Technical Reference.
Essbase handles recovery under normal situations. However, sometimes you may want to load the spreadsheet update log manually. For example, if you have restored from a recent backup and do not want to lose changes made since the backup or you experience a media failure, you can recover transactions from the update log. To do so, use the Essbase command-line facility, ESSCMD, from the server console.
The following ESSCMD command sequence loads the update log:
LOGIN hostnode username password SELECT appname dbname LOADDATA 3 filepath:appname.ATX EXIT
To simplify loading the update log, prepare a batch file as described in Using Script and Batch Files for Batch Processing.
When SSAUDIT or SSAUDITR is specified, Essbase logs spreadsheet update transactions chronologically. Essbase uses two files:
Both files are stored on the server.
The spreadsheet update log can get quite large, even if you are using SSAUDITR, Essbase clears the log only after you back up data. If spreadsheet updates are frequent, consider periodically deleting the log manually.
When a database is started after a shutdown, if spreadsheet logging is enabled, Essbase writes the following message to the database log:
Starting Spreadsheet Log volumename\app\appname\dbname\dbname.atx for database dbname
Starting Spreadsheet Log Hyperion\products\Essbase\EssbaseServer\app\app1\sample\sample.atx for database Sample
To ensure successful spreadsheet update logging, stop and restart the application after either of the following:
Any operation that causes a restructure. See Optimizing Database Restructuring.
Running any of the following ESSCMD commands:
CREATEAPP
CREATEDB
COPYDB
RENAMEDB
Essbase ensures that if you enable spreadsheet logging, updates cannot take place without being logged. If for any reason Essbase cannot write to the update log, Essbase stops the transaction and issues an error message.
SSAUDIT and SSAUDITR are available only from the essbase.cfg file.
Hybrid Analysis provides a way to integrate a relational database with a multidimensional database so that lower-level members and their associated data remain in the relational database while upper-level members and their associated data reside in the Essbase database. This option presents additional issues regarding data consistency and integrity.
For information about ensuring that the data is correct in all locations, see Managing Data Consistency in Hybrid Analysis.