Home » Oracle » Oracle Database » ORA-01017 Oracle Error Resolution Tips

ORA-01017 Oracle Error Resolution Tips

How to fix ORA-01017

ORA-01017  is one of the common errors seen while connecting to the Oracle database.

Here is what the documentation says about this error

ORA-01017 invalid username/password

Here is the checklist to run to resolve the ORA-01017: invalid username/password

(1)The main issue with an ORA-01017 error is an invalid user ID and password combination.  You have to make sure,you are entering the right password

In case the target system is 11g, the password can be Case sensitive

You can check the parameter in the system

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL>

When the above parameter is set to true, the case sensitivity is on, You may disable it and check the connection again

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

And then try connecting. If it works, then you know the case sensitivity is the problem, you may want to alter the user password  and write it somewhere to remember the case -sensitive password and then again enable the system parameter

SQL> ALTER user test identified by TEST1;

User altered.
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

System altered.

(2) It may be that the user ID is invalid for the target system. Check if the user ID exists as the username column in the dba_users view.

select username from dba_users where username ='<user name>';

(3) Check your $ORACLE_SID  or $TWO_TASK environmental parameter. If your $ORACLE_SID is set to the wrong database then you may get an ORA-01017 error because you are connecting to the wrong Oracle database.

(4) Check your “tnsnames.ora” to ensure that the TNS service is pointing to the right database. You can use tnsping command to check that it also

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-JUNE-2016 23:01:06

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

Used parameter files:
/oracle/product/11.2.0.4/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = techgoeasy.com)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST)))
OK (0 msec)

(5) You may get an ORA-01017 error in the dataguard environment and standby environment also

See also  ORA-00257: archiver error. Connect internal only, until freed.

Make sure the SYS user password is the same on both the Primary and standby sides.  Create the Oracle password file using orapwd with the same password on both the primary and standby side

With Oracle database 12c, in the case Primary RAC database, we need to have a password file at a shared  location

orapwd file='+DATA/TEST/PASSWORDFILE/oraTEST' entries=10 dbuniquename=TEST password=<sys pass>

(6) Sometimes, there could be other reasons for the error and ORA-01017 is a misleading error.

You can trace the call stack by using the truss or trace command

Linux:
strace -o /tmp/strace_user.output -cfT sqlplus Scott/tiger@test

AIX, Solaris:
truss -fea -o /tmp/truss_user.output sqlplus scott/tiger@test

HP-UX:
tusc -afpo /tmp/tusc_user.output -aef sqlplus scott/tiger@test

(7) This error can be encountered during RMAN active duplication also

 Cause The SYS password is not the same between the original/source database and auxiliary/duplicate database. 
SOLUTION Perform the following steps:
 1) Copy the password file from the original/source database to the auxiliary/duplicate database. 
 2) Run the following OS command "cksum" to check whether the password files are the same on both the original/source database and auxiliary/duplicate database. 
cksum {password_file_name}

(8) Case-Insensitive Passwords and ORA-1017 Invalid Username or Password

The Oracle Database 12c release 2 (12.2) default authentication protocol is 12 (Exclusive Mode). This protocol requires case-sensitive passwords for authentication. Review your options if you have earlier release password versions.

Starting with Oracle Database 12c release 2 (12.2), the default value for SQLNET.ORA parameter ALLOWED_LOGON_VERSION_SERVER is changed to 12. This parameter refers to the login authentication protocol used for the server, not the Oracle Database release.

By default, Oracle no longer supports case-insensitive password-based authentication; only the new password versions (11G and 12C) are allowed. The case-insensitive 10G password version is no longer generated.

See also  ORA-00001 unique constraint violated

If you have accounts that require 10G password versions, then to prevent accounts using that password version from being locked out of the database, you can change from an Exclusive Mode to a more permissive authentication protocol.

Password version can be checked as

select username,password_version from dba_users;

Log in as an administrator.

Edit the SQLNET.ORA file to change the SQLNET.ALLOWED_LOGON_VERSION_SERVER setting from the default, 12, to 11 or lower. For example:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

(9)   ORA-01017 using “sqlplus / as sysdba”

This can happen if the OS user where you are trying to use the above command is not member of dba group.

Make sure the OS user is part of DBA group.

Also check the sqlnet.ora  .if you have  sqlnet.authentication_services=none ,then also you may get this error

Hope you like the content on ORA-01017 invalid username/password

Related articles
ORA-00911 : This post is for common causes of ORA-00911: invalid character in oracle with examples and resolution to help you complete the job
ORA-29913 :troubleshooting tips for the ORA errors like ora-29913: error in executing odciexttableopen callout, ora-29913: error in executing odciexttablefetch callout
ORA-00257 :Learn how to troubleshoot for ORA-00257 archiver error. Connect internal only error.Various resolution and example provided in step by step manner.
alter user identified by values : Learn about Oracle password ,stored in database, algorithm , how to login as user without changing the oracle password using alter user identified by values
ORA-28000 :ORA-28000 the account is locked is very common error. Check out this post on how to solve it step by step easily without any issues
ORA-00904 :This post for the description and possible solutions of ORA-00904: invalid identifier.troubleshooting tips is also provided
ORA-28002 :This post in on how to resolve ORA-28002 the password will expire. What can be done to altogether avoid it by creating new profile
password versions in Oracle
Oracle 12.2 Documentation

See also  How to create SQL Patch in Oracle

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top