Oracle Streams: SET_TAG and Capture Rules (Skip DDL & DML)

If you ever tried to use dbms_streams.set_tag function to avoid replicating DDL or DML in your Streams environment, we’ll you unpleasantly surprised that it doesn’t work (I hope you didn’t try it on production right way ).

This post deals with steps you have to implement to get it working.

First, every entry in the redo log files has a tag associated with it. The default value for this tag is null. The capture process default behavior is not to check for tags at all, in other words, it will read and process every entry in the redo log regardless of it’s tag value. But what we want is for the capture process to read and process null tags only and ignore non-null tags (these are the DML or DDL that will be skipped).

So the question, how to change the capture process behavior so it will ignore non-null tags?
The answer is by adjusting the capture rules for DML & DDL (depends on whether you need to ignore both operations).

The following scripts assume you have the default Streams configuration without adding any additional custom rules.

Get Capture Rule name and rule condition.
select rule_name, rule_condition
from dba_streams_rules
where rule_set_owner = ‘STRMADMIN’
and streams_type = ‘CAPTURE’;

It should return 2 rows, 1 rule for DML and the other for DDL.

Here how the DDL capture rule looks like before changing:

((((:ddl.get_object_owner() = ’schema name’ or :ddl.get_base_table_owner() = ‘schema name’) and :ddl.get_source_database_name() = ‘database name’ )) and (:ddl.get_compatible() <= dbms_streams.compatible_10_2))

Now add condition to capture null tags only

BEGIN
DBMS_RULE_ADM.ALTER_RULE (
rule_name => ‘CWSAPP66′,
condition => ‘((((:ddl.get_object_owner() = ”CWSAPP” or :ddl.get_base_table_owner() = ”CWSAPP”) and :ddl.get_source_database_name() = ”CWSRD” ))
and (:ddl.get_compatible() <= dbms_streams.compatible_10_2) and (:ddl.is_null_tag() = ”Y”))’,
evaluation_context => NULL);
END;
/

Now from a new session

dbms_streams.set_tag(‘01’);

DDL or DML changes will not get replicated

dbms_streams.set_tag(null); — go back to replicating everything

Remember if using bi-directional replication, you have to adjust capture rules in every site.

Hazem Ameen
Senior Oracle DBA

Would you Let Women Drive in Saudi Arabia

Not so long ago, I was driving my van and sitting right next to me one of the mildest tempered people I knew in my life, my wife. However, this time, she was yelling at me saying, FOLLOW HIM, GET HIM.

The person that made my wife yell was not a thief or an attacker, but your common average driver in KSA. The driving in Saudi is a combination of rude, aggressive, and in some cases flat out suicidal. It definitely gets under your skin and you respond rather erratically like.

Back in USA people are scared of drunk drivers, but in Saudi, we are fearsome of most drivers and especially of teen drivers. There are young teens that barely can see over the wheel and driving an 8-15 passenger 4×4 SUV. It came no surprise that KSA has one of the highest car accident death rates in the world.

Knowing all risks involved, would you let your wife or daughter drive (if it is ever allowed)? Even though is none of my business, but I don’t think so, not unless KSA fixes this domestic problem. Why would anybody want to take a risk on his wife’s or daughter’s life!

If you ever in KSA, here are some tips to help you cope with this issue:

Few expatriates elect not to own a car but rather relying on company’s transportation and taxis.

Some co-workers take longer and easier routes just to elude traffic and come to the job calmer.

Know the problematic routes and times of traffic congestion to avoid them.

Remind yourself to always remain calm. This is one fight you can’t really win.

Most expatriates buy 4×4 SUVs; they are safer in case car accidents (God forbid).

Drive defensively, can’t stress this point enough.

Hazem Ameen

How to use Fine Grained Auditing (FGA) to Identify Unused Tables in Oracle 10g

After years of changes made to an in-house system, it became very hard for our developers to identify which tables are no longer used. Going through hundreds of thousands of line of code and cross referencing hundreds of tables manually is not a simple task. So, and like usual, send it over to the DBA team.

I’m must admit it wasn’t a stimulating task in the beginning, but once we figured we can use fine grained auditing (FGA), it became more exciting.

Here is the procedure we used, we have only 1 main schema:

1)      Identify tables with DML. This is easily done by checking user_tab_modifications.

2)      Create a table unused_tables containing all tables not in user_tab_modifications. With this step, you would eliminate some of the tables currently being used (hopefully most of them):

create table unused_tables as
Select table_name from user_tables
Minus
Select table_name from user_tab_modifications

3)      Enable FGA for “selects” only on the tables in previous step. FGA puts overhead on your system so don’t enable it on all your tables at once if you have thousands of tables. Enable few say 50 or use you best judgment at a time.

# generate script to enable auditing

‘exec dbms_fga.add_policy(object_schema => ”your_schema”, object_name => ”’ || table_name || ”’, policy_name => ”’ ||
table_name || ‘_P”);’
from unused_tables;

4)  Look under DBA_FGA_AUDIT_TRAIL and disable policies for tables that have a trail. Here is script we use to disable policies, we run about once a day:

# generate script to disable auditing

select ‘exec dbms_fga.disable_policy(object_schema => ”’ || object_schema || ”’ , object_name => ”’ || object_name ||
”’, policy_name => ”’ || policy_name || ”’);’
from
(
select distinct a.object_schema, a.object_name, a.policy_name
from DBA_FGA_AUDIT_TRAIL a, DBA_AUDIT_POLICIES b
where a.object_schema = b.object_schema
and a.object_name = b.object_name
and a.policy_name = b.policy_name
and b.enabled = ‘YES’
)

5)  Repeat steps 3 & 4 until you enable auditing for all tables

6)  Tables with enabled policies and no audit trail are these tables you can safely drop. Off course you have to wait an X amount of time (weeks most probably) to confirm.

7)  Be aware that if you do a nightly dump (export), it will show in the audit trail. We normally delete these entries from the audit trail. Our export is done with a different user then the schema owner:

delete from sys.fga_log$ where dbuid = ‘export user’;

commit;

Hazem Ameen
Senior Oracle DBA