Shell Script to Monitor Data Guard Status

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

EBusiness - "Authentication Failed" while Save As Concurrent Output in the Internet Explorer

How to solve Oracle Apps "Authentication Failed" error while saving a concurrent output in IE

Got the error "Authentication Failed" when i try to save a concurrent output on my computer.
The saved concurrent request output does not contain anything other than "Authentication Failed"

Solution

1.  From Internet Explorer, select Tools > Interent Options
2.  On the General tab, in the "Temporary Internet files"  select the Settings
      For IE 8 and Above: Click on the Settings button under Browsing history
3. Change "Check for newer versions of stored pages:" to "Automatically"
4. Save changes

Using TOAD for Oracle on Windows 8 64bit

Update: Please note that TOAD 64 Bit is Now available for Windows 8 64 Bit Version. You can install it along with Oracle Client 64 Bit. It will work smoothly.

Today I have installed Oracle 10gR2 32 bit Client and TOAD for Oracle 11.5.0.56 on my Windows 8 laptop and when I try to connect to database it  gave the error :

can't initialize oci.error -1

So, Followed the below workaround to resolve this issue and I am able to connect to databases.

1. Go to the folder where Toad is installed.
2. Right Click on the Toad.exe and select Compatibility Tab
3. Select the check box "Run this program in compatibility mode for"
4. Select Windows XP (Service Pack 3) in the list box below
5. Save the settings by clicking OK

Now open the Toad and I am able to connect to the databases.successfully

Note the following:

Oracle Client Installed Must be 32 bit.
Toad is running in 32 bit environment.
Windows 8 is 64 bit

 

How to resolve MRP stuck issues on a physical standby database

How to resolve MRP stuck issues on a physical standby database

On my standby database, one day suddenly redo apply stopped and log files are showing the following error messages. On investigating, found that MRP process not running and doesn't apply more logs

ORA-16766: Redo Apply unexpectedly offline

ORA-00332: archived log is too small - may be incompletely archived

ORA-00334: archived log: <Archive log file name>

MRP stuck issues on a physical standby database

MRP0: Background Media Recovery terminated with error 332

Environment:

10g R2 Single  Instance Database with Dataguard Broker on Linux

I have performed the following activities and was able to restart the redo log apply

Solution:

1.       Verify the size & checksum of the reported archive log between primary and standby database. If they are different, then copy the archive log from primary to standby.  Can use md5sum to verify the checksum.

If the archive log not available you can restore from rman backup as follows

connect target /  catalog=username/pwd@catdb

run {
restore archivelog from logseq=340 until logseq=340 thread=1;
}

2.       Once the file is copied, restart the redo apply service.

Stop the managed recovery and start the manual recovery.

DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='LOG-APPLY-OFF';
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='ONLINE';

3.       Verify the redolog apply started

ps –ef | grep mrp