ORA-01017: invalid username/password; logon denied

##########################
## Error
##########################

ORA-01017: invalid username/password; logon denied

### Full Error

ERROR:
ORA-01017: invalid username/password; logon denied

##########################
#  Error Occurred
##########################

Error occurred while trying to connect a database using RAC service name in 11.2.0.3.0 version

##########################
## Command Executed
##########################

Connect using sqlplus using database service name

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Tnsping & Try Connecting Database over network
#########################################

C:\Users\abc123>tnsping siddb_test
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-AUG-2016 11:06:51
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\Program Files (x86)\Oracle 11g\11g\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sidhost)(PORT = 1521)))
 (CONNECT_DATA = (SERVICE_NAME = siddb_SVC)))
OK (30 msec)
C:\Users\abc123>sqlplus sidusr/sidpass@siddb_test
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:09:27 2016
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

=====================================================================================================================

#########################################
# 2) Connect from DB Server
#########################################

As the Remote connections using service name is failing. I tried logging directly to the database to verify if passwords are working fine.

[oracle@sidhost2 bin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 28 21:26:16 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      siddb
SQL> conn sidusr/sidpassConnected.SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      siddb
SQL>

So the password is the right password. But it doesnt work using remote connection only.

=====================================================================================================================

#########################################
# 3) Try connecting over remote without Service Name
#########################################

By default, every database will have a database service registered with the same database name. For eg:, IN siddb database, we have created a service called siddb_SVC. But a default service named "siddb" will be available in database and can be connected.

So i've modified the tnsnames to use default database service "siddb"

C:\Users\abc123>tnsping siddb_test
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-AUG-2016 11:40:31
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\Program Files (x86)\Oracle 11g\11g\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sidhost)(PORT = 1521)))
 (CONNECT_DATA = (SERVICE_NAME = siddb)))
OK (10 msec)
C:\Users\abc123>sqlplus sidusr/sidpass@siddb_test
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:40:35 2016
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
C:\Users\abc123>

Now i can connect database using default service "siddb" but not using "siddb_SVC"

=====================================================================================================================

#########################################
# 4) Check Database Listener
#########################################

As the remote connections happens via listener, lets check the listener services.

lsnrctl status service

[oracle@sidhost2 dbs]$ . oraenv_+ASM2
The Oracle base remains unchanged with value /ofa/u01/app/oracle/admin
[oracle@sidhost2 dbs]$ lsnrctl status service
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-AUG-2016 11:35:32
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
TNS-01101: Could not find service name service
[oracle@sidhost2 dbs]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-AUG-2016 11:35:38
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-AUG-2016 14:42:45
Uptime                    4 days 20 hr. 52 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ofa/u01/app/oracle/grid/11.2.0.4/network/admin/listener.ora
Listener Log File         /ofa/u01/app/oracle/admin/diag/tnslsnr/sidhost2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=111.22.333.444)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=111.22.333.555)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "siddev" has 1 instance(s).
...
Service "siddb" has 1 instance(s).
  Instance "siddb2", status READY, has 1 handler(s) for this service...
Service "siddb_SVC" has 2 instance(s).  Instance "SIDNEW", status READY, has 1 handler(s) for this service...  Instance "siddb2", status READY, has 1 handler(s) for this service...
[oracle@sidhost2 dbs]$

=====================================================================================================================

#########################################
# 5) Reason for Failure
#########################################

From the above listener service output we can see that "siddb_SVC" service has 2 instances registered "SIDNEW" & "siddb2".

siddb2 seems to be right service. But SIDNEW is another database instance.

=====================================================================================================================

#########################################
# 6) Check Database Service
#########################################

[oracle@sidhost2 dbs]$ srvctl status service -d SIDNEW
[oracle@sidhost2 dbs]$ srvctl config database -d SIDNEW
Database unique name: SIDNEW
Database name: SIDNEW
Oracle home: /ofa/u001/app/oracle/product/11.2.0.4
Oracle user: oracle
Spfile: +SHAREDDATA07/SIDNEW/spfileSIDNEW.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: SIDNEW
Database instances: SIDNEW1,SIDNEW,SIDNEW3
Disk Groups: SHAREDDATA07,SHAREDFRA01,SHAREDREDO01,SHAREDREDO02,SHAREDREDO03
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@sidhost2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 23 11:38:57 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> sho parameter service
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      siddb_SVC
SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      SIDNEW
SQL>

From above output, we can clearly see that service name was registered wrongly in a different database.

One of our member in the DBA team, has created SIDNEW database using "siddb" template but didn't change all the parameters which caused this confusion.

=====================================================================================================================

##########################
## Solution
##########################

#### Modify the database service name.

SQL> Alter system set service_names='SIDNEW_SVC' sid='*';
System altered.
SQL> sho parameter service
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      SIDNEW_SVC
SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      SIDNEW
SQL>

=====================================================================================================================
After modifying the service name, the connections are working good.
=====================================================================================================================

C:\Users\abc123>sqlplus sidusr/sidpass@siddb
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:43:27 2016
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
C:\Users\abc123>tnsping siddb
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-AUG-2016 11:43:34
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\Program Files (x86)\Oracle 11g\11g\network\admin\sqlnet.ora
Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sidhost)(PORT=1521))(LOAD_BALANCE
=yes)(FAILOVER=on))(CONNECT_DATA=(SERVICE_NAME=siddb_SVC)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=60)(DELAY=10))))
OK (20 msec)
C:\Users\abc123>

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================