Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted October 13, 2014

Skipping Schema DML in an Oracle 11.2 Logical Standby Database

By David Fitzjarrell

An interesting issue presented itself just recently with a logical standby database I manage. The database is used for generating reports and the client wanted to skip all DML activity for a given schema as it wasn't necessary for reporting purposes. I had done this in version 10.2.0.x; it was a simple procedure:

alter database stop logical standby apply;
exec dbms_logstdby.skip('DML','<schema>','%')
alter database start logical standby apply;

Pretty simple stuff, really. In the 10.2.0.x standby configuration I had been lucky that no DDL changes for the specified schema had occurred so missing a step wasn't obvious as it didn't impact the apply process. For a while the 11.2 standby was happily applying transactions and skipping DML for the specified schema. Overnight, however, the apply process shut down for no immediately apparent reason. The schema in question had all of its DML ignored so why did apply stop? Looking into the alert log provided the following set of errors:

ORA-26808: Apply process AS01 died unexpectedly.
ORA-02149: Specified partition does not exist
LOGSTDBY Apply process AS03 server id=3 pid=85 OS id=27432 stopped

This required further investigation. Checking the primary database I found 594 partitions for tables owned by the schema that was supposed to be skipped. On the logical standby I found 43 fewer partitions for that same schema. So it appeared that the actions I took the day before, to skip all DML activity for this schema, weren't enough. Pondering the issue I went to the Oracle online documentation and eventually found information showing a step I had missed. Skipping the schema DML wasn't enough, the DDL also had to be ignored and to do that required the following set of instructions:

     schema_name => '<schema>', -
     object_name => '%');
EXECUTE DBMS_LOGSTDBY.SKIP ('DML', '<schema>', '%');

Using the abort command does two things: it stops the apply process and it rolls back any pending transactions. This is necessary so that 'offending' transactions, the ones that throw the listed error, get rolled back; this 'resets' the transaction 'pointer' for Log Miner so that when the apply process is restarted it can skip the transactions that threw the errors. Notice the second step in the skip process:

     schema_name => '<schema>', -
     object_name => '%');

This is necessary to tell Oracle to completely skip any DDL for the specified schema and keeps transaction processing going even if there are DDL changes on the primary database for the schema you want to skip. Since I hadn't executed the skip procedure twice, with the two sets of slightly different parameters, only half of the job was done; it wasn't apparent until partitions were added to the specified schema on the primary that the skip wasn't processed completely. Once that missing step had been executed, and the original skip statement was re-submitted, the apply process started applying transactions and the standby lag started getting shorter and shorter.

It pays to read the documentation when performing unfamiliar tasks or what seem to be familiar tasks in a new release. Missed steps can create issues that can seem to be mysterious; verifying the required steps is necessary to ensure a task is completed successfully. What you may have given the least thought to may be the very thing that stops you in your tracks.

See all articles by David Fitzjarrell

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM