Consolidating two Infrastructure Management Server installations that use two separate Oracle databases into a single Oracle database

This topic lists the procedure to consolidate two BMC TrueSight Infrastructure Management Server installations that use two separate Oracle databases into a single Oracle database.

Assume you have the following deployment scenario:

You have two BMC TrueSight Infrastructure Management Servers (tsim-1 and tsim-2). Tsim-1 is running on Oracle database instance (instance-a) and tsim-2 is running on Oracle database
instance (instance-b).

Related topics

Supporting-multiple-Infrastructure-Management-Servers-on-a-single-Oracle-database-instance

Manually-setting-up-an-Oracle-database

  1. Apply Infrastructure Management 10.5 on tsim-1.
  2.  Apply Infrastructure Management 10.5 on tsim-2.

  3. On instance-a, as a SYS user, run the following script  to create the tablespace and users required to migrate the data on tsim-2 data to instance -a. Choose the same tablespace and user that you selected when installing tsim-2. 

    InstallationDirectory\Utilities\oracle\custom_table_dump_pnet\

    create_oracle_ts_user.sql

    Note

    If the user name and tablespace name that you entered are the same as the ones you used while installing tsim-1 with instance-a, specify a different user name and tablespace name here.

  4. Stop tsim-2.
  5.  Set the SID of instance-b by performing the following steps:
    1. Set ORACLE_SID=<SID of instance-b>
    2. Type sqlplus
    3.  Enter user-name: sys/<password>@< SID of instance-b> as sysdba
  6. Run the following SQL script and specify the location of the export dump directory and user at the prompt:

    InstallationDirectory    \Utilities\oracle\custom_table_dump_pnet\
    custom_dump_dir_pnet.sql 

  7. Update the InstallationDirectory\Utilities\oracle\custom_table_dump_pnet\pnet_exp_users.par file as follows:

    SCHEMAS=<instance-b user name>, <instance-b reporting user name>

     

    This creates two files in the PNET_DUMP_DIR directory:


    EXP_ALL_TABLE_DATA.DMP and exp_all_table_data.LOG

  8. Export all the tsim-2 data from instance-b to instance-a by running the following command:

    expdp <sys of instance-b>/<password>@< SID of instanceb> PARFILE=<path of the pnet_exp_users.par file>

    This completes the export process. If the two instances are physically located on two different computers, then copy the dump file into the computer in which instance-a resides

  9. Connect to the instance-a database as follows
    1.  Set ORACLE_SID=<SID of instance-a>
    2. Type sqlplus
    3. Enter user-name: sys/<password>@< SID of instance-a> as sysdba

  10. Run the following SQL script and specify the location of the directory and user after prompting:

      InstallationDirectory\Utilities\oracle\custom_dump_dir_pnet.sql 

  11. Copy EXP_ALL_TABLE_DATA.DMP (created in step 7) to the dump directory created in step 10.

  12. Update the InstallationDirectory\Utilities\oracle\custom_table_dump_pnet\pnet_imp_users.par file as follows:
     

    #for different Source/Target Tablespace name/Schema name uncomment REMAP parameter and change the name accordingly

    REMAP_TABLESPACE=TS_<instance-b tablespace name>:TS_<instance-a new tablespace name>

    REMAP_SCHEMA=<instance-b user name>:<instance-a new user name>

    REMAP_SCHEMA=<instance-b reporting user name>:<instance-a new reporting user name>

  13. Import all the data to instance-a using the following command:

    impdp sys/<password>@< SID of instance-a> PARFILE=<pnet_imp_users.par with full path>

  14. After the data is imported to instance-a, use the pw dbconfig command to change the database configuration of tsim-2 to point to the new user on instance-a as follows:

     

    pw dbconfig set -database oracle -host <host name of oracle server:1521> -sid < SID of instance-a> -username <new main user name of instance-a> 

    -password <new main password> -repusername <new report user name of instance-a> -reppassword <new report user password>

  15. Start tsim-2.

    The tsim-2 system now uses the same Oracle instance (instance-a) that is used by tsim-1.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*