Explicit connection to a database without using tnsnames SQLPLUS 1

[juniper911]

Hi I am a complete n00b to sqlplus/Oracle but I have a user who is trying to connect to an oracle db from command line.

He is getting unable to resolve service names error message. After much googling , checking TNS_ADMIN variable, checking the sqlnet.ora and tnsnames.ora files I cannot see anything wrong.

I wanted to know if its possible to connect using sqlplus to the database if I specified all the information in the query. I found in this potential fix

sqlplus sys/password@//server:1521/orcl as sysdba

However as stated earlier, I am new to this so not sure that some of the placeholders are instead of...

I just need to know what sys and orcl as sysdba are and if they need to be substitued. Also is this expression valid?

The other wierd thing is that the user can connect using the sql plus client to the same database.

Much appreciated for any help.

 

[KenCunningham]

sys is the 'super user' of the Oracle world and orcl is the default database name if creating one when installing oracle or using dbca. Do you know the sys password? Do you know the name of the Oracle database you're trying to access? If not, you should be able to find the latter in the tnsnames.ora file specifying connections.

The internet - allowing those who don't know what they're talking about to have their say.

 

[SantaMufasa]

Juniper,

First, let's discuss the Big Back Door that Oracle leaves in the database-administration world. The "back door" consists of these items:

[ul][li]The o/s user is a member of the o/s's "Database Administration Group".[/li][li]The o/s user has set/profiled her/his[/li][ul][li]$ORACLE_SID=<database instance name>[/li][li]$ORACLE_HOME=<fully qualified path root directory to Oracle Home directory>[/li][/ul][li]With above settings, user can connect to SQL*Plus in this fashion:


% sqlplus / as sysdba

The above command authorizes o/s user to connect as the "SYS" (Oracle Super User) by virtue of her/his membership in the DBA group, and connects to the database that the $ORACLE_SID environment variable points to.[/li][/ul]

Now, for non-"SYS" connectivity, there are several options. Among those options are:[ul][li]Implicit connection to the database that the $ORACLE_SID environment variable specifies. (See above example).[/li][li]Use Oracle's Transparent Network Substrate (TNS). This depends upon the existence of valid TNS entries in your tnsnames.ora file, which usually resides in your $ORACLE_HOME/network/admin directory. The entries in the tnsnames.ora file contain the network-connectivity parameters that satisfy all of the over-the-network connections such as:[/li][ul][li]PROTOCOL (e.g., TCP), HOST (i.e., IP address or network name of server upon which the Oracle target instance resides)[/li][li]PORT (e.g., 1521)[/li][li](Oracle) SERVICE_NAME or SID name (e.g., ORCL, etc.)[/li][li]INSTANCE_NAME (e.g. node name if using Real Application Clusters (RAC))[/li][/ul][li]Lightweight Directory Access Protocol (LDAP).[/li][/ul]

To confirm that your client session is able to successfully "see"/connect to an Oracle instance, you can, from a command prompt, execute this:

tnsping <Oracle tns-alias name>

If your client machine cann successfully access <Oracle tns-alias name>, then output similar to the following should appear:

C:\>tnsping xyz

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 13-JAN-2011 10:55:30

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used (LDAP or TNSNAMES) adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host or IP>)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)LOAD_BALANCE=YES)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<servicer>)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC))))
OK (20 msec)

If the ping was successful, the important result is the OK (20 msec), which confirms the roundtrip connection status and the time it took to make the connection.

I've probably bored you to tears at this point, but perhaps something of the above will help you troubleshoot your issue.

Please post your findings or additional questions here.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[juniper911]

Reply to Ken.

I do have the details of the username, password and the database. I do not know what level of access this user has but using the same credentials they work when using the SQL Plus client.

Can I use the details I have and put them in place of sys/orcl?

e.g. sqlplus user1/password@//oracleserver:1521/OrclDB as sysdba

I'm using Oracle 9.2 client


Reply to Mufasa

When I try to TNSPING the database I get this error message

Used parameter files:
C:\Oracle\Ora92\network\admin\sql

TNS-03505: Failed to resolve name

When I check the tnsnames.ora file in the admin location I can see an entry for the database with all the relevant entries. There is only 1 tnsnames.ora file on the client PC.

I do not think the Oracle Sid and Oracle Home variables is set as I cannot see them in the Environment Variables in System Properties.

I will try and set them and see if it works.

Many thanks to both of you for your input.

J

 

[KenCunningham]

Can you post the entry in tnsnames.ora? I have had some success in the past be renaming sqlnet.ora to sqlnet.old, forcing oracle to use the tnsnames.ora file to resolve the name. Once you've set the SID and the Oracle home, you should be able to connect without using the @ string as the connection is implicit in these variables. Let us know how you get on.

The internet - allowing those who don't know what they're talking about to have their say.

 

[juniper911]

This is the entry in the tnsnames.ora file for the database, I have renamed the variables.

DATABASE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.192.51)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DATABASE)
)
)

I can ping the host and the telnet to the port.

How do I set the SID and Oracle home variable? Is this a registry hack?

 

[KenCunningham]

You can open a command prompt window and issue:

set ORACLE_SID=DATABASE

set ORACLE_HOME=<Path to Oracle Home> (ie top level Oracle directory C:\oracle\ora92 for example).

You can set these permanently by adding these variables via the Properties/Advanced/Environment Variables settings if you right-click on My Computer.

You might also want to check that your PATH includes the oracle home bin directory.

Hoep this helps.

The internet - allowing those who don't know what they're talking about to have their say.

 

[juniper911]

Hi Unfortuneately this did not work, this user said that someone else can achieve connecting to the same database from the command prompt.

sqlplus user/password@database

I will check their machine and see what difference there are...I will keep you posted.

 

[juniper911]

RESOLUTION

The user resolved the issue himself by installing SQL plus 11G client himself and the command is now working.

Thanks for your help