I needed to create a connection to our HR Oracle database yesterday, so I installed the 11G client with Administrator profile on my 2008 R2 box. The database team gave me a tsnames.ora file to put in the Admin folder, so I did. I then gave the Sync Service account full control over the Oracle root folder and rebooted the server. I then created a new Oracle MA at which time the machine just froze on me after I filled out the Connect to Database screen in the wizard and hit next. I ended up leaving it running and returned this morning to find that it had successfully made a connection. I finished the wizard, created the run profiles and then ran the Stage Only run profile. It's been 2 hours now, and nothing is reporting back in the console. No errors, no projects, and the MA has not errored out. I'm lost. I have two environments, so just for kicks I went to the second ILM server, installed the Oracle client as before and now again I'm stuck on the Connect to Database screen in the create wizard. It's been 20 minutes and the MA still has not connected to the database. I have successfully ran a SQL command from the dos prompt and made a connection to the database extremely fast. Is there anything I'm overlooking in regards to setting up this MA? Any tips on making it run fast like it should?

Ryan, FWIW, Oracle 11G is not supported as far as I know (see the ILM FAQ). I also know that performance is very sensitive with respect to the client version (it uses the Oracle OLEDB provider that comes with your client instead of Microsoft's). Have you tried using a previous version of the client? Have you tried your Oracle tools to see what is going on? Also, are you using FIM or ILM? As far as I know, ILM doesn't run on 2008 R2. Paul. Paul Loonen (Avanade) | MCM: Directory 2008 | MVP: ILM

I'm using just the Sync service of FIM and not the Portal. I kept getting errors when trying to install the Oracle 10g client saying that it wouldn't install on the 2008 R2 box. So I went with 11g client because it would install. My DBA came over and tested the connection to the Oracle database from the server using his command lines and said everything looked good on his end.

Did you use the 64bit version of the client?Paul Loonen (Avanade) | MCM: Directory 2008 | MVP: ILM

Yes. I just uninstalled the 11g client and found a way to bypass the OS error when trying to install the 10g client. I'm currently installing the client now and hope I can get this to work.

No luck. Same results using the 10g client. The "Connect to Database" just sits there and freezes up. It's been running for 15 minutes now trying to connect.

Do you have UAC enabled/disabled? So, you are able to query the database and table/view using the Oracle client itself, correct? Could you trace what the server actually is doing? - Is the process on the FIM server hanging (hang on the FIM side, bug in MA or client)? -> Resource monitor Do you see any traffic between FIM server and DB server - while connecting the MA? - while running the import? And also, is the MA querying the DB server and waiting or sending a faulty query? -> network sniffing, monitor query on DB client or SQL server side? Kind regards, PeterPeter Geelen (Traxion) - Sr. Consultant IDA (http://www.fim2010.be) [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or "Helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster.]

I have run into a similar issue in the past and was able to resolve it by adding 'Self Tuning=false' to the connection string. My issue was that the connection for a password sync wouldn't drop though. We used 11g and I don't recall any issue with the MA setup. But maybe it will help!Keith

BTW, here is the article I found about my issue: http://stackoverflow.com/questions/2782169/oracle-data-provider-pegs-iis-worker-process-when-web-site-is-stopped Is your processor pegged when this is going on?Keith

Fresh from the oven - so, still warm - 11G is supported. Cheers, MarkusMarkus Vilcinskas, Knowledge Engineer, Microsoft Corporation

No, the CPU sits around 30% during the process. Last night I reverted the Oracle client to 10g (10.2.0.4) and ran a full import. The CS finished the import of 176,242 objects in 4 hours. Now I'm starting the Sync which I suspect will take a few hours to complete as well. The Sync Service machine is a VM with 2 processors, 8 Gbr running on 2008 R2, and the SQL server is a VM as well on 2008 R2, SQL 2005 with 2 procs and 12 Gbr. My imports and synchs on the AD environment take around 1-1.5 hours to complete the full process. There is roughly 150,000 objects there, so 4 hours to do just the import of the Oracle database seems excessive. I'll see if I can find that "Self Tuning" string and modify it.

Out of interest, what exactly is your HR Oracle database? The reason I ask is that there is a UNIFY technology (Identity Broker) which has been used to write a number of different proprietary HR connectors which support FIM (via an ECMA) designed to load volumes the size you are talking about (600K students for example) where the database back-end is Oracle. We have a number of HR application connectors for FIM in the pipeline, and I would be interested to find out if yours is one of them, or if it's "in house". If not then a plug-in for yours could potentially be commissioned if the native ORA MA proves unable to meet your requirements. Generally speaking you especially get performance degradation when you use multi-value attributes in your database MA. Bob Bradley, www.unifysolutions.net (FIMBob?)

I have run into a similar issue in the past and was able to resolve it by adding 'Self Tuning=false' to the connection string. My issue was that the connection for a password sync wouldn't drop though. We used 11g and I don't recall any issue with the MA setup. But maybe it will help! Keith I'm not an Oracle DBA, so how and where do I add the "Self Tuning = False" string? My dba is out of the office for a week, so any help now would be appreciated.

I have run into a similar issue in the past and was able to resolve it by adding 'Self Tuning=false' to the connection string. My issue was that the connection for a password sync wouldn't drop though. We used 11g and I don't recall any issue with the MA setup. But maybe it will help! Keith I'm not an Oracle DBA, so how and where do I add the "Self Tuning = False" string? My dba is out of the office for a week, so any help now would be appreciated. I assume it's in your tnsnames.ora file on the FIM server.My Book - Active Directory, 4th Edition My Blog - www.briandesmond.com

Here is segment of my tnsnames.ora file that list the instance name I'm connecting to. How do I add the Self Tuning string to this syntax? HU1P = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = sun01d0) (PORT = 1511) ) ) (CONNECT_DATA = (SID = HU1P) ) )

Still no luck. It's taking the Oracle MA nearly 3 hours to make a connection to the database before it starts to import and sync. If I successfully run SQLPlus on the box with no issues and can query the database within seconds. Is there anything in FIM that I can adjust to make the connection faster?

i have the same issue on FIM environment, the management agent of the oracle is very very slow to retrieve schema or after i pass 3h to retrive and complete teh management agent setup it cannot makes any imports to teh CS i search on how to change the self tuning option and i found this, it modifies a key in the windows registery, but it is not makes any difference. i hope to reach to an answer in this bug

I just tried the samething with no luck. I have an open ticket with Microsoft on this case now. Hopefully they can help with this. The issue is that the MA is taking 3 hours to connect to the database. After it connects, it moves quickly. I've performed a TSNping and connected to the Oracle database through SQLPlus with no issues.

Has anyone seen a solution to this? I am having the same problem with horribly slow Oracle MA performance with FIM. We are switching from MIIS 2003 SP2 to FIM 2010 With MIIS SP2 on a 6-year-old 32-bit machine, a full import of 32,000 records from an Oracle MA with a multivalue table takes 7 minutes. With FIM on the 64-bit machine, 11g client, two-quad-core cpu's, 8G-RAM. Same source tables. Full import is taking 1-hour 10 minutes. I also tried the self tuning setting (HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\2.112.1.0\SelfTuning=0) and it didn't have any effect) Thanks, Al

My Oracle DBA was able to see the query coming from the MA once it started to hit the database. He said the query wasn't calling anything, so it just sat there for hours. Unfortunately the DBA was not around to look at the query once information did start to populate, so hopefully he can today. Here is the query that he saw coming from the MA. SELECT * FROM (SELECT NULL TABLE_CATALOG, DECODE (owner, 'PUBLIC', NULL, owner) TABLE_SCHEMA, object_name TABLE_NAME, DECODE ( owner, 'SYS', DECODE (object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'SYSTEM', DECODE (object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'DMSYS', DECODE (object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'ORDSYS', DECODE (object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'EXFSYS', DECODE (object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'WMSYS', DECODE (object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'MDSYS', DECODE (object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'CTXSYS', DECODE (object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'OLAPSYS', DECODE (object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), 'WKSYS', DECODE (object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', object_type), object_type ) TABLE_TYPE, NULL TABLE_GUID, NULL DESCRIPTION, NULL TABLE_PROPID, created DATE_CREATED, last_ddl_time DATE_MODIFIED FROM all_objects WHERE object_type IN ('TABLE', 'VIEW') UNION SELECT NULL table_catalog, DECODE (o2.owner, 'PUBLIC', NULL, o2.owner) table_schema, o2.object_name table_name, o2.object_type table_type, NULL table_guid, NULL description, NULL table_propid, o2.created DATE_CREATED, o2.last_ddl_time DATE_MODIFIED FROM all_objects o2, all_objects o3, all_synonyms s WHERE o2.object_type = 'SYNONYM' AND (o3.object_type = 'TABLE' OR o3.object_type = 'VIEW') AND o2.owner = s.owner AND o2.object_name = s.synonym_name AND s.table_owner = o3.owner AND s.table_name = o3.object_name) DBSCHEMA_TABLES ORDER BY 4 DESC, 3 ASC, 2 ASC /Ryan

Thanks, Ryan. In my case the Oracle MA seems to start normally, but just creeps along very slowly, in the area of 450 records/min. On the MIIS machine it runs almost 10 times as fast. There is very little cpu activity and not a lot of disk activity going on. Does anyone know whether the new Oracle MA is using ODP.NET or Oracle OLEDB? Maybe it is a problem in the configuration of the Oracle library settings? Using sqlplus from the command line on the machine retrieves the same tables in seconds Thanks, Al

We are experiencing the same issue, slow to create an Oracle management agent, slow to refresh schema on that agent and slow import and export. We are importing about 10,000 objects and there is no activity for about an hour then suddenly all of the objects are imported in about 5 minutes. After opening a case the support rep suspected it was an issue with the Oracle client software. The problem occurs with both the Oracle 10 and 11 clients but does not occur with the Oracle 9 client, which is very difficult to find since it is end of life and not supported on 2008 R2 obviously. In my testing the problem occurs with MIIS 2003 SP2, ILM 2007 FP1 SP1 and FIM 2010. To encounter the problem you must be using the Oracle client 10 or higher and the instance you are retrieving data from must have a large number of tables and views, not to be confused with the number of objects you are importing from your target table/view. In our case several thousand views based on several hundred tables and over 20,000 synonyms. After reverting the client to version 9 on a server 2003 box running MIIS the initial creation of the management agent which previously took 1 hour now takes about 10 seconds. This seems to confirm the suggestion by support that it is an Oracle client issue. Those who haven't yet upgraded to FIM and therefore are using an older Oracle client with ILM or whose instance has a small number of tables and views, again not to be confused with the number of objects within the target table/view, would never see this problem.

Hi There, You can get the Oracle 10g Client to install on Windows Server 2008 R2, you can use this one. http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html Thanks B

Hi There, You can get the Oracle 10g Client to install on Windows Server 2008 R2, you can use this one. http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html Thanks B

Hi There, You can get the Oracle 10g Client to install on Windows Server 2008 R2, you can use this one. http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html Thanks B

Hi There, You can get the Oracle 10g Client to install on Windows Server 2008 R2, you can use this one. http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html Thanks B Hey, didn't know about that one. I had this issue when doing an MIIS to FIM upgrade... 10g wouldn't install on R2 and 11g at the time wasn't supported by FIM (though I'm guessing according to Markus' comment it is.. link please Markus?)... kinda screwed me. The resolution was to open the oraparam.ini file included with the installer and add 6.1 to the supported versions: Windows=5.0,5.1,5.2,6.0,6.1 Maybe not supported, probably not recommended, but definitely works for installing 10g and getting it to work with FIM @RRLowrie: the only thing I can really recommend is to check your Oracle client configuration in regards to the server alias... we've seen some clients who have multiple servers listed but only open a firewall to a single server. When the connectivity driver round-robins it times out on the firewalled server. Overall, however, it sounds like yours might just be a performance issue of FIM itself.

Hi There, You can get the Oracle 10g Client to install on Windows Server 2008 R2, you can use this one. http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html Thanks B Hey, didn't know about that one. I had this issue when doing an MIIS to FIM upgrade... 10g wouldn't install on R2 and 11g at the time wasn't supported by FIM (though I'm guessing according to Markus' comment it is.. link please Markus?)... kinda screwed me. The resolution was to open the oraparam.ini file included with the installer and add 6.1 to the supported versions: Windows=5.0,5.1,5.2,6.0,6.1 Maybe not supported, probably not recommended, but definitely works for installing 10g and getting it to work with FIM @RRLowrie: the only thing I can really recommend is to check your Oracle client configuration in regards to the server alias... we've seen some clients who have multiple servers listed but only open a firewall to a single server. When the connectivity driver round-robins it times out on the firewalled server. Overall, however, it sounds like yours might just be a performance issue of FIM itself.

To resolve the issue our DBA's linked a SQL database with the Oracle database which allowed me to collect the data using the SQL MA. What took me hours before to complete now takes a few minutes. I did a web search on the query above and found many peolpe having issues with the Oracle client drivers. I did not find a solution or setting that fixed it.Ryan

One of our DBA's found another way around the issue which works better than the SQL link I mentioned in the above reply. He created another table in the Oracle database called "All_Objects" and put in the column names, but no data. This tricks the Oracle Client - SQL query mentioned above by running against an empty table which ends very quickly, and starts to process the data right away. I hope this information helps others who are or will have the same problem.Ryan