ORA - 01017:invalid username/password; logon denied , ORA-28040, ORA-03134

Issue:
When application schema was trying to connect the database from SQL Developer, they are facing intermittent issues with the below. out every 3 attempts only one connection is establishing successfully.

DB version: 12.1.0.2
2 node RAC
Status : Failure -Test failed ORA- 01017:invalid username/password; logon denied

This issue may also arrives when you face below errors.If the client release does not meet or exceed the value defined by this parameter, then authentication fails with one of these two errors:

ORA-28040: No matching authentication protocol error

ORA-03134: Connections to this server version are no longer supported

But in this case we faced below issue.

ORA- 01017:invalid username/password; logon denied

Solution:

In general issue will be with your password, so make sure to ask application team to use the valid credentials.

But here in this situation,

someone has modified the $GI_HOME/network/admin/sqlnet.ora file only node 1, 

which is has having sqlnet.allowed_logon_version_server=8 value but later it modified to sqlnet.allowed_logon_version_server=12 and  due to that a your application proctol is not able to authenticate the sqlnet.allowed_logon_version_server=12 version for your client connections.

So just revert back the changes sqlnet.allowed_logon_version_server of the working node in cluster (or) to the allowed authenicate protocal version.

Note: You can check in test Database and observe the changes.

For UNIX systems,

the sqlnet.ora can be located any of these locations $ORACLE_HOME/network/admin (or) $GI_HOME/network/admin.

By default,  $ORACLE_HOME/network/admin 

The Oracle documentation notes that a setting of 8 permits most password versions, and allows any combination of the dba_users.password_versions values 10g, and 11g.  

sqlnet.allowed_logon_version_server=8:  A setting of 8 permits most password versions, and allows any combination of the dba_users.password_versions values 10g, 11g, and 12c.  

sqlnet.allowed_logon_version_server=12a: A sqlnet.allowed_logon_version_server setting of 12a permits only the 12c password version.

A value than 12 means the server is less compatible in terms of the protocol that clients must understand in order to authenticate. the server is also more restrictive in terms of the password version that must exist to authenticate any specific account. the ability for a client to authenticate depends on the dba_users.password_versions value on the server for that account.

The default is sqlnet.allowed_logon_version_server=11, but the following values are supported for sqlnet.allowed_logon_version_server, depending on the version of the authentication protocol.

sqlnet.allowed_logon_version_server=12a:  For Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later.

sqlnet.allowed_logon_version_server=12:  For the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended).

sqlnet.allowed_logon_version_server=11:  For Oracle Database 11g authentication protocols (default).

sqlnet.allowed_logon_version_server=10:  For Oracle Database 10g authentication protocols.

sqlnet.allowed_logon_version_server=8:  For Oracle8i authentication protocol

Hope this is clear..!!!

Generate Automatic Workload Repository (AWR) report:

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.

Features of AWR:

• Some of the system and session statistics collected from V$SYSSTAT and V$SESSTAT views
• Object statistics that determine both access and usage statistics of DB segments.
• ASH statistics.
• Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
• SQL statements that are producing the highest load on the system, based on elapsed time and CPU time.

Types of AWR reports:

@?/rdbms/admin/awrrpt.sql --> Generates an HTML or text report that displays statistics for a range of snapshot Ids.

@?/rdbms/admin/awrrpti.sql -->  Generates an HTML or text report that displays statistics for a range of snapshot
Ids on a specified database and instance.

@?/rdbms/admin/awrsqrpt.sql -->  Generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids.
Run this report to inspect or debug the performance of a SQL statement.

@?/rdbms/admin/awrsqrpi.sql -->  Generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance.
Run this report to inspect or debug the performance of a SQL statement on a specific database and instance

@?/rdbms/admin/awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and
configuration settings between two selected time periods.

@?/rdbms/admin/awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.

In order to run this reports you should have DBA role granted and you need to choose the snap id's interval based on your issue time.

awrrpt.sql is the most key common report and that is very helpful when you start investigating your journey on performance issues.