Unable to log in to database as SYS with Oracle SQL Developer

15
votes

I've worked with Oracle for some time but very much a noob with the Admin side of things and am learning, so bear with me.

I cannot log on to my database (orcl_test) with SQL Developer with the SYS username. I can log on just fine in SQLPlus with SYS as SYSDBA - when I try with SQL Developer I get an error:
ORA-01017: invalid username/password; logon denied.

Logging on as SYS as SYSDBA in SQLPlus, I created a test table within the database and granted the test user SCOTT with SELECT permissions. The Scott user can log on through SQL Developer w/o problem and access the allowed tables.

I have checked that the Scott user and SYS are logging in using the same settings -
Hostname: (ip address)
Port: 1521
SID: orcl_test

For SYS I flag the role SYSDBA - but otherwise the settings are the same.

Any thoughts on why I can't log on using SYS? Am I just overlooking something or have I configured my db incorrectly?

Not sure if this is relevant but I cannot use 'localhost' for the hostname, I have to enter the IP address. Where do I configure Oracle to recognize localhost?

This is a new installation of Oracle 11.2.0.1.0 on a standalone test box running Windows XP. Running Oracle SQL Developer 1.5.5.

4
oracleloginoracle-sqldeveloper
If you try with connection type "TNS", what happens? – user800014
Good call! Although still sure something is wrong: I changed connection type to TNS (checked the orcl_test entry in tnsnames.ora - looks fine). It fails when Network Alias: "orcl_test" is used but if I select Connect Identifier instead and leave it blank then SYS as SYSDBA conencts fine. Any explanation what just happened here would be appreciated. – owlie
Do you have more than one client installed on the machine? Search for tnsnames.ora, exists more than one? – user800014
No other instances - only other tnsnames.ora files are the oracle samples installed under network\admin\sample. – owlie
Never, ever create your regular tables (not even test tables) as SYS or SYSTEM. Just don't do it. – a_horse_with_no_name

4 Answers

15
votes

From the The SYSDBA System Privilege and (Logging In and Connecting to the Database as SYSDBA:

1--Connecting AS SYSDBA invokes the SYSDBA privilege. If you omit the AS SYSDBA clause when logging in as user SYS, the SQL Command Line rejects the login attempt.

2--Using SQL Developer, open a database connection to the SYS user AS SYSDBA.

So - if this works for you:

sqlplus sys/Oracle_1@pdborcl as sysdba;

Try: "SYS AS SYSDBA" as below:

enter image description here

Alternatively:

you can type in: "SYS" and select from dropdown-menu ROLE: SYSDBA.

enter image description here

5
votes

If I understand correctly the database is on the same host as the SQL Developer installation? Are you fully qualifying the connection when testing with SQLPlus: 

sqlplus "sys/password@database as sysdba"

It may be the case that the error is correct. Make sure you've created a password file.

3
votes

The sys password that you are using is not valid.

But because you are connecting to oracle by command on an Oracle machine, Oracle does not check your password even though your password is not right.

Just change the sys password and try again.

2
votes

su - oracle

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=Euro2016 entries=5

which means: write to a file $ORACLE_HOME/dbs/orapw$ORACLE_SID a password Euro2016 which is used by SYSDBA

Now you can connect with sqldeveloper: username sys, password Euro2016 with chosen SYSDBA , hostname, port, sid from $ORACLE_SID