Here is a shell script that can be scheduled to monitor the status of the data guard. The best method for monitoring data guard and databases is to user Oracle Enterprise Manager Grid Control and setup and Manage the Data Guard.
Environment Used to test the script.
OS: Enterprise Linux Server release 5.3
Database: 11.2.0.2
No of Standby DB: 1
Data guard Type: Physical Standby
Below script will connect to primary database and query V$ARCHIVE_DEST_STATUS and V$ARCHIVED_LOG to get the data guard status and un-applied log sequence count. If the data guard status is in error or count of un-applied logs are greater than 2, a subroutine can be called to alert the DBA. Assume that DEST_ID 2 is Standby Database.
#!/usr/bin/ksh
#Script to monitor Data guard Status and alert if there is any issue.
#=======================================================================
DG_STATUS=`sqlplus -s "/ as sysdba" <<EOF
set heading off verify off feedback off serveroutput on
declare
l_stat varchar2(30);
l_gap_stat varchar2(100);
l_stat1 varchar2(150);
l_stat2 varchar2(20);
l_gap_cnt number;
l_appld_seq number;
begin
l_stat2 :='DG_NORMAL';
SELECT STATUS, GAP_STATUS into l_stat, l_gap_stat FROM "V\\$ARCHIVE_DEST_STATUS" WHERE DEST_ID = 2;
l_stat1 := l_stat||l_gap_stat;
dbms_output.put_line(l_stat1);
if l_stat1 <> 'VALIDNO GAP' then
-- dbms_output.put_line('Datagaurd Status is in Error');
l_stat2 := 'DG_ERRORR';
end if;
select count(1) into l_gap_cnt from "V\\$ARCHIVE_GAP";
dbms_output.put_line('Gap count: '||l_gap_cnt);
if l_gap_cnt>0 then
dbms_output.put_line('Datagaurd Status is in Error');
l_stat2 := 'DG_ERRORR';
end if;
SELECT COUNT(1) into l_appld_seq FROM "V\\$ARCHIVED_LOG" WHERE APPLIED='NO' and DEST_ID=2;
dbms_output.put_line('Unapplied log count: '||l_appld_seq);
if l_appld_seq>2 then -- Archived log may not apply immediatly so chk for more than 2
-- dbms_output.put_line('Datagaurd: Log apply is stopped');
l_stat2 := 'DG_ERRORR';
end if;
dbms_output.put_line(l_stat2);
end;
/
quit
EOF`
rm -rf ./DG_PREV_STAT
cp ./DG_CURR_STAT ./DG_PREV_STAT
DG_PREV_STAT=`cat ./DG_PREV_STAT`
echo $DG_STATUS
l_stat=${DG_STATUS: -9}
echo $l_stat
if [ "$l_stat" = "DG_NORMAL" ]; then
echo "NORMAL" > ./DG_CURR_STAT
if [ "$DG_PREV_STAT" = "NORMAL" ]; then
echo "No Change in Dataguard Status"
else
echo "Status of Dataguard changed from ERROR to NORMAL"
fi
else
echo "ERROR" > ./DG_CURR_STAT
if [ "$DG_PREV_STAT" = "ERROR" ]; then
echo "No Change in Dataguard Status"
else
echo "Status of Dataguard changed from NORMAL to ERROR"
#--- Call Subroutine to Alert DBA -------
# For Example use wget function to call a SMS gateway URL
# or mail command to send email.
#-----------------------------------------
EOFT
fi
fi
Environment Used to test the script.
OS: Enterprise Linux Server release 5.3
Database: 11.2.0.2
No of Standby DB: 1
Data guard Type: Physical Standby
Below script will connect to primary database and query V$ARCHIVE_DEST_STATUS and V$ARCHIVED_LOG to get the data guard status and un-applied log sequence count. If the data guard status is in error or count of un-applied logs are greater than 2, a subroutine can be called to alert the DBA. Assume that DEST_ID 2 is Standby Database.
#!/usr/bin/ksh
#Script to monitor Data guard Status and alert if there is any issue.
#=======================================================================
DG_STATUS=`sqlplus -s "/ as sysdba" <<EOF
set heading off verify off feedback off serveroutput on
declare
l_stat varchar2(30);
l_gap_stat varchar2(100);
l_stat1 varchar2(150);
l_stat2 varchar2(20);
l_gap_cnt number;
l_appld_seq number;
begin
l_stat2 :='DG_NORMAL';
SELECT STATUS, GAP_STATUS into l_stat, l_gap_stat FROM "V\\$ARCHIVE_DEST_STATUS" WHERE DEST_ID = 2;
l_stat1 := l_stat||l_gap_stat;
dbms_output.put_line(l_stat1);
if l_stat1 <> 'VALIDNO GAP' then
-- dbms_output.put_line('Datagaurd Status is in Error');
l_stat2 := 'DG_ERRORR';
end if;
select count(1) into l_gap_cnt from "V\\$ARCHIVE_GAP";
dbms_output.put_line('Gap count: '||l_gap_cnt);
if l_gap_cnt>0 then
dbms_output.put_line('Datagaurd Status is in Error');
l_stat2 := 'DG_ERRORR';
end if;
SELECT COUNT(1) into l_appld_seq FROM "V\\$ARCHIVED_LOG" WHERE APPLIED='NO' and DEST_ID=2;
dbms_output.put_line('Unapplied log count: '||l_appld_seq);
if l_appld_seq>2 then -- Archived log may not apply immediatly so chk for more than 2
-- dbms_output.put_line('Datagaurd: Log apply is stopped');
l_stat2 := 'DG_ERRORR';
end if;
dbms_output.put_line(l_stat2);
end;
/
quit
EOF`
rm -rf ./DG_PREV_STAT
cp ./DG_CURR_STAT ./DG_PREV_STAT
DG_PREV_STAT=`cat ./DG_PREV_STAT`
echo $DG_STATUS
l_stat=${DG_STATUS: -9}
echo $l_stat
if [ "$l_stat" = "DG_NORMAL" ]; then
echo "NORMAL" > ./DG_CURR_STAT
if [ "$DG_PREV_STAT" = "NORMAL" ]; then
echo "No Change in Dataguard Status"
else
echo "Status of Dataguard changed from ERROR to NORMAL"
fi
else
echo "ERROR" > ./DG_CURR_STAT
if [ "$DG_PREV_STAT" = "ERROR" ]; then
echo "No Change in Dataguard Status"
else
echo "Status of Dataguard changed from NORMAL to ERROR"
#--- Call Subroutine to Alert DBA -------
# For Example use wget function to call a SMS gateway URL
# or mail command to send email.
#-----------------------------------------
EOFT
fi
fi
