Ora 01017 invalid username password logon denied как исправить - Исправление недочетов и поиск решений вместе с Examum.ru

При входе в базу данных Oracle может выдаваться ошибка ORA-01017: invalid username/password; logon denied, хотя пароль при вводе набирается правильный. Причин может быть несколько, но в данном посте будет рассмотрена одна из них – инициализационный параметр sec_case_sensitive_logon.

Параметр sec_case_sensitive_logon позволяет включать или выключать чувствительность к регистру паролей в базе данных Oracle (БД). Параметр принимает два значения – TRUE или FALSE, при TRUE – пароли пользователей чувствительны к регистру, а при FALSE, соответственно, нет. Значение параметра sec_case_sensitive_logon можно просмотреть командой show parameter sec_case_sensitive_logon. Запрос ниже показывает, что параметр имеет значение TRUE. Это означает, что чувствительность к регистру паролей в БД включена.

SQL> show parameter sec_case_sensitive_logon;

NAME                       TYPE     VALUE
------------------------   -------  -------
sec_case_sensitive_logon   boolean  TRUE

Изменить значение параметра sec_case_sensitive_logon можно командой alter system set sec_case_sensitive_logon = false или alter system set sec_case_sensitive_logon = true. Команда ниже отключает чувствительность к регистру паролей.

SQL> alter system set sec_case_sensitive_logon = false;
System altered.

Начиная с версии Oracle Database 12.1.0.1, параметр sec_case_sensitive_logon считается устаревшим. Это значит, что Oracle не вносит в него дальнейших изменений, и пользователи не должны менять значение параметра. Значение по умолчанию TRUE. Если же значение будет изменено, то пользователь получит предупреждение при запуске БД:

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Также, начиная с Oracle Database 12c release 2 (12.2), по умолчанию версией протокола аутентификации является 12 (известный как Exclusive Mode). Этот протокол для аутентификации требует чувствительные к регистру пароли. Например, для Oracle Database 12c release 2 (12.2) значение по умолчанию для параметра SQLNET.ALLOWED_LOGON_VERSION_SERVER в файле SQLNET.ORA равно 12. Файл SQLNET.ORA по умолчанию находится в следующей директории операционной системы:

$ORACLE_HOME/network/admin

Параметр SQLNET.ALLOWED_LOGON_VERSION_SERVER отображает протокол аутентификации, используемый для сервера. И по умолчанию, Oracle больше не поддерживает пароли, не чувствительные к регистру – разрешены только новые версии паролей (11G и 12C). В связи с этим при входе в БД с значением FALSE для параметра sec_case_sensitive_logon можно получить ошибку:

ORA-01017: invalid username/password.

Данная ситуация возникает из-за того, что параметр sec_case_sensitive_logon имеет значение FALSE и параметр SQLNET.ALLOWED_LOGON_VERSION_SERVER имеет значение 12 или 12a. Oracle Database не запрещает использование значения FALSE параметра sec_case_sensitive_logon, когда значение SQLNET.ALLOWED_LOGON_VERSION_SERVER равно 12 или 12a. Но при таких условиях, все учетные записи кроме имеющих роль sysdba становятся недоступными. И именно такие настройки вызывают ошибку ORA-01017: invalid username/password. Есть два способа выхода из этой ситуации.

Первый способ – необходимо присвоить параметру sec_case_sensitive_logon значение TRUE. Это решение рекомендовано, так как обеспечивает более безопасные пароли. В этом случае не нужно будет менять пароли для учетных записей. Система будет поддерживать версии протоколов пароля 11g и 12c, которые используются учетными записями. Хотелось бы отметить, что версия протокола пароля не всегда равна версии Oracle Database. Например, далее в примерах используется Oracle Database 18c Express Edition и при этом используется версия протокола пароля 11g и 12с.

Вторым способом является присвоение параметру SQLNET.ALLOWED_LOGON_VERSION_SERVER в файле SQLNET.ora значение, ниже 12, например, 11 версию протокола аутентификации. Но это решение подразумевает необходимость смены паролей для всех пользователей БД с ролью, отличной от sysdba. Ниже в примерах показывается возникновение ошибки и ее решение двумя вышеописанными способами.

Пример 1. Возникновение ошибки при изменении параметра sec_case_sensitive_logon. Выполняется подключение к подключаемой базой данных (Pluggable Database – PDB) XEPDB1 Oracle Database 18c Express Edition под пользователем sys:

[oracle@dushanbe ~]$ sqlplus sys/sys@//dushanbe:1521/XEPDB1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:28:26 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Проверяется текущее значение параметра sec_case_sensitive_logon. Результат команды показывает, что параметр чувствительности к регистру пароля включен:

SQL> show parameter sec_case_sensitive_logon;

NAME TYPE VALUE
------------------------ ------- -------
sec_case_sensitive_logon boolean TRUE

Назначается пароль пользователю hr и выполняется выход из БД:

SQL> alter user hr identified by hr;
User altered.
SQL> exit
Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

Выполняется подключение к базе данных под пользователем hr.

[oracle@dushanbe ~]$ sqlplus hr/hr@//dushanbe:1521/XEPDB1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:30:00 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Last Successful login time: Tue Feb 23 2021 16:20:53 +05:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Подключение успешно прошло под пользователем hr.

Далее, выполняется отключение от базы под пользователем hr и подключение к контейнерной базе данных (Container Database – CDB) Oracle Dabase 18c Express Edition под пользователем sys.

SQL> exit
Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
[oracle@dushanbe ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:30:51 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Изменяется значение параметра sec_case_sensitive_logon на FALSE.

SQL> alter system set sec_case_sensitive_logon = false;
System altered.

Проверяется новое значение параметра sec_case_sensitive_logon.

SQL> show parameter sec_case_sensitive_logon;

NAME                       TYPE     VALUE
------------------------   -------  -------
sec_case_sensitive_logon   boolean  FALSE

Для информации: значение параметра sec_case_sensitive_logon в Oracle Database 18c Express Edition необходимо сменить в контейнерной базе данных, а не в подключаемой базе данных. В противном случае можно получить следующую ошибку:

ERROR at line 1: ORA-65040: operation not allowed from within a pluggable database

Далее, нужно подключиться к подключаемой базе данных под пользователем hr.

[oracle@dushanbe ~]$ sqlplus hr/hr@//dushanbe:1521/XEPDB1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:31:35 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.

При подключении система выдает ошибку, сообщающую о том, что был введен неверный логин или пароль.

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

Enter user-name:

Исправить данную ошибку можно, обратно сменив значение параметра sec_case_sensitive_logon на TRUE. Выполняется подключение к БД под учетной записью sys и запускается изменение значения параметра sec_case_sensitive_logon на TRUE.

[oracle@dushanbe ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:30:51 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> alter system set sec_case_sensitive_logon = true; 
System altered.
SQL> show parameter sec_case_sensitive_logon;

NAME                       TYPE     VALUE
------------------------   -------  -------
sec_case_sensitive_logon   boolean  TRUE

Проверяется, поможет ли возврат значения параметра успешно подключиться к базе данных. Подключение к БД происходит под пользователем hr еще раз.

[oracle@dushanbe ~]$ sqlplus hr/hr@//dushanbe:1521/XEPDB1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:32:58 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Last Successful login time: Wed Mar 03 2021 15:30:00 +05:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Как можно убедиться, подключение прошло без ошибок после возвращения значения на TRUE.

Пример 2. Возвращается параметру sec_case_sensitive_logon значение FALSE, чтобы смоделировать ошибку и показать второй способ решения. Выполняется подключение к БД под пользователем sys и меняется значение параметра sec_case_sensitive_logon на FALSE.

[oracle@dushanbe ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:30:51 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> alter system set sec_case_sensitive_logon = false;
System altered.
SQL> show parameter sec_case_sensitive_logon;

NAME                       TYPE     VALUE
------------------------   -------  -------
sec_case_sensitive_logon   boolean  FALSE

Ниже видно, что при попытке подключения под пользователем hr система выдает ту же ошибку – ORA-01017: invalid username/password; logon denied.

[oracle@dushanbe ~]$ sqlplus hr/hr@//dushanbe:1521/XEPDB1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:34:13 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.

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

Enter user-name:

Выполняется исправление ошибки другим способом. Осуществляется переход в папку $ORACLE_HOME/network/admin и проверяется ее содержимое.

[oracle@dushanbe ~]$ cd $ORACLE_HOME/network/admin
[oracle@dushanbe admin]$ ls -l
total 16
-rw-r-----. 1 oracle oracle    372 Jan 13 21:39 listener.ora
drwxr-xr-x. 2 oracle oinstall   64 Jan 13 21:36 samples
-rw-r--r--. 1 oracle oinstall 1441 Aug 27  2015 shrept.lst
-rw-r-----. 1 oracle oracle    228 Feb  1 16:23 sqlnet.ora
-rw-r-----. 1 oracle oracle    417 Jan 13 21:48 tnsnames.ora

На подключение к базе данных также влияет значение параметра SQLNET.ALLOWED_LOGON_VERSION_SERVER в файле sqlnet.ora. Как было сказано выше, по умолчанию для версий Oracle Database 12.2 и выше используется версия алгоритма пароля, равная 12. В Oracle Database 18с Express Edition, которая используется в данном примере, параметр SQLNET.ALLOWED_LOGON_VERSION_SERVER в файле sqlnet.ora отсутствует. Это значит, что БД использует версию алгоритма паролей равную 12 по умолчанию. Вручную, добавив строку SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 задается значение параметра равное 11. После этого содержимое файла sqlnet.ora выглядит следующим образом:

[oracle@dushanbe admin]$ cat sqlnet.ora 

#sqlnet.ora Network Configuration File: /opt/oracle/product/18c/dbhomeXE/network/admin/sqlnet.ora
#Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Пароли пользователей кроме имеющих роль sysdba должны быть изменены после изменения значения параметра SQLNET.ALLOWED_LOGON_VERSION_SERVER на 11 версию. Иначе они получат ошибку при входе, как показано в примере ниже.

[oracle@dushanbe admin]$ sqlplus hr/hr@//dushanbe:1521/XEPDB1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:37:55 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.

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

Выполняется подключение под пользователем sys и проверяется версия протоколов пароля пользователя hr:

[oracle@dushanbe admin]$ sqlplus sys/sys@//dushanbe:1521/XEPDB1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:38:36 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> select username, password_versions from DBA_USERS where username='HR';

USERNAME   PASSWORD_VERSIONS
---------  -----------------
HR         11G 12C

Результат выполнения команды показывает, что у hr до сих пор применяются версии протоколов пароля 11g и 12c. Необходимо сменить ему пароль, чтобы в данном случае исключить ошибку при входе пользователя. Для этого, изменяется пароль пользователю hr и проверяется версия паролей пользователя hr.

SQL> alter user hr identified by hr;
User altered.

SQL> select username, password_versions from DBA_USERS where username='HR';

USERNAME   PASSWORD_VERSIONS
---------  -----------------
HR         10G 11G 12C

После смены пароля выполняется подключение под пользователем hr. Ниже результат команды показывает, что подключение прошло успешно.

[oracle@dushanbe admin]$ sqlplus hr/hr@//dushanbe:1521/XEPDB1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 3 15:41:56 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Last Successful login time: Wed Mar 03 2021 15:32:58 +05:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

На этом завершается описание способов решения ошибки ORA-01017: invalid username/password; logon denied, связанной с параметром sec_case_sensitive_logon.

Источник

ORA-01017 means that you either provided an incorrect pair of username and password, or mismatched authentication protocol, so the database that you tried to connect denied you to logon.

SQL> conn hr/hr@orcl ERROR: ORA-01017: invalid username/password; logon denied

There’re several errors patterns that throw ORA-01017.

Common Situations
Connect to Oracle 19c
Database Links
Public Database Links
Standby Database
RMAN Duplication

Common Situations

For solving ORA-01017, you should inspect the following items carefully.

Connect Identifier

Sometimes, your credentials are correct, you just went for the wrong destination. So please check the connect identifier, and you can make some tests if necessary.

C:Usersedchen>tnsping orcl ... Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL))) OK (0 msec)

Password

Case-Sensitive

Most password problem are case-sensitive problem. By default, SEC_CASE_SENSITIVE_LOGON initialization parameter is set as TRUE which means that everything involves password are all case-sensitive, even though you didn’t quote the password.

SQL> conn hr/hr@orcl Connected. SQL> alter user hr identified by HR;


  User altered.
  SQL> conn hr/hr@orcl
 ERROR:
 ORA-01017: invalid username/password; logon denied

As you can see, IDENTIFIED BY clause treats password as a case-sensitive string with or without quotation marks. That is, you have to use it case-sensitively.

SQL> conn hr/HR@ora19cdb Connected.

Special Character

If your password contains any special character, you have to double-quote it.

SQL> alter user hr identified by "iam@home";


  User altered.
  SQL> conn hr/"iam@home"@ora19cdb
 Connected.

Other Considerations

Beside case-sensitive and special character problems, you can try the following things.

Check whether CAPS LOCK is enabled or not, this could ruin every password you typed.
Type the password in a text editor to make sure it’s right.
Change the password if there’s no other way to solve it.
Set SEC_CASE_SENSITIVE_LOGON to FALSE if the problem is becoming global.

Username

Normally, you don’t have to care about case-sensitive problem on username, because username creation follows Oracle object naming rule that I have explained about the differences between quoted and non-quoted identifiers pretty much.

Connect to Oracle 19c

After you set SQLNET.ALLOWED_LOGON_VERSION=8 in your 12c, 18c or 19c database server to overcome ORA-28040 for your users who may be using old releases like Oracle 9i clients, users still have a great chance to see ORA-01017, even though you use correct username / password to login.

This is because the password of the PDB users must be expired before applying new logon protocol.

Solution

You should expire user’s password who is using old Oracle client. For example, user HR in a PDB.

Normal Users

First, get into the PDB.

SQL> show pdbs


      CON_ID CON_NAME                       OPEN MODE  RESTRICTED
 ---------- ------------------------------ ---------- ----------
          2 PDB$SEED                       READ ONLY  NO
          3 ORCLPDB1                       READ WRITE NO
 SQL> alter session set container=ORCLPDB1;
  Session altered.
  SQL> show con_name
  CON_NAME
 ------------------------------
 ORCLPDB1

Let the password expire.

SQL> alter user hr password expire;


  User altered.

Provide a new password for the user, an uppercase password is preferable for old clients.

SQL> alter user hr identified by <PASSWORD> account unlock;


  User altered.

Privileged Users

For users like SYS or SYSTEM, it’s a little bit complicated.

First, go to the root container.

SQL> alter session set container=CDB$ROOT;


  Session altered.
  SQL> show con_name
  CON_NAME
 ------------------------------
 CDB$ROOT

Expire SYSTEM‘s password for all containers.

SQL> alter user system password expire container=all;


  User altered.

Provide a new password for SYSTEM, an uppercase password is preferable for old clients.

SQL> alter user system identified by <PASSWORD> account unlock;


  User altered.

You can try again now.

Database Links

Before troubleshooting error ORA-01017, please make sure that every basic portion of connection string that you provided is correct.

Let’s me show you how I reproduce ORA-01017 when connecting to a remote database via a database link, then I will explain the cause of problem.

In 12c, we set the password of a user by ALTER USER.

SQL> alter user scott identified by scott;

In 9i, we created a database link for connecting to the 12c database.

SQL> create database link ora12c_scott connect to scott identified by scott using 'ORA12C';


  Database link created.

Then we tested the connectivity of the database link.

SQL> select sysdate from dual@ora12c_scott; select sysdate from dual@ora12c_scott * ERROR at line 1: ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from ORA12C_SCOTT

Although it seemed nothing wrong with the statement, we got ORA-01017 eventually.

Rationale

In 12c, IDENTIFIED BY clause treats the non-quoted password as a case-sensitive string, which is lower-cased in this case. But in 9i, IDENTIFIED BY clause treats the non-quoted password as an upper-cased one, no matter what case it is in the statement. That’s the problem.

Generally speaking, non-quoted identifiers in Oracle should be recognized as upper-cased ones and quoted identifiers are regarded as whatever they are in quotation marks. So I think Oracle makes the password string an exception in order to comply with some security policies, which is starting from 11g.

Solution

To solve ORA-01017, we should make the password to be saved as a lower-case one in the database link. But how? Let’s keep going.

First of all, we dropped the incorrect database link in the 9i database.

SQL> drop database link ora12c_scott;


  Database link dropped.

Then we created the database link with the password quoted. The database link will save the password as it is in the double quotes.

SQL> create database link ora12c_scott connect to scott identified by "scott" using 'ORA12C';


  Database link created.

Then we tested the database link again.

SQL> select sysdate from dual@ora12c_scott;


  SYSDATE
 ---------
 17-DEC-19

This time, we succeeded.

Please note that, IDENTIFIED BY clause treats non-quoted password as case-sensitive one starting from 11g.

Pubic Database Links

In this case, I know you have created and tested a public database link, say BOSTON, but some user complained about that the database link does not work.

If you’re pretty sure the public database link can be successfully connected to the remote database, then you should check the user’s private database link and ask him/her to remove the same name private database link, BOSTON. To drop a private database link, you need to login as the owner.

The thing is, when an user tries to query a remote database via a database link, the private database link takes precedence over the public database link. And yes, the private and the public database link can have the same name, no name collision issue.

Standby Database

Before troubleshooting error ORA-01017, please make sure that every basic portion of connection string that you provided is correct.

When switching over to the standby database by DGMGRL, ORA-01017 may cause switchover to be interrupted. But luckily, the interruptions are not serious.

There’re two error patterns for ORA-01017 in DGMGRL Switchover, one is common mistake, the other is pretty hard to troubleshoot.

OS Authentication
Case-Sensitive Name

ORA-01017 due to OS Authentication

We are able to connect to databases by OS authentication to check data guard status.

[oracle@primary-19c ~]$ dgmgrl ... DGMGRL> connect / Connected to "PRIMDB" Connected as SYSDG.

For example, we can show broker configuration.

DGMGRL> show configuration verbose;


  Configuration - drconf
    Protection Mode: MaxPerformance
   Members:
   primdb - Primary database
     standb - Physical standby database
    Properties:
     FastStartFailoverThreshold      = '30'
     OperationTimeout                = '30'
     TraceLevel                      = 'USER'
     FastStartFailoverLagLimit       = '30'
     CommunicationTimeout            = '180'
     ObserverReconnect               = '0'
     FastStartFailoverAutoReinstate  = 'TRUE'
     FastStartFailoverPmyShutdown    = 'TRUE'
     BystandersFollowRoleChange      = 'ALL'
     ObserverOverride                = 'FALSE'
     ExternalDestination1            = ''
     ExternalDestination2            = ''
     PrimaryLostWriteAction          = 'CONTINUE'
     ConfigurationWideServiceName    = 'COMPDB_CFG'
  Fast-Start Failover:  Disabled
  Configuration Status:
 SUCCESS

Also, we can show the status of primary or standby databases.

DGMGRL> show database primdb;


  Database - primdb
    Role:               PRIMARY
   Intended State:     TRANSPORT-ON
   Instance(s):
     PRIMDB
  Database Status:
 SUCCESS
  DGMGRL> show database standb;
  Database - standb
    Role:               PHYSICAL STANDBY
   Intended State:     APPLY-ON
   Transport Lag:      0 seconds (computed 0 seconds ago)
   Apply Lag:          0 seconds (computed 0 seconds ago)
   Average Apply Rate: 1.00 KByte/s
   Real Time Query:    ON
   Instance(s):
     STANDB
  Database Status:
 SUCCESS

But we can’t perform a switchover.

DGMGRL> switchover to standb; Performing switchover NOW, please wait... Operation requires a connection to database "standb" Connecting ... ORA-01017: invalid username/password; logon denied


  Warning: You are no longer connected to ORACLE.
          connect to instance "STANDB" of database "standb"

Since we provided empty credentials, the broker used the empty username and password to connect to the standby database for a switchover. That’s why we got ORA-01017.

It just like we try to connect to a database with empty credentials like this:

[oracle@primary-19c ~]$ sqlplus /@standb ... ERROR: ORA-01017: invalid username/password; logon denied

It’s the same pattern of ORA-01017.

Solution

Please explicitly provide user credentials (username/password pair) to connect to database in DGMGRL. Usually, we use SYS to operate switchovers in DGMGRL.

DGMGRL> connect sys@primdb; Password: Connected to "PRIMDB" Connected as SYSDBA.

Then we perform a switchover.

DGMGRL> switchover to standb; Performing switchover NOW, please wait... Operation requires a connection to database "standb" Connecting ... Connected to "STANDB" Connected as SYSDBA. New primary database "standb" is opening... Operation requires start up of instance "PRIMDB" on database "primdb" Starting instance "PRIMDB"... Connected to an idle instance. ORACLE instance started. Connected to "PRIMDB" Database mounted. Database opened. Connected to "PRIMDB" Switchover succeeded, new primary is "standb"

Good, no more ORA-01017.

ORA-01017 due to Case-Sensitive Name

This error case could be rare and complicated, but we can still learn something from it. So be patient with it.

Data Guard Configuration

Let’s check some data guard configurations before switching over to the standby database.

Check Static Service for DGMGRL in listener.ora

For switching over smoothly, we usually add a very special static service to listener for data guard broker.

Primary Server

... SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=primdb) (GLOBAL_DBNAME=primdb_DGMGRL) (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1) ) )

Standby Server

[oracle@standby-19c ~]$ cat $ORACLE_HOME/network/admin/listener.ora ... SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=standb) (GLOBAL_DBNAME=standb_DGMGRL) (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1) ) )

Check Listener Status

Primary Server

[oracle@primary-19c ~]$ lsnrctl status ... Services Summary... Service "COMPDB_CFG" has 1 instance(s). Instance "PRIMDB", status READY, has 1 handler(s) for this service... Service "PRIMDB" has 1 instance(s). Instance "PRIMDB", status READY, has 1 handler(s) for this service... Service "PRIMDBXDB" has 1 instance(s). Instance "PRIMDB", status READY, has 1 handler(s) for this service... Service "primdb_DGMGRL" has 1 instance(s). Instance "primdb", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

Standby Server

[oracle@standby-19c ~]$ lsnrctl status ... Services Summary... Service "COMPDB_CFG" has 1 instance(s). Instance "STANDB", status READY, has 1 handler(s) for this service... Service "STANDB" has 1 instance(s). Instance "STANDB", status READY, has 1 handler(s) for this service... Service "STANDBXDB" has 1 instance(s). Instance "STANDB", status READY, has 1 handler(s) for this service... Service "standb_DGMGRL" has 1 instance(s). Instance "standb", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

As we can see, the special static services for DGMGRL are working on both listeners.

Switchover by DGMGRL

First of all, we have to connect to the primary database in DGMGRL.

Connect to Data Guard Broker

[oracle@primary-19c ~]$ dgmgrl ... DGMGRL> connect sys@primdb Password: Connected to "PRIMDB" Connected as SYSDBA.

First Attempt of Switchover

DGMGRL> switchover to standb; Performing switchover NOW, please wait... Operation requires a connection to database "standb" Connecting ... Connected to "STANDB" Connected as SYSDBA. New primary database "standb" is opening... Operation requires start up of instance "PRIMDB" on database "primdb" Starting instance "PRIMDB"... ORA-01017: invalid username/password; logon denied


   Please complete the following steps to finish switchover:
         start up instance "PRIMDB" of database "primdb"

We found ORA-01017 during switching over. It seemed that the broker tried to connect to the new standby database (former primary) PRIMDB, but it failed with ORA-01017.

No matter what cause it could be, we should startup the new standby database for data synchronization.

Startup Standby Database

We startup the instance manually by running dbstart in order to recover the data guard synchronization.

[oracle@primary-19c ~]$ dbstart Since ORACLE_HOME is not set, cannot auto-start Oracle Net Listener. Usage: /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart ORACLE_HOME Processing Database instance "PRIMDB": log file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/startup.log

By the way, we usually use dbstart to automatically startup the instance and the listener on system boot.

Then we checked the status.

[oracle@primary-19c ~]$ sqlplus / as sysdba ... SQL> select name, open_mode, database_role from v$database;


  NAME      OPEN_MODE            DATABASE_ROLE
 --------- -------------------- ----------------
 COMPDB    READ ONLY WITH APPLY PHYSICAL STANDBY

Please note that, READ ONLY WITH APPLY is a feature of active data guard, which is called real-time query.

DGMGRL Problem Tracing

Here comes the most important part. For tracing the connection problem that caused ORA-01017 in DGMGRL, we enabled the debug mode.

Connect to Data Guard Broker

We added -debug option for DGMGRL utility to enable debug mode.

[oracle@primary-19c ~]$ dgmgrl -debug Created directory /u01/app/oracle/product/19.0.0/dbhome_1/dataguard DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Aug 13 21:22:52 2019 Version 19.3.0.0.0


  Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  Welcome to DGMGRL, type "help" for information.
 DGMGRL> connect sys@primdb
 Password:
 [W000 2019-08-13T21:23:03.360-08:00] Connecting to database using primdb.
 [W000 2019-08-13T21:23:03.361-08:00] Attempt logon as SYSDBA
 [W000 2019-08-13T21:23:03.421-08:00] Successfully logged on as SYSDBA
 [W000 2019-08-13T21:23:03.421-08:00] Executing query [select sys_context('USERENV','CON_ID') from dual].
 [W000 2019-08-13T21:23:03.423-08:00] Query result is '0'
 [W000 2019-08-13T21:23:03.423-08:00] Executing query [select value from v$parameter where name = 'db_unique_name'].
 [W000 2019-08-13T21:23:03.430-08:00] Query result is 'PRIMDB'
 Connected to "PRIMDB"
 [W000 2019-08-13T21:23:03.431-08:00] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
 [W000 2019-08-13T21:23:03.440-08:00] Oracle database version is '19.3.0.0.0'
 Connected as SYSDBA.

We’re in debug mode of DGMGRL.

Second Attempt of Switchover

In fact, it’s a switch back operation within debug mode.

DGMGRL> switchover to primdb; [W000 2019-08-13T21:23:39.512-08:00] <DO_MONITOR version="19.1"><VERIFY object_id="4096" level="minor"/></DO_MONITOR>


   [W000 2019-08-13T21:23:39.555-08:00] <RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
   Performing switchover NOW, please wait...
 [W000 2019-08-13T21:23:39.563-08:00] <DO_CONTROL version="19.1"><DO_MOVE type="Switchover" site_id="16777216"/></DO_CONTROL>
   [W000 2019-08-13T21:24:09.465-08:00] <TABLE  name="DG BROKER CLIENT OPERATION LIST"><DESCRIPTION ><COLUMN  name="OPERATION" type="string" max_length="20"></COLUMN><COLUMN  name="INSTANCE_ID" type="integer" max_length="30"></COLUMN><COLUMN  name="CONNECT" type="string" max_length="4095"></COLUMN></DESCRIPTION><TR ><TD >OPENING</TD><TD >16842753</TD><TD >(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary-19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRIMDB_DGMGRL)(INSTANCE_NAME=PRIMDB)(SERVER=DEDICATED)))</TD></TR><TR ><TD >STARTUP</TD><TD >33619969</TD><TD >(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby-19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDB_DGMGRL)(INSTANCE_NAME=STANDB)(SERVER=DEDICATED)))</TD></TR></TABLE>
   New primary database "primdb" is opening...
 Operation requires start up of instance "STANDB" on database "standb"
 Starting instance "STANDB"...
 [W000 2019-08-13T21:24:12.470-08:00] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby-19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDB_DGMGRL)(INSTANCE_NAME=STANDB)(SERVER=DEDICATED))).
 [W000 2019-08-13T21:24:12.470-08:00] Attempt logon as SYSDBA
 ORA-01017: invalid username/password; logon denied
   Please complete the following steps to finish switchover:
         start up instance "STANDB" of database "standb"

In the above log, the broker tried to connect to the standby database STANDB, but it failed with ORA-01017. The best thing is that it showed the connect descriptor that it used to connect to the database.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby-19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDB_DGMGRL)(INSTANCE_NAME=STANDB)(SERVER=DEDICATED)))

Please note that, the service name used to connect is a static service. That is to say, we can connect the database with the static service, no matter it is idle, nomount or restricted as long as the listener is up.

Test the Connect Descriptor

We used sqlplus to test the connect descriptor. Let’s see how I use it.

[oracle@primary-19c ~]$ sqlplus sys@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby-19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDB_DGMGRL)(INSTANCE_NAME=STANDB)(SERVER=DEDICATED)))" as sysdba


  SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 13 21:28:30 2019
 Version 19.3.0.0.0
  Copyright (c) 1982, 2019, Oracle.  All rights reserved.
  Enter password:
 ERROR:
 ORA-01017: invalid username/password; logon denied
   Enter user-name:

Oh, it’s true, I cannot connect to the database with the connect descriptor. The connection failed with ORA-01017.

It looked like that it can reach the right listener and find the right Oracle home (ORACLE_HOME), but it cannot find the right instance (SID_NAME) to enter.

What’s wrong? What the difference between the connect descriptor and the listener configuration? Can you tell?

Startup Standby Database

Although we are in troubleshooting, we still need to startup the instance as soon as possible in order to recover the data guard synchronization.

[oracle@standby-19c ~]$ dbstart Since ORACLE_HOME is not set, cannot auto-start Oracle Net Listener. Usage: /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart ORACLE_HOME Processing Database instance "STANDB": log file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/startup.log [oracle@standby-19c ~]$ sqlplus / as sysdba ... SQL> select name, open_mode, database_role from v$database;


  NAME      OPEN_MODE            DATABASE_ROLE
 --------- -------------------- ----------------
 COMPDB    READ ONLY WITH APPLY PHYSICAL STANDBY

Solution

A tiny difference I found is that the names used in the connect descriptor by the broker are all upper-cased, but the names for static service in listener.ora are lower-cased. Could it be the cause to ORA-01017?

Modify listener.ora

It seemed a little stupid, but I still changed all names in upper-case on both listener configurations to comply with the broker’s behaviors.

Primary Server

[oracle@primary-19c ~]$ vi $ORACLE_HOME/network/admin/listener.ora ... SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=PRIMDB) (GLOBAL_DBNAME=PRIMDB_DGMGRL) (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1) ) )

Then we restarted the listener.

[oracle@primary-19c ~]$ lsnrctl stop; lsnrctl start ... [oracle@primary-19c ~]$ lsnrctl status ... Services Summary... Service "COMPDB_CFG" has 1 instance(s). Instance "PRIMDB", status READY, has 1 handler(s) for this service... Service "PRIMDB" has 1 instance(s). Instance "PRIMDB", status READY, has 1 handler(s) for this service... Service "PRIMDBXDB" has 1 instance(s). Instance "PRIMDB", status READY, has 1 handler(s) for this service... Service "PRIMDB_DGMGRL" has 1 instance(s). Instance "PRIMDB", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

Standby Server

[oracle@standby-19c ~]$ vi $ORACLE_HOME/network/admin/listener.ora ... SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=STANDB) (GLOBAL_DBNAME=STANDB_DGMGRL) (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1) ) )

Then we restarted the listener.

[oracle@standby-19c ~]$ lsnrctl stop; lsnrctl start ... [oracle@standby-19c ~]$ lsnrctl status ... Services Summary... Service "COMPDB_CFG" has 1 instance(s). Instance "STANDB", status READY, has 1 handler(s) for this service... Service "STANDB" has 1 instance(s). Instance "STANDB", status READY, has 1 handler(s) for this service... Service "STANDBXDB" has 1 instance(s). Instance "STANDB", status READY, has 1 handler(s) for this service... Service "STANDB_DGMGRL" has 1 instance(s). Instance "STANDB", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

Third Attempt of Switchover

[oracle@primary-19c ~]$ dgmgrl ... DGMGRL> connect sys@primdb Password: Connected to "PRIMDB" Connected as SYSDBA. DGMGRL> switchover to standb; Performing switchover NOW, please wait... Operation requires a connection to database "standb" Connecting ... Connected to "STANDB" Connected as SYSDBA. New primary database "standb" is opening... Operation requires start up of instance "PRIMDB" on database "primdb" Starting instance "PRIMDB"... Connected to an idle instance. ORACLE instance started. Connected to "PRIMDB" Database mounted. Database opened. Connected to "PRIMDB" Switchover succeeded, new primary is "standb"

Good, we switch over to the standby database smoothly. Try to switch back to the primary database.

DGMGRL> switchover to primdb; Performing switchover NOW, please wait... Operation requires a connection to database "primdb" Connecting ... Connected to "PRIMDB" Connected as SYSDBA. New primary database "primdb" is opening... Operation requires start up of instance "STANDB" on database "standb" Starting instance "STANDB"... Connected to an idle instance. ORACLE instance started. Connected to "STANDB" Database mounted. Database opened. Connected to "STANDB" Switchover succeeded, new primary is "primdb"

To my surprise, ORA-01017 in this case turned out to be an instance name mismatch problem caused by case-sensitive SID_NAME in listener.ora.

Please note that, the key action to troubleshoot the problem is to enable debug mode of DGMGRL to check any clues related to the database connection.

RMAN Duplication

Before troubleshooting error ORA-01017, please make sure that every portion of connection string that you provided is correct.

I tried to connect both primary and standby database by RMAN. But it failed with ORA-01017 error.

[oracle@primary01 ~]$ rman target sys@primdb auxiliary sys@standb


  Recovery Manager: Release 11.2.0.4.0 - Production on
  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
  target database Password:
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-00554: initialization of internal recovery manager package failed
 RMAN-04005: error from target database:
 ORA-01017: invalid username/password; logon denied

I am pretty sure that my parameters are fine, so here is my solution: overwrite current password of sys, and then transport the password file to the standby server.

[oracle@primary01 ~]$ sqlplus / as sysdba ... SQL> show parameter password


  NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 remote_login_passwordfile            string      EXCLUSIVE
  SQL> alter user sys identified by password;
  User altered.

Transport the password file to the standby server.

[oracle@standby01 ~]$ scp -p primary01:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwcompdb /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/

Now, we can connect both database by RMAN.

Источник

Bonus Content: Download the Oracle SQL Cheat Sheet

Have you gotten an ORA-01017 error in your Oracle database? Learn what caused it and how you can resolve it in this article.

So you’re trying to connect to a database or run a query across a database link, and you get this message:

ORA-01017: invalid username/password; logon denied

There are a few reasons that you can get this error:

Your username or password is actually incorrect
The database configuration is not correct (tnanames.ora, $ORACLE_SID parameter)

Now, let’s look at the solution to this error.

ORA-01017 Solution

There are a few ways to resolve the ORA-01017 error:

Check the username and password are correct
Oracle 11g passwords are case sensitive, so ensure that your connection string caters for this
Check the database link setup if you’re using a database link

Ensure that the Username and Password are Correct

It seems like an obvious step, but make sure that the username and password you’re entering are correct.

If you have them stored somewhere else, make sure you’re looking up the right password.

Also, try entering the username and password manually instead of copying and pasting (or vice versa) to make sure there are no issues with carriage returns or strange characters.

Case Sensitive Passwords in Oracle 11g

If you’re connecting to an Oracle 11g database, the passwords are case-sensitive.

This was a new feature introduced in Oracle 11g. It’s a database setting that can be turned on and off, but if it’s on, you’ll need to treat your connection string differently.

To fix this, you can specify your password inside double quotes when connecting to the database.

For example, if your password is “DatabaseStar”, and you’re connecting to a database like this, you’ll get an error:

CONN bob/databasestar;

To resolve this error, specify the password in double quotes:

CONN bob/"DatabaseStar";

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

Check the Database Link Setup

If you’re getting this error while running a query, but you’ve already logged in to the database, it might be because your query uses a database link, and the connection details for the database link are incorrect.

To check the database link, you can run a simple query against it to see if it works.

You can then check the connection string to make sure that the user is correct, and that the password is case sensitive (if you’re using an Oracle 11g database).

If you’re connecting to an Oracle 11g database but running the query from a 10g or earlier version, the case sensitivity still applies. It’s related to the database you’re connecting to, not from.

Using the earlier example, if you specify your password in the connection string as DatabaseStar, you’ll get an error.

This is because the password of DatabaseStar is converted to uppercase, which will be DATABASESTAR. This is then checked against the stored value of DatabaseStar, and is found to be different.

To resolve it, put your password inside double quotes.

CONNECT TO order_database IDENTIFIED BY bob USING "DatabaseStar";

So, check these things for your database connection string.

Check Your TNSNAMES.ORA File

Your TNSNAMES.ORA file contains a list of service ID information for your databases.

You could be getting this ORA-01017 error if the information in that file is incorrect.

Open the file and check that this information is correct, such as the service name and ID.

So, that’s how you can check for and resolve any ORA-01017 invalid username/password issues.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

Источник

I’m trying to connect to a schema on 11g (v11.2.0.1.0) from a PC with 9i (v9.2.0.1) client. It seems to connect fine to some schemas, but not this one — it comes back with a ORA-01017 Invalid Username/Password error every time.

The username and password are DEFINITELY correct — can anyone think of a reason why this wouldn’t work?

Are there any fundamental incompatibilities between 9i and 11g?

asked Jan 23, 2013 at 9:59

user1578653user1578653

4,83816 gold badges45 silver badges74 bronze badges

for oracle version 12.2.x users cannot login using case insensitive passwords, even though SEC_CASE_SENSITIVE_LOGON = FALSE if PASSWORD_VERSIONS of user is not 10g.

following sql should show the PASSWORD_VERSIONS for a user.

select USERNAME,ACCOUNT_STATUS,PASSWORD_VERSIONS from dba_users;
USERNAME          ACCOUNT_STATUS    PASSWORD_VERSIONS 
---------------   --------------    -----------------
dummyuser         OPEN              11G 12C

to make PASSWORD_VERSIONS compatible with 10g

add/modify line in sqlnet.ora of database to have SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
restart database
change/expire password for existing user
new users created will also have same settings
after above steps PASSWORD_VERSIONS should be something like this

select USERNAME,ACCOUNT_STATUS,PASSWORD_VERSIONS from dba_users;
USERNAME          ACCOUNT_STATUS    PASSWORD_VERSIONS 
---------------   --------------    -----------------
dummyuser         OPEN              10G 11G 12C

answered Jul 27, 2017 at 5:22

ManishSinghManishSingh

99610 silver badges9 bronze badges

I had a similar issue some time ago. You must be careful with quotes and double quotes. It’s recommended to reset the user password, using a admin credentials.

ALTER USER user_name IDENTIFIED BY new_password;

But don’t use double quotes in both parameters.

answered Jan 14, 2016 at 15:55

TheGabiRodTheGabiRod

4184 silver badges14 bronze badges

If all else fails, try resetting the password to the same thing. I encountered this error and was unable to work around it, but simply resetting the password to the same value resolved the problem.

answered Sep 3, 2013 at 9:34

lucrusselllucrussell

5,0022 gold badges32 silver badges38 bronze badges

You may connect to Oracle database using sqlplus:

sqlplus "/as sysdba"

Then create new users and assign privileges.

grant all privileges to dac;

answered Mar 26, 2016 at 17:51

Lay LeangsrosLay Leangsros

9,1067 gold badges34 silver badges39 bronze badges

Oracle 11 G and 12 C versions suggest to use more complex passwords, Although there is no issues during the user creation. The password must be alphanumeric and with special character.

Verify the password version and status of the user:

select * from dba_users where username = <user_name>;

Amend it to be like below in case of 11G 12C:

alter user <user_name> identified by Pass2019$;

Now test connection!

answered Jan 22, 2019 at 17:59

ramram

2,2453 gold badges26 silver badges38 bronze badges

I had the same issue and put double quotes around the username and password and it worked:
create public database link «opps» identified by «opps» using ‘TEST’;

answered Oct 18, 2013 at 17:33

I am not an expert. If you are getting ORA-01017 while trying to connect HR schema from SQL Developer in Oracle 11g
Please try to unlock the HR as follows

alter user HR identified by hr
DEFAULT tablespace users
temporary tablespace temp
account unlock;

answered Jan 9, 2014 at 10:00

I had the same error, but while I was connected and other previous statements in a script ran fine before! (So the connection was already open and some successful statements ran fine in auto-commit mode)
The error was reproducable for some minutes. Then it had just disappeared.
I don’t know if somebody or some internal mechanism did some maintenance work or similar within this time — maybe.

Some more facts of my env:

11.2
connected as: sys as sysdba
operations involved … reading from all_tables, all_views and granting select on them for another user

answered Oct 11, 2016 at 12:48

Andreas CovidiotAndreas Covidiot

4,1985 gold badges50 silver badges95 bronze badges

I had a similar issue. The id/pw I was using was correct, but I was getting ORA-01017 Invalid Username/Password on one particular connection only.

It turned out that my connection settings in Oracle SQL Developer had the «Use DB Proxy Authentication» turned on in the «Proxy User» tab. I didn’t notice that for quite a while.

After I unchecked that option, it worked fine.

answered Apr 6, 2021 at 22:12

Glen LittleGlen Little

6,8934 gold badges46 silver badges68 bronze badges

I also had the similar problem recently with Oracle 12c. It got resolved after I changed the version of the ojdbc jar used. Replaced ojdbc14 with ojdbc6 jar.

answered Sep 23, 2020 at 8:27

I face the same issue for connection in laravel 7 I face issue that my password is used in charater base when the password goes to the connect file is not go incomplete from .so I give the string password in my oci8.php file

answered May 11, 2022 at 11:33

The tip on Oracle’s OTN =
Don’t type your password in TOAD when you try to connect and let it popup a dialog box for your password. Type the password in there and it will work. Not sure what they’ve done in TOAD with passwords but that is a workaround. It has to do with case sensitive passwords in 11g. I think if you change the password to all upper case it will work with TOAD.
https://community.oracle.com/thread/908022

answered Jun 29, 2015 at 8:57

ridiridi

1629 bronze badges

I also got the same sql error message when connecting through odp.net via a Proxy User.

My error was that my user was created with quotation marks (e.g. «rockerolf») and I then also had to specify my user in the connectionstring as User Id=»rockerolf»..

In the end I ended up deleting the user with the quotation marks and create a new one without..

face palm

answered Feb 29, 2016 at 12:05

gautejohangautejohan

4285 silver badges8 bronze badges

I had a similar problem recently with Oracle 12c. I created a new user with a lower case password and was able to login fine from the database server but all clients failed with an ORA-01017.
The fix turned out to be simple in the end (reset the password to upper case) but took a lot of frustrating effort to get there.

answered Sep 22, 2017 at 21:07

Jim HJim H

1061 silver badge3 bronze badges

Credentials may be correct and something else wrong. I based my pluggable DB connection string on its container DB. Instead of the original parent.example.com service name the correct appeared to be pluggable.example.com.

answered Nov 6, 2017 at 15:10

Jan MolnárJan Molnár

4107 silver badges14 bronze badges

I know this post was about 11g, but a bug in the 12c client with how it encrypts passwords may be to blame for this error if you decide to use that one and you:

Don’t have the password case-sensitivity issue (i.e. you tried ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE and resetting the password and still doesn’t work),
Put quotes around your password in your connection string and it still doesn’t help,
You’ve verified all of your environmental variables (ORACLE_HOME, PATH, TNS_ADMIN), and the TNS_ADMIN registry string at HKLMSoftwareOracleKEY_OraClient12Home is in place,
You’ve verified your connection string and user name/password combination works in Net Manager, and
You can connect using SQL*Plus, Oracle SQL Developer using the same credentials.

All the basic checks.

Fix: Try setting HKLMSystemCurrentControlSetControlLsaFIPSAlgorithmPolicyEnabled to 0 in the registry (regedit) to disable FIPS.

Oracle.ManagedDataAccess and ORA-01017: invalid username/password; logon denied

ORA-01005 error connecting with ODP.Net

https://community.oracle.com/thread/2557592?start=0&tstart=0

https://dba.stackexchange.com/questions/142085/ora-01017-invalid-username-passwordlogon-denied/142149#142149

answered Jun 24, 2016 at 14:26

vapcguyvapcguy

7,0131 gold badge55 silver badges50 bronze badges

in my case with oracle database using tableplus build 373 on my m1 device
type username and password in another text editor, copy and paste into username and password in tableplus. when i type in tableplus the text automatically changes to lowercase. because i had no problem with sqldeveloper, but when trying tableplus it always refused now i have solved it. i dunno why its problem on apps or other, but i have solved by the trick.

answered Jun 29, 2021 at 0:42

Long time to answer, but I suggest to add

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

into sqlnet.ora

That will fix your issue.

markalex

6,1301 gold badge5 silver badges27 bronze badges

answered Mar 15 at 8:12

Источник

ORA-01017: invalid username/password; logon denied error occurs when an invalid username or password was submitted in an attempt to connect on to Oracle. The ORACLE username and password should be the same as those specified in the GRANT CONNECT statement. The connect statement uses the username/password format for username and password. It’s possible that the username, password, or both are incorrect. The error ORA-01017: invalid username/password; logon denied will be fixed if the correct user name and password are used to connect to the Oracle server.

While attempting to connect to the database, the user name or password may be invalid or misspelt. Passwords in Oracle 11g and later are case sensitive. It’s possible that the password was created before to 11g, and the error ORA-01017: invalid username/password; logon denied occurred after the update. The error will be fixed if you define the password as case sensitive. It’s possible that the connection string contains incorrect database connection information. In the database link setup, double-check all of the settings.

The Problem

The database connection string is used to connect to the database in Oracle when you want to make a database connection. Oracle database could not create a connection using the string if improper connection settings were added to connect to the database. It’s possible that the user name or password is wrong. It’s possible that the database settings or database connection link setup is improper or faulty. The error ORA-01017: invalid username/password; logon denied is going to be thrown.

[oracle@localhost ~]$ sqlplus sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 6 20:37:22 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> conn hr/hr1
ERROR:
ORA-01017: invalid username/password; logon denied


SQL>

Solution 1

An error ORA-01017: invalid username/password; logon denied will appear if you attempt to connect to an Oracle database with an incorrect password. It’s possible that the password is wrong or that the password has been deactivated. The oracle database connection could not be made if you used an invalid password. Double-check that the password is accurate. If you forget your password, use the system user password to reset it. Alternatively, you can contact your database administrator to have the database user password reset.

[oracle@localhost ~]$ sqlplus sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 6 20:37:22 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter user hr identified by hr;

User altered.

SQL>

Solution 2

The Oracle database will give an error if you supply an incorrect user name. To connect to the Oracle database, the correct user name must be specified in the connection command. The error ORA-01017: invalid username/password; logon denied will be issued if an incorrect user name is provided, or if the user name does not exist or was not created in the Oracle database. Ascertain that the database user exists and is accessible in the Oracle database.

select USERNAME, PASSWORD_VERSIONS from DBA_USERS where USERNAME='HR';

Output

USERNAME PASSWORD_VERSIONS
---------------------------
HR	11G 12C

Solution 3

It’s possible that you’re using an earlier version of Oracle than Oracle 11g. You’ve now updated to Oracle 11g or higher. The database fails to create a database connection with the same database credentials after the update. From Oracle 11g onwards, the password is case sensitive. If you’re using an older Oracle version, you can use case-insensitive passwords. Since the update version, the password has been failing. The error ORA-01017: invalid username/password; logon denied will be resolved if the password is changed to be case sensitive or if the existing password is reset.

show parameter sec_case;

Output

NAME                     TYPE    VALUE 
------------------------ ------- ----- 
sec_case_sensitive_logon boolean TRUE

[oracle@localhost ~]$ sqlplus sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 6 20:37:22 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> conn hr/hr

Solution 4

If you’re experiencing this error ORA-01017: invalid username/password; logon denied while executing a query but have previously signed in to the database, it’s possible that your query utilises a database link and the database link’s connection details are wrong. To determine if the database link is functional, perform a basic query against it. Check the following details before connecting to the database

hostname     : localhost
port         : 1521
sid          : orcl  (any one Sid or service name)
service name : xe
user name    : hr
password     : hr

Solution 5

A list of service ID information for your databases may be found in the tnsnames.ora file. Open the file and double-check that the information, such as the service name and ID, is valid. Make any required adjustments to the database connection string or the tnsnames.ora file if any information from the file differs from the database connection string.This will resolve the error ORA-01017: invalid username/password; logon denied

Источник