10g Logical StdBy ArcLog Delete Lag

This is, to me, and interesting piece of info. about managing a Logical 
Standby.  We've had one for our Prod SIS (Student Information System) 
database for over a year.  I've had to re-create it a couple of times 
because I just couldn't solve some problem or other that hung it up and 
have gotten to know what and where to look better and better.  There's not 
a lot of info. out there that I can find.

For the last couple days I've noticed that the Log StdBy was applying 
Archived Redo Logs as they were shipped over from Prod, but wasn't doing 
the automatic delete of applied arclogs as it usually does.  At the worst 
it was over 2,000 arclogs behind.  At 128 MB each, that's a lot of space. 
Couldn't find anything in Dba_Logstdby_Events, v$LogStdBy, or anywhere 
else to suggest the reason.  I stopped and started SQL Apply, even bounced 
the LogStdBy a couple times to try to resolve the issue.  It got down to 
between 300 and 400 arclogs behind, but just wouldn't "catch up" with the 
Deletes - the arclogs were still being mined as soon as received.

This morning I discovered that, since early Monday, a process that updates 
our SIS with Student Attendance data was reapplying all the Attendance for 
the whole SchoolYear.  That process was producing about 75 million rows 
per day in the SIS Attendance Audit table.  It writes an Audit row for 
every column of the Attendance table affected, which is why there were so 
many Audit rows being created.  The Audit table had grown from about 100 
million rows early Monday to about 250 million rows today.

As soon as we disabled that process the LogStdBy caught up and is now back 
to its usual 1 to 4 arclog accumulation.  Almost every problem in a 
LogStdBy originates in the Primary DB, but this is one I've not seen 
before.

Hope this helps someone else with their LogStdBy(s).

Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk)  /  512.935.5929 (pager)

Sent from my old PC,
   revitalized and secured by Ubuntu Linux.

Other related posts: