Re: not your every day communication problem

Ed Stevens <nospam_at_noway.nohow> wrote in message news:<m29dbvkjnj2spp4uc4n3l96iprq9isat2i_at_4ax.com>...
> I just returned from a service call that I was able to fix, but don't
> understand everything I saw on the way.
>
> Platform was Oracle 8.1.7.4 on NT Server.
>
> Application resides on the same server as the db, which is located off
> site, but there is a wan connection back to our data center. That
> connection is used to authenticate the domain user under which the app
> runs. TNSNAMES and LISTENER describes a normal network connection,
> referencing the IP address of the server. I know that isn't what
> should have been done, but there we are.
>
> <snip>
>
> I know that I want to get this app to an IPC or BEQ connection to cut
> the network out of the picture, but I'm curious about the difference
> in behavior between being logged on to the server as a local user vs.
> a domain user.

Ed,

If you do not use a tns alias, you will be connecting over bequeath. You can set the environment variable ORACLE_SID a number of ways. I don't have any NT4 servers around here anymore, but it should be similar to W2K.

I'd highly recommend the book "Oracle 9i for Windows 2000 tips and techniques" (Jesse, Hart, Sale). There is a sample chapter (chap 3) from this text up on otn.

When you log into the server console, that user's profile is used. In a domain environment, a roving profile may be used, whereby the profile is stored on the network (possibly on a domain controller) rather than on the local machine.

One particularly noteworthy failure mode scenario is when a network user logs onto an oracle server console with a network account - that has mapped network drives. In the event that their network mappings conflict with the volume labels used by local storage - the drive letters are shuffled. Dynamically. Immediately.

An Oracle Instance is not very fond of this type of thing - and will offline the affected datafiles. more than likely - it affects either the system tablespace's datafile(s) and crashes the instance, or a controlfile is no longer there, and it crashes the instance.

Funny - rebooting the server and logging in as a different user - will usually allow instance recovery to fix the symptom. The root cause is fixed by removing the server from the W2K domain, disabling NetBIOS over TCP/IP and stopping the server service and dis-allowing the mcse from logging in by not providing him with a local user account. (insert obligatory uninstall win32 software and install linux quote here).

okay, way off track here. sorry.

Backup (export) the registry with regedit.exe prior to performing this.

1. for all users

control panel > system > advanced > environment variables > system variables.

This will seem hairy, but highlight an existing entry, change the name to ORACLE_SID and populate the value with the oracle_sid and click ok. This will not modify the existing entry, but will create a new one. (this has a much better interface in W2K).

you'll need to logout for this to take effect.

2. for one user (current user)

control panel > system > advanced > environment variables > user variables.

highlight an existing entry, change the name to ORACLE_SID and populate the value with the oracle_sid and click ok. This will not modify the existing entry, but will create a new one. (this has a much better interface in W2K).

you'll need to logout for this to take effect.

3. for one cmd shell

set oracle_sid=<mysid>
sqlplus myusername/mypassword

this does not use a tns alias, so the tnsnames.ora file is not read - and a connection is attempted over bequeath. set tracing on for the client to see for yourself.

4. for an oracle_home, using the Oracle Administration Assistant

start > programs > Oracle - OraHome81 > Database Administration > Oracle Administration Assistant for Windows NT.

expand the '+' to show the nodes
"Computers" > "<localhost>" > "Oracle Homes" > "OraHome81". Right click on the home and select "Properties". You'll see the default SID that is currently set.

5. edit the registry directly, using regedt32:

HKLM\SOFTWARE\ORACLE\HOME0\ORACLE_SID : REG_SZ : <mysid>

If you have multiple oracle homes, you'll have to determine which home the

6. IPC - use the gui Net Manager tool to add IPC to the list of connect descriptors in the tnsnames.ora and listener.ora files. (Make sure that the same key is used)
Stop and restart the listener service.

D:\Oracle\Ora81\BIN>tnsping drak92

TNS Ping Utility for 32-bit Windows: Version 8.1.7.4.0 - Production on 05-MAY-20
03 18:48:44

(c) Copyright 1997 Oracle Corporation. All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=IPC)(Key=IPCKEYPLD)) OK (50 msec)

hth,

Paul Received on Mon May 05 2003 - 18:13:03 CDT