Troubleshooting of Standby Database
There are many times when we need to troubleshoot dataguard status and we want to know if standby databases are in SYNC or not.
Below are few SQL queries which are very useful to troubleshoot any problem with log archives which needs to be applied to standby database
set pages 200
set feedback off
set heading on
Select ('Database named '||name||' is '||open_mode||' and is in '||database_role||' role') as "DATABASE_INFO" from v$database;
set heading off
select ' ' from dual;
select '#################### RECOVERY_INFO ###################' from dual;
set feedback off
set heading on
col host_name for a20
Set lin 135
Select distinct i.instance_name, i.host_name, r.inst_id, r.recovery_mode from gv$archive_dest_status r, gv$instance I where i.inst_id=r.inst_id and r.recovery_mode <> 'IDEL';
set heading off
set feedback off
select ' ' from dual;
select '#################### GAP_INFO ######################' from dual;
set feedback off
set heading on
select process, client_process, status, thread#, sequence# from gv$managed_standby where thread#<>0 and process like 'MRP%';
set heading off
set feedback off
select ' ' from dual;
select '################### LOG_APPLY_INFO ###################' from dual;
set heading on
set feedback off
Select thread#, max(sequence#), applied from v$archived_log where applied = 'YES' group by thread#, applied;
set heading off
set feedback off
select ' ' from dual;
Set feedback off
Set heading off
select '---------------------------------------------------------------------------------' from dual;
Select 'Please apply the log sequence '||sequence#||' of thread '||thread#||'.' from gv$managed_standby where thread#<>0 and status like 'WAIT%';
Select 'MRP0 is '||status||' successfully' from gv$managed_standby where thread#<>0 and status like 'APPL%';
select '---------------------------------------------------------------------------------' from dual;
select 'Archive log sequence '||sequence#||' is not yet archived on primary side also.' from v$archived_log where thread#=(select thread# from v$managed_standby where status like 'WAIT%') and sequence#=(select sequence# from v$managed_standby where status like 'WAIT%');
select 'Archive log sequence '||sequence#||' is not yet archived on primary side also.' from v$archived_log where thread#=(select thread# from v$managed_standby where status like 'WAIT%') and archived='NO';
select ' ' from dual;
select '-----Storage Info----' from dual;
set lin 140
set pages 200
set heading off
set feedbac off
set echo off
Select 'INFO 1: The standby destination '||name||' is of '||round((Total_mb/1024), 0)||' GB, among this '||round((Free_mb/1024), 0)||' GB is free and '||round(((((Total_mb/1024)-(Free_mb/1024))/(Total_mb/1024))*100), 0)||'% is used.' from v$asm_diskgroup where name=(select trim('+' from value) from v$parameter where name='standby_archive_dest');
Select 'INFO 2: Storage if fine.' From v$asm_diskgroup where name=(select trim('+' from value) from v$parameter where name='standby_archive_dest') and free_mb>((Total_mb/100)*8);
Select 'INFO 2: WARNING: Storage has only '||round(100-((((Total_mb/1024)-(Free_mb/1024))/(Total_mb/1024))*100), 0)||'% Percentage free space.' From v$asm_diskgroup where name=(select trim('+' from value) from v$parameter where name='standby_archive_dest') and free_mb<((Total_mb/100)*8);
Select 'INFO 2: CRITICAL: Space need to be freed from storage because standby destination is 100% full.' From v$asm_diskgroup where name=(select trim('+' from value) from v$parameter where name='standby_archive_dest') and total_mb=(total_mb-free_mb);
Set lin 80
Set pages 20000
Set heading off
Set feedback off
Set echo off
!rm create_space_standby.rcv
!rm create_space_standby.sql
select 'NOTE: System is About to create Rman rcv file "create_space_standby.rcv".' from dual;
Spool create_space_standby.rcv
Select 'run {' from dual;
Select 'crosscheck archivelog all;' from dual;
select 'delete noprompt archivelog sequence between '||min(sequence#)||' and '||max(sequence#)||' thread '||thread#||';' from v$archived_log where applied='YES' and deleted='NO' group by thread#, applied, deleted;
select '}' from dual;
select 'exit' from dual;
spool off
set lin 140
select 'Rman will automatically start creating the space if archive destination usage is above 50%.' From v$asm_diskgroup where name=(select trim('+' from value) from v$parameter where name='standby_archive_dest') and free_mb<((total_mb/100)*50);
set heading off
set feedback off
set echo off
alter session set nls_date_format='ddmmyyyy_HHMI';
spool create_space_standby.sql
select '!rman target / cmdfile create_space_standby.rcv log Log_standby_purge_'||sysdate||'.log' From v$asm_diskgroup where name=(select trim('+' from value) from v$parameter where name='standby_archive_dest') and free_mb<((total_mb/100)*50);
spool off
@create_space_standby.sql
select ' To manually create space in Standby destination execute latest RMAN file create_space_standby.rcv' from v$asm_diskgroup where name=(select trim('+' from value) from v$parameter where name='standby_archive_dest') and total_mb=(total_mb-free_mb);
select ' To manually create space in Standby destination execute latest RMAN file create_space_standby.rcv' from v$asm_diskgroup where name=(select trim('+' from value) from v$parameter where name='standby_archive_dest') and free_mb<((Total_mb/100)*8);
set feedback off
set heading on
col name for a10
PROMPT ***** Disk Group Details under ASM control ****
select NAME , TYPE, TOTAL_MB, FREE_MB, round(FREE_MB/TOTAL_MB * 100,1) PCT_FREE,BLOCK_SIZE, SECTOR_SIZe, OFFLINE_DISKS, STATE from v$asm_diskgroup;
set feedback on
###############################
# BACKUP_INFO
###############################set feedback off
ALTER SESSION SET NLS_DATE_FORMAT='yyyymmdd HH:MI';
Set pages 5000
Set lin 135
Col tag for a10
Col COMPRESSED for a11
Col DEVICE_TYPE for a12
Set feedback off
set heading off
set verify off
Select '-----Tags--------' from dual;
Select distinct tag from V$BACKUP_PIECE_DETAILS where tag like 'ARCH%' or tag like '%LEV1' or tag like '%LEV0' or tag like 'CNTR%' or tag like 'SP%';
Select ' ' from dual;
Select ' ' from dual;
Select '--------PLEASE CHOOSE Tag FROM ABOVE:-------' from dual;
Select ' ' from dual;
set heading on
col Media for a10
col START_TIME for a14
col COMPLETION_TIME for a14
set lin 140
select BS_KEY, DEVICE_TYPE, nvl(media,'---X---') as "Media" , TAG, STATUS status, START_TIME, COMPLETION_TIME, round((ELAPSED_SECONDS/60/60), 0) Hours, round((bytes/1024/1024/1024), 0) as "GB", COMPRESSED, DELETED from V$BACKUP_PIECE_DETAILS where tag=upper('&Tag') and deleted='NO' order by device_type, COMPLETION_TIME, deleted;
set heading off
Select ' ' from dual;
select '-------BACKUP INFO FOR L0, L1, AO BACKUPS-------' from dual;
select ' Last Level0 backup is completed on "'||completion_time||'.' from V$BACKUP_PIECE_DETAILS where BS_KEY=(select max(BS_KEY) from V$BACKUP_PIECE_DETAILS where tag='DB_LEV0');
Select ' ' from dual;
select ' Last Level1 backup is completed on "'||completion_time||'.' from V$BACKUP_PIECE_DETAILS where BS_KEY=(select max(BS_KEY) from V$BACKUP_PIECE_DETAILS where tag='DB_LEV1');
Select ' ' from dual;
select ' Last ARCHIVE Only backup is completed on "'||completion_time||'.' from V$BACKUP_PIECE_DETAILS where BS_KEY=(select max(BS_KEY) from V$BACKUP_PIECE_DETAILS where tag='ARCH_ONLY');
select ' ' from dual;
Select ' ' from dual;
select '>>>>>>>>This information is only for reference purpose.<<<<<<<<' from dual;
Select ' ' from dual;
select '>>>>>>>>FINISH<<<<<<<<<<' from dual;
set heading off
set feedback off
select ' ' from dual;
select '******************************' from dual;
