Link to home
Start Free TrialLog in
Avatar of ittechlab
ittechlabFlag for Canada

asked on

SQL Plus login

i am trying to login to sqlplus and i typed the following info.

userid : system
password : ****
host string: computername

i am getting the following error .
invalid password or username.

if i type the following username

sys
***
then i get ORA-28009: connection to sys should be as sysdba or sysoper. can someone advice. I even change the Password from oem gui. still same error.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

try to connect like this (will prompt for the password):
sqlplus "sys as sysdba"
To login as "sys" it must be: "sys as sysdba".  All other Oracle login accounts though (including "system") should work as you tried *IF* these two things are true:
1. the username and password you provided are both valid (Oracle is not case sensitive for either usernames or passwords)
2. the "host string" value matches a value in your tnsnames.ora file or whatever you have set up for SQL*net  (this may be the same as the computername, but it doesn't have to be, and often isn't)
sqlplus "system@computername as sysdba"
Avatar of ittechlab

ASKER

when i hit sqlplus i get a logon screen prompted to type username, password and hoststring. I tried all the above and didn't work.

I did hit enter without typing any information and it went to the sql> to enter username and password.  If i type sys as sysdba and password now it works. I didn't really solve the problem yet. I need to know how to resolve this when we enter information with the dialog.
Yes, the normal SQL*Plus login screen has three fields:
User name:
Password:
Host string:

If you provide valid values for all three fields, it will log you in successfully.  The "sys" account is slightly different than all others, since it requires that you enter: "sys as sysdba" for the username.

The value for "Host string:" may be optional depending on your installation, so you may be able to leave that blank.

Can you give us some more inflormation, like:
1. Where are you trying to log in (on a client or on the server)?
2. Where is the database (on a network server, or on your client PC)?
3. Did you configure SQL*Net to use a local tnsnames.ora file, or do you have a different way to identify your server?
1. trying to login to server
2. On the same machine.
3. how to check?
How to check your SQL*Net configuration?

Look in the network/admin directory of your Oracle_home for a file named: tnsnames.ora.  Open it with a text editor, and look at the entries that start at the left margin.  They usually have multiple lines of indented information under each entry that starts at the left margin.

The value that you supply for the "Host name:" in the login must match one of these entries in your tnsnames.ora file.
I checked that and i have added that entry when i login to sql plus.

username: sys as sysdba
password : *****
hoststring : lanka01

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_string>] | /

I am getting the above error message.
If i user the following username

username : system
password : manager
hoststring : lanka01

i am getting the following message
ORA-12514: TNS:listener could not resolve SERVICE.

If i use the following user name its works
user name : scott
password : tiger
hoststring : lanka01

the above error didn't make sense.
With the same "Host string:" value, it should not be possible to get the ORA-12154 error for one username, but not for another.

The "sys as sysdba" login also gives me many problems in SQL*Plus.  I usually log in another user first, then connect to "sys" when I need to (which is rarely) at the "SQL>" prompt like this:
connect sys@lanka01 as sysdba [Enter]

That will then prompt you for the password for sys.
This is what i have from tnsnames.ora

# TNSNAMES.ORA Network Configuration File: I:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lanka01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ittech)
    )
  )

ITTECHLAB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lanka01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ittech)
    )
  )
how about username : system
ASKER CERTIFIED SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Username: system
Password: manager
Host string: test

or, this should also work:
Username: system
Password: manager
Host string: ittechlab
 

works.  I am kind of confused about the hoststring. I thought hoststring is the Host name which is lanka01. Test and Ittech is database name isn't that right? can you explain the root cause thanks a lot.
No, the value needed for the "Host string:" line of the SQL*Plus login box *IS NOT* the physical name of the server!  That value must match a tnsalias that you have defined in your tnsnames.ora file.  In your case, that is either:
TEST
or
ITTECHLAB

(and it is not case-sensitive, at least on Windows)
ok. I got it. Thanks.  If i install only sqlplus in windows xp machine sitting remote site. Can i just install a sqlplus tool from oracle and connect to my server. where do i get that tool and install?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forced accept.

Computer101
EE Admin