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)