I was working on a case, where Oracle MA was required to be created to sync Oracle DB's identity data with FIM, so that it can be synced with other identity stores. Following link provides most of the information needed http://social.technet.microsoft.com/wiki/contents/articles/18548.fim-2010-r2-how-to-make-a-connection-to-oracle-database-11g.aspx to connect to Oracle DB store. But there were few things, which I wanted to make sure everybody knows and its easy for them to configure this easily .
In my scenario admin was getting following error, when they had configured settings as shown below.
Data Source : SID (provided by oracle DBA)
Table/view : Name of table view(provided by DBA)
Database auth, with credentials of the user to make connection ( Provided by DBA)
so with Above information , they were getting " Failed to retrieve schema"
To make sure we are able to connect to Oracle server, I took netmon while trying to connect and connection was fine but still we were not able to get the schema. Oracle DBA tested with the command prompt with sqlplus and test work fine.
We then configured it as below to get it to work.
Datasource : SERVERIP:PORTNO(of the oracle server)/(SID)-provided by oracle DBA, rest is straight forward and same as they were using before.
There is a third party link that explains this connection string http://stackoverflow.com/questions/2134617/what-is-a-tnslistener-in-the-context-of-oracle, I found it useful so thought of sharing it as well.
Once we got this working rest of MA configuration is as usual.