10g Logical StdBy ArcLog Delete Lag
Date: Wed, 22 Jun 2011 15:50:10 -0500
Message-ID: <OFFFCC4D14.45C0ED97-ON862578B7.0070A919-862578B7.00727085_at_austinisd.org>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 22 2011 - 15:50:10 CDT