Hi all,
We have another problem here with logical standby (v.10.1.0.7.0 Solaris). I found similar thread here, but that did not help.
Everything was doing fine for some time, but week ago suddenly SQL apply became so slow - in 24hours it has processed like 10 minutes of logs.
eg
select latest_time, applied_time from V$LOGSTDBY_PROGRESS
returns
02.02.2010. 9:48:14 | 27.01.2010. 10:27:23
There are about 400 unapplied logs now in DBA_LOGSTDBY_LOG and about 50 new arrive each day.
If I query V$LOGSTDBY_PROCESS, it returns something like
COORDINATOR,ORA-16116: no work available
ANALYZER,ORA-16116: no work available
APPLIER,ORA-16230: committing transaction 111 3 312266
APPLIER,ORA-16123: transaction 205 6 89764 is waiting for commit approval
APPLIER,ORA-16125: large transaction 87 10 383805 is waiting for more data
APPLIER,ORA-16123: transaction 154 10 135298 is waiting for commit approval
APPLIER,ORA-16123: transaction 264 2 45226 is waiting for commit approval
READER,ORA-16127: stalled waiting for additional transactions to be applied
BUILDER,ORA-44604: Barrier synchronization on DDL with XID 274.2.60230 (waiting on 5 transactions)
PREPARER,ORA-16116: no work available
and these states doesn't changes for about 10-15 minutes.
tried to experiment with changing apply server count and changing other parameters (like PRESERVE_COMMIT_ORDER) but nothing changes.
There are no other processes on this database that could slow down SQL apply.
Also if I try to stop sql apply ALTER DATABASE STOP LOGICAL STANDBY APPLY, it takes forever.
Any help or directions would be appreciated.