Shulou(Shulou.com)06/01 Report--

[fault handling] ORA-28040: No matching authentication protocol

1.1 BLOG document structure map

1.2Preface 1.2.1 introduction and precautions for technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) obsession:

Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter and ORA-28040: No matching authentication protocol errors occur frequently in ① alarm logs, and 9i clients connect to 12c higher version of the solution.

Use the oerr command under ② Windows

Tips:

① article has been updated synchronously on itpub (http://blog.itpub.net/26736162), blog Garden (http://www.cnblogs.com/lhrbest) and Wechat official account (xiaomaimiaolhr).

All the code used in the ② article, related software, related materials and the pdf version of this article can be downloaded from the cloud disk of wheat seedlings. The cloud disk address of wheat seedlings can be found at: http://blog.itpub.net/26736162/viewspace-1624453/.

③ if the code format of the web page article is out of order, please download the document in pdf format to read.

④ in this BLOG, the output part of the code is generally placed in a table with one row and one column.

If there are any mistakes or imperfections in this article, please correct them as much as you can, ITPUB or QQ. Your criticism is the biggest motivation of my writing.

1.3 Fault analysis and resolution process 1.3.1 introduction to the fault environment

Project

Source db

Db Typ

RAC

Db version

12.1.0.2.0

Db storage

ASM

OS version and kernel version

SuSE Linux Enterprise Server (SLES 11) 64 bit

1.3.2 Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter occurs frequently in the alarm log of fault occurrence and error information.

Or JDBC connection Oracle12c reported the following error:

Caused by: java.sql.SQLException: ORA-28040: No matching authentication protocol

At oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:112)

At oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:331)

At oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:283)

At oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:278)

At oracle.jdbc.driver.T4CTTIoauthenticate.receiveOsesskey (T4CTTIoauthenticate.java:294)

At oracle.jdbc.driver.T4CConnection.logon (T4CConnection.java:357)

At oracle.jdbc.driver.PhysicalConnection. (PhysicalConnection.java:441)

At oracle.jdbc.driver.T4CConnection. (T4CConnection.java:165)

At oracle.jdbc.driver.T4CDriverExtension.getConnection (T4CDriverExtension.java:35)

At oracle.jdbc.driver.OracleDriver.connect (OracleDriver.java:801)

At java.sql.DriverManager.getConnection (DriverManager.java:582)

At java.sql.DriverManager.getConnection (DriverManager.java:154)

Or using a 9i client to connect to a 12c database will report an error of ORA-28040: No matching authentication protocol.

1.3.3 the fault analysis and resolution process is viewed using the oerr command, under Oracle 11g:

[oracle@orcltest ~] $oerr ora 28040

28040, 0000, "No matching authentication protocol"

/ / * Cause: No acceptible authentication protocol for both client and server

/ / * Action: Administrator should set SQLNET_ALLOWED_LOGON_VERSION parameter

/ / on both client and servers to values that matches the minimum

/ / version supported in the system.

[oracle@orcltest ~] $

Under 12c:

Oracle@HQsPSL-PSCV-R02:/oracle/app/oracle > oerr ora 28040

28040, 0000, "No matching authentication protocol"

/ / * Cause: There was no acceptable authentication protocol for

/ / either client or server.

/ / * Action: The administrator should set the values of the

/ / SQLNET.ALLOWED_LOGON_VERSION_SERVER and

/ / SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the

/ / client and on the server, to values that match the minimum

/ / version software supported in the system.

/ / This error is also raised when the client is authenticating to

/ / a user account which was created without a verifier suitable for

/ / the client software version. In this situation, that account's

/ / password must be reset, in order for the required verifier to

As you can see, the solution for this parameter is different under 11g and 12c.

After querying the parameter SQLNET.ALLOWED_LOGON_VERSION, it is found that it is discarded in 12c and replaced by SQLNET.ALLOWED_LOGON_VERSION_CLIENT and SQLNET.ALLOWED_LOGON_VERSION_SERVER.

The customer said that he had encountered an error of ORA-28040: No matching authentication protocol before adding this parameter.

Solution: under Oracle user (not grid user), comment out the original SQLNET.ALLOWED_LOGON_VERSION=8 of the $ORACLE_HOME/network/admin/sqlnet.ora file (if there is no sqlnet.ora file, create one) and change it to the following line:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

There is no need to restart the database or monitor, nor do you need to restart the application.

The differences are as follows:

SQLNET.ALLOWED_LOGON_VERSION_SERVER: controls the client version that can connect to 12c databases (client-- > orace 12c db)

SQLNET.ALLOWED_LOGON_VERSION_CLIENT: controls which versions of the database 12c databases can connect to (orace 12c db-- > other versions of oracle db), for example, controls which versions of oracle libraries can be connected to through DB LINK.

Therefore, the main thing that works in this case is the need to configure SQLNET.ALLOWED_LOGON_VERSION_SERVER.

Special attention should be paid to:

(1) if it is RAC, because RAC is a listener that uses grid, many people think that it is adding "SQLNET.ALLOWED_LOGON_VERSION_SERVER=8" to "/ u02/app/12.1.0/grid/network/admin/sqlnet.ora". In fact, this is wrong, but it is still adding "SQLNET.ALLOWED_LOGON_VERSION_SERVER=8" to $ORACLE_HOME/network/admin/sqlnet.ora.

(2) the version mentioned above refers to the version of dba_users.password_versions.

In Oracle 12c, although sqlnet.oraplus SQLNET.ALLOWED _ LOGON_VERSION=8 can solve the problem, because this parameter has been deprecated at 12c, SQLNET.ALLOWED_LOGON_VERSION_CLIENT and SQLNET.ALLOWED_LOGON_VERSION_SERVER are used instead. If you continue to use this parameter, you will report "Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter." endlessly in the alarm log, as shown below:

In addition, for JDBC error reporting, you can also download the jdbc driver jar package that supports oracle12c. Link: http://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html

Note: if the local jdk version is 1.6, the download ojdbc6.jar;jdk version is 1.7, then download ojdbc7.jar

As shown in the following figure:

You can also download the ojdbc7.jar package from the lib library directory in the ORACLE installation directory, and then load the ojdbc7.jar into the development environment.

[oracle@orcltest dbhome_1] $ll $ORACLE_HOME/jdbc/lib/ojdbc*

-rw-r--r-- 1 oracle oinstall 3447295 Aug 23 2011 / u02/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc5dms_g.jar

-rw-r--r-- 1 oracle oinstall 2617019 Aug 23 2011 / u02/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc5dms.jar

-rw-r--r-- 1 oracle oinstall 3425922 Aug 23 2011 / u02/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc5_g.jar

-rw-r--r-- 1 oracle oinstall 2095661 Aug 23 2011 / u02/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc5.jar

-rw-r--r-- 1 oracle oinstall 4486070 Aug 23 2011 / u02/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6dms_g.jar

-rw-r--r-- 1 oracle oinstall 3327656 Aug 23 2011 / u02/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6dms.jar

-rw-r--r-- 1 oracle oinstall 4462913 Aug 23 2011 / u02/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6_g.jar

-rw-r--r-- 1 oracle oinstall 2714016 Aug 23 2011 / u02/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6.jar

[oracle@orcltest dbhome_1] $

1.3.4 official documentation and MOS explanation on this issue, there are many documents on MOS for reference.

Http://docs.oracle.com/database/121/UPGRD/deprecated.htm#UPGRD60010

Deprecated and unsupported features https://docs.oracle.com/database/121/UPGRD/deprecated.htm#BABEDDGA in 1.3.4.1 12c

1.4 ORA-28040 fault simulation wheat seedlings have databases of 7, 8, 9, 10, 11, 12c, so simulate this error by the way.

The database is 12c on the server side and 9i on the client side. We try to connect to the 12c database on the client side:

Microsoft Windows [version 10.0.10240]

(C) 2015 Microsoft Corporation. All rights reserved.

D:\ Users\ xiaomaimiao > set ORACLE_HOME=D:\ Program_files\ U01\ app\ oracle\ product\ ora92

D:\ Users\ xiaomaimiao > set ora

ORACLE10G=D:\ Program files\ app\ oracle\ product\ 10.2.0\ db_1

ORACLE11G=D:\ Program_files\ U01\ app\ oracle\ product\ 11.2.0.1\ dbhome_1

ORACLE8I=D:\ Program files\ app\ oracle\ product\ ora8i

ORACLE_HOME=D:\ Program_files\ U01\ app\ oracle\ product\ ora92

D:\ Users\ xiaomaimiao > cd% ORACLE_HOME%/bin

D:\ Program_files\ U01\ app\ oracle\ product\ ora92\ bin > sqlplus-v

SQL*Plus: Release 9.2.0.1.0-Production

D:\ Program_files\ U01\ app\ oracle\ product\ ora92\ bin > tnsping ora12c

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0-Production on 19-DEC-2016 17:44:59

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

D:\ Program_files\ U01\ app\ oracle\ product\ ora92\ network\ admin\ sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.59.128) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lhrdb12c)

OK (10 msec)

D:\ Program_files\ U01\ app\ oracle\ product\ ora92\ bin > sqlplus lhr/lhr@ora12c

SQL*Plus: Release 9.2.0.1.0-Production on Mon Dec 19 17:45:07 2016

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-28040: No matching authentication protocol

Enter user-name:

You can see the error reporting ORA-28040: No matching authentication protocol.

We add the following line to $ORACLE_HOME/network/admin/sqlnet.ora on the server side:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

Retry the connection:

D:\ Program_files\ U01\ app\ oracle\ product\ ora92\ bin > sqlplus lhr/lhr@ora12c

SQL*Plus: Release 9.2.0.1.0-Production on Mon Dec 19 17:51:54 2016

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL >

You can see that the connection is normal.

If you comment out the SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 and SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8 in the server's $ORACLE_HOME/network/admin/sqlnet.ora and replace them with SQLNET.ALLOWED_LOGON_VERSION=8, as follows:

SQLNET.ALLOWED_LOGON_VERSION=8

# SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

# SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

Try to connect to the database:

D:\ Program_files\ U01\ app\ oracle\ product\ ora92\ bin > sqlplus lhr/lhr@ora12c

SQL*Plus: Release 9.2.0.1.0-Production on Mon Dec 19 17:56:29 2016

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL >

You can connect normally, but when you view the alarm log, you have the following output: Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter. Moreover, each time we connect to the database, we output a row of this data, which is consistent with the problem we analyzed earlier.

Use oerr command under 1.5 Windows because the customer's environment is 12c Linux, but he does not have 12c Linux environment, so it is more troublesome to install, so simply install a version of Windows. As a result, an error was reported when executing oerr ora:

C:\ Users\ xiaomaimiao > oerr ora 10041

Oerr: Cannot access the message file E:\ app\ oracle\ product\ 12.1.0\ dbhome_1\ rdbms\ mesg\ oraus.msg

No such file or directory

C:\ Users\ xiaomaimiao > oerr ora 01555

Oerr: Cannot access the message file E:\ app\ oracle\ product\ 12.1.0\ dbhome_1\ rdbms\ mesg\ oraus.msg

No such file or directory

The error file (E:\ app\ oracle\ product\ 12.1.0\ dbhome_1\ rdbms\ mesg\ oraus.msg) does not exist, and any * .msg file does not exist, so copy the relevant $ORACLE_HOME/rdbms/mesg/*.msg file from 12c Linux to Windows environment:

[oracle@orcltest mesg] $pwd

/ u02/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg

[oracle@orcltest mesg] $ll * .msg

-rw-r--r-- 1 oracle oinstall 4070 Jul 25 2008 amduus.msg

-rw-r--r-- 1 oracle oinstall 6298 Apr 14 2011 asmcmdus.msg

-rw-r--r-- 1 oracle oinstall 5886 Aug 3 2007 dbvus.msg

-rw-r--r-- 1 oracle oinstall 23309 Jan 28 2010 dgmus.msg

-rw-r--r-- 1 oracle oinstall 175881 May 11 2011 diaus.msg

-rw-r--r-- 1 oracle oinstall 49483 Jan 28 2010 expus.msg

-rw-r--r-- 1 oracle oinstall 15148 Nov 8 2009 gimus.msg

-rw-r--r-- 1 oracle oinstall 47609 Feb 18 2009 impus.msg

-rw-r--r-- 1 oracle oinstall 3585 Nov 3 2009 kfedus.msg

-rw-r--r-- 1 oracle oinstall 3457 Nov 6 2008 kfodus.msg

-rw-r--r-- 1 oracle oinstall 1792 Mar 1 2009 kfsgus.msg

-rw-r--r-- 1 oracle oinstall 26775 Nov 1 1999 kgpus.msg

-rw-r--r-- 1 oracle oinstall 3113 Sep 3 1997 kopus.msg

-rw-r--r-- 1 oracle oinstall 72528 Sep 17 2011 kupus.msg

-rw-r--r-- 1 oracle oinstall 4651 Sep 3 1997 lcdus.msg

-rw-r--r-- 1 oracle oinstall 22043 Nov 27 2006 nidus.msg

-rw-r--r-- 1 oracle oinstall 129827 May 5 2011 ocius.msg

-rw-r--r-- 1 oracle oinstall 734 Mar 8 2010 opwus.msg

-rw-r--r-- 1 oracle oinstall 4922454 Sep 17 2011 oraus.msg

-rw-r--r-- 1 oracle oinstall 178311 Aug 25 2009 qsmus.msg

-rw-r--r-- 1 oracle oinstall 391272 Sep 17 2011 rmanus.msg

-rw-r--r-- 1 oracle oinstall 40078 Jul 30 2001 sbtus.msg

-rw-r--r-- 1 oracle oinstall 123863 May 22 2010 smgus.msg

-rw-r--r-- 1 oracle oinstall 20433 Jan 13 2010 udeus.msg

-rw-r--r-- 1 oracle oinstall 20572 Jan 13 2010 udius.msg

-rw-r--r-- 1 oracle oinstall 143025 Jul 27 2009 ulus.msg

[oracle@orcltest mesg] $

Then the execution will be OK.

C:\ Users\ xiaomaimiao > oerr ora 01555

01555, 00000, "snapshot too old: rollback segment number% s with name\"% s\ "too small"

/ / * Cause: rollback records needed by a reader for consistent read are

/ / overwritten by other writers

/ / * Action: If in Automatic Undo Management mode, increase undo_retention

/ / setting. Otherwise, use larger rollback segments

About Me

.

● author: wheat seedlings, only focus on the database technology, pay more attention to the application of technology

● article is updated synchronously on itpub (http://blog.itpub.net/26736162), blog Park (http://www.cnblogs.com/lhrbest) and personal Wechat official account (xiaomaimiaolhr).

● article itpub address: http://blog.itpub.net/26736162/viewspace-2131338/

● article blog park address: http://www.cnblogs.com/lhrbest/p/6219687.html

● pdf version of this article and wheat seedling cloud disk address: http://blog.itpub.net/26736162/viewspace-1624453/

● QQ group: 230161599 WeChat group: private chat

● contact me, please add QQ friend (642808185), indicate the reason for adding

● was completed at Agricultural Bank of China from 15:00 to 2016-12-25 19:00 on 2016-12-19.

The content of the ● article comes from the study notes of wheat seedlings, and some of it is sorted out from the Internet. Please forgive me if there is any infringement or improper place.

Copyright ● all rights reserved, welcome to share this article, please reserve the source for reprint

.

The mobile phone captain clicks the image below to identify the QR code or the Wechat client scans the following QR code to follow the Wechat official account of wheat seedlings: xiaomaimiaolhr, and learn the most practical database technology for free.

Cdn.qqmail.com/zh_CN/htmledition/p_w_picpaths/function/qm_open/ico_mailme_02.png ">

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.