If local connection works, but listener connection does not, most likely reason is ORACLE_HOME setting in listener.ora is wrong (directory does not exist, setting has syntax errors, for example a / at the end is not allowed).
What could be slightly more worrying is the following:
[oracle@localhost admin]$ lsnrctl status
LSNRCTL for Linux: Version 18.104.22.168.0 - Production on 06-DEC-2004 13:48:00
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
Version TNSLSNR for Linux: Version 22.214.171.124.0 - Production
Start Date 06-DEC-2004 09:50:20
Uptime 0 days 3 hr. 57 min. 39 sec
Trace Level off
Listener Parameter File /home/oracle/OraHome1/network/admin/listener.ora
Listener Log File /home/oracle/OraHome1/network/log/listener.log
Listening Endpoints Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "bluetest" has 1 instance(s).
Instance "bluetest", status UNKNOWN, has 1 handler(s) for this service...
Service "bluetest.domain" has 1 instance(s).
Instance "bluetest.domain", status READY, has 2 handler(s) for this service...
The command completed successfully
I don't know how to correct the status for the bluetest instance though.
# sqlplus firstname.lastname@example.org results in ORA-12154
Don't worry about lsnrctl status output, this has to do with automatic instance registration (introduced in 8i).
In fact, you don't need a listener.ora (if port 1521 is used), a database registers itself with the listener. What you see (bluetest.domain) is derived from init.ora parameter SERVICE_NAMES (if not specified explicitly it defaults to DB_NAME.DB_DOMAIN, both also init.ora parameters and normally defined during database creation).
If the database is not running, lsnrctl status will not show bluetest.domain.
ORA-01034 shows the listener 'thinks', database is not started.
To rule out definitely, service registration causes the problem, modify listener portnumber to another value, although I don't think it will help.
You may try to generate configuration files (tnsnames.ora,sqlnet.ora,listener.ora) using netmgr utility to rule out any syntax errors.
Ok, I regenerated the files using netmgr and in the process i changed the port number in listener.ora to 1522. After that tnsping fails with TNS-12541: TNS:no listener and obviously sqlplus user/password@bluetest fails with the same error.
So the listener configuration is important.
The problem is still there. I am beginning to suspect that there is something seriously wrong with the installation or the database kernel.
Would it not be better to redo that or recreate the database and try everything from scratch.
Oracle screwed the pooch on this one...
In their zeal to secure the database after several high profile exploits were published, some propeller head decided to change the permissions on all files, so any user not in the privilege group (usually dba) can't access the files in $ORACLE_HOME. You may see where some dolt suggests adding any user who needs to use the oracle binaries to the dba group, but that will open a HUGE security hole. **don't do it**
Who ever is in the dba group can connect as user SYS without a password ala:
Basically the message is that there is no instance called 'bluetest' up and running. This can be
a) because it's not up, or
b) because it's not up even though you think it is.
In the second case, there may be a service name, instance name or tns name mismatch.
Go into the database using "/ as sysdba" and show the parameters, specifically the INSTANCE
SQL> show parameter instance
you should get an instance name.
Check if the database is registering itself with the listener, by entering the command
and see what instances it's watching.
Also, try sqlplus user/password (without @bluetest), after setting the ORACLE_SID.
I note that you seem to be using root - generally considered a bad idea, and I have had a few hiccups, especially when I've tried to start the database under root. If so, try starting it as the official oracle database owner and see what happens.