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.
- 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:
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
- 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.
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.
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.
Post-cleanup activities
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:
- Oracle:
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:
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:
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files OPTION (RECOMPILE);