RE: 10.2.0.4 Logical StdBy Hung SQL Apply

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of japplewhite_at_austinisd.org Sent: Thursday, May 26, 2011 10:09 AM
To: oracle-l_at_freelists.org
Cc: oracle-l-bounce_at_freelists.org
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)

--
http://www.freelists.org/webpage/oracle-l