CODEWITHSUNDEEP
oracle-databaseubuntu-12.04
mike
mike

Reputation: 1

Unable to connect to oracle with different OS user accout

I have installed Oracle on Ubuntu with OS user oracle. When I try to connect to Oracle when logged on as different OS user (other than oracle), I get error:

SQL> connect user1/user1@orcl 
ORA-12154: TNS:could not resolve the connect identifier specified

However, if I don't mention Oracle SID then I don't get any error:

SQL> connect user1/user1
Connected to:
Oracle Database 
SQL>

I have declared ORACLE_HOME and SID in the user profile. But it did not work. Can someone please help me to understand the issue.

Upvotes: 0

Views: 340

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

In a sense you don't really have a problem; you can connect locally using the ORACLE_HOME and ORACLE_SID environment variables, which is fine.

When you use the connect user1/user1@orcl version, the orcl is not your SID; as explained in the documentation it is an 'Oracle Net connect identifier'.

connect_identifier

An Oracle Net connect identifier. The exact syntax depends on the Oracle Net configuration. For more information, refer to the Oracle Net manual or contact your DBA. SQL*Plus does not prompt for a service name, but uses your default database if you do not include a connect identifier.

So you are connecting over a network connection, even though its to the same host, rather than internally using IPC. You don't even need to have ORACLE_SID set when you do that, because that is used for IPC and isn't part of the network connection.

Now, the connect identifier may well actually be the same string as your SID, but it doesn't have to be. Usually when you're using a single term like orcl you're using a 'net service name' which is defined in your tnsnames.ora file. (Depending on how the naming methods are configured in your sqlnet.ora, which determines how the connect identifier is resolved; but this is still the most common configuration I think).

The Oracle Net configuration can be configured using the netca configuration assistant, as the oracle user, though you can create a private tnsnames.ora to override that.

See the Oracle Net admin guide for more about how connectivity works and is configured, and the SQL*Plus user guide for a shorter version.

In order for your user1/user1@orcl connection to work you would need a tnsnames.ora entry looking something like:

orcl = 
 (DESCRIPTION = 
   (ADDRESS = (PROTOCOL=tcp)(HOST=my_host)(PORT=1521))
   (CONNECT_DATA = (SERVICE_NAME = orcl)))

... where my_host is the DNS name or IP address of your Oracle server, and 1521 is the port the listener is configured to run on. (The listener has to be running for you to be able to connect this way, too). The SERVICE_NAME is not necessarily the same as your SID either, but depends on the database configuration; the lsnrctl status command will show what service names are available.

The ORA-12154 error is telling you that you either don't have a tnsnames.ora file at all, or that it doesn't contain an entry for the alias orcl.

As @a_horse_with_no_name points out, there are other forms of connect identifier, and you don't necessarily need a tnsnames.ora file for a network connection; the options are explained here. I've just focused on the one you were trying to use. You don't necessarily need to use any of them though since you are able to connect locally.

Upvotes: 1

Related Questions