ORA-16198: Received timed out error from KSR & LAD:2 network reconnect

I have setup Data Guard and log synchronization happening without any issue but recently I found below errors, which are happening rarely in alert.log file

GWR (PID:13557): ORA-16198: Received timed out error from KSR
LGWR (PID:13557): Attempting LAD:2 network reconnect (16198)
LGWR (PID:13557): LAD:2 network reconnect abandoned
2020-07-13T15:32:25.865034-07:00
Errors in file /oracle/diag/rdbms/orcl/ORCL/trace/orcl_lgwr_13557.trc:
ORA-16198: Timeout incurred on internal channel during remote archival
LGWR (PID:13557): Error 16198 for LNO:2 to 'orcl_stdby'
2020-07-13T15:32:25.865253-07:00
LGWR (PID:13557): LAD:2 is UNSYNCHRONIZED
LGWR (PID:13557): Failed to archive LNO:2 T-1.S-5003, error=16198
LGWR (PID:13557): Error 16198 disconnecting from LAD:2 standby host 'orcl_stdby'

Redo Transport Services failed with ORA-16198 error from primary database standby database using LGWR SYNC mode.

Data Guard Broker log shows below error messages…

Data Guard Broker Status Summary:

  Type                        Name                             Severity  Status

  Configuration               orcl_dg_fsfconf                   Warning  ORA-16608: one or more members have warnings

  Primary Database            orcl                              Success  ORA-0: normal, successful completion

  Physical Standby Database   orcl_stdby                        Warning  ORA-16853: apply lag has exceeded specified threshold

When I very configuration everything looks good and no issues.

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Aug 3 15:19:29 2020
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - orcl_dg_fsfconf
Protection Mode: MaxAvailability
Members:
orcl - Primary database
orcl_stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 34 seconds ago)

If this is happening frequently then it is critical and must take action immediately. In my case it is happening rarely which lean towards time out setting. I see that my current DB has net_timeout as 30.

After research, I have increased the net_timeout to 60 and issue has been resolved.

In case if you are not using Data Guard broker then you can run below command from SQL*Plus

SQL> alter system set log_archive_dest_2 service="orcl_stdby", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcl_stdby" net_timeout=30, valid_for=(online_logfile,all_roles)

In case if you are using DGMGRL command-line interface, then you can run below command

DGMGRL> EDIT DATABASE 'ORCL' SET PROPERTY NetTimeout = 30;

Thanks,
Satishbabu G, Oracle ACE
http://oracleracepxert.com

ORA-00942: table or view does not exist

When calling a table directly or through procedure/function...etc user may receive error “ORA-00942: table or view does not exist”.

For ex:-
CREATE OR REPLACE FUNCTION PROC1
(Num IN NUMBER)
RETURN NUMBER
IS
BEGIN
INSERT INTO Table1 SELECT * FROM SCOTT.Table2 WHERE ID = Num;
END;

PL/SQL: ORA-00942: table or view does not exist

SQL> select * from table1;

select * from table1

              *

ERROR at line 1:

ORA-00942: table or view does not exist

Possible reasons:
1. The table name or view name spelled wrongly
2. The table or view doesn’t exist
3. The user doesn’t have required permissions

In some cases users have select access and able to query data but when running from a procedure they still receive “ORA-00942: table or view does not exist”

The reason for this error is the access granted trough a ROLE not directly. In order to access another user table from a procedure you need to have SELECT privilege granted directly.

Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)

Normally users see ORA-04030 error with shortage of RAM, small PGA and kernel parameter config.

 ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)

 Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/ORCL/ORCL/incident/incdir_8937/ORCL_ora_11258_i8937.trc"

 Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached

 ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)

 ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)

 Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/ORCL/ORCL/incident/incdir_8938/ORCL_ora_11258_i8938.trc"

 Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached

Users can refer metalink Note 233869.1 titled "Diagnosing and Resolving ORA-4030 errors"

By looking the error it is clear that “out of process memory”, where oracle cannot get the required RAM. But note that you need to analyze the trace to find out the root cause

In my case I found that one of the un-optimized PL/SQL job consuming the resources and caused the issue.

Current SQL Statement for this session (sql_id=gjn38xnrxcfbj) -----

BEGIN procedure_1(:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 ) ; END;

57% pid 67: 4089 MB used of 4092 MB allocated <= CURRENT PROC

Dump of Real-Free Memory Allocator Heap [0x7f2c640b4000]

mag=0xfefe0001 flg=0x5000003 fds=0x6 blksz=65536

blkdstbl=0x7f2c640b4010, iniblk=66560 maxblk=524288 numsegs=115

In-use num=65424 siz=4290117632, Freeable num=0 siz=0, Free num=1 siz=20512768

In our case max_map_count value is not enough and needs to investigage why the pl/sql processes requires such a alarge amout of memory. In this case adjusting PGA_AGGREGATE_TARGET or MEMORY_TARGET will not limit the size processes can grow.

To find out current vale of max_map_count run below command

$cat /proc/sys/vm/max_map_count
65530

To change the value run below command
sysctl -w vm.max_map_count= <greater than 65530>

Increase the value by following metalink note
Modification of "vm.max_map_count" on Oracle Linux 6 (Doc ID 2195159.1)

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

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 Database Upgrade from 10.2.0.4 to 11.2.0.4

Before you upgrade make sure you have backup taken backup of the database/

1. Run Pre-Upgrade Information Tool on 10.2.0.4 database

It is under 11ghome/rdbms/admin/ utlu112i.sql

sqlplus '/ as sysdba'

spool pre_upg_11201_info.log

@/oracle/upgrade/utlu112i.sql

spool off

Make the changes as suggested by pre-upgrade toll.

Sample:

SQL> @/oracle/upgrade/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool    05-22-2013 19:23:20

**********************************************************************

Database:

**********************************************************************

--> name:          ORCLDB

--> version:       10.2.0.4.0

--> compatible:    10.2.0.4.0

--> blocksize:     8192

--> platform:      Linux x86 64-bit

--> timezone file: V4

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 624 MB

.... AUTOEXTEND additional space required: 144 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 16 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 547 MB

.... AUTOEXTEND additional space required: 97 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 61 MB

**********************************************************************

Flashback: OFF

**********************************************************************

**********************************************************************

Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]

**********************************************************************

WARNING: --> "sga_target" needs to be increased to at least 672 MB

WARNING: --> "java_pool_size" needs to be increased to at least 128 MB

**********************************************************************

Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]

**********************************************************************

-- No renamed parameters found. No changes are required.

**********************************************************************

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

**********************************************************************

--> background_dump_dest         11.1       DEPRECATED   replaced by

"diagnostic_dest"

--> user_dump_dest               11.1       DEPRECATED   replaced by

"diagnostic_dest"

--> core_dump_dest               11.1       DEPRECATED   replaced by

"diagnostic_dest"

**********************************************************************

Components: [The following database components will be upgraded or installed]

**********************************************************************

--> Oracle Catalog Views         [upgrade]  VALID

--> Oracle Packages and Types    [upgrade]  VALID

--> JServer JAVA Virtual Machine [upgrade]  VALID

--> Oracle XDK for Java          [upgrade]  VALID

--> Oracle Workspace Manager     [upgrade]  VALID

--> Oracle Text                  [upgrade]  VALID

--> Oracle XML Database          [upgrade]  VALID

--> Oracle Java Packages         [upgrade]  VALID

--> Oracle interMedia            [upgrade]  VALID

--> Expression Filter            [upgrade]  VALID

--> Rule Manager                 [upgrade]  VALID

**********************************************************************

Miscellaneous Warnings

**********************************************************************

WARNING: --> Database is using a timezone file older than version 11.

.... After the release migration, it is recommended that DBMS_DST package

.... be used to upgrade the 10.2.0.4.0 database timezone version

.... to the latest version which comes with the new release.

WARNING: --> Database contains schemas with stale optimizer statistics.

.... Refer to the Upgrade Guide for instructions to update

.... schema statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

....   SYS

....   CTXSYS

WARNING: --> Database contains INVALID objects prior to upgrade.

.... The list of invalid SYS/SYSTEM objects was written to

.... registry$sys_inv_objs.

.... The list of non-SYS/SYSTEM objects was written to

.... registry$nonsys_inv_objs.

.... Use utluiobj.sql after the upgrade to identify any new invalid

.... objects due to the upgrade.

.... USER PUBLIC has 4 INVALID objects.

.... USER JEFFC has 3 INVALID objects.

.... USER SYS has 2 INVALID objects.

WARNING: --> Database contains schemas with objects dependent on network

packages.

.... Refer to the Upgrade Guide for instructions to configure Network ACLs.

WARNING:--> recycle bin in use.

.... Your recycle bin turned on.

.... It is REQUIRED

.... that the recycle bin is empty prior to upgrading

.... your database.

.... The command:  PURGE DBA_RECYCLEBIN

.... must be executed immediately prior to executing your upgrade.

PL/SQL procedure successfully completed.

SQL> spool off;

2: Make Init parameter changes and copy the init.ora file to 11g oracle home

Put compatible = 10.2.0.4.0 to avoid the unnecessary errors.

3: run utlrp.sql Twice to compile invalid objects

cd $ORACLE_HOME/rdbms/admin/

SQL> @utlrp.sql

4: check timezone, it needs to be upgraded

SQL> SELECT version FROM v$timezone_file;

   VERSION

         4

5: Gather Dictionary stats

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

6 Purge Recycle bin

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

7: Put database in no-archive log mode

shut immediate

startup mount

alter database noarchivelog;

8: shut down database and listener

Shut immediate

lsnrctl stop LISTENER_ORCLDB

9: prepare listener file or add this database entry in 11gr2 home

10: set the env, we are using CSH

setenv ORACLE_BASE /oracle

setenv ORACLE_HOME /oracle/product/11.2.0/dbhome_1

setenv PATH $ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

setenv LD_LIBRARY_PATH $ORACLE_HOME/lib

setenv ORACLE_SID ORCLDB

setenv ORACLE_BASE /oracle

11: runupgrade

cd $ORACLE_HOME/rdbms/admin

spool /oracle/upgrade/upgrade_ORCLDB_11201.log

@catupgrd.sql

12: post upgrade

Run below scripts:

SQL> @utlu112s.sql

SQL> @catuppst.sql

SQL> @utlrp.sql

Upgrade is done

13. Make sure to check the all components are valid and upgrade to 11.2.0.4 and send screen shot

14: Upgrading timezone

Make sure the exiting timezone using below commands

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

SQL> SELECT version FROM v$timezone_file;

conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

-- check if this select
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

------------------------------ --------------

DST_PRIMARY_TT_VERSION         11

DST_SECONDARY_TT_VERSION       1

DST_UPGRADE_STATE              UPGRADE

-- you can check what tables need to updated using

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

no rows selected

-- restart the database
shutdown immediate
startup

--Timezone should be upgraded to V11 now, run the following command to verify

SQL> select * from v$timezone_file;

FILENAME                VERSION

-------------------- ----------

timezlrg_11.dat              11

-- now upgrade the tables who need action

set serveroutput on

VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

Failures:0

PL/SQL procedure successfully completed.

-- If there are no failures then end the upgrade

VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

An upgrade window has been successfully ended.

Failures:0

PL/SQL procedure successfully completed.

15. After update take Database backup

Refer : Complete Checklist for Manual Upgrades to 11gR2 (Doc ID 837570.1)