Click here for aggregate storage version
Change database-wide settings.
Permission required: create_application.
Syntax
Use alter database to change the following database-wide settings:
Keyword | Description | ||||
---|---|---|---|---|---|
reset | Clear all data and linked-reporting objects from the database, but preserve the outline. | ||||
reset all | Clear all data, Linked Reporting Objects, and the outline. | ||||
reset data | Same as using reset. | ||||
validate data to local logfile... | Create a local log file with all index combinations for which blocks contain invalid block headers. Before using this MaxL statement, be sure that the server is not performing other operations, such as calculations or data loads; otherwise, an exception error may occur. The recommended procedure is:
For example, alter application sample disable connects; alter system logout session on database sample.basic; alter database sample.basic validate data to local logfile 'invalid_blocks'; alter database sample.basic repair invalid_block_headers; | ||||
validate using... | Check the database for data and structural integrity. A file is created containing error messages if there are problems. The default error file is VALIDATE.LST in the application or database directory. For example: Hyperion/products/Essbase/EssbaseServer/app/sample/basic/VALIDATE.LST. The validate utility verifies the following:
| ||||
repair invalid_block_headers | Delete all blocks that have invalid headers. Before using this statement, see validate data to local logfile. | ||||
recover freespace | Explicitly recover database freespace in the event of a crash or abnormal shutdown. Beginning with Release 7.0, freespace recovery only occurs if you explicitly request it. | ||||
force restructure | Explicitly restructure the database to eliminate or reduce fragmentation. | ||||
load alias_table | Load an alias table from a file to the current database. The feeder file (FILE-NAME) must follow these rules:
Sample contents of a feeder file for loading an alias table: $ALT_NAME "400-10" Guava "400-20" Tangerine "400-30" Mango $END | ||||
unload alias_table | Delete the specified alias table. | ||||
add variable | Create a database-level substitution variable by name, and optionally assign a string value for the variable to represent. You can assign or change the value later using set variable. A substitution variable acts as a global placeholder for information that changes regularly. Substitution variables may be referenced by calculations and report scripts. If substitution variables with the same name exist at server, application, and database levels, the order of precedence for the variables is as follows: a database level substitution variable supersedes an application level variable, which supersedes a server level variable. | ||||
drop variable | Remove a substitution variable and its corresponding value from the database. | ||||
delete lro | Delete Linked Reporting Objects linked to the active database for a given user name or modification date. | ||||
unlock all objects | Unlock all objects on the database that are in use by a user or process. | ||||
begin archive to file | Prepare the database for backup by an archiving program, and prevent writing to the files during backup. This statement requires the database to be started. Begin archive achieves the following outcomes:
Begin archive and end archive do not perform the backup; they simply protect the database during the backup process. | ||||
end archive | Return the database to read-write mode after backing up the database files. This statement requires the database to be started. End archive achieves the following outcomes:
| ||||
archive to file | Write a copy of the database files to a specified archive file that resides on the Essbase Server computer. Provide the full pathname to an existing directory and the name of the archive file. If only the archive file name is provided, Essbase writes the archive file to ARBORPATH/app. Oracle recommends writing the archive file to a different disk than the one where ARBORPATH is located and recommends that you name the file with a .arc extension. By default, Essbase creates a single, large archive file. The size of the archive file corresponds to the size of the database you back up and is limited only by disk space. If, however, in your environment you cannot use large files or the file-transfer tools that you use cannot handle large files, you can configure Essbase to split the archive file into multiple files, with each file no larger than 2 GB. In the essbase.cfg file, set the SPLITARCHIVEFILE configuration setting to TRUE.
If you are backing up a database to an existing archive file, you must use the force archive to file grammar to overwrite the file.
| ||||
force archive to file | Overwrite the contents of an existing archive file.
| ||||
restore from file | Restore a database with the contents of the specified archive file. If you have configured Essbase to split the archive file into multiple files (SPLITARCHIVEFILE), you only need to specify the file name of the main archive file that you want to restore (for example, samplebasic.arc). All archive files must reside in the same directory as the main archive file. Typically, you restore a database to the application and database from which the backup was taken and, therefore, the names of the backed up and restored database and its associated application are the same. If, however, the names of the backed up database and application are not the same as the application and database to which you are restoring data, you must use the force restore from file grammar. | ||||
restore from file...replace disk volume VOL-REPL | Restore a database with the contents of the specified archive file and replace the specified disk volumes. Valid values for the VOL-REPL argument are a comma-separated list of volumes to replace:
The number of disk volumes used and the space required for the restored database must be the same as for the database before it was backed up. Only the name of disk volumes can be different. | ||||
force restore from file... | Use the contents of the specified archive file to restore to a database that has different names than the archived database or its associated application. For example, you can use the archive file for Sample.Basic to restore to Sample.New (the database name is different), MyCompany.Basic (the application name is different), or MyCompany.New (both names are different). | ||||
replay transactions | Replays the database transactions that were logged after the last replay request was originally executed or after the last restored backup's time (whichever occurred later). 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. | ||||
replay transactions after LOG-TIME | Replays the transactions that were logged after the specified time. Enclose the TIME value in quotation marks; for example: '11_20_2007:12:20:00' | ||||
replay transactions using sequence_id_range ID-RANGE | Replays the transactions specified by a comma-separated list of sequence ID ranges. A range can consist of:
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. The order of sequence IDs are tracked across multiple replay commands.
| ||||
rename to | Rename the database. When you rename a database, the database directory is also renamed. | ||||
comment | Create a description of the database. The maximum number of characters is 80. This description is available to database administrators. To annotate the database for Spreadsheet Add-in users, use set note. |
Example
alter database Sample.Basic archive to file /Hyperion/samplebasic.arc;
Backs up Sample.Basic database files to the specified archive file (samplebasic.arc) on Essbase Server.
alter database Sample.Basic force archive to file /Hyperion/samplebasic.arc;
In backing up the Sample.Basic database files, overwrites the existing archive file (samplebasic.arc).
alter database Sample.Basic restore from file /Hyperion/samplebasic.arc;
Restores the Sample.Basic database using the samplebasic.arc archive file.
alter database MyCompany.New force restore from file /Hyperion/samplebasic.arc;
Uses the archive file for the Sample.Basic database (samplebasic.arc) to restore the MyCompany.New database.
alter database Sample.Basic restore from file /Hyperion/samplebasic.arc replace disk volume 'C' with 'F', 'D' with 'G', 'E' with 'H';
Restores the Sample.Basic database using the samplebasic.arc archive file and replaces the specified disk volumes.
alter database Sample.Basic replay transactions using sequence_id_range 1 to 10,20 to 100;
Replays the transactions in the Sample.Basic database with sequence IDs 1 through 10 and 20 through 100.
alter database Sample.Basic replay transactions after '11_20_2007:12:20:00';
Replays all transactions that were logged after the specified time.
See Also