Oracle Tips and Tricks — David Fitzjarrell

October 7, 2014

To Skip, Or Not To Skip

Filed under: General — dfitzjarrell @ 14:02

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 on a low-traffic database:


alter database stop logical standby apply;
exec dbms_logstdby.skip('DML','','%')
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 were 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:


ALTER DATABASE ABORT LOGICAL STANDBY APPLY;
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL',-
     schema_name => '', -
     object_name => '%');
EXECUTE DBMS_LOGSTDBY.SKIP ('DML', '', '%');
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;  

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 throwing the errors. Notice the second step in the skip process:


EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL',-
     schema_name => '', -
     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.

Skip, skip, skip to my Lou.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: