Preparing for offline database cleanup

Before you run Offline Cleanup, review the following preparatory tasks and perform the tasks that are necessary for your environment:

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
To maintain data consistency, ensure that all Application Servers are shut down and that no ETL processes are running on the database.
Ensure that the Application Server is upgraded to BMC Server Automation version 8.3 SP2 or later.
Ensure that the database statistics are updated before running the wizard.
BMC recommends that you run an ETL for data older than the retention period that you want to store in the Reporting Data Warehouse. After Cleanup is run, the data will not be available to load into the Data Warehouse.
Back up the database before you begin the installation.
You can choose to upgrade the database either before or after performing Offline Cleanup.
Database user is required to have following privileges or prerequisites:
- Oracle. The Oracle DBA must ensure that the BladeLogic database user has the appropriate permissions to run the Offline Cleanup:
  - 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.

For an Oracle database, follow these steps:
Click to expand the steps for the Oracle Database
1. Obtain the most recent version of external-files.zip and extract its contents. For a description of this file, see Downloading-the-installation-files.
2. Using the files extracted from external-files.zip, copy the /db_scripts/oracle/upgrade directory into a directory on your Application Server.
  Note
  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.
3. The Oracle DBA must perform the following steps:
  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.
  Warning
  You must log on as sysdba to run the migration_setup_OM.sql script in the next step.
  Note
  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:
  start migration_setup_OM.sql

Prior to running the cleanup process, it is worthwhile to capture the current size of the database tables so that these sizes can be compared to the post-cleanup sizes. To capture database table sizes, run one of the provided scripts, depending on your database type — either Oracle or SqlServer.

Note

Because the Oracle script queries the USER_TABLES to determine the size and row counts, ensure that a table statistics run has completed prior to running the analysis script.

For a list of database tables that are impacted by the cleanup process and their correlation with the Offline Cleanup modules, expand the following table.

Module name	Table name
Job Run Event	JOB_RUN_EVENT
Job Run Event	DEPLOY_JOB_RUN_EVENT
Snapshot	SNAPSHOT_AUTH_DEPLOY_JOB_RUNS
Snapshot	SNAPSHOT_COMPONENT_STATS
Snapshot	SNAPSHOT_PART
Snapshot	SNAPSHOT_GENERIC_ASSET
Snapshot	SNAPSHOT_SYSTEM_INFO_OBJECT
Snapshot	SNAPSHOT_EVENT_LOG
Snapshot	SNAPSHOT_DOT_NET_ASSEMBLY
Snapshot	SNAPSHOT_COM_OBJECT_CNT
Snapshot	SNAPSHOT_METABASE_OBJECT_CNT
Snapshot	SNAPSHOT_BLFILE_CNT
Snapshot	SNAPSHOT_GPO_SETTING
Snapshot	SNAPSHOT_VMWARE_OBJ_CNT
Snapshot	SNAPSHOT_CFG_FILE_OBJECT_CNT
Snapshot	SNAPSHOT_COM_OBJECT
Snapshot	SNAPSHOT_GPO_SETTING_CNT
Snapshot	SNAPSHOT_METABASE_OBJECT
Snapshot	SNAPSHOT_BLFILE
Snapshot	SNAPSHOT_OS_USER
Snapshot	SNAPSHOT_OS_GROUP
Snapshot	SNAPSHOT_CONFIG_FILE_OBJECT
Snapshot	SNAPSHOT_SOFTWARE
Snapshot	SNAPSHOT_SERVICE
Snapshot	SNAPSHOT_SYS_INFO_OBJECT_CNT
Snapshot	SNAPSHOT_REGISTRY_OBJECT
Snapshot	SNAPSHOT_REGISTRY_OBJECT_CNT
Snapshot	SNAPSHOT_VMWARE_OBJECT
Compliance	COMPLIANCE_REMEDIATION_RUN
Compliance	CR_GROUP_RESULT
Compliance	JOB_RESULT_CR_GROUP_RESULT
Compliance	COMP_RULE_GROUP_RESULT_XREF
Compliance	COMPLIANCE_RULE_RESULT
Compliance	COMP_RULE_GROUP_RULE_RESULT
Compliance	CONDITION_RESULT
Compliance	CR_RULE_RESULT_SELECTOR
Compliance	COMP_ENTRY_RESULT
Compliance	COMP_ENTRY_RESULT_DETAIL
Compliance	COND_RESULT_SUB_COND_RESULT
Compliance	PROPERTY_CONDITION_RESULT
Compliance	ASSET_ATTRIBUTE_COND_RESULT
Compliance	ASSET_SELECTOR_RESULT
Compliance	SELECTOR_CONDITION_RESULT
Compliance	SELECTOR_RESULT_PATH
Compliance	HRB_COMP_RULE_GROUP_RESULT
Compliance	HRB_CONDITION_RESULT
Compliance	SCAP_RESULT_TARGET_DATA
Compliance	SCAP_RESULT_NETWORK_DATA
Compliance	SCAP_RESULT_RULE_DATA
Deploy	DEPLOY_JOB_RUN
Deploy	DEPLOY_JOB_RUN_CLASS_REF
Deploy	DEPLOY_JOB_RUN_ATTRIBUTE_REF
Deploy	JOB_RESULT_GEN_DEPOT_OBJECT
Deploy	DEPLOY_JOB_DEVICE_RUN
Patching	PATCHING_JOB_RESULTS_ASSOC
Patching	PATCHING_JOB_RUN_PART
Patching	PATCH_RMD_DNL_JOB_RUN_ASSOC
Patching	PATCH_CUJ_RESULT
Patching	BL_PATCH_JOB_RESULT_COUNT
Patching	BL_PATCH_JOB_RES_PATCH_COUNT
Patching	PATCH_DEPENDENCY_MAP
Patching	BL_PATCH_ANALYSIS_RES_DATA
Patching	BL_PATCH_RES_DEVICE_CNT
Patching	BL_PATCH_DOWNLOAD_RES_DATA
Patching	PATCH_DOWNLOAD_RESULT
Patching	PATCH_RMD_RESULT
Patching	PAJOB_RUN_CATALOG_ASSOC
Patching	PATCH_RMD_JOB_ASSOC
Audit	AUDIT_OBJECT
Audit	AUDIT_RESULT_PART_RULE_SET
Audit	AUDIT_OBJECT_PART
Audit	AUDIT_GPO_SETTING_CNT
Audit	AUDIT_REGISTRY_OBJECT_CNT
Audit	AUDIT_COM_OBJECT_CNT
Audit	AUDIT_OS_USER
Audit	AUDIT_CONFIG_FILE_OBJECT
Audit	AUDIT_DOT_NET_ASSEMBLY
Audit	AUDIT_TARGET_PART_STATUS
Audit	AUDIT_COM_OBJECT
Audit	AUDIT_METABASE_OBJECT_CNT
Audit	AUDIT_GPO_SETTING
Audit	AUDIT_EVENT_LOG
Audit	AUDIT_CFG_FILE_OBJECT_CNT
Audit	AUDIT_VMWARE_OBJECT
Audit	AUDIT_OBJECT_PART_COUNT
Audit	AUDIT_BLFILE
Audit	AUDIT_SOFTWARE
Audit	AUDIT_BLFILE_CNT
Audit	AUDIT_VMWARE_OBJ_CNT
Audit	AUDIT_SYS_INFO_OBJECT_CNT
Audit	AUDIT_REGISTRY_OBJECT
Audit	AUDIT_SYSTEM_INFO_OBJECT
Audit	AUDIT_SERVICE
Audit	AUDIT_METABASE_OBJECT
Audit	AUDIT_OS_GROUP
Audit	AUDIT_GENERIC_ASSET
Audit Trail	AUDIT_TRAIL
Audit Trail	APP_SERVER_AUDIT_TRAIL
Audit Trail	AUTO_PRINCIPAL_AUDIT_TRAIL
Audit Trail	BL_ACL_POLICY_AUDIT_TRAIL
Audit Trail	BL_ASSET_CLASS_AUDIT_TRAIL
Audit Trail	BLGROUP_AUDIT_TRAIL
Audit Trail	BLROLE_AUDIT_TRAIL
Audit Trail	BLUSER_AUDIT_TRAIL
Audit Trail	BL_ACL_TEMPLATE_AUDIT_TRAIL
Audit Trail	BL_AUTH_PROFILE_AUDIT_TRAIL
Audit Trail	COMPONENT_AUDIT_TRAIL
Audit Trail	CUSTOM_COMMAND_AUDIT_TRAIL
Audit Trail	DEPOT_OBJECT_AUDIT_TRAIL
Audit Trail	DEVICE_AUDIT_TRAIL
Audit Trail	EXECUTION_TASK_AUDIT_TRAIL
Audit Trail	EXTENDED_OBJECT_AUDIT_TRAIL
Audit Trail	JOB_AUDIT_TRAIL
Audit Trail	LDAP_CONNECTION_AUDIT_TRAIL
Audit Trail	LDAP_QUERY_AUDIT_TRAIL
Audit Trail	PM_DEVICE_AUDIT_TRAIL
Audit Trail	PROP_SET_CLASS_AUDIT_TRAIL
Audit Trail	PROP_SET_INSTANCE_AUDIT_TRAIL
Audit Trail	ROUTING_POLICY_AUDIT_TRAIL
Audit Trail	SERVER_CONFIG_DEF_AUDIT_TRAIL
Audit Trail	SYSTEM_PACKAGE_AUDIT_TRAIL
Audit Trail	TEMPLATE_AUDIT_TRAIL
Audit Trail	TOKEN_RULE_SET_AUDIT_TRAIL
Shared Data Objects	CONFIG_FILE_OBJECT
Shared Data Objects	CONFIG_FILE_OBJECT_VALUE
Shared Data Objects	OBJECT_OVERFLOW
Shared Data Objects	OBJECT_OVERFLOW_DETAIL
Job Run Framework	JOB_RUN
Job Run Framework	JOB_RUN_OVERRIDDEN_PSI
Job Run Framework	JOB_RUN_ETL_CDC
Job Run Framework	NIMBUS_TEMPLATE_INSTANCE
Job Run Framework	JOB_RESULT
Job Run Framework	JOB_RUN_SCHEDULE
Job Run Framework	JOB_RESULT_VSMDISCOVERY
Job Run Framework	JOB_RESULT_DEPOT_OBJECT
Job Run Framework	JOB_RESULT_TEMPLATE
Job Run Framework	JOB_RESULT_DEVICE
Job Run Framework	JOB_RESULT_ATRIUM_TO_BL_SYNC
Job Run Framework	JOB_RESULT_BLGROUP
Job Run Framework	JOB_RESULT_COMPONENT
Job Run Framework	NTISAN_NIC
Job Run Framework	NTILAN_NIC
Job Run Framework	NTIBOOT_ORDER
Job Run Framework	NTIPOLICY
Job Run Framework	NTIVMNIC_CONFIG
Job Run Framework	NTIAUDIT_TRAIL
Job Run Framework	BATCH_JOB_RUN
Job Run Framework	EXECUTION_TASK_JOB_RUN

To evaluate the impact of the cleanup process on the BMC Server Automation job data that you have stored in your database, you can use the dbm_cli command line utility. Use the following information modes:
- To evaluate how much data would be cleaned out with various retention times, run the following command:
  ./dbm_cli.nsh -eval
  For a sample output to this command, click to expand.
  |------------------------------------------------------------------------------------------------|
  Evaluating Retention Scenarios
  |------------------------------------------------------------------------------------------------|
  MODULE \ RETENTION --> 15 DAYS 60 DAYS 90 DAYS 180 DAYS 365 DAYS 730 DAYS
  Job Run Event -2 -2 -2 -2 -2 -2
  Snapshot 100 98 97 94 88 77
  Compliance 99 95 93 84 67 32
  NSH Script 98 91 86 67 41 24
  Deploy 98 94 92 82 63 30
  Patching -1 -1 -1 -1 -1 -1
  Audit 71 0 0 0 0 0
  Audit Trail -2 -2 -2 -2 -2 -2
  -----NOTE-----
  * Figures denote % of deletable data for each module
  * "-1" denotes no data available
  |------------------------------------------------------------------------------------------------|
  This sample output indicates the following findings:
  Audit Trail and Job Run Event modules are not evaluated for cleanup percentages. This is indicated by the number -2 (Not applicable).
  Patching data was not available for any of the retention periods. This is indicated by the number -1 (No data available).
  Audit data is between 15 to 60 days old.
  Other tables would greatly benefit from a cleanup with a retention policy below 90 days (90% or more of the data would be removed).
  Note
  You can, alternatively, access a similar report through the GUI — through the Settings panel of the Database Maintenance Wizard. See Running offline database cleanup.
- To determine what jobs and job runs would be affected by various retention times, run a command similar to the following example command:
  ./dbm_cli.nsh -impact Snapshot=30
  This command is useful for determining whether the retention value passed during offline cleanup would remove any job runs that might need to be kept, as well as to see which specific jobs would be affected by the cleanup.
  
  For an example of the generated CSV file, click to expand.
  
  The CSV file contains a list of jobs and job runs affected by the cleanup, in the following format:
  JOB TYPE,OWNER,JOB NAME, RETENTION,CREATION DATE, LAST RUN
  "Snapshot Job","BLAdmin","Inventory Snapshot","null","2014-04-23 13:03:52","2014-09-30 17:00:00"

Preparing for offline database cleanup

On this page