Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Paused - waiting for DDL to complete

chuckersJun 16 2014 — edited Jul 1 2014

In our downstream server, after a batch job of some sort is executed in the upstream server, we're constantly thrown into a state of "PAUSED - WAITING FOR DDL TO COMPLETE", and it takes a really long time to complete. When I try & look up the SQL statement that Streams is sitting on, it's never a DDL statement:

select  a.apply_name, a.server_id, q.sql_text sqltext

from v$streams_apply_server a, v$sqltext q, v$session s

where a.sid = s.sid and s.sql_hash_value = q.hash_value

and s.sql_address = q.address and s.sql_id = q.sql_id

order by a.apply_name, a.server_id, q.piece;

So I'm a little puzzled on how to improve performance, or if it's even possible. If it matters, we have parallelism set to 1, because we were getting so many deadlock errors otherwise. This slowness from "..WAITING FOR DDL..." was present before that change, however.

Thanks for your help,

--=Chuck

Comments

Renu-Oracle

Hi,

Similar issue is reported in Bug which was suspended due to some reasons.

You can try to ignore the problematic DDL transaction which is causing the issue.

Thanks,
Renu

Hi ,

Please confirm if your replication setup is DML only or both DDL and DML.

Apply process will move this state "PAUSED - WAITING FOR DDL TO COMPLETE"  only when DDL operations are performed at Source database.

It will catch up as soon as the DDL transaction is completed.

Either you can ignore this transaction and manually perform DDL on target .

Or

Tag the DDL transaction so that it is not captured by Capture process and manually perform DDL on target .

Or

Wait for the Apply process to complete its task.

Thanks,

Lalitha

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 29 2014
Added on Jun 16 2014
2 comments
2,395 views