Running the Database Maintenance Wizard (SP2)
BMC Server Automation provides a separate database cleanup utility for offline maintenance. In most cases, data older than the retention period is not used very often and becomes an overhead to the database. You can use this utility to reduce the size of the database by deleting data that is older than the retention period. BMC recommends running the offline clean up utility before migrating data in a database.
Before you begin
To use the Offline Cleanup utility on Red Hat Linux, you must have certain 32-bit library files installed.Click here for the list of library files.
- libstdc++-33 or compat-libstdc++-33
- libtermcap or compat-libtermcap
- In order to maintain data consistency, ensure that all Application Servers are shut down and no ETL processes are running on the database.
- BMC recommends that you run ETL for data older that the retention period to prevent any loss of data.
- In case of an Oracle database, the Oracle DBA must perform the following steps:
- Click to expand steps for Oracle Database
- Obtain the most recent version of external-files.zip and extract its contents. For a description of this file, see Downloading the installation files.
Using the files extracted from external-files.zip, copy the /db_scripts/ <db_type>/upgrade directory into a directory on your Application Server.
BMC recommends using a directory that is at or close to the root of a disk drive. This practice avoids excessively long paths. Windows paths are limited to 255 characters.
- The Oracle DBA must perform the following steps:
- Confirm that adequate TEMP and UNDO space is available. See Determining sizing requirements for Oracle databases.
- Copy /db_scripts/oracle/upgrade/migration_setup_OM.sql, which you extracted from external-files.zip, to a location where you have access to SQL*Plus.
Log on to SQL*Plus as sysdba.
You must log on as sysdba to run the migration_setup_OM.sql script in the next step.
If your installation has chosen a schema owner for the core BMC Server Automation database other than the default schema owner BLADELOGIC, you must modify the migration_setup_OM.sql script run in the next step to use the schema owner user name for your installation. After the data migration has completed, the schema owner can have its additional migration roles and permissions returned to their normal state.
Set the BMC Server Automation user's roles and permissions required for running the offline database cleanup by entering the following command:
BMC recommends the following Troubleshooting tips before you install the Wizard:
- Backup the database before you begin the installation.
- You can use Database Maintenance Wizard only after the Application Server is upgraded to BMC Server Automation version 8.3 SP2.
- Database upgrade can be done before or after running the Database Maintenance Wizard.
Running the offline database cleanup utility
Log on the Application Server that you plan to run the offline database cleanup utility (ensure that you have the required permissions to access the Database Maintenance Wizard).
To start the Database Maintenance Wizard navigate to the location where the installable file is located. The path and file name for Microsoft Windows and Linux are listed in the table below:
Platform Path Installable File Microsoft Windows C:\Program Files\BMC Software\BladeLogic\NSH\br\dbm-rcp dbm.exe Linux and UNIX /opt/bmc/bladelogic/NSH/br/dbm-rcp dbm.nsh
If the Wizard is launched through a third party utility, for example Exceed, the screen resolution should be set to 1024*768.
Before you proceed to the Settings pane, ensure that all Application Servers connected to the database are shut down and no ETL processes are running.
On the Settings pane, select the retention period for your data. The retention period can be set to a maximum of 9999 days and a minimum of 3 days. This retention period is applied to all Job Result data irrespective of the existing Retention Policy set at the individual Job level.
Once the Database Maintenance process has started, you can see the progress of each module and its status on the Database Maintenance pane.
The wizard will not run the Database Maintenance process for modules that have been cleaned within a period of 180 days.
You can pause the offline database cleanup process by clicking the Stop button. However, the system only halts the process at stage from where it can recover without any loss or corruption of data. For example, in the following screenshot, the system has halted only after completing the cleanup process for the first two modules.
When the Database Maintenance Wizard is in the Halted state, you can do either of the following:
Click Restart to continue the clean up process from where it was halted.
The wizard will perform the following actions if the process is restarted:
- It will rerun the Database Maintenance process for modules that were completed before a period of 180 days.
- It will rerun the Database Maintenance process for modules that were run unsuccessfully.
Click Finish to end the clean up process and exit the wizard. You can restart the wizard to continue the clean up activities at a later time.
The Summary panel of the Wizard displays each module with its status.
You can find a detailed log report of the Database Maintenance process in the run_log.csv file which at the following location:<Path to BladeLogic>/NSH/br/dbm-rcp/run_log.log
The Database Maintenance Wizard creates swap tables during its run, that are dropped after the cleanup process. The space still remains occupied and should be reclaimed back. The Database administrator can perform the following steps for Oracle and SQL Server respectively:
To reclaim the free space in tablespaces Database Administrator can follow Oracle documentation. Following query can be used to see the actual required size of datafiles to resize:
SELECT file_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) reqdsize, ceil( blocks*&&blksize/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings FROM dba_data_files a, ( SELECT file_id, MAX(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b WHERE a.file_id = b.file_id(+) ORDER BY savings DESC
- SQL Server:
To reclaim the available space Database Administrator can follow SQL Server documentation. Following query can be used to see the the actual required size of datafiles to resize:
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB], size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB] FROM sys.database_files OPTION (RECOMPILE);