OIM: IT Resources in the database

While setting up a staging environment at a client’s site, I needed to mirror the Production OIM instance to the Staging environment. The steps involved are outside the scope of this blog, outlined in detail in Oracle’s Metalink article How To Export and Import an OIM Instance? [ID 555655.1]

There is one very important caveat to note when doing this. If you take a snapshot of the production database and insert it into your development environment, when you bring that OIM instance up, it will contain all production data; including IT Resource connections. If there were any scheduled tasks in your production instance that would’ve ran during your transition, they may run as soon as you bring your development OIM instance up. Against your production resources!!

The way I found to avoid this is to blank the server name/IP values out directly in the database prior to launching your freshly imported development environment. For this, you need to know where IT Resources are stored in the database.

While there are more tables to hold other metadata, the following 3 have the key information:
  • SVR – Contains all the different resource names (ie.  AD Server, iPlanet User, Exchange Server, etc…)
  • SPD – Contains a list of all the fields (ie. Server Name, SSL, Port, Root DN, etc…)
  • SPV – Contains values for all fields – This list is encrypted, but since all we want to do is blank them, it’s ok.
The following SQL code will show you a list of the Resources, Fields, and Values
select svr.svr_name, spd.spd_field_name, svp.svp_key,svp_field_value
from svp
inner join spd on spd.spd_key = svp.spd_key
inner join svr on svr.svr_key = svp.svr_key;
Browse through the results and look for the field names that suggest a server name or ip address. This will depend on each resource, but common names are “Server Address” or “Server Name”. For each resource you want to blank out, note the corresponding key (column svp.svp_key).
For each field, run the following UPDATE statement substituting the svp_key value for X,Y,Z
UPDATE SVP SET svp_field_value = '' WHERE svp_key in (X,Y,Z)

More Here