Home » Server Options » Data Guard » detecting archive log gaps (oracle 11.2.0.2 linux)
detecting archive log gaps [message #570888] Sat, 17 November 2012 11:15 Go to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
I have a two node rac DB (prim and stdby) with two threads. I am trying to put together a script that will detect any gaps in the log files.

I know that MRPO only runs on one node. When I run the following query (see below) I can only see the sequence# for one of the threads.

select thread#, sequence#, process from gv$managed_standby;

 THREAD#  SEQUENCE# PROCESS
---------- ---------- ---------
         1     119739 MRP0
...
...



I than query my PRIM DB and subract the value from my STBY DB to see the difference (gap)


select (sequence# - &scn) from v$log where thread# = &thrd and status ='CURRENT'



Can there be gaps in more than one thread? If so, how can I see the diffences?
Thanks to all who answer.
Re: detecting archive log gaps [message #570890 is a reply to message #570888] Sat, 17 November 2012 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have a two node rac DB (prim and stdby) with two threads


Do you mean you have 4 nodes, 3 for the primary and 2 for the standby?

Quote:
I am trying to put together a script that will detect any gaps in the log files


Just query gv$archived_log.applied on any primary node.

Regards
Michel
Re: detecting archive log gaps [message #570891 is a reply to message #570890] Sat, 17 November 2012 13:04 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
unsure how ths will show me the gap? I suspect i need to query both stdby and prim and do some math? Or come up with a more robust query
Re: detecting archive log gaps [message #570893 is a reply to message #570891] Sat, 17 November 2012 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From the definition of the column:
Quote:
APPLIED Indicates whether the archivelog has been applied to its corresponding standby database (YES) or not (NO).

Of course, you could not trust Oracle but in this case why should trust other views or queries on them?

You can also query GV$ARCHIVE_GAP which gives you another information but can you trust it?

Regards
Michel

[Updated on: Sat, 17 November 2012 13:25]

Report message to a moderator

detecting archive log gaps [message #571344 is a reply to message #570893] Fri, 23 November 2012 14:25 Go to previous message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
I am unsure why gv$archive_gaps did not show me any gaps.

To all that are interested this is what I put together, which seems to be accurate as far as I can tell


# Returns yes=0 or NO=1
#


is_standby_manged_recovery_enabled()
{
   if [ -n "$SCRIPT_PATH" ]
   then
      eval "$SCRIPT_PATH"
   fi


   MRP_PROCESS=`sqlplus -s /nolog <<EOT
   conn $DB_ID/$DB_PWD
   set heading off
   set feedback off
   select process from gv\\$managed_standby where process like 'MR%';
EOT`

   if [ -z "$MRP_PROCESS" ]
   then
     return 1
   fi
   return 0
}

check_for_gap()
{

   if [ -n "$SCRIPT_PATH" ]
   then
      eval "$SCRIPT_PATH"
   fi


  #
  # Connect to standby DB and get the thread and sequence number of the
  # Managed recovery process.
  #

STDBY_THREAD_N_SEQ=`sqlplus -s /nolog <<EOT
conn $DB_ID/$DB_PWD
set heading off
set feedback off
select thread#,sequence# from gv\\$managed_standby where process like 'MR%';
EOT`

  echo $STDBY_THREAD_N_SEQ | grep -qiE 'ora|error|sp2' > /dev/null 2>&1
  if [ $? -eq 0 ]
  then
     echo "Error: Looking for sequence in STDBBY DB=$ORACLE_SID" > $ERR_FILE
     return 1
  fi


  #
  # Parse out the thread and sequence number from the standby DB.
  #

  STDBY_THRD=`echo $STDBY_THREAD_N_SEQ | cut -f1 -d' '`
  STDBY_SEQ=`echo $STDBY_THREAD_N_SEQ | cut -f2 -d' '`

  #
  # Connecting to primary db and get the sequence number.
  #

_prim_seq=`sqlplus -s $DB_ID/$DB_PWD@$PRIM_TNS <<EOT
set heading off
set feedback off
set verify off
select sequence#  from v\\$log where thread# = $STDBY_THRD and status='CURRENT';
EOT`

  echo $_prim_seq | grep -qiE 'ora|error|sp2' > /dev/null 2>&1
  if [ $? -eq 0 ]
  then
     echo "Error: Looking for SEQ in primary DB $PRIM_TNS" > $ERR_FILE
     return 1
  fi


  #
  # remove spaces and newline returned form sqlplus
  #

  PRIM_SEQ=$(echo $_prim_seq | tr -d ' ')
  return 0

}


is_standby_manged_recovery_enabled
if [ $? -eq 1 ]
then
   echo "Error: $ORACLE_SID standby manged recovery process is down...." >> $ERR_FILE
   return 1
fi

check_for_gap

ARCHIVE_GAP=$PRIM_SEQ-$STDBY_SEQ
if [ "$ARCHIVE_GAP" -gt "$GAP_THRESHOLD" ]
then
   echo "Found a gap of $ARCHIVE_GAP archive logs:" >> $ERR_FILE
   echo "STDBY SID=$ORACLE_SID STDBY_SEQ=$STDBY_SEQ PRIM_DB=$PRIM_TNS PRIM_SEQ=$PRIM_SEQ" >> $ERR_FILE
else

   echo "In Sync STDBY SID=$ORACLE_SID STDBY_SEQ=$STDBY_SEQ PRIM_DB=$PRIM_TNS PRIM_SEQ=$PRIM_SEQ" 

fi

ARCHIVE_GAP=$PRIM_SEQ-$STDBY_SEQ



[Updated on: Fri, 23 November 2012 14:26]

Report message to a moderator

Previous Topic: ORA-24333 - Zero Iteration Count
Next Topic: Snapshot standby
Goto Forum:
  


Current Time: Fri Aug 22 15:17:54 CDT 2014

Total time taken to generate the page: 0.22544 seconds