RE: 10.2.0.4 Logical StdBy Hung SQL Apply
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxx>
- To: "japplewhite@xxxxxxxxxxxxx" <japplewhite@xxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 May 2011 07:36:27 -0400
Jack,
Does the following sql show what sql statement it is hanging on? Optionally,
you could tell it to skip that transaction. Some DDL transactions cannot be
applied.
This script gives the most recent entry in the dba_logstdby_events view.
Below is a normal status. Errors will show up with the corresponding
sql statement that failed in the "EVENT" column.
set lines 132
column event format a60
column status format a60
select status, status_code, event
from dba_logstdby_events
where event_time =
(select max(event_time)
from dba_logstdby_events);
to skip a single transaction:
set serveroutput on
declare
cursor c1 is
select xidusn,xidslt,xidsqn
from dba_logstdby_events
where event_time =
(select max(event_time)
from dba_logstdby_events);
begin
execute immediate 'ALTER DATABASE STOP LOGICAL STANDBY APPLY';
for c1_rec in c1 loop
dbms_output.put_Line('skipping ' || c1_rec.xidusn || '.' ||c1_rec.xidslt || '.'
||c1_rec.xidsqn);
dbms_logstdby.skip_transaction(c1_rec.xidusn,c1_rec.xidslt,c1_rec.xidsqn);
end loop;
execute immediate 'ALTER DATABASE START LOGICAL STANDBY APPLY';
end;
/
HTH.
Tom
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of japplewhite@xxxxxxxxxxxxx
Sent: Thursday, May 26, 2011 10:09 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: oracle-l-bounce@xxxxxxxxxxxxx
Subject: 10.2.0.4 Logical StdBy Hung SQL Apply
Hi,
It's 64 bit Oracle10g EE on 64 bit RHEL 4 Linux.
According to V$LogStdB my Builder process has been doing this for hours and
hours:
ORA-16245: paging in transaction 5, 45, 347459
I've gone through "Oracle10g Data Guard SQL Apply Troubleshooting (Doc ID
312434.1)", but nothing is revealed. I increased the SQL Apply Max_SGA from
the default of 30 MB up to 2 GB, as well as the number of Appliers. Still no
joy. No errors anywhere that I can find.
Anybody have a suggestion?
Thanks.
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk) / 512.935.5929 (pager)
Other related posts:
