ERROR: "ORA-28040: No matching authentication protocol"

Error: The following error is coming when trying to connect Oracle 12c database from below Oracle 12c client client 

ERROR: "ORA-28040: No matching authentication protocol"

Reason:

This is an Oracle issue and further inquires should be opened with Oracle support. Here is the information we have and a workaround:

 Bug 14575666

 In 12.1, the default value for the SQLNET.ALLOWED_LOGON_VERSION parameter has been updated to 11. This means that database clients using pre-11g JDBC thin drivers cannot authenticate to 12.1 database servers unless the SQLNET.ALLOWED_LOGON_VERSION parameter is set to the old default of 8.

 This will cause a 10.2.0.5 Oracle RAC database creation using DBCA to fail with the ORA-28040: No matching authentication protocol error in 12.1 Oracle ASM and Oracle Grid Infrastructure environments.

Solution : 

Set SQLNET.ALLOWED_LOGON_VERSION=8 in the oracle/network/admin/sqlnet.ora file. Now reload the listener and try to connect

[oracle@sisdb network]$ cd admin/
[oracle@sisdb admin]$ cat sqlnet.ora 
# sqlnet.ora Network Configuration File: /orasoft/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

[oracle@sisdb admin]$ vim sqlnet.ora 

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8


[oracle@sisdb admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 20-JAN-2018 13:42:41

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sisdb)(PORT=1521)))
The command completed successfully

pg_dump: server version: 9.5.10; pg_dump version: 9.2.13 (Mismatch)

Scenario : Today when running pg_dump command to take backup got the following error

-bash-4.2$ /var/lib/pgsql/pgbackup.sh
pg_dump: server version: 9.5.10; pg_dump version: 9.2.13
pg_dump: aborting because of server version mismatch
pg_dump: server version: 9.5.10; pg_dump version: 9.2.13
pg_dump: aborting because of server version mismatch
pg_dump: server version: 9.5.10; pg_dump version: 9.2.13
pg_dump: aborting because of server version mismatch
iti_20171214-093346.bak                                                                                                                                     100%    0     0.0KB/s   00:00   
protrack_20171214-093346.bak                                                                                                                                100%    0     0.0KB/s   00:00   
sdwm_20171214-093346.bak                                                                                                                                    100%    0     0.0KB/s   00:00   
-bash-4.2$

Solution

Check the version of psql, pg_dump, pg_restore

-bash-4.2$ psql --version
psql (PostgreSQL) 9.2.13
-bash-4.2$
-bash-4.2$ pg_dump --version
pg_dump (PostgreSQL) 9.2.13
-bash-4.2$ which pg_dump
/bin/pg_dump
-bash-4.2$

[root@inhouse-dmz-db1 backups]# su - postgres
Last login: Wed Dec 13 16:48:52 BDT 2017 on pts/1
-bash-4.2$ psql -version
psql (9.2.13, server 9.5.10)
WARNING: psql version 9.2, server version 9.5.
         Some psql features might not work.
Type "help" for help.

postgres=#

-bash-4.2$ ps aux | grep postgres
root      3747  0.0  0.0 209916  3208 pts/0    S    Dec11   0:00 su - postgres
postgres  3748  0.0  0.0 116284  3064 pts/0    S    Dec11   0:00 -bash
postgres  6592  0.0  0.1 382880 21048 ?        Ss   Dec13   0:01 postgres: iti iti 172.17.205.122(46656) idle
postgres  6593  0.0  0.0 380896 14904 ?        Ss   Dec13   0:00 postgres: iti iti 172.17.205.122(46657) idle
postgres  6594  0.0  0.2 391480 38560 ?        Ss   Dec13   0:03 postgres: iti iti 172.17.205.122(46658) idle
postgres  7745  0.0  0.0 380744 15608 ?        Ss   Dec13   0:02 postgres: iti iti 172.17.205.122(46669) idle
postgres  7746  0.0  0.0 379972  7264 ?        Ss   Dec13   0:00 postgres: iti iti 172.17.205.122(46670) idle
postgres  7747  0.0  0.3 387940 63532 ?        Ss   Dec13   0:04 postgres: iti iti 172.17.205.122(46671) idle
postgres  7749  0.0  0.2 392080 48544 ?        Ss   Dec13   0:06 postgres: iti iti 172.17.205.122(46672) idle
postgres  7750  0.0  0.0 379272  4104 ?        Ss   Dec13   0:00 postgres: iti iti 172.17.205.122(46673) idle
postgres  7752  0.0  0.0 379272  4104 ?        Ss   Dec13   0:00 postgres: iti iti 172.17.205.122(46674) idle
root     10788  0.0  0.0 209908  3204 pts/1    S    09:40   0:00 su - postgres
postgres 10789  0.0  0.0 116164  2848 pts/1    S    09:40   0:00 -bash
postgres 11003  0.0  0.0 139492  1632 pts/1    R+   09:46   0:00 ps aux
postgres 11004  0.0  0.0 112648   952 pts/1    S+   09:46   0:00 grep --color=auto postgres
postgres 13213  0.0  0.2 377704 42080 ?        S    Dec12   0:06 /usr/pgsql-9.5/bin/postgres -D /pgdata/9.5/data
postgres 13228  0.0  0.0 190360  1588 ?        Ss   Dec12   0:00 postgres: logger process 
postgres 13232  0.0  0.6 378276 109772 ?       Ss   Dec12   0:01 postgres: checkpointer process 
postgres 13233  0.0  0.3 377964 62180 ?        Ss   Dec12   0:01 postgres: writer process 
postgres 13234  0.0  0.0 377704  5960 ?        Ss   Dec12   0:03 postgres: wal writer process 
postgres 13235  0.0  0.0 378848  2872 ?        Ss   Dec12   0:05 postgres: autovacuum launcher process 
postgres 13236  0.0  0.0 192480  1624 ?        Ss   Dec12   0:00 postgres: archiver process   last was 000000010000000000000068
postgres 13237  0.0  0.0 192780  2020 ?        Ss   Dec12   0:11 postgres: stats collector process 
postgres 13261  0.0  0.0 379012  3204 ?        Ss   Dec12   0:02 postgres: wal sender process repuser 172.17.205.178(40716) streaming 0/69356E28
postgres 14289  0.0  0.0 107932   624 pts/0    S+   Dec13   0:00 tail -f postgresql-Wed.log
-bash-4.2$

-bash-4.2$ /usr/pgsql-9.5/bin/postgres --version
postgres (PostgreSQL) 9.5.10
-bash-4.2$

Now relink the pg_dump and pg_restore with 9.5.10 version

root@inhouse-dmz-db1 backups]# cd /usr/bin/
[root@inhouse-dmz-db1 bin]# ls -lrt pg*
-rwxr-xr-x. 1 root root 428600 Jan 25  2014 pgawk
-rwxr-xr-x. 1 root root  28320 Oct  6  2014 pgrep
-rwxr-xr-x. 1 root root 125656 Jun 15  2015 pg_restore
-rwxr-xr-x. 1 root root  75600 Jun 15  2015 pg_dumpall
-rwxr-xr-x. 1 root root 308744 Jun 15  2015 pg_dump
-rwxr-xr-x. 1 root root  28648 Jun 15  2015 pg_config
lrwxrwxrwx. 1 root root     37 Dec 10 16:47 pg_basebackup -> /etc/alternatives/pgsql-pg_basebackup
[root@inhouse-dmz-db1 bin]# mv pg_dump pg_dump.bak
   
[root@inhouse-dmz-db1 bin]# ln -s /usr/pgsql-9.5/bin/pg_dump ./
[root@inhouse-dmz-db1 bin]# ls -lrt pg_r*
-rwxr-xr-x. 1 root root 125656 Jun 15  2015 pg_restore
[root@inhouse-dmz-db1 bin]#

[root@inhouse-dmz-db1 ~]# su - postgres
Last login: Thu Dec 14 09:40:05 BDT 2017 on pts/1
-bash-4.2$ pg_dump --version
pg_dump (PostgreSQL) 9.5.10
-bash-4.2$ 

-bash-4.2$ pg_restore --version
pg_restore (PostgreSQL) 9.2.13
-bash-4.2$

[root@inhouse-dmz-db1 bin]# mv pg_restore pg_restore.bak
[root@inhouse-dmz-db1 bin]# ln -s /usr/pgsql-9.5/bin/pg
   
[root@inhouse-dmz-db1 bin]# ln -s /usr/pgsql-9.5/bin/pg_restore ./
[root@inhouse-dmz-db1 bin]# ls -lrt pg_r*
-rwxr-xr-x. 1 root root 125656 Jun 15  2015 pg_restore
[root@inhouse-dmz-db1 bin]#

-bash-4.2$ pg_restore --version
pg_restore (PostgreSQL) 9.5.10
-bash-4.2$ 

Now try to take backup again !!!!!!!!!!!!!

ERROR: could not open extension control file "/usr/pgsql-9.5/share/extension/tablefunc.control": No such file or directory

Error:

[root@said extension]# su - postgres
Last login: Wed Dec 13 14:33:47 BDT 2017 on pts/1
-bash-4.2$ psql -U sors
psql (9.2.13, server 9.5.10)
WARNING: psql version 9.2, server version 9.5.
         Some psql features might not work.
Type "help" for help.

sors=# CREATE EXTENSION tablefunc;
ERROR:  could not open extension control file "/usr/pgsql-9.5/share/extension/tablefunc.control": No such file or directory
sors=# exit
sors-# \q
-bash-4.2$ exit

Reason: 

postgresql95-contrib package is not installed or wrong version installed

The tablefunc module includes various functions that return tables (that is, multiple rows). These functions are useful both in their own right and as examples of how to write C functions that return multiple rows.

Solution:

[root@said extension]# ls -lrt
total 12
-rw-r--r--. 1 root root 381 Nov  8 23:31 plpgsql--unpackaged--1.0.sql
-rw-r--r--. 1 root root 179 Nov  8 23:31 plpgsql.control
-rw-r--r--. 1 root root 332 Nov  8 23:31 plpgsql--1.0.sql
[root@said extension]# pwd


root@said yum.repos.d]# yum install postgresql-contrib
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check
---> Package postgresql95-contrib.x86_64 0:9.5.10-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                              Arch                                   Version                                             Repository                              Size
==============================================================================================================================================================================================
Installing:
 postgresql95-contrib                                 x86_64                                 9.5.10-1PGDG.rhel7                                  pgdg95                                 533 k

Transaction Summary
==============================================================================================================================================================================================
Install  1 Package
.

Total download size: 533 k
Installed size: 1.8 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql95-contrib-9.5.10-1PGDG.rhel7.x86_64.rpm                                                                                                                     | 533 kB  00:00:01   
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql95-contrib-9.5.10-1PGDG.rhel7.x86_64                                                                                                                             1/1
  Verifying  : postgresql95-contrib-9.5.10-1PGDG.rhel7.x86_64                                                                                                                             1/1

Installed:
  postgresql95-contrib.x86_64 0:9.5.10-1PGDG.rhel7                                                                                                                                           

Complete!



[root@said yum.repos.d]# cd /usr/pgsql-9.5/share/extension/
[root@said extension]# ls -lrt
total 932
-rw-r--r--. 1 root root   394 Nov  8 23:31 timetravel--unpackaged--1.0.sql
-rw-r--r--. 1 root root   156 Nov  8 23:31 timetravel.control
-rw-r--r--. 1 root root   486 Nov  8 23:31 timetravel--1.0.sql
-rw-r--r--. 1 root root   134 Nov  8 23:31 tcn.control
-rw-r--r--. 1 root root   274 Nov  8 23:31 tcn--1.0.sql
-rw-r--r--. 1 root root  1160 Nov  8 23:31 tablefunc--unpackaged--1.0.sql
-rw-r--r--. 1 root root   174 Nov  8 23:31 tablefunc.control
-rw-r--r--. 1 root root  2153 Nov  8 23:31 tablefunc--1.0.sql


[root@said extension]# su - postgres
Last login: Wed Dec 13 14:55:33 BDT 2017 on pts/1
-bash-4.2$ psql  -U sors sors -c "CREATE EXTENSION tablefunc"
CREATE EXTENSION
-bash-4.2$ pwd
/var/lib/pgsql