How to move an OIM database between two OIM instances

On the source OIM instance:

1.Set the environment:

export ORACLE_SID=<dbsid>

export ORACLE_HOME=<oracle_home_path>

export PATH=$ORACLE_HOME/bin:$PATH

2. exp system/<system_password> file=<file_name>.dmp owner=<SOURCE_OIM_DB_USER> log=exp_<file_name>.log consistent=y statistics=none

3.Get the tablespaces used by the OIM database account:

 a) select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='<SOURCE_OIM_DB_USER>';

 b) select distinct tablespace_name from dba_segments where owner='<SOURCE_OIM_DB_USER>';

On the destination OIM instance:

4.Set the environment:

export ORACLE_SID=<dbsid>

export ORACLE_HOME=<oracle_home_path>

export PATH=$ORACLE_HOME/bin:$PATH

5.Take a backup of the destination database schema:

exp system/<system_password> file=<file_name>_backup.dmp owner=<OIM_DB_USER> log=exp_<file_name>.log statistics=none

6.Take a backup of the following files: $OIM_HOME/config/.xldatabasekey and $OIM_HOME/config/.xlkeystore

7. Drop the current OIM database user:

sqlplus system/password

sqlplus>drop user <OIM_DB_USER> cascade;

The tablespace associated with the OIM database user and the related datafiles could also be dropped if there are no objects inside.

8.Create the OIM database user (it should be the same name as that dropped at the step 5, but the tablespaces are those obtained in step 3 a)):

prepare_xl_db.bat <db_connect_string> <$ORACLE_HOME> <OIM_DB_USER> <password> <tablespace_name> <db datafile location> <datafile_name> <temporary tablespace> <system password>

If there are additional tablespaces, they need to be created at this step - see the output of step 3 b)

9.Import the dmp file into the destination OIM database:

imp system/oracle file=<file_name>.dmp fromuser=<SOURCE_OIM_DB_USER> touser=<OIM_DB_USER> log=imp_<file_name>.log

Note: Before importing the dmp file, please ensure that the tablespace(s) related with the OIM database user are correctly dimensioned as the all the datas can be loaded successfully. This can be done by calculating the occupied space in the source database.

10.Login to the destination OIM database as OIM_DB_USER and execute:

   SQLPLUS>update SDK set SDK_SCHEMA='<OIM_DB_USER>';

   SQLPLUS>commit;

11.Copy from source to destination the following files: $OIM_HOME/config/.xldatabasekey and $OIM_HOME/config/.xlkeystore

12(optional). Login to OIM destination database and execute:

 update pty set pty_value = '<JAVA_HOME>/bin' where pty_keyword = 'XL.CompilerPath';

commit;

where JAVA_HOME is the JDK home used by OIM.

Note: During the execution of exp and imp tools OIM server has to be down.