Consolidating two Infrastructure Management Server installations that use two separate Oracle databases into a single Oracle database
- Apply Infrastructure Management <latest_version> on tsim-1.
- Apply Infrastructure Management <latest_version> on tsim-2.
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 a different tablespace, user name compared to existing tablespace, and user present on instance-a.
InstallationDirectory\Utilities\oracle\custom_table_dump_pnet\
create_oracle_ts_user.sql
- Stop tsim-2.
- Set the SID of instance-b by performing the following steps:
- Set ORACLE_SID=<SID of instance-b>
- Type sqlplus
- Enter user-name: sys/<password>@< SID of instance-b> as sysdba
- 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 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
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
- Connect to the instance-a database as follows
- Set ORACLE_SID=<SID of instance-a>
- Type sqlplus
- Enter user-name: sys/<password>@< SID of instance-a> as sysdba
Run the following SQL script and specify the location of the directory and user after prompting:
InstallationDirectory\Utilities\oracle\custom_dump_dir_pnet.sql
- Copy EXP_ALL_TABLE_DATA.DMP (created in step 7) to the dump directory created in step 10.
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>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>
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>Start tsim-2.
The tsim-2 system now uses the same Oracle instance (instance-a) that is used by tsim-1.