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:
     1. 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.

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

     3. 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"