Solved

SQL Plus login

Posted on 2006-06-19
19
8,187 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:ittechlab
  • 8
  • 6
  • 2
  • +2
19 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
try to connect like this (will prompt for the password):
sqlplus "sys as sysdba"
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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)
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
sqlplus "system@computername as sysdba"
0
 

Author Comment

by:ittechlab
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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?
0
 

Author Comment

by:ittechlab
Comment Utility
1. trying to login to server
2. On the same machine.
3. how to check?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0
 

Author Comment

by:ittechlab
Comment Utility
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.
0
 

Author Comment

by:ittechlab
Comment Utility
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0
 

Author Comment

by:ittechlab
Comment Utility
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)
    )
  )
0
 

Author Comment

by:ittechlab
Comment Utility
how about username : system
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 63 total points
Comment Utility
OK, so you have two different TNS aliases that you can pick from (either: "TEST" or "ITTECHLAB") but they both point to the same instance ("ittech") on the same host (lanka01).

The default password for "system" is "manager", so you should be able to log in to SQL*Plus like this:
Username: system
Password: manager
Host string: test

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

Author Comment

by:ittechlab
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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)
0
 

Author Comment

by:ittechlab
Comment Utility
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?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 62 total points
Comment Utility
>Can i just install a sqlplus tool from oracle and connect to my server. where do i get that tool and install?
take the same installation media as for the oracle server, but choose to install the client tools also.
if you choose an advanced customized installation, you can reduce to install only the sqlplus, but ususally, the "full" client will avoid later reinstallation of things you will need anyhow to administer a oracle server remotely
0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Forced accept.

Computer101
EE Admin
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now