Running the Database Maintenance Wizard

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. This will allow faster execution of tasks and quicker GUI responses in BMC Server Automation. 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
  • libXi
  • libXext
  • libX11
  • libxcb
  • libXau
  • libXtst
  • libXrender
  • glibc.i686
  • gtk2.i686
  • libXtst.i686
• 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.
• Backup the database before you begin the installation.
• Also ensure that database statistics are updated before running the wizard.
• Database user is required to have following privileges or prerequisites:
  • Oracle:
    • Create Job
      Syntax: GRANT CREATE JOB TO <bsa schema user>
    • Create External Job
      Syntax: GRANT CREATE EXTERNAL JOB TO <bsa schema user>
    • Execute on DBMS scheduler
      Syntax: GRANT EXECUTE ON DBMS_SCHEDULER TO <bsa schema user>
  • SQL Server:
    • The database user should have sysadmin role.
      Syntax: EXEC master..sp_addsrvrolemember @loginame = <bsa db user>, @rolename = 'sysadmin'
    • SQL Server Agent Service is running.

Impact of Database Maintenance process on Back/Recovery and High Availability

The NOLOGGING clause used in Database Maintenance while creating table/indexes has following impacts:

• Impact on Backup and Recovery: You need to take full backup of the Database after Database Maintenance is completed, because this process uses object level nologging while purging objects which in turn causes the backup taken before Database Maintenance to become invalid.
• Impact on High Availability setup: Before starting Database Maintenance, it is required to stop the archive log sync-up with standy database, because Database Maintenance uses object level nologging while purging objects. Standby setup becomes invalid after Database Maintenance process. It must re-create standby after Database Maintenance. This impact is also observed on other high availability setups, for example, Dataguard and GoldenGate.

Running the offline database cleanup utility

1. 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 Solaris	/opt/bmc/bladelogic/NSH/br/dbm-rcp	dbm

   Note

   If the Wizard is launched through a third party utility, for example Exceed, the screen resolution should be set to 1024*768.

2. During the Installation phase, Database Maintenance will gather the retention statistics. This activity may take a few minutes to move on to the Settings pane.

3. On the Settings pane configure the following fields:

   • Before you configure the settings, click on the link given above the table to get the approximate values of data cleanup for different retention periods. The following figure shows the approximate data cleanup that appears after clicking on the link:
     
     
     Based on this data you can enter the desired retention period.

   • Module: Select the modules for which you want to run the cleanup utility. If the modules selected have a dependency on Job Run Event, Job Run Event is selected by default.

   • Result Retention Period (days): Enter the result retention period for your data. This period is the number of days for which data is retained in the database. Data older than this period is purged by the Database Maintenance utility. Default retention period for Job Run Event and Audit Trail is 14 days and for all other modules it is 90 days.

     Note

     Result retention period for the Job Run Event and Audit Trail modules takes precedence over the retention period for other modules. As a result, after the maintenance process, if the job run retention value for any module is greater than the retention period of the Job Run Event and Audit Trail modules, the details of Job Run Event and Audit Trail will not be available.

     For example, if the result retention period for the NSH Script module is 20 days and for the Job Run Event and Audit Trail modules, the retention period is 14 days, the Job Run Event and Audit Trail details for the NSH Script module will be available for 14 days only.

   • Data Cleanup % (Approx.):  This field calculates the approximate percentage of data cleanup based on the value of retention period after tab-out.

     Info

     BMC recommends that the data cleanup percentage is more than the threshold value (30%). The computation of the cleanup percentage is disabled for two modules, namely, Job Run Event and Audit Trail.

   • Last Run Date: Shows the last date when the cleanup was done for respective modules.
   • Last Run Status: Shows the status of the maintenance process when the the last cleanup was done for respective modules.
   • Select All: By clicking on this button, all the modules are selected for cleanup.
   • Clear All: By clicking on this button, all the result retention values entered will be cleared.
   • Reset Default Retention Period: By clicking on this button. the result retention values will be set to default values for all the modules.

   • Configure Database Maintenance Execution Duration: This is the duration in minutes for which the maintenance process will run. The range is 60 minutes to 1440 minutes (1 hour to 24 hours).

     Note

     • If the duration is not defined, the process will continue until completion or till when it is explicitly stopped.
     • After completion of the specified duration, the maintenance process will end only after it has reached an appropriate stage in its execution. This is done to prevent data inconsistency and  it might take a while  for the process to stop.
   
   Click Next after entering the settings to run the Database Maintenance.

4. Once the Database Maintenance process has started, you can see the progress of each module and its Status, Start Time, End Time and Time Elapsed on the Database Maintenance pane.
   

5. You can halt 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.

   

6. When the Database Maintenance Wizard is in the Halted state, you can do either of the following:

   • Click Restart to start the clean up process again.

     Note

     The wizard will rerun the Database Maintenance process for all the selected modules if the process is restarted:

   • 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.

7. The Summary panel of the Wizard displays each module with its status.
   

   Note

   You can find a detailed log report of the Database Maintenance process in the run_log.log and dbmaintenance.log file which is at the following location:

   <Path to BladeLogic>/NSH/br/dbm-rcp/run_log.log and  <Path to BladeLogic>/NSH/br/dbm-rcp/dbmaintenance.log
   

Post-cleanup activities

After the Database Maintenance process ends, it is recommended that you gather the statistics for all objects. This is done to improve the performance.

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:

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);