Saturday, January 21, 2023

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)

No comments:

Post a Comment