Oracle error 1017

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.

1. Common Situations
2. Connect to Oracle 19c
3. Database Links
4. Standby Database
5. 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;

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";

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

Let the password expire.

SQL> alter user hr password expire;

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

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

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;

Expire SYSTEM‘s password for all containers.

SQL> alter user system password expire container=all;

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

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

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';

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 make the password 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;

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';

Then we tested the database link again.

SQL> select sysdate from dual@ora12c_scott;

This time, we succeeded.

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

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.

1. OS Authentication
2. Case-Sensitive Name

1. 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;

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

DGMGRL> show database primdb;

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

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.

2. 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

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;

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

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>

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

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;

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

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

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.

Troubleshooting ORA-1031 or ORA-1017 Errors When Connecting As SYSDBA (Doc ID 730067.1)

Last updated on DECEMBER 16, 2022

Applies to:

Purpose

This document is intended to supply some commonly used steps for diagnosing errors ORA-1031 or ora-1017 encountered while connecting as sysdba.

In the most recent versions the error for this kind of authentication failures was changed from ORA-01031 to ORA-01017 , this was done to consistently return the error «ORA-01017: invalid username/password; logon denied» for all authentication failures and ORA-01031 for all insufficient privileges errors occuring when already connected to the database.

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Security Products.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

Ask Questions, Get Help, And Share Your Experiences With This Article

SYSDBA Password File Authentication

Troubleshooting ORA-1031 when connecting as SYSDBA using OS Authentication

1. Check whether the OS user is part of the OSDBA group.

2. Check the value of the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA

ORA-12638 might be seen when the user is managed in Active Directory rather than locally

Troubleshooting ORA-1031 seen while using password file authentication

1.Check the value of parameter remote_login_passwordfile. This has to be set to either EXCLUSIVE or SHARED:

2. Check whether the password file with the correct name exists in the right directory/folder and has the right ownership and permissions

3. Check whether the user was granted the SYSDBA privilegeВ

4. Check that there is no issue with the configuration of the listener used to connect to the database.

Frequent problems with passwordfile authentication

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-01017: invalid username/password; logon denied WARNING: ASM communication error: op 18 state 0x40 (1017) (Doc ID 2292526.1)

Last updated on MAY 25, 2021

Applies to:

Symptoms

Database startup failed with

ORA-01017: invalid username/password; logon denied
WARNING: ASM communication error: op 18 state 0x40 (1017)
ERROR: slave communication error with ASM
NOTE: Deferred communication with ASM instance
Errors in file /diag/rdbms/ / /trace/ _pmon_266738.trc:

Changes

Customer added oracle user to a new group

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

1017 error in oracle

The Oracle Database 12 c 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 12 c release 2 (12.2), the default value for the SQLNET.ORA parameter ALLOWED_LOGON_VERSION_SERVER is changed to 12 . This parameter refers to the logon 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.

If the following conditions are true, then you may have accounts that are prevented from logging into the database after upgrading to 12.2:

You are upgrading a server that has user accounts created in an earlier Oracle Database release.

User accounts created in the earlier release use a case-insensitive password version from an earlier release authentication protocol, such as the 10G password version.

Earlier release user accounts have not reset passwords.

The server has been configured with SEC_CASE_SENSITIVE_LOGON set to FALSE, so that it can only authenticate users who have a 10G case-insensitive password version.

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.

Oracle does not support case-insensitive password-based authentication while running in an Exclusive Mode. The default authentication protocol in Oracle Database 12 c release 2 (12.2) is an Exclusive Mode. Oracle only supports case-insensitive authentication with the following conditions:

The server is running in a mode other than an Exclusive Mode

The 10G password version is present

Option for Servers with Accounts Using Only 10G Password Version

After you upgrade to Oracle Database 12 c release 2 (12.2), complete the following procedure to enable accounts using the 10G password version:

Источник

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

При входе в базу данных 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. Это означает, что чувствительность к регистру паролей в БД включена.

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

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

Также, начиная с 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 по умолчанию находится в следующей директории операционной системы:

Параметр 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:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На подключение к базе данных также влияет значение параметра 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 выглядит следующим образом:

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

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

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

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

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

Источник

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

In my previous articles, I have given the basic idea about the most common errors in Oracle. There are lot of common errors in oracle, which has been searched thousands of times on google.OrA-01017 is most common error which has been search approximately 15000 times on google.ORA-01017 is very common error occurred during the log in of oracle application. When user enters the wrong username and password system denies logging in and oracle fires the ORA-01017: invalid username/password error. That is not the only reason for which this error will come. There might be several possible reasons for this error like, permissions (grants), tnsnames.ora, improper connection string or configuration of sqlnet.ora file.

Why this error will come?

  The ORA-01017 error is another most common error that will come because of wrong username and password entry. Most of the times the user will get this error in spite of entering correct username and password. Following is the list of possible reasons of this error:

1.Wrong username or password:

The most possible reason of this error is entering wrong username or entering wrong password while connecting with the database.

2.Entering Incorrect user ID:

The userid must present in dba_users table.If the userid is not present in this table then the 01017 error will come.

3.ORACLE_SID parameters :

DBA needs to check ORACLE_SID parameters.If the ORACLE_SID parameters set to wrong system id this error will come

4.tnsnames.ora file issue :

Check the tnsnames.ora file to ensure that tns server will point to correct server.

5.External user authentication :

If you are using external OS user authentication then we need to verify the valid user id.

 NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

Resolution of this error:

User needs to check different parameters to resolve this error. Following are the possible resolutions of this error:

1.Enter correct username and password:

The first and most important possible solution to resolve this error is try entering correct username and password and try to connect the database server. Most of the times because of entering wrong password this error will come. Try altering password using following statement:

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

2.Privilege issue:

There might be the user privilege issue for that user. So try giving all privileges to the user, which you tires to connect. You can use following statement to give all privileges:

  Grant all to username;

3.Correct ORACLE_SID parameters :

ORACLE_SID parameter setting is also most critical part to resolve this error.So try setting correct system id for ORACLE_SID environmental parameters.

4.Check user is Entering correct username :

Most of times user is not entering correct username while connecting to the database. For checking the username use following statement :

Select user_name from dba_users where user_name=’Name of user entered to connect’;

5.External authentication user :

For external authentication user we need to connect the database using following statement :

User / password

Example :

Connect sys/as sysdba;

6.tnsnames.ora :

Check the connection string entered in tnsnames.ora. tnsnames should always point to correct server and correct instance.If user fails to insert correct instance name then userid and password may not exist in database. So check for correct instance is entered or not in tnsnames.ora file.