ODP.NET working on local computer but not on server. Error: TNS:could not resolve the connect identifier specified

I don't know for sure, but it sounds similar to problems I had which were fixed by setting the ORACLE_HOME environment variable to point to the original Oracle Home.

in your case, i think it would be
SET ORACLE_HOME=D:\oracle\produc\10.2.0\db_1

I don't think i can do that though because there are websites already running under the current oracle home?

I presume your websites were working before the ODP.Net installl?  

I had a similar situation.
We had an existing oracle client installation with various applications using it.
I installed ODP.Net.   The original applications continued to work but I could not connect from my .Net application via ODP.Net.    However,  If (from .Net) I ran up a COM component from one of the original applications , THEN tried the ODP.Net connection it worked.

I think that loading the COM session caused the orginal Oracle dlls to be loaded and the orginal home to be used.   ODP.NET then used those values and dlls (rather than the files in the odp.net install tree)  and connected successfully.

Setting ORACLE_HOME to the original value (ie. before the ODP.Net instaltion) allowed everything to work.  

actually, it's just occurred to me that the websites are currently running on MS Access anyway and none of those are currently accessing any oracle database on that server so i can probably mess around a bit.

So, i'm not sure though if the oracle home has changed or not... it's currently D:\OracleClient\Install which i think is probably what it was before... so what do you suggest i do... the way you got it to work before, is that a possibility on my side? or the ultimate answer is just changing the oracle home? I guess maybe i could just try and change it to D:\oracle\product\10.2.0\client_1 then as that's where the ODP.NET was installed to... although the databases i'm using are in the db_1 folder...

If you have multiple ORACLE_HOMES you either need a tnsnames.ora (and sqlnet.ora) file for each
or
create one common one and set  a TNS_ADMIN variable to the folder containing the common file.  This can either be an OS Environment variable or a registry variable for the individual ORACLE_HOMES.

the environmental variables have changed after the install with the ODP.NET one added to the start. This doesn't seem to be on my local machine. could this be causing an issue?

presumably it was D:\oracle\product\10.2.0\db_1\bin;D:\OracleClient\INSTALL\bin;

and now it's

D:\oracle\product\10.2.0\client_1\bin;D:\oracle\product\10.2.0\db_1\bin;D:\OracleClient\INSTALL\bin;

Should I try and remove the D:\oracle\product\10.2.0\client_1\bin and do a restart, or is this unlikely to solve anything?

I tried messing around with the PATH - the only thing that worked for me was setting the ORACLE_HOME environment to the original environment.    Effectively this allows ODP.NET to sit on top of your previous oracle client.

I would encourage you to try it before you do restarts etc.   You can always revert if it doesn't work.

I agree about the PATH statement.  In this instance I don't think it matters.

ORACLE_HOME is typically the solution but to solve an ORA-12154, you need to tell the Oracle products where a properly configured tnsnames.ora file is.

You either need to copy the old one into the new ORACLE_HOME/network/admin folder or use TNS_ADMIN to point to the correct one.

but I think the oracle home hasn't changed (although I'm not sure because i'm not sure what it was to begin with). But to put it this way, the Oracle home is currently D:\OracleClient\Install (as per registry) , it's definitely not in the newly created ODP folder D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

Also, I forgot to mention this is a 64 bit machine whereas mine is a 32 bit machine... that's another possible issue...

but actually, sorry but these two questions are probably related, although i'll happily give points for both (https://www.experts-exchange.com/questions/26934227/ODP-NET-OracleDataReader-returns-no-rows-on-server-data-is-returned-fine-when-running-locally-in-Visual-Studio.html?anchorAnswerId=35323737#a35323737). At some point I stopped getting the above error and now get no error at all as per that question.

I have a bad feeling about this! Why would it apparently connect and when I run any query, there is no error produced?

(and of course, the query is supposed to do something but does nothing, even a select query produces an empty table, but still no error, and update query does nothing with no error)

Just to clarify and then I'll stop bothering you!

When you say 'the oracle home hasn't changed' it suggests that the ORACLE_HOME has never been  explicitly set.   This is the situation I experienced.  In the absence of this variable, your original client was using the TNS names file within its installation tree.

When you installed ODP.NET,  it installed a new client/home with a new location for the TNS names.   Without a ORACLE_HOME vairable it defaults to looking for the TNS name within its own installlation tree.

If you specify ORACLE_HOME to use the original location,  your older client will continue to be used.  
The difference will be that ODP.NET will now look in the original home for the TNS file and the oracle client dlls and you may find that all will be will.

I appreciate you have not changed the value of ORACLE_HOME.    I suspect it has never been specified in the past but now it needs to be because of the existence of more than one home.

Also, I found that no matter what I did in the registry, it was the ORACLE_HOME env variable that mattered.    I thought this was pretty old fashioned but couldn't get around it.

>>but I think the oracle home hasn't changed

Technically it did.  As soon as you installed another Oracle Product on the machine, you actually created another ORACLE_HOME.  The old one didn't change but you affected the environment.

>>sorry but these two questions are probably related, although

Related but I believe still different.  Otherwise I would have to delete one of them...

ok so the Oracle home as specified in the registry is NAME: ORACLE_HOME with Data: D:\OracleClient\Install. Are we saying that this is not necessarily the real oracle home? or I guess what you're saying is it is the default. There is another key called NAME: ORACLE_HOME_NAME with Data: DEFAULT_HOME... is this confirming that?

Ok, if this is the case, what exactly do i need to do? I need to explicitly set the Oracle home to the original oracle home, it sounds like from what you say. So what I'm guessing is that I need to set it to the first folder db_1 according to SET ORACLE_HOME=D:\oracle\produc\10.2.0\db_1

I tried to run that in command prompt on the server and nothing happened. Where do I run that from and how exactly? I don't see a change oracle homes facility on the server

>>you're saying is it is the default

Yes.  You have more than one home.

>>I need to explicitly set the Oracle home to the original oracle home, it sounds like from what you say

That is what Paul_Harris_Fusion is saying.  That is not what I'm saying.

In this instance you have two Experts with differing opinions.  Pick which approach you want to test so we do not cross post and confuse you more than you already are.

I agree with slightwy - test an approach and see what works for you.

I think the summaries are (correct me if I am wrong) :
paul_harris_fusion:  use only one of the oracle homes even when accessing through ODP.NET
slightwy:  share a TNS Names file between two oracle homes and contain using both oracle homes.  

>>slightwy:  share a TNS Names file between two oracle homes and contain using both oracle homes.  

Either: share a single tnsnames.ora using TNS_ADMIN
or: copy a known good tnsnames.ora file to ALL oracle homes (in the network/admin folders).

i already had the same tns file copied to both oracle homes:

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN  and
D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

and I can connect through Toad (don't know if you guys know that, it seems to be something similar to sqlplus). So, the tns files must be ok then right?

As for the Oracle home thing, I don't mean to be stupid... but I'm just not sure how to change the oracle home... you say you have to do 'SET ORACLE_HOME=D:\oracle\produc\10.2.0\db_1' but I tried running that in command prompt and didn't work not surprisingly as I've no idea what I'm supposed to do with that...

>>i already had the same tns file copied to both oracle homes:

And you still get an ORA-12154?  If so, then it looks like my option is out (for now).

I've never set ORACLE_HOME in the registry so I'll leave Paul to answer that one but I believe it is the DEFAULT_HOME you mention.

Until he responds, try setting it in the Windows Environment Variables from the computer properties.  Once set, resetiis.

You can connect through Toad because it is still using your original Oracle Home.

I can't remember if I set ORACLE_HOME using a command prompt or using the environment variables accessed by right-clicking My Computer > selecti Properties > select the Advanced tab > sekect Environment variables.

I do remember that nothing I did in the registry woirked.

I notice that I put a typo in the command last time (I left the t off product):

Try in a command window:
First check the current value as follows:
> SET ORACLE_HOME
Then change it
> SET ORACLE_HOME=D:\oracle\product\10.2.0\db_1

Here are a couple of links that seem related:
1.  http://stackoverflow.com/questions/491876/why-doesnt-odp-net-11-xcopy-deployment-work-on-a-machine-with-oracle-db-10-insta
2.  http://www.brothersincode.com/post/Oracle-ODPnet-xcopy-deployment-for-aspnet.aspx
3. http://www.gbogea.com/2009/08/17/ocienvnlscreate-failed-with-return-code-1

The second one suggest setting the ORACLE_HOME from within your .Net app iso that ODP.Net goes to the right home.    You can do this on a session  basis I believe so don't have to mess with the whole system.

I have included the last one because the use of ProcessMonitor to see what dlls were being loaded was exactly how I figured out my problem.

I'm thinking you might be right about this Paul. I was looking at TOAD on the server and it connects through an Oracle home called OarDb10g_home1 which it says in in the path D:\oracle\product\10.2.0\db_1

But, when I look in the registry, I see that the ORACLE_HOME key has a value of D:\OracleClient\INSTALL. So I'm guessing your right in that I'm connecting through TOAD through one oracle home (which is working) but that when the website is published, it tries to connect through the default home which isn't working. In fact, TOAD shows that Oracle Home and states that it is NOT VALID.

So, I tried using OUI to change the default home by going into environmental variables and setting the correct Oracle Home as the TOP choice. I think all this does though is change the order of the paths in the environmental variables of the server. When I look at the registry, the default Oracle Home key still hasn't changed. And more to the point, the website running in IIS (after reset) is still not working. I'll do some more investigation on changing the Oracle home... but if you can show me how to do that, it sounds like it might indeed fix the problem...

>>> So, I tried using OUI to change the default home by going into environmental variables and setting the correct Oracle Home as the TOP choice
I don't understand this....there is only one possible value for ORACLE_HOME so I don't understand the TOP choice.    If you have simply adjusted the order of the paths in the PATH variable then this did not work for me.
To change ORACLE_HOME, use a command prompt on the server as follows:
First check and note down the current value as follows:
> SET ORACLE_HOME    
Then change it
> SET ORACLE_HOME=D:\oracle\product\10.2.0\db_1     (or whatever your original path is)

If you want, you can verify the dlls that are loaded using the ProcessMonitor that you can download from Microsoft as part of the sysinternals kit.  The process is described in the link 3 of my previous post.  If you see some programs loading dlls from one oracle home and other programs using another, then that should shed some light on what is happening.

Or you can just try changing the ORACLE home and testing your connectivity.

well you won't believe what i just found out. I don't know how long this was happening for because i was just using firefox the whole time, but I just happened to try the site in IE... and guess what... it works???

So it's working in IE but not in firefox... but doesn't work in IE when i run it directly on the server (i.e. use IE on the server)

Am I going crazy or is something very odd going on?

actually, i can confirm the same thing when debugging. When i debug using IE it works. When I debug using firefox... there is simply no data returned, no error, nothing. The query runs on the database, but nothing is returned even though the query is exactly the same??

ok disappointingly, the latest is that it is only working on my computer in IE. If i try other peoples computers and use IE (or firefox) then it's still not working...

I still don't know if you have tried setting the ORACLE_HOME env variable.   I would set this AND the registry value to the correct (working) oracle home.

Can you confirm that this is the case?

i ran the SET ORACLE_HOME=D:\oracle\product\10.2.0\db_1 and it seemed to work ok. After that I ran SET ORACLE_HOME and the result was D:\oracle\product\10.2.0\db_1.

However, if I try it again now... and run SET ORACLE_HOME the result is 'Environment variable ORACLE_HOME not defined'. So I don't know what's going on here.

As for changing the registry values. I should do this by hand then? Is that a bit risky? And there are quite a lot of keys which say D:\OracleClient\INSTALL. Do I change everything in the Oracle section to D:\oracle\product\10.2.0\db_1 ?

and does that require an iisreset after changing the registry or a reboot of the server? (and same question on the SET ORACLE_HOME command in the command prompt)

>>and same question on the SET ORACLE_HOME command in the command prompt

This only sets that variable in that specific command window.  Not for the entire machine.

You should set it for all environments using the 'Right click on My Computer' step in http:#a35325556

Leave the registry stuff for the moment and just try the env variable.

Doing it at the command prompt only persists it for the lifetime of the command prompt window.   Sorry to send you down that route.

Do it in   My Computer > right-click > Properties > Advanced > Environment Variables

Look in System Variables and see if ORACLE_HOME is already listed.
If it already exists Edit it,  else create a new variable

ok, the ORACLE_HOME was not set. I set it to D:\oracle\product\10.2.0\db_1. And indeed if I run a new command prompt and do SET ORACLE_HOME it is set.

I tried connecting to the server using Firefox (since IE mysteriously works on my machine but no one elses), and it didn't work. Does that above change require an iisreset or a reboot? (if it does I may have to schedule that and do it later)

Sorry - I don't know if this requires a restart

An iisreset wouldn't hurt.

If your app is running in it's own app pool you should just be able to stop/start that pool.

I have no idea what might be causing the IE/FF issue.  When you say 'no rows' is the main object being rendered?  What .Net object are you using to display the rows?

Check the page source to see if FF is actually getting the results just not rendering properly.

well it's weird but the issue is just local to my computer, so, well... i've got no idea what's going on there. Obviously there's lots of things that are gonig to be different between my computer and everyone else's. Mine has oracle client installed, tns files, visual studio, odp.net etc etc etc where as other people don't. So when they connect to the server, neither FF or IE work. When I connect, IE works and firefox doesn't.

Ok, I can't shedule an outage now, I'll have to do a restart tomorrow, well I have to do one then anyway so I'll see what happens after that and get back to you if the is remains...

I can't see why your client setup should affect the connectivity of the web app unless you are running a click-once app from a url.....

well you got me. it doesn't make any sense...

i'll get back to you tomorrow after restart to see if that fixes anything

Hi, ok i did the restart, and I guess something changed, although maybe not for the better.

Now the error I get when trying to connect from IE is:

ORA-12170: TNS:Connect timeout occurred

I can't connect through TOAD anymore either. On my pc the error is:

ORA-12535: TNS: Operation timed out.

And can't connect through TOAD on the server. So, all connection seems to be lost now.

So I guess you should revert the changes and restart IIS.

However,  I still fail to see how making this change on the server could have any effect on connecting from your pc.

Sorry it didn't work for you.

>>ORA-12535: TNS: Operation timed out

99.9 percent of the time, this is a firewall issue.

Once the listener verifies the connection request, it opens a random high port to hand off the connection.  If the firewall blocks that random port, you get a timeout.

Check with the network/system staff to see what changed.  Also verify the local Windows firewall isn't blocking you.

well whatever changed must have been through something I did with those changes yesterday right? It's hard to know what else to do here.

Maybe you can just recommend some reading for me on understanding oracle and getting it working, connecting etc etc and i'll give the points for that (well, I'll give points to you on this topic paul and slightwv on the other post because you've offered a lot of help at least even if the solution is not obvious, i've probably learnt one or two things at least).

What I think i'll have to do here is build the application on MS Access backend (because I'm familiar with that) and then continue to try and solve these oracle issues and then port the application over to oracle when (if?) i figure this out...

If you are willing to continue with this, I'm willing to stick it out!  Oracle can be very frustrating to deal with when starting out but, like most things in life, once you understand it, it isn't all that bad.  You just need to do certain things a certain way.

Just let me know if you want to back everything out and try again.

I'm not sure what documentation to start you with.

We've used Oracle and ASP (VBScript)/ASP.Net since 2000.

I've scanned the ODP.Net doc just now and have NEVER done any of the steps covered in the Configuration section.  I've never modified the machine.config and I've been using ODP.Net ever since if came out (Was using OO4O - Oracle Objects for OLE before that).

http://download.oracle.com/docs/cd/E11882_01/win.112/e18754/toc.htm

You should also read up on how Oracle networking works:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/dist_pro.htm#CNCPT1272

ok i'll take a look at that.

So, lets say i was to start again and try and remove everything oracle related from the server... might that be the next step here? Although, presumably I'd just have the same problems again, I'd install Oracle for 32 bit (32 bit running on a 64 bit server). Then, I'd install ODP... and I guess I'd just be back to where I started?

What would your next step be here do you think?

Pretty much the same but now you have "Experts"?

First priority would be to remove/undo everything and get the system stable and working.

I would forget all about the 32Bit Oracle Client.  Just download/install the ODAC:  http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html

Note: The version you download MUST BE THE EXACT SAME as the one used to compile the app.  I have yet to figure out how to mix and match even minor version differences.

You can verify the correct version by right-clicking on the Oracle.DataAccess.dll, selecting properties then looking at the version.  They need to match exactly.

Hi, had to wait before I had an opporunity to restart the server.

So, I tried to revert those changes... first opened the oui.exe and reset the primary Oracle home (the one that appears first in the environmental variables) to the top and did an iisreset. Didn't work (couldn't connect through TOAD). Restarted server. Didn't work. So, I tried to change the Oracle home in the environmental variables also. I couldn't remove it though (previously it wasn't set to anything) so I had to try and set it to D:\OracleClient\INSTALL; Tried iisreset, didn't work. Tried server restart, didn't work.

So, the error I get in TOAD now when i try to connect is: Can't initialize OCI. Error -1. The details in TOAD for that connection are shown below... and look ok I guess. I checked and the OCT.dll file exists, and exists in the path specified D:\oracle\product\10.2.0\db_1\Bin\oci.dll

So... I guess something is different still to the way it was before

SOFTWARE\WOW6432NODE\ORACLE\KEY_ORADB10G_HOME1 
  ORACLE_HOME_NAME:  OraDb10g_home1
  ORACLE_HOME:  D:\oracle\product\10.2.0\db_1
  ORACLE_SID:  orcl1
  NLS_LANG:  ENGLISH_IRELAND.WE8MSWIN1252
  SQLPATH:  D:\oracle\product\10.2.0\db_1\dbs
  LOCAL:  
  D:\oracle\product\10.2.0\db_1\Bin exists. 
  D:\oracle\product\10.2.0\db_1\Bin is in PATH. 
  Client DLL:  D:\oracle\product\10.2.0\db_1\Bin\oci.dll
  Client Version:  10.2.0.1.0
  Home is valid.

Select allOpen in new window

On top of that actually, I just tried to connect to Oracle using SQLPLUS and got the following errors:

message file sp1<lang>.msb not found
sp2-0750: You may need to set Oracle_Home to your Oracle Software Directory.

So, not sure what that means exactly but it's making me wonder if something has changed or is wrong in the registry. I am attaching a number of files showing registry keys that might be relevant in case that is useful for you to see


 
 
 
 
 

>>So, I tried to revert those changes... first opened the oui.exe and reset the primary Oracle home

You have tried so many things that we can spend days trying to undo everything manually.  I suggest you uninstall the ODAC, ensure everything else is still running and we start over.

Ok i think I uninstalled it there... there's so much going on and so many files i don't know what i'm doing to be honest. I went in to OUI again and uninstalled what looked to be the ODAC. That removed the KEY_ODACHome1 so i guess that sounds good.

Tried to connect through TOAD again and still get error: Can't initialize OCI. Error -1

Also get same error on SQLPLUS (don't even get to the point where i can enter username/password):
message file sp1<lang>.msb not found
sp2-0750: You may need to set Oracle_Home to your Oracle Software Directory.

Maybe i should just take everything Oracle related off and try and start again... although, I don't know how to set up the whole thing, or set up databases etc...

>>That removed the KEY_ODACHome1 so i guess that sounds good.

You need to manually unset the PATH variable as well and if you tried to set an envrionment variable ORACLE_HOME, remove that as well (not the registry).

>>Maybe i should just take everything Oracle related off and try and start again... although, I don't know how to set up the whole thing, or set up databases etc...

Since there were already apps running on the server, I would not suggest this unless you can ensure you can put everything back EXACLTY as it is.

oh, i didn't restart server though, so i guess i will try and do that monday morning... can't do it now obviously, but if i'm in early i can do it before people get in

the apps running on the server under oracle at the moment are not important. I just had a couple of Oracle databases set up for me, so theoretically would like to keep those, but they can be removed. Ultimately, I guess i just have to keep trying to get this working and often in these kinds of situations that does mean starting from scratch. so I'll just have to try and learn how to deal with these things

humm, my evironmental variables now look ilke this: D:\oracle\product\10.2.0\client_1\bin;D:\oracle\product\10.2.0\db_1\bin;... more stuff...;D:\OracleClient\INSTALL\jdk\bin

and the OracleHome is still what i set it to D:\OracleClient\INSTALL; It seems to require a value, if I remove it completely the OK button becomes greyed out so I can't enter nothing into that environment variable?

>>oh, i didn't restart server though, so i guess i will try and do that monday morning

Restart shouldn't be necessary.


>>so theoretically would like to keep those

Once we get the old 10g set back up right you can do a simple EXPORT of the important users/schemas.  It's a simple matter to import things into another database down the road.

>> i just have to keep trying to get this working and often...

If to many things are changed and you cannot back them out to get back to a known starting point, starting over is typically better.

Granted, fixing it is a great way of learning but in a Q&A forum like Experts-Exchange learning but posting back and forth is troublesome and time consuming.



I know you probably don't believe me on this but Oracle really isn't that difficult.  You just need to learn the 'how' it does things.  Once you figure that out, it is all down hill form there.

Remove the ODAC from the PATH and ORACLE_HOME from the environment variables and see if the 10g stuff starts working again.

>>humm, my evironmental

You caught me typing a novel...

>>and the OracleHome is still what i set it to D:\OracleClient\INSTALL

In the Environment variables or in the registry?  I don't know of any required environemnt variables.

yeah in the environment variables... this is what we were talking about before in post 35332916

it was previously empty, but now I put in that value, and I can't set it back to empty again for the reason stated, the OK button greys out and is unselectable when I remove everything and try and change the variable

>> I know you probably don't believe me on this but Oracle really isn't that difficult.  You just need to learn the 'how' it does things.  Once you figure that out, it is all down hill form there.

I guess it's one of those things... it's easy when you know how! but until then it looks like a big black hole! Hopefully I'll start to get the hang of it sooner rather than later

 

In the image above, select ORACLE_HOME then click 'Delete'.

In the PATH, remove all entries mentioning "D:\OracleClient"

>>it's easy when you know how! but until then it looks like a big black hole!

lol.... I feel your pain!  Trust me...  I'm an old dog and HATE learning new tricks!

Well we have some improvement there, SQLPlus starts up now at least after removing the references and asks me for login details. Unfortunately when I try to logon I get the following error

ora-12541: TNS: No Listner

From a command prompt:
lsnrctl status

If it's not running:  lsnrctl start

Post the results if it fails to start.

Here's the results. I guess listener is not properly configured. Thanks for this, I am leaving for the day now though so may not be able to do anything further until Monday. let me know what your instructions are :-)

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.237.47.217)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 61: Unknown error

Check the PATH.

Make sure you are executing the lsnrctl from  D:\oracle\product\10.2.0\db_1\bin and that your listener.ora file under  D:\oracle\product\10.2.0\db_1\network\admin is correct.

I'll be around when you get back to this.

Hi, ok i did it in the path you suggest, but seems a similar result

D:\oracle\product\10.2.0\db_1\BIN>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-APR-2011 09:47:20

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.237.47.217)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 61: Unknown error


The listener in the path: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\     looks like this:


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.237.47.217)(PORT = 1521))
    )
  )

Never looked at that file before, but it mentions the OracleHome again, so I'm wondering if this has something to do with whatever we changed when messing around with the Oracle Homes...

just to clarify everything, the environmental variables still look like 'D:\oracle\product\10.2.0\client_1\bin;D:\oracle\product\10.2.0\db_1\bin;'

In the universal installer is says the default Oracle Home is: D:\OracleClient\INSTALL

The universal installer also says there is an oracle home called OraDb10g_home1 with location D:\oracle\product\10.2.0\db_1.

Not in office today so my help is limited:

The oracle_home in the listener.ora file is correct.  The default home should be able to be tweaked in the listener but as long as the path is set, it shouldn't matter for now.

What happens if you try to start the listener?

This is what I got below when trying to start listener...

D:\oracle\product\10.2.0\db_1\BIN>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-APR-2011 14:02:26

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting tnslsnr: please wait...

Failed to start service, error 0.
TNS-12560: TNS:protocol adapter error

Tried to start manually actually and you can see what happened below:

 
Tried also to start the SQL*Plus thing just to see what would happen but didn't start either.  As per this post http://forums.oracle.com/forums/thread.jspa?threadID=885033 (the third post) I'm thinking the Oracle_Home variable could still be wrong... whatever we've done, I guess that was the only thing we really changed (apart from removing ODAC now also)... what you think?

You need to remove the references to the instant client.  Is there any left in the registry or environment?

instant client references, what would they look like exactly?

I don't see the word 'Instant' in any of the Oracle keys that I look at or in the environmental variables

Oracle seems to suggest removing everything using the OUI but I see nothing regarding Instant Client in there, although I get the feeling that the Instant Client can be installed without the OUI anyway... are you sure that the instant client is actually on the server? i suppose it's probably not then...

>> what would they look like exactly?

Anything referencing "OracleClient\Install"

>>Oracle seems to suggest removing everything using the OUI

The instant client doesn't use OUI.  For future reference:  OUI never removes everything.

oohhh... that's the instant client? did that install with something else like ODP or how is it there?

So, does this mean I need to manually go and delete all the registry keys which reference that? sounds a bit iffy...

I mean, that key is all over the place in the registry... take a look at the first picture of my registry values in the post 35399164 above

oops..  my mistake.  I actually meant ODP.Net not instant client.  Sorry for the confusion.  I'm starting to confuse questions.

Like I mentioned before, OUI leaves a lot of stuff hanging around.  I've always relied on manual removal of Oracle products.  Since you aren't comfortable yet, I might not have you manually remove all entries.

In the image you referenced, do you see ORACLE_HOME set to the OracleClient\Install installation?  You need to set this back to the other home:  D:\oracle\product\10.2.0\db_1

ok did that.... although if i was having a guess, i'd would guess that this didn't change...

there's another key there called KEY_OraDb10g_home1 and I'm thinking that one was really the one responsible for the connection?

Unfortunately I've only got another couple hours here before i'm off for about a week and a half so i guess i'm not going to be able to do much more until then...

>>i'd would guess that this didn't change

Does the listener now start?
Are you sure there is nothing in the current environment variables pointing to the ODP.Net install?

If not, something is still being cross-referenced somewhere.

I truly believe trying to undo what was previously manually done is causing you a lot more frustration than is really necessary.

I know I keep saying this and I also know you probably don't believe me but Oracle isn't really that hard to work with.  Granted its not just running setup.exe and everything works, it does need some configuring.

Since the old 10g stuff is out there just for you, I would export the databases to preserve the data then start over with everything.

This will also allow you to move the database to 11g.

listener doesn't start

environmental variables path is: D:\oracle\product\10.2.0\client_1\bin;D:\oracle\product\10.2.0\db_1\bin;C:\Program Files\HP\NCU;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Microsoft Network Monitor 3\;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Windows Imaging\;

there's a PERL5LIB environmental variable which says:
D:\oracle\product\10.2.0\db_1\perl\5.8.3\lib\MSWin32-x86;D:\oracle\product\10.2.0\db_1\perl\5.8.3\lib;D:\oracle\product\10.2.0\db_1\perl\5.8.3\lib\MSWin32-x86;D:\oracle\product\10.2.0\db_1\perl\site\5.8.3;D:\oracle\product\10.2.0\db_1\perl\site\5.8.3\lib;D:\oracle\product\10.2.0\db_1\sysman\admin\scripts;

that's pretty much well all i see in the environmental variables

I don't mind doing whatever it takes at this stage, we can remove everything Oracle and start again if necessary. The one thing I did notice of course last time, is that whatever change we made, it didn't adversely affect anything until a restart ocurred, so that would suggest if we undid the change a restart would again be required? However, at this stage, i would agree that neither of us has a clue what's wrong and obviously from where you are it's very difficult to assess what's happened.

So, maybe we can just remove oracle altogether using the OUI and then remove all keys or whatever else is lying around and start again?

I doubt a reboot will fix anything but feel free to try.  It couldn't hurt.

Do you want to preserve the data in the 10g databases?  If so, don't start removing anything yet.

>>So, maybe we can just remove oracle altogether using the OUI

If you want to remove everything:  Forget the OUI.

Stop all Oracle related services.
Delete the Oracle services.
Delete the entire Oracle key from the registry.  
Delete c:\program files\Oracle folder
Delete all you can from d:\oracle (some files will be locked and cannot be deleted).
Reboot.
Finish off deleting d:\oracle
If anything is still locked, try shutting down IIS.  That is typically the only other service that locks Oracle files.

ok, i will start again when i return on this. thanks again for help you're putting in a lot of effort! i would award you extra points if posssible for it.

i will need to stay with 10g as that's the database I have been given. So, it's currenly on the D: drive so i won't delete it from there (i mean, that's where the zipped installation is). I'll use the OUI i guess to just remove whatever it has on it, and then manually delete the rest (can't hurt I guess to just use OUI ininitally)

I'll let you know how I get on... :)

Not really about the points.  Most of us just like helping.

In this case, I'm trying to win over a new Oracle supporter!  If I let it drop, you would probably always hate Oracle.

>>So, it's currenly on the D: drive so i won't delete it from there

If you have a database you need to export it/back it up.  Just 'not deleting' it really won't help once you remove the registry entries/services.

If you are wanting to preserve the 10g stuff as-is, then we're back to where we were and trying to undo the ODP.Net install and get the original 10g install back running.

ok sorry, was in holiday in beirut there! ok back to business again i guess...

Ok I tried to follow everything as per http://krsethur.wordpress.com/2006/06/13/uninstalling-oracle-10g-manually-from-windows-xp/

Only thing I couldn't do was remove the D:\Oracle folder as it was in use by some other process. I stopped all services under oracle and deleted the registry software keys (current user and local machine) and event log keys. Deleted C:\Program Files (x86) \ Oracle. Couldn't see where C:\Documents and Settings\All Users\Start Menu\Programs\Oracle was to delete from the programs menu, so just deleted it directly off the menu

I guess I will need to restart the computer and then try and delete the Oracle folder... after I set the start up of the services to disabled (should I not delete / remove these as well?)

No problem on the delay.  Hope you are well rested (or not depending on how festive it was) fro mthe holiday.

I mentioned the reboot in http:#a35440884.  Some Windows services lock a few Oracle files.  If you remove everything you can, after the reboot you can typically remove the rest.

If not, there is always safe mode.

ok, I'll do that hopefully monday morning i guess. or maybe later even.

After that, I'll just reinstall everything as before (well, not ODP yet I guess, just Oracle)... and we'll take it from there?

Sounds like a plan.  I'll be here.

Ok i managed to reboot the computer yesterday evening... and deleted the Oracle folder so that's fine. Just before I reinstall anything though, there is another folder called 'Oracle Client' in there. It has a SETUP.EXE file and I'm just wondering whether I should be deleting this also (or maybe just moving it somewhere cause I don't want to delete it and find I don't have an oracle client anymore (although I guess I would have assumed that is automatically installed with Oracle anyway?)

i'm just not sure where that folder came from or why it's there,  if someone put it there manually and then installed the client from it...

Hey, ok well i guess we could be back in business (i just left that file i mentioned above alone).

So, I reinstalled oracle and there was one error saying:

Enterprise manager configuration failed due to the following error: Error instantiating EM configuration files. Refer to log file at xxxx for more details. You can retry configuring this database with enterprise manager later by manually running xxxx emca script.

So, not sure if that's a problem or not, but I was able to start up toad anyway and log onto the database under SYSDBA (at least, i didn't see any error). So, I guess the next step is to create a database and and a table and try and log on from my pc down here? Not sure how to create a database... maybe you can point me as to how to do that or a link for me to read up. Otherwise I'll just do a bit of reading later and see if i can figure it out...

oh and, i noticed when it was installing that it mentioned something like 'installing oracle provider for .NET'

so that kind of made me think that maybe ODP is automatically installed with 10g? (granted perhaps an old version)

If you installed software only I'm not sure why it was trying to configure Enterprise Manager but OK.

To create a database run the Database Configuration Assistant (DBCA).  It sort of walks you through the process.

>>so that kind of made me think that maybe ODP is automatically installed with 10g?

I don't recall that being a default option but I've not installed 10g for a long time.  If you rerun the installer you can verify ODP is in fact installed.

managed to do that ok. created a database and was able to connect on the server through toad. Wasn't able to connect through toad from my pc, but then was messing around and saw that i could create a user. So I created a user with unlimited privilages (a bad idea?) and was able to connect using the username and password for that user. Is that ok?

One odd thing though is that when i created the database i specifically checked the option to create a number of standard tables, just so i could check everything had worked. However, I don't see those tables... so I'm wondering about that.

As far as ODP.NET is concerned, there is definitely an ODP.NET registry key there now under the oracle key. I can see something in the OUI and points to this folder D:\oracle\product\10.2.0\db_1\odp.net. So I guess it must be installed. This is the enterprise edition, so not sure if that makes a difference from what you might have used?

>>So I created a user with unlimited privilages (a bad idea?)

It 'depends'.  Typically you don't need a DBA level account just sitting around for no reason.  For now, I say leave it.  We can always figure out why you cannot connect remotely later.  It will likely be a remote authentication issue.

>>created the database i specifically checked the option to create a number of standard tables,

I don't remember an option to create specific tables.  I think you can choose to install the sample schemas but not tables.

To see these schemas:  select username from dba_users;

You should see users like 'SCOTT' and 'HR'.  These are the main sample schemas.

>>This is the enterprise edition, so not sure if that makes a difference from what you might have used?

Looks like ODP is installed.  Not suer if Enterprise Edition did it by default.  Shouldn't matter as long as it is there.

yep indeed i can see those schemas there (although not from my pc only from server. From my pc it shows only some of them, i guess because of privilages again or something).

So, I manged to create a table and connect to it from my pc in TOAD. Next step was to try and connect to it through code. So I created the following:

Dim con As OracleConnection = New OracleConnection()
con.ConnectionString = Data Source=ORCL;User ID=UserID;Password=myPassword;
con.Open()

and unfortunately got a ORA-12154: TNS:could not resolve the connect identifier specified error. Checked the TNS file and seems ok (after all, it was ok to connect through toad)

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

so not sure what happened there

also i've no sqlnet.ora file in my ora92 folder on my pc... not sure if that makes a difference when connecting through code

>>i guess because of privilages again or something

Almost definitely.  You need to be 'granted' access to see them.  If you granted the DBA role to your new user then you will be able to see them.

>>and unfortunately got a ORA-12154:

Verify the referenced DLLs are the same ones that Toad is using.

>>also i've no sqlnet.ora file in my ora92 folder on my pc...

Shouldn't for that error.

oh this is getting frustrating again! I notice that i can add data into the table on the server and view it in toad, and i can add data into the table on my pc and view in toad, but oddly, i don't seem to be able to view on my pc in toad, the data that was added on the server via toad.... if you get me...

So, when I look in toad on my pc... i can see the stuff that i added from my pc, but i can't see the stuff that was added when i was on the server. So, again, presumably it has something to do with privilages... but there's such a ridiculous amount of options in toad that i can't even find my way back to where i created the new user with all the privilages. And even then, probably i need to create some kind of role instead?

how do i verify that the referenced dlls are the same one that toad is using? you mean the odp.net dlls or something?

ok ok phewwww....

well the last issue i mentioned is ok anyway, the tns file that was being referenced was not in the ora92home but in the E:\oracle\product\10.2.0\client_1\NETWORK\ADMIN folder which was the one that was created when i installed ODP.NET

So i was able to connect through code and run a query...

Does explain the trouble i was having though viewing things in toad... but i'll have to check that further tomorrow... maybe you have some idea

that should read Doesn't

must be getting tired ;-)

so, are we good here or do I need to address anything?

well there is the first issue in post 35778795

and also i still couldn't could get it working on the server (when published and running through IIS), got error 'The provider is not compatible with the version of Oracle client'

so i'm not done yet. If ODP.NET is installed, the version does not work with whatever version I have installed I suppose... or something is wrong

Cool.  

About the version:  when you compiled the .Net app you referenced a specific OracleDataAccess.dll.

You either need that exact version od ODP.Net installed or you can tweak your app.config.  I've never done the app.config but another trusted Expert says it can be done.

Here's the link that talks about ODP.Net and versions.  I posted the link to the app.conf portion but the entire question talks about this issue.

https://www.experts-exchange.com/questions/27020817/The-provider-is-not-compatible-with-the-version-of-Oracle-client.html?&anchorAnswerId=35709210#a35709210

thanks for that, ok well, we're sort of back where we started now anyway. So, at that point I installed ODP.NET on the server, and what happened was it created a new folder in the Oracle folder on the server (same as my pc) which effectively created a second Oracle home I guess.Ok this may take a bit of time to explain... but stay with me here cause I'm wondering if I'm starting to get what might have happened:

So I'm not sure which route to go down (config or installing the same version as I have on my pc) but I guess trying to get the latest version on the server would, theoretically at least, be preferable (unless we're going to have the same issues as before). If that's the case, then I suppose I need to understand what's happening there. For instance, on my pc the oracle home was previously within the E:\oracle\ora92 folder and indeed if i look at the registry keys it looks like that's still the default home.

However, there is another key called ODP.NET and that references the new home that was installed when ODP.NET was installed (in E:\oracle\product). So, I don't know how this works, but I can only assume that the default home is not used when ODP.NET is used. What must happen is that ODP.NET looks at the ODP.NET key and not at the Home0 key to find the home to use. (I show the two keys attached below). As you can see from the pictures, the keys look ver different, but the one on my pc is at least referencing the correct oracle home... the one which was created by ODP.NET.

But get this, that oracle home is NOT referenced in the environmental variables... which seems to suggest to me the environmental variables have nothing to do with this, at least as far as ODP.NET working is concerned. What I guess might have happened is that ODP.NET was already on the server when I installed the newer version of ODP.NET and possibly the old key was still being referenced, or it never got set up properly. But, at the very least, I guess the databases were in the initial default oracle home, and not in the ODP.NET oracle home. So... the oracle home needs to be set in the ODP.NET key?

I guess that's a bit long winded but maybe some logic there


ODP.NET-key-on-my-local-pc.JPG
ODP.NET-key-on-server.JPG

indeed i notice in that question you say 'The closest I can get is two versions of the ODP.Net on the same machine.  Somehow the different .Net apps can find their correct version.  Never had to dig into the 'how'.'

so... i guess it must be those ODP.NET keys then?

>>So... the oracle home needs to be set in the ODP.NET key?

I've never had to mess with the ODP keys in the registry.  I'm not sure it will recognize a new key under that entry anyway.

>>so... i guess it must be those ODP.NET keys then?

I would be afraid to guess.  I sort of always assumed the DLL magically 'knew' where 'home' was.  If I was going to make a guess, I would say it looks at the DllPath key if if cannot find any other 'reference'.

Some env variables can override 'default' behaviors.  If none are present, it should take default actions.

great thanks again. i assume that's a permissions issue... hope i haven't tired you out too much!

Aiden

>>hope i haven't tired you out too much!

Never.  I hope I didn't confuse you to much!  This has been a very interesting question!

solved