ORA-28040: No matching authentication protocol after Oracle 12c upgrade.

After Oracle 12c upgrade users started seeing below errors

ORA-28040: No matching authentication protocol

The main issue is older versions of drivers will not able to connect 12c server. In Oracle 12.1, the default value for the SQLNET.ALLOWED_LOGON_VERSION parameter is set to 11. This means that database clients using pre-11g drivers cannot authenticate to 12.1 database servers unless the SQLNET.ALLOWED_LOGON_VERSION parameter is set to the old default of 8 in $ORACLE_HOME/network/admin/sqlnet.ora

This can be done by using either :

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 (JDBC 10g drivers by default support this security version implementation)
OR
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10 (If you had applied the patch 6779501, on JDBC 10.2.0.4 / 10.2.0.5, OR if the jar is bundled with Weblogic Server)
OR
The alternative is to leave the default SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 on the database and upgrade the JDBC clients. You can download the JDBC 11g or 12c from http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

Note that SQLNET.ALLOWED_LOGON_VERSION has been deprecated in 12c.

Even after updating above values users might receive below error.
ORA-01017: invalid username/password: logon denied

In this case users need to add following parameter to sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NONE)

Also few users received ORA-28040 error when creating older version of databases (for ex: 10g) using DBCA

Reference :
Error "ORA-28040: No matching authentication protocol" When Using SQLNET.ALLOWED_LOGON_VERSION ( Doc ID 755605.1 )
JDBC Version 10.2.0.4 Produces ORA-28040 Connecting To Oracle 12c (12.1.0.2) Database ( Doc ID 2023160.1 ).

Thanks
Satishbabu Gunukula, Oracle ACE
https://oracleracexpert.com

Oracle Application Express is INVALID and Remove Oracle Apex

We are trying to upgrade a database and noticed that “Oracle Application Express” component is “INVALID” .

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
APEX 3.2.1.00.12 INVALID
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 VALID

SQL> Select COMP_NAME,VERSION,STATUS from dba_registry where COMP_ID=’APEX’;
COMP_NAME STATUS VERSION
----------------------------------- --------------- ------------------------------
Oracle Application Express INVALID 3.2.1.00.10

To avoid issues during the upgrade we decided to remove the Oracle Apex as it is not in use. We have followed below steps to remove the Oracle Application Express.

$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/apex/apxremov.sql

After removing Application Express started the upgrade but received warning that “Oracle Server” component is INVALID.

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 INVALID
CATPROC 11.2.0.4.0 INVALID

We noticed that object SYS.HTMLDB_SYSTEM is shown as invalid after removing the Oracle APEX. After investigation I have noticed that apexremov.sql script removes APEX, but leaves the HTMLDB_SYSTEM package and synonym.

I ran below command to drop the invalid packages
SQL> drop package htmldb_system;
SQL> drop public synonym htmldb_system;

Now I don’t see any more INVALID objects but I still see that CATALOG,CATPROC are still INVALID.

SQL> select object_name, object_type from dba_objects
2 where owner = 'SYS' and status = 'INVALID';
no rows selected

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 INVALID
CATPROC 11.2.0.4.0 INVALID

I ran below script to validate CATPROC and CATALOG. Note that run the same CATALOG you need to change the two entries in the script. (i.e. replace CATPROC with CATALOG before running).

sqlplus / as sysdba
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$
WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||'
Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/

Ran utlrp.sql script and now the CATALOG and CATPROC showing as VALID.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 VALID

We are able to upgrade the database successfully.

Thanks,
Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

Webinar: Oracle Database Upgrade to 12c and Available Methods

Oracle Database 12c has many exciting new features and in order to take advantage of these features you need to upgrade the databases from older versions to Oracle 12c.

In this webinar, I will explain best practices and availble methods for you to upgrade/migrate your databases to 12c.

Date and time: Mar 30th 2018 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation link "Click here". Don’t miss the chance…!

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

CATPROC and CATALOG are INVALID in the Registry

We are working on pre-upgrade steps and noticed that “Oracle Database Packages and Types” and “Oracle Database Catalog Views “ components are  showing as INVALID.

SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS

---------- ---------------------------------------- ------------------------------ --------------------------------------------
ORDIM      Oracle Multimedia                                        11.2.0.4.0                     VALID

XDB        Oracle XML Database                                       11.2.0.4.0                     VALID

EXF        Oracle Expression Filter                                    11.2.0.4.0                     VALID

RUL        Oracle Rules Manager                                       11.2.0.4.0                     VALID

OWM        Oracle Workspace Manager                         11.2.0.4.0                     VALID

CATALOG    Oracle Database Catalog Views                 11.2.0.4.0                     INVALID

CATPROC    Oracle Database Packages and Types       11.2.0.4.0                     INVALID

JAVAVM     JServer JAVA Virtual Machine                     11.2.0.4.0                     VALID

XML        Oracle XDK                                                          11.2.0.4.0                     VALID

CATJAVA    Oracle Database Java Packages                   11.2.0.4.0                     VALID

I see that CATPROC, CATALOG  is INVALID in the registry and followed below steps to validate.

Step1: Validate ‘packages and types’ by running catalog and catproc scripts

$sqlplus "/as sysdba"
SQL> spool cata_logfile.txt
SQL> startup upgrade
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> spool off

Note that you need to use Use 'startup migrate' instead of ‘startup upgrade’ if database version is lower than 10g.

This should resolve the issue. If CATPROC, CATALOG is still invalid in the registry then follow next step

Step2:- Run below script as SYSDBA until it returns 'CATPROC can be validated now'. This script recompiles all dependent objects. 

 Note that you need to replace CATPROC with CATALOG in below script and execute to recompile the objects related to CATALOG.

REM ***************
REM CHECKVALID.SQL
REM ***************
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
cid = 'CATPROC';
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||'
Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/

Validate CATPROC. CATALOG by executing following command
SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATALOG;

Now I can see that all Database components are objects are VALID.

SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS

---------- ---------------------------------------- ------------------------------ --------------------------------------------
ORDIM      Oracle Multimedia                                        11.2.0.4.0                     VALID

XDB        Oracle XML Database                                       11.2.0.4.0                     VALID

EXF        Oracle Expression Filter                                    11.2.0.4.0                     VALID

RUL        Oracle Rules Manager                                       11.2.0.4.0                     VALID

OWM        Oracle Workspace Manager                         11.2.0.4.0                     VALID

CATALOG    Oracle Database Catalog Views                 11.2.0.4.0                     VALID

CATPROC    Oracle Database Packages and Types       11.2.0.4.0                     VALID

JAVAVM     JServer JAVA Virtual Machine                     11.2.0.4.0                     VALID

XML        Oracle XDK                                                          11.2.0.4.0                     VALID

CATJAVA    Oracle Database Java Packages                   11.2.0.4.0                     VALID

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com