Oracle database 19c Dynamic sequence cache - LAST_VALUE for sequences is incorrectly calculated

Recently after  migrating to  19c  , Application team started complaining that sequences  value are not properly calculated . 

It was later found that  it was due to  Dynamic sequence cache  introduced in 19c . 

This issue is supposed to be fixed in 19.13 

If the nextval value of the sequence is frequently called in the application, there may be performance 

problems.  

The default sequence cache is usually 20, I still recommend configuring cache 200 to start when creating.

The feature is enabled by default and requires no additional setup by a DBA or end user. This feature resizes sequence caches dynamically based on the rate of consumption of sequence numbers. As a result sequence cache sizes can grow well beyond the configured DDL cache size.

If a sequence is used across nodes in a RAC setting, this can result in large gaps in sequence numbers seen across instances.

If you see large gaps in sequence numbers on 19.10 or later, and your application is sensitive to such gaps, then you can disable this feature with the following setting:

_dynamic_sequence_cache = FALSE;

References :

19c LAST_VALUE for sequences is incorrectly calculated when next cache of numbers required (Doc ID 2797098.1)

Sequence dynamic cache resizing feature (Doc ID 2790985.1)

Gathering Information for Troubleshooting Oracle Physical Standby Database /Dataguard

Writing  this blog to keep all  scripts handy for handling dataguard  issues 

Script to Collect Data Guard Physical and Active Standby Diagnostic Information for Version 10g and above (Including RAC) ( ID 1577406.1)

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/script-to-collect-data-guard-physical_21.html

Script to Collect Data Guard Primary Site Diagnostic Information [ID 241374.1]

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/script-to-collect-dataguard-primary.html

Script to Collect Data Guard Primary Site Diagnostic Information for Version 10g and above (Including RAC). (Doc ID 1577401.1)

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/script-to-collect-data-guard-primary.html

Script to Collect Data Guard Physical Standby Diagnostic Information [ID 241438.1]

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/script-to-collect-data-guard-physical.html

SRDC - Collect Data Guard Diagnostic Information (Doc ID 2219763.1)

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/srdc-collect-data-guard-diagnostic.html

Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql) (Doc ID 1064487.1)

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/script-to-collect-log-file-sync.html

Checking Log Transport Lag and Log  transport error  : 

Have documented sql used  in below separate blog foe easiness 

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/monitoring-oracle-standby-log-transport.html

Improving Log transport : 

1) Using multithreaded log writer / lgwr  process for Sync  log transport 

2) Increasing Archiver process for async log transport . 

Handling Huge archive gap or when archive is missing :

1) Applying Incremental Scn based backup 

Logs to checks :

1)  Prod and Dr Site Alert Logs 

2) Prod and Dr Site  dg broker log 

3) Prod site lgwr logfile . 

DGMGRL Commands : 

Spool dgconfig.log 

DGMGRL> SHOW INSTANCE VERBOSE sales1;

DGMGRL> SHOW INSTANCE sales1;

DGMGRL> show configuration verbose;

DGMGRL> show configuration verbose “tststby”;

DGMGRL> show database prod1;

DGMGRL> show database prod1dr;

DGMGRL> show database prod1 statusreport;

DGMGRL> show database prod1 inconsistentProperties;

DGMGRL> show database prod1 inconsistentlogxptProps;

DGMGRL> show database prod1 logxptstatus;

DGMGRL> show database prod1 latestlog;

DGMGRL> show fast_start failover;

DGMGRL> show database STYDB InconsistentProperties 

DGMGRL>show database STYDB InconsistentLogXptProps

 

DGMGRL>show database PRIMDB statusreport

DGMGRL>show database PRIMDB sendQentries

DGMGRL>show database STYDB recvqentries

DGMGRL>show database PRIMDB topwaitevents

DGMGRL> edit configuration set property tracelevel=support;

DGMGRL> edit database PRIMDB set property LogArchiveTrace=8191;

DGMGRL> edit database STYDB set property LogArchiveTrace=8191;

DGMGRL> edit configuration reset property tracelevel ;

DGMGRL> edit database PRIMDB reset property logarchivetrace;

DGMGRL> edit database STYDB reset property logarchivetrace;

DGMGRL> VALIDATE DATABASE ‘chennai’;

DGMGRL> validate database verbose standby 

DGMGRL> VALIDATE network configuration for all ; 

EVENT

To determine which resource is constraining asynchronous transport, use krsb stats which can be enabled by setting event 16421 on both the primary and standby databases:

alter session set events ‘16421 trace name context forever, level 3’;

To disable krsb stats set event 16421 to level 1:

alter session set events ‘16421 trace name context forever, level 1’;

Checking Dataguard Parameters :

set linesize 500 pages 0

col value for a90

col name for a50

 select name, value

from v$parameter

where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',

               'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',

               'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',

                     'log_file_name_convert', 'standby_file_management');

SELECT db_param.NAME,

       db_param.VALUE,

       db_db.db_unique_name,

       db_db.database_role

FROM   v$parameter db_param,

       v$database  db_db

WHERE  db_param.NAME IN ( 'db_file_name_convert',

                          'db_name',

                          'db_unique_name',

                          'fal_client',

                          'fal_server',

                          'local_listener',

                          'log_archive_config',

                          'log_archive_dest_1',

                          'log_archive_dest_2',

                          'log_archive_dest_3',

                          'log_archive_dest_state_1',

                          'log_archive_dest_state_2',

                          'log_archive_dest_state_3',

                          'log_file_name_convert',

                          'standby_archive_dest',

                          'standby_file_management',                        

                          'remote_login_passwordfile',

                          'log_archive_format'

                        )

ORDER BY db_param.NAME;

############# Dataguard redo apply rate ##########

watch -n 5 ./guard_apply_takip.sh 

#!/bin/bash

date;

hostname;

export ORACLE_HOME=/oracle/product/11.2.0

export ORACLE_SID=TESTDB

echo "*******************************************************************************************************************"

sqlplus -S / as sysdba  <<EOF

 set lines 200

 set feedback off

 set pages 0 

 col comments for a20

 col start_time for a30

 col item for a30

 col units for a15

 col last_apply_time for a30

 select to_char(R.START_TIME,'DD.MON.YYYY HH24:MI')start_time , 

        R.ITEM, R.UNITS, R.SOFAR , 

        to_char(R.TIMESTAMP,'DD.MON.YYYY HH24:MI:SS') last_apply_time

    from v\$recovery_progress R

    where start_time = (select max(start_time) from v\$recovery_progress) order by start_time, item;

 select process, status, thread#, sequence#, block#, blocks from v\$managed_standby order by 1;

EOF

echo ""

echo "*******************************************************************************************************************"

top -cbn 1 | head -n 40

Other Views : 

###############    Gather the per log redo generation rate, .  ###############  

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

SQL> select thread#,sequence#,blocks*block_size/1024/1024 MB,(next_time-first_time)*86400 sec, blocks*block_size/1024/1024)/((next_time-first_time)*86400) "MB/s" from v$archived_log 

where ((next_time-first_time)*86400<>0) 

and first_time between to_date('2015/01/15 08:00:00','YYYY/MM/DD HH24:MI:SS') 

and to_date('2015/01/15 11:00:00','YYYY/MM/DD HH24:MI:SS') 

and dest_id=1 order by first_time;

 

##########  To Check Lag  ################ 

col NAME format a10

 select NAME,TIME,UNIT,COUNT,LAST_TIME_UPDATED from V$STANDBY_EVENT_HISTOGRAM where

 name like '%lag' and count >0 order by LAST_TIME_UPDATED;

###############  To check speed  of  Log apply  On Datagurd  ########

  set lines 120 pages 99

 alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

 select START_TIME, ITEM, SOFAR, UNITS from gv$recovery_progress;

 Reference : 

   

https://docs.oracle.com/en/database/oracle/oracle-database/21/haovw/tune-and-troubleshoot-oracle-data-guard.html#GUID-30CD6E1C-1CE2-4BB6-A404-896D5C06ECCE

How to Generate AWRs in Active Data Guard Standby Databases (Doc ID 2409808.1)