Re: connect system/password@xe doesn't work

On Sun, 11 Mar 2007 03:46:39 -0700, JL19348 wrote:

>
> Hi, I am not a DBA, I am interested in Oracle (4 weeks experience
> mainly at work), and am moving from Windows/SQL Server to Linux/Oracle
> for my 2 computer home network for learning.
>
> I have a problem with my new Oracle XE installation. During
> installation I accepted all the defaults. My problem is that I can
> connect as some users from computer2 to computer1 (which has Oracle
> on it) but not all, specifically system/manager. I need to connect as
> system/manager onto computer1 from computer2 as computer1 doesn't have
> a monitor/vga card/keyboard. Do I need to set something somewhere
> (init.ora ??) or has my installation failed somehow. You will see what
> I have tried below for computer1 and computer2 and the Oracle error
> message I am getting. What I see tells me that the passwords and
> tnsnames.ora are setup properly too.
>
> Thank you
> John
>

Some basic theory on how this entire shmoz works. That may help you decide what you need to do ...

In the Oracle world, the connection between the client (sqlplus, .Net, ODBC, JDBC/OCI, and so on) and the database is managed by a software layer called 'Oracle Networking'. (Previously known as SQL*Net, Net8, etc.)

On the database server side, Oracle Networking uses a network listener to catch network requests to the service. In the TCP/IP world, by default the Listener waits on port 1521. When a request comes in, the Listener checks whether it knows of the service being requested. If it knows of the service, the Listener communicates with the service to establish a connection.

A typical database service will create a new process/thread on a new port, tell the Listener, which tells the client, which switches to the new port, which allows the client to communicate with the service instead of the Listener.

The Listener is a Windows 'service' (Control Panel>Admin Tools>Services) or a *nix process (which can be an init.d service). It uses the Listener.ora file for configuration parameters. Services may be hard coded in that file, or they (such as the XE database) may register on the port that the Listener uses.

On the client side the terminology is a lot more fuzzy. For example, tyhe client PC uses runs a client program (such as SQLPlus) which connects to the ORacle Networking client.

The Oracle Networking client determines the target service (host, listener port, database) from passed parameters, and does a lookup in the tnsnames.ora (similar to /etc/hosts) or in Oracle Names server (like DNS) or in LDAP server (as DNS implemented in OpenLDAP. It then contacts the Listener as above to initiate the connection.

When you attempt a connection (for example, from sqlplus) you use a string like

        connect user/password_at_alias
where the 'alias' is used as the lookup key in the TNS resolution mechanism (such as TNSNAMES.ORA). The lookup returns the host, the listener port and the service name.

Some of the common glitches I've encountered in the connectivity path:

• Unfortunately (or otherwise) each Oracle software install tends to have it's own TNSNAMES.ORA. That's like creating a new /etc/hosts for each program you install - that contains the host name resolution for that program. If you don't consolidate them yourself, you may not be getting to the wrong target, or not having the target in the correct copy of the file.
• Not configuring the TNS resolution file/method in the SQLNET.ORA
• Not having a suitable entry, or having wrong values, in the TNSNAMES.ORA
• Having a DEFAULT_DOMAIN in the SQLNET.ORA and forgetting that the default domain is appended to an alias when there is no '.' in the alias ... in which case you need to find an 'alias.domain' in the TNSNAMES.ORA
• Firewalls, AntiVirus or SELinux, blocking the network at the server. Specifically port 1521, from the initial listener attempt, and then 'any port' from the service connection access.
• Listener not running
• Service not being registered with the listener.

The last 2 can be checked by issuing 'lsnrctl status' at the command line.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.