This blog post is geared for Essbase administrators who definitely has the need to shutdown Essbase for their environment periodically or occasionally. For regular maintenance, the administrator most probably has the need to update system configurations or perform regular database backup. Occasionally, the administrator may face a hung process that he/she will need to deal with. While there are many ways of doing it, I want to discuss the right way, the safest way, the way that needs to be done, the best practice. Why? Because if you don’t, you may face the bigger problem of security file corruption or worse yet, database corruption.
Essbase Shutdown Periodically
In most environments, the Essbase administrator would invoke MaxL scripts to shutdown Essbase prior to performing the necessary maintenance routine tasks. Alarmingly, many chooses to simply issue the shutdown command i.e. ‘alter system shutdown’. While this seems to work 90% of time, for the remaining 10%, Essbase will likely be hung by a user process or by locked objects. Therefore, I want to show you the better way of shutting down Essbase, by taking other factors into consideration.
First let’s take a look at the proper MaxL script that should be applied:
spool on to <path>/myshutdown.log
login <adminuser> <adminpassword> on <host>;
alter system logout session all force;
alter database <application>.<database> unlock all objects;
alter system kill request all;
alter system unload application all;
alter system shutdown;
logout;
spool off;
exit;
What we need to pay attention to, are the commands that were issued prior to the final shutdown command. For instance, the command ‘alter system logout session all force‘ will log out all users, including those that maybe retrieving or calculating data. This command takes care of the common problem that are often seen by administrators: ‘Cannot unload database [Simple.Basic] while user [Daniel Poon] is performing database operation. Wait for the user to complete the operation, or ask the user to abort it. Log out all users and then unload the database.’
Next, the command ‘alter database <application>.<database> unlock all objects‘ will unlock all the objects that had been locked by users. For example, it’s quite common that the outline gets locked which could affect a number of maintenance operations.
And then, the command ‘alter system kill request all‘ will terminate all the existing processes that are running.
After that we will issue the command ‘alter system unload application all‘ to shutdown each individual application and database(s).
The above three steps illustrates a proper shutdown sequence, taking into account the very likely scenario that someone may still be engaged with the database when the administrator wants to perform maintenance. After these three steps are completed should you really be issuing the final ‘alter system shutdown’ to shutdown Essbase.
Essbase Shutdown Occasionally
In the unlikely event that the Essbase server is hung, and the administrator was not able to shutdown the server with the above MaxL commands, there maybe little choice except to terminate the Essbase agent.
In Windows, you can simply end the process within the task manager.
In Linux, you will need to first locate the process ID of the Essbase agent by issuing the command: ‘ps -ef | grep ESS‘. After noting down the process ID e.g. 1366, you can then try to shutdown the server using: ‘kill -15 1366‘. The ‘-15′ is a switch that allows the program to do any clean up prior to closing off the process. However, if that fails, you may have no choice but to close it with the command: ‘kill -9 1366‘.
Please note that we do not recommend shutting down Essbase by terminating the Essbase agent because there’s significant risks associated with abnormal shutdown. Therefore our disclaimer to you is that ‘use at your own risk !‘ Now in my experience, Essbase normally comes back online without issues after an abnormal shutdown 98% of time. But when the 2% strikes, a number of scenarios may occur:
- Essbase database security file is corrupted. You will need to restore the security file from the essbase.bak file.
- Essbase database is corrupted and cannot be started and exception files (.xcp) will be created. You will need to restore the entire database from backup.
- Invalid block header maybe introduced into the cube, this is the silent killer which may pose problems later on.
Prevention Better Than Cure
In order to minimize the problems associated with an abnormal shutdown, we recommend the following:
Put in QRYGOVEXECTIME in the essbase.cfg to prevent the types of queries that will cause Essbase to be overly preoccupied with processing and refuses to shutdown:
- A long-running query against a database that accesses attributes at a high level, forcing many dynamic calculations to occur.
- A query that uses the “Drill to bottom” option in a large dimension.
- A query that uses the “Drill to all levels” option in a a large dimension.
Change the Essbase timeout frequency by issuing the following commands in MaxL:
alter system set session_idle_limit INTEGER 60 minutes;
alter system set session_idle_poll INTEGER 30 minutes;
The idea here is we will reduce the user timeout down from the standard 60 minutes to 30 minutes. Firstly, this reduces the number of idle users in the system, and as a result, it also reduces the chance of hanging or problems caused by running out of system resources. Secondly, the above commands will increase the backup frequency of the Essbase security file, in order to reduce the chance of database corruption even if Essbase would be shutdown abnormally.
Last but not least, we recommend you to perform regular backup of your Essbase database e.g. exports and file system backup. This will be invaluable when there is a need to restore the database should disaster strikes.