Authentication enhancements in Oracle 23c

Oracle 23c offers longer passwords improved security in authentication now supports up to 1024 bytes

• Oracle Data pump Export and import support longer encryption passwords up to 2024 bytes long

• Oracle Call interface (OCI) and Oracle C++ Call interface support up to 1024 bytes long password for user authentication.
• JDBC think driver support up to 1024 characters for password
• Oracle Database (including Autonomous) and clients supports password up to 1024 bytes

You can login into Oracle Database using Microsoft Azure Active Directory single sing-on OAuth2 access token. Multicloud feature integrates Oracle Database and Azure AD and you can perform this integration on

• Oracle 19.16 and later (Back ported) but not for Oracle 21c.
• Oracle Autonomous Database on Dedicated/Shared Exadata Infrastructure
• Oracle Exadata Clod Service
• Oracle Base Database Service

You can map AD users to Oracle Database schema and roles and also you can login ODP.NET can login into Oracle Database Using Microsoft Azure Active Directory. The UTL_HTTP support SHA-256/512 and XDB HTTP supports SHA512, authentication and updated Kerberos Library support.

The password length helps accommodating Oracle Identity Access management (IAM) and Identity Cloud service (IDCS) and helps enabling uniform password rules.

Oracle 23c offers many Improvements in Kerberos security and MIT Kerberos version 1.20.1 supports cross domain, windows credential guard and multiple principals.

KERBEROS5_CC_NAME and KERBEROS5_PRINCIPAL can be specified in tnsnames.ora and the values must match for user authentication.

kuser =
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orahost)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=ORCL))
(SECURITY=(KERBEROS5_CC_NAME = /tmp/kuser/krb.cc) (KERBEROS5_PRINCIPAL = kprinc)))

Kerberos parameters can be specified in Sqlnet.ora file but note that some parameters you can set at server level, and some are at client level and few you can set on both.

You can set below parameters on both client and server

SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5)

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle

SQLNET.KERBEROS5_CONF=<Kerberos_configfile_path >

SQLNET.KERBEROS5_CONF_MIT=(TRUE)

SQLNET.FALLBACK_AUTHENTICATION=FALSE

SQLNET.KERBEROS5_CLOCKSKEW=1200

The below parameter is not required on the server, but in case if your client in Microsoft Windows then you may want to consider setting OSMSFT:// or MSLSA

SQLNET.KERBEROS5_CC_NAME= <Kerberos_CC_name_withpath>

This setting is not usually required for the client or the server.

SQLNET.KERBEROS5_REALMS=<Kerberos_realms_path >

Only set this parameter on the server

SQLNET.KERBEROS5_KEYTAB=<Kerberos_keytab_path > 

Thanks & Regards,

https://oracleracexpert.com

WARNING: inbound connection timed out (ORA-3136)

The ORA-3136 will be written into alert.log when users fails to provide credentials and cannot authenticate within the set timeout value.

The default value set for below parameters in sqlnet.ora is 60 seconds.

SQLNET.INBOUND_CONNECT_TIMEOUT
INBOUND_CONNECT_TIMEOUT_listener_name

You will see below error in the alert.log file

Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 19.0.0.0.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Version 19.5.0.0.0
Time: 14-OCT-2023 18:06:02
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.xxx.xx)(PORT=51290))
2023-10-12T16:06:02.222415-07:00
WARNING: inbound connection timed out (ORA-3136)
2023-10-12T16:15:18.866821-07:00

In the above log you can see the client IP and port, you can able to check corresponding entry in the listener log as well. Also, you can see the time stamp of user initiated the connection and time stamp when user got error, it will be more than 60 sec.

The main reason for the error is …

1. When user or application trying to connect using wrong credentials or no attend made under 1 min threshold default value of the instance.

You can able to reproduce the issue by entering wrong credentials

 
SQL> sqlplus HR/xxxx@orcl
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 13 19:31:30 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.

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

$ tail -f orcl_alert.log
Fri Oct 13 19:32:31 2023
WARNING: inbound connection timed out (ORA-3136)

2. If the database has some performance issues or any network delay can cause long time and cannot authenticate user within default time out i.e. 60 seconds

If the database has some load causing the slowness, then increasing SQLNET.INBOUND_CONNECT_TIMEOUT to higher value will help to reduce the errors.

No DB restart required when you make the change in sqlnet.ora but note that it will be applicable to next server process.

3. The server receives the request but cannot be able to authenticate with in default time out i.e. 60 seconds

Find out what causing the delay in authentication if required increase the time out value.

If you are receiving error frequently you can enable tracing using below command
SQL> alter system set events '3136 trace name errorstack level 3';

 
You can trun off tracing using
SQL> alter system set events '3136 trace name context off';

Note that tracing will be generated under USER_DUMP_DEST or BACKGROUND_DUMP_DEST

If you still seeing these warnings I would suggest to raise a ticket with oracle support

Thanks,
https://oracleracexpert.com

Errors ORA-12154, ORA-29003 when connecting to Autonomous Data Warehouse using Django

When connection to Autonomous Data Warehouse Using Django web framework first we come across “ORA-12154” error. That means Django cannot be able to connect to the Oracle Database service name with the settings in settings.py file.

File "C:\django\db\backends\oracle\base.py", line 254, in get_new_connection return Database.connect( cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve the connect identifier specified

First copy the tnsnames.ora into local system and test tnsping.

C:\> tnsping <service Name>

If the tnsping is working fine, then below are the passible reason for ORA-12154 error

• TNS_ADMIN might not be configured or set the right path
• There might be a typo in service name
• Connection details in settings.py might not be correct

We found that TNS_ADMIN is not set to the right path and updating the correct path resolved the issue. But we have received new error

ORA-29003: SSL transport detected mismatched server certificate

Django supports Oracle Database 19c or higher versions but cx_oracle python driver required. We verified that required driver is installed. We try to connect using SQL*PLUS but still receiving same error. That means there is no issue with the settings.

After research we found that 12c client is not supported for new mTLS authentication as per document “ALERT: Action Required for Autonomous Databases (Doc ID 2911553.1)”

We have installed Oracle 19c client and were able to connect without any issues.

Below are the options to resolve the issue

1. Install certified oracle client versions 11.2.0.4.220719 (or later), 18.19 (or later), 19.2 (or later), 21 (base release or later)

2. Update Autonomous database instance to allow both TLS and mTLS authentication.

• Goto Autonomous Database Details page --> Network --> click Edit in the Mutual TLS (mTLS) Authentication field.
• Change the value to allow TLS authentication, deselect Require mutual TLS (mTLS) authentication
• Click Update

3. Use ssl_server_dn_match=no in the connect string (when using 12c client updating the value to “no” didn’t help )

I hope this helps.

Thanks
Satishbabu G, Oracle ACE Pro

https://oracleracexpert.com

How to enable Oracle SQL*Net Client, Server and Listener Tracing

To enable tracing make sure TNS_ADMIN environment variable should be set and add below tracing parameters in sqlnet.ora. Pls note that any trace settings apply to all server processes so you need to careful when making changes on Prod, I would always suggest to test before applying changes on PRD.
Enable SQL*Net server and Client side tracing.
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_UNIQUE_CLIENT = on
TRACE_LEVEL_SERVER = SUPPORT
TRACE_DIRECTORY_CLIENT = \tmp
TRACE_FILE_CLIENT = Client_Trace.log
TRACE_DIRECTORY_SERVER = \tmp
TRACE_FILE_SERVER = Server_Trace.log
TRACE_TIMESTAMP_ CLIENT = ON
DIAG_ADR_ENABLED= OFF

Starting from 11g and higher to enable client, server trace you need to add DIAG_ADR_ENABLED= OFF

SQL*Net Listener tracing
TRACE_FILE_LISTENER = Listener_Trace.log
TRACE_DIRECTORY_LISTENER = \tmp
TRACE_LEVEL_LISTENER = SUPPORT
DIAG_ADR_ENABLED_LISTENER_<LISTENER> = OFF

To start Listener Trace you need to set DIAG_ADR_ENABLED_<listener name> = OFF (must set to OFF)

To enable Tnsping Trace
TNSPING.TRACE_LEVEL = SUPPORT
TNSPING.TRACE_DIRECTORY = <Trace file path>

Enabling Dynamic Listener Tracing
LSNRCTL> set current_listener listener_ORCL
Current Listener is listener_ORCL
LSNRCTL> set trc_level 16
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_level" set to support
The command completed successfully
LSNRCTL> set trc_directory /oracle/diag/tnslsnr/host1/listener_orcl/trace
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_directory" set to /oracle/diag/tnslsnr/host1/listener_orcl/trace

Turn off dynamic tracing using:
LSNRCTL>set current_listener listener
LSNRCTL>set trc_level OFF


Thanks

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

Data Guard Broker Support for DBMS_ROLLING Upgrades

Data guard broker can remain on during a DBMS_ROLLING upgrade and no need to disable it. In oracle 19c there are many enhancements for Data Guard Broker Support for DBMS_ROLLING Upgrades

• Data Guard broker support is enabled by using the DBMS_ROLLING.BUILD_PLAN procedure
• Before starting a DBMS_ROLLING upgrade the fast-start failover feature must be disabled.
• Role changes are permissible during when rolling upgrade is in progress.
• During rolling upgrade any attempt to enable fast-start failover is rejected.
• Broker support is enabled by default during execution of the DBMS_ROLLING.BUILD_PLAN procedure
• The broker prevents a role change to a standby which is not protecting the current primary. The role changes to the Trailing Group Standby are allowed before the switchover phase. After the switchover phase, role changes are only allowed to the Leading Group Standbys.
• The broker will notifies Global Data Services and Oracle Clusterware as appropriate during the rolling upgrade.
• If the upgrade target is an Oracle RAC database, then the broker automatically reduces the target standby to one instance during the start of the upgrade process and allows the upgrade to proceed. Without the broker, the start of the upgrade is rejected if target has multiple instances running.
• The switchover step during a rolling upgrade should be performed using the DBMS_ROLLING.SWITCHOVER procedure.
• The status of a rolling upgrade being done using the PL/SQL package DBMS_ROLLING and the information is displayed in the broker commands SHOW CONFIGURATION and SHOW DATABASE output.

Please see the output of SHOW CONFIGURATION, where you can see configuration status as ROLLING DATABASE MAINTENANCE IN PROGRESS

Configuration - DRSystem
Protection Mode: MaxPerformance
Members:
North_Sales - Primary database
South_Sales - Transient logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ROLLING DATABASE MAINTENANCE IN PROGRESS

Please see the output of SHOW Database , where you can see Database status as WARNING.

Database – DRNode1
Role: Physical standby database
Intended State: APPLY-ON
Transport Lag: ***
Apply Lag: ***
Average Apply Rate: ***
Real Time Query: OFF
Instance(s):
South
Database Warning(s):
ORA-16866: database converted to transient logical standby database for rolling
database maintenance
Database Status:
WARNING

Three are specific Requirements DBMS_ROLLING Upgrades on a CDB

• To use DBMS_ROLLING the database compatibility should be set to 12.2 or higher

• Different character sets pluggable databases in a CDB supported for upgrade using DBMS_ROLLING

• The TNS services referenced in the LOG_ARCHIVE_DEST_n parameters must be services that resolve to the root container of the destination database.

• The process assisting DBMS_ROLLING can only execute from the root container.

• While a DBMS_ROLLING upgrade is in progress, if any DDL is executed to start the install, upgrade, or patching of an container then you will get an error.

• When user attempt to start a DBMS_ROLLING upgrade when upgrade to an application container is in progress user will get an error. Before calling DBMS_ROLLING.SWITCHOVER all container databases on the transient logical standby must be plugged in and opened, this helps eliminating logical standby apply engine halt because it cannot apply to a given PDB.

Regards

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

Webinar: How to Convert Single Instance to RAC

I am conducting the webinar once again due to overwhelming response from Oracle community. After this Webinar I hope you should be able to understand Oracle RAC benefits and "How to Convert your single instance to RAC”.

Date and time: Thursday, Sept 15th, 2016 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Oracle RAC and its Benefits
• Overview of different RAC conversion methods
• Overview of different Storage options
• Convert Single instance to Oracle RAC Using RMAN
• Overview
• Prerequisites
• Demonstration
• Check the logs
• Post Conversion Steps
• References
• Contact Info
• Q&A

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com and reserve your spot. Note that registrations are limited and first come and first serve basis.

You will receive an email confirmation with meeting session link.
For presentation link Click here .

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

ORA-12547: TNS:lost contact

When I tried to login into oracle I have received below error
sapec4:ec4adm 61> sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 29 14:00:17 2011

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

SQL> connect / as sysdba
ERROR:
ORA-12547: TNS:lost contact

Here is the simple solution for that

Solution1:-
1.Check “oracle:dba” should have ownership on $ORACLE_HOME/bin/sqlplus
$chown oracle:dba $ORACLE_HOME/bin/sqlplus
2.$ORACLE_HOME/bin/sqlplus should have 6751 permission
$chmod 6751 $ORACLE_HOME/bin/sqlplus

If you still have the problems try solution 2.

Solution2:-
You may receive this error if oracle binaries are not linked correctly, stop databases and relink the binaries.

$ ORACLE_HOME/bin/ relink all

If you find below errors in listener.log then problem related to limit of PROCESSES value.

TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact

Solution:- Increase the PROCESSES parameter in the init.ora to a higher value
SQL> Alter system set processes= “”

After fixing TNS issue you may see below error

ORA-27140: attach to post/wait facility failed

Solution:-
$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com