Re: 9i on Windows 2000 Server Workgroup

Can we try and simplify?

In any Oracle installation, your listener.ora and your tnsnames.ora will contain a line similar to this one:

(ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521))

Which immediately raises the question: what the hell is "Mozart"?? You therefore have a names resolution issue. "Mozart" as a name needs to be turned into an IP address. You can, incidentally, obviate the need for names resolution at this point, because you could re-writer that line in both listener.ora and tnsnames.ora to read:

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521)) But there is then a second issue: one way or another, I'm going to end up with that "HOST=" bit of the file pointing to an IP address. But is the IP address the correct one? Is Mozart *really* the machine at 192.168.1.200, or has some other machine somehow managed to swipe that IP address?

So there are two issues: a names resolution issue, and an accuracy-of-address issue.

DNS assists with the first one, but there are other, simpler methods for doing the same thing, such as using a static ...\etc\hosts file. But all those methods risk coming a-cropper over the second issue: how accurate is the information they provide? If you statically declare "MOZART=192.168.1.200" in, say, ...\etc\hosts, then you had better not be using DHCP, because that will one day mean that mapping of name to address turns out to be wrong. So it is not that DHCP is "bad" or "evil" or that Oracle doesn't like it. It is simply that Oracle needs to be able to accurately map a machine name to an IP address, and DHCP threatens its ability to do that.

Now, you say to me, "But I've been working with DHCP in a domain for a year and it's all been fine". That, I suspect, is because you were running in a Windows Active Directory domain, which utilises *dynamic* DNS, in conjunction with the dynamic IP addresses of DHCP. Although the IP address for Mozart changes over time, it doesn't matter because the DNS records doing the machine name-address mapping are updated at the same time. Without a domain, however, you aren't going to have Active Directory, even though you are still able to use DHCP, as you point out. At best, therefore, you're going to have old-fashioned *static* DNS servers, and you're then immediately back with the problem of maintaining the accuracy of the name-to-address mapping.

It's also the case that you've been lucky in that year of trouble-free Oracle use with DHCP in a domain. Because the Intelligent Agent and the Management Server get *very* upset when IP addresses change, even in a domain. Now, maybe you're not using the Agent. And maybe you're not using the Management Server. And maybe, you got lucky in the sense that just because a DHCP lease expires, it doesn't necessarily or always mean your machine gets given a new IP address (I use DHCP here at Dizwell, with a lease expiry of 8 days. I'm still using the same IP address I was first given in December 2003. That's because I'm not adding new machines onto my network all the time, so it happens that I just keep being re-assigned the same IP address whose lease just expired). But that is all just luck and happenstance. It is *still* extremely bad practice to install Oracle onto a machine that uses DHCP to get its IP address, *even in a domain*, because there is no guarantee about the permanence of that IP address, and core Oracle components will fail the minute it changes.

Now, none of this has anything to do with your actual problem of ORA-12514, which is simply an Oracle service names issue, which is why changing global database names will have helped somewhat. Fixing that requires only that you understand Oracle, and know how to create databases and listeners properly.

But what I'm saying to you is simply this: DHCP isn't evil or wrong; but it *will* royally screw up the fundamental job of the listener. Dynamic DNS in Active Directory hides that truth from domain users, for a good long time. But the issue is still there, domain or not, workgroup or not. And if you don't have an Active Directory, you're going to be in the same trouble a whole lot quicker, because there's no dynamic DNS to hide behind. So please: workgroup or domain -it doesn't matter: don't install Oracle onto a machine that uses DHCP.

Now, you also say, "nowadays most workgroups use DHCP". Let's not quibble about the fact that actually most *networks* use DHCP, domain or workgroup. It's a pain in the neck to have to manage hundreds of client PCs with static IP addresses (and I'm old and dumb enough to remember having to do it that way). Of course DHCP is widely used. But it doesn't have to be used for *every* computer on a network. DHCP allows you to set up 'reservations', where a given IP address is not available for dynamic allocation to any machine that asks for it, but is statically reserved for a single, particular machine based on its network card's MAC address. Were you to set up such a reservation for your Oracle server, its IP address would never change. Technically, I suppose that means the server is still using DHCP -but as far as I'm concerned, it's actually only using HCP, because the "Dynamic" bit of it has been removed from the equation. The other thing I do here is to limit the scope of the DHCP allocations, so that anything from 192.168.1.200 and above are never part of the DHCP allocations, and thus a small pool of 50 addresses or so are available for static use. That is how Mozart really doesn't use DHCP in my domain.

So, just because 'everyone uses DHCP' doesn't mean to say that 'everyone uses DHCP stupidly, blindly, and without thinking about the implications'. Use it intelligently for those machines which really need it (basically, clients). And intelligently excise from its scope those machines which need and benefit from the certainties of fixed IP addresses. You can do all of that in a workgroup as successfully as you can in a Domain. All it requires is that you know how Windows networking works.

So finally, as I said earlier, none of that has much bearing on an ORA-12514 error. But you would be foolish to ignore it as a possible contributory factor, and in any case diagnosing listener and Oracle networking problems in general is a whole lot easier when DHCP is not confusing the picture.

Regards
HJR "fu manchu" <leondobr_at_verizon.net> wrote in message news:hinhd0p04cv7q2722gsmk4sqp70drjo38t_at_4ax.com...
> I probably wasn't clear enough in stating the problem, and for that I
> apologize. I thought this whole thread was about keeping the listener
> alive using DHCP on a workgroup. I ignored the bit in your first post
> about using a fixed IP address because that's a solution I don't want
> to try until I turn a few shades more purple (purpler?) from
> frustration. Nowadays nearly all workgroups use DHCP, some network
> admins won't allow you to use anything else, so I assumed that was
> understood, which was my error.
>
> I've been running several instances of Oracle 9i on three machines
> using DHCP on a domain (where I can't get a static IP address) for
> over a year with no problems. I assume that's because the listener can
> always find the info from the domain DNS. I know that the Oracle
> installer works okay in this environment with exactly the same
> Installer setup input I've been using, otherwise I wouldn't have kept
> on trying for so long on the workgroup.
>
> There isn't a domain DNS on a workgroup and I want to fool the
> listener somehow into finding the instance. Using a global name with
> an extension got me a little bit farther on.
>
> So, in summary, I know for sure that you can install and run Oracle 9i
> on a machine on a domain with an IP assigned by DHCP, and what I want
> to find out is either that it is categorically impossible to get a
> listener to find a instance on a workgroup. or a clever workaround to
> fool the listener into finding it.
>
> I'd be willing to bet that this is exactly the same problem many of
> the others you've helped encountered. They just weren't as bloody
> minded about making it work with DHCP on a workgroup.
>
Received on Wed Jun 23 2004 - 00:38:23 CDT