Wednesday, October 24, 2012

Skipping Users In Logical Standby

Logical standby's have their place for reporting requirements. They do require their own special attention. Logical standby's are generally used to fulfill a reporting requirement which would only cause performance issues in the production instance. However, not everything needs to be replicated to the logical standby. Individual users who have access to production environments and can create objects are one type of events that can be skipped. If they are creating objects and data with a requirement to replicate, that's a whole other issue. When we create a user in a production environment it will be replicated to the logical standby, however, we do not want their objects or the data to be replicated from production to the logical standby. So we issue the following with each user create:  
ALTER database stop logical standby apply; 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt =>'DML',schema_name => 'NewUser',object_name => '%'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt =>'SCHEMA_DDL',schema_name => 'NewUser',object_name => '%');
 alter database start logical standby apply immediate;
 **Note Substitute the actual user for NewUser.

The key is to remember to do this when each user is created. Or have an automated process that will trigger these command on the logical standby.

No comments:

Post a Comment