ittechlab
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.
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.
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)
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"
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.
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?
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?
ASKER
1. trying to login to server
2. On the same machine.
3. how to check?
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.
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.
ASKER
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_st ring>] | /
I am getting the above error message.
username: sys as sysdba
password : *****
hoststring : lanka01
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<
I am getting the above error message.
ASKER
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.
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.
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.
ASKER
This is what i have from tnsnames.ora
# TNSNAMES.ORA Network Configuration File: I:\oracle\ora92\NETWORK\AD MIN\tnsnam es.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)
)
)
# TNSNAMES.ORA Network Configuration File: I:\oracle\ora92\NETWORK\AD
# 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)
)
)
ASKER
how about username : system
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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)
TEST
or
ITTECHLAB
(and it is not case-sensitive, at least on Windows)
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
sqlplus "sys as sysdba"