MIM 2016: Use Generic SQL Connector to connect to Oracle 12c R2 Server

Okay we know that the OOB MIM Oracle connector (with v11 or V12 client) will not connect to Oracle 12c R2 DB. This is not just because it is not a supported connection but it just will not work. I have tried it, Oracle 12c R2 DB, using Oracle 12 client, then using Oracle 11 client. Now both will work from the MIM server if you use the SQLplus client that comes with Oracle to do an independent connection test but when you use the MIM Oracle connector, it just will not work and I know my Oracle quite a bit. I figure there must be something that changed in Oracle 12c R2 and the way it handles an api call that the MIM client is calling. If your DB is V12 R1, you can use the V11 client and that will work.

So, what is our next option? The MIM Generic SQL connector using the Oracle 12 64-bit ODBC drivers. The MIM Generic SQL Connector is supported with ALL 64-bit ODBC drivers. I think what is key here is you have got to know your tnsnames.ora config very well. The Oracle admin should be able to help with that.

Install Oracle ODBC client on the MIM Server

One thing about Oracle is that there are many files and many downloads and many options. Here is the client I downloaded.

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

Chose

Extract the bits from the zip file to a folder called say ORCLodbc and click Install

Click Next to English

Choose Create a New Windows User (Should be running with an account that can do this). You can use a domain account (recommended) or a local Server account. You can also pre-create the account (recommended) and specify use existing windows user.

Click next to the path

Select all the components

Check the box and click Next

Perform verification checks may take a while, its memory/resource intensive, I would say that you do this in off hrs because it can affect your MIM server

Click Install

Take a screen shot of these details at the end, they will be useful later

Set the TNSNames.org file

This is very important. See my blog post on setting this up. Here is what my TNSNames file looked like. Not sure if the one for 12 is different from 11. The Oracle Admin should be able to configure this

Test the ODBC connection

On the MIM Server, if you are on Windows 2012, search for “data sources”, click “Set up ODBC data sources (64-bit)”

Click “System DSN”, enter what corresponds to your tnsnames file

Click Test Connection, the Oracl id and password that will be used for the MIM connection

You should see this

Generate DSN file

Now you need to generate a DSN file for your connector. Click the File DSN tab, click add, select oracle client

Give a name to the file, click next

Click finish

You will be prompted to enter login info. Enter the same as when you tested the connection

Create MIM Connector

We are going to do an example of connection to an HR View on the Oracle DB with employee info all in a View

Select Create MA, Select Generic SQL MA

On the Connectivity page, select the DSN file you created, select DN as anchor

On Schema one enter User as Object type, click next

Enter the View table name, click next

Enter the DN, we will use employeeid as anchor

Enter the attribute properties

Delta strategy click None (not sure it works well with Orcl but you can always do a delta like with the OOB Orcl connector) but you must enter the date format

Click next on partitions

Click next on objecttype

Select all the attributes you want

Click next to Anchor

Configure any Connector filter you want

Configure any join, projection rule you want

Configure attribute flow

Configure deprovisioning

Configure Extensions and finish

Configure Run profile

Run Full Import

One thought on “MIM 2016: Use Generic SQL Connector to connect to Oracle 12c R2 Server

  1. Pingback: MIM 2016: Understanding Oracle DB and the MIM Oracle Connector | tlktechidentitythoughts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s