Thursday, March 19, 2009

What If You Don't Prepare The Source Table For Instantiation?

As you know, the DBMS_STREAMS_ADM package provides shortcuts to setup Streams; one of this shortcut is that it prepares the table for instantiation on the source database when you use ADD_TABLE_RULES. Obviously this assumes the package connects to that source database if that's a downstream capture. To say it in another way, you must use a database link if you want the table to be prepared by ADD_TABLE_RULES in the case of a downstream capture; If that's not the case, don't forget to use DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION on the source database.

But what happens if you don't prepare the source table for instantiation?

There is probably no better way to check than to test it. I've tried to change my approach this time! I won't describe how to setup your environment; Instead you'll find attached a script that set Streams up with that exact case, i.e. it doesn't run prepare_table_instantiation! If you want to give it a try, you can download it from this link: streams-no-prepare.sql. Use Right-Click->Save As... Hopefully it won't be too buggy!

The script requires 1 only database: (1) the database should be 11g; that may work with 10g but I did not test it at all, (2) it must have the USERS and TEMP tablespaces created, (3) it has to be in ARCHIVELOG mode and (4) you should be able to connect with "/ as sysdba". To use the script, run:
sqlplus /nolog @streams-no-prepare.sql
If you get any issue, leave a comment on this post with your email; it won't appear and we'll discuss your issue offline if you find a bug. The script should:
  • Create the whole configuration, except for the table preparation:
  • Start the Apply and Capture
  • Create a second script drop-no-prepare.sql to drop the configuration, once you are done with it
If you wait for a few minutes, Streams should work:
col capture_name format a16
col state format a30 wor wra
col capture_message_number format 999999999999999
col apply_name format a16
set lines 80

select capture_name,
capture_message_number capture_scn,
apply_name,
state
from v$streams_capture;

CAPTURE_NAME CAPTURE_SCN APPLY_NAME STATE
---------------- ----------- ---------------- ------------------------------
STREAMS_CAPTURE 1316028 STREAMS_APPLY CAPTURING CHANGES

select apply_name,
status
from dba_apply;

APPLY_NAME STATUS
---------------- --------
STREAMS_APPLY ENABLED
So we'll perform our test:
insert into source.t1
values (3,'Text 3');

commit;

col id format 99
col text format a10

select *
from destination.t1;

ID TEXT
--- ----------
1 Text 1
2 Text 2
But even if you wait you'll get no apply error and the change will never be applied. Prepare the table for instantiation now and insert a new data:
exec dbms_capture_adm.prepare_table_instantiation('source.t1','keys');

insert into source.t1
values (4,'Text 4');

commit;
If you check the destination table you'll see only changes after the table has been instantiated are captured:
col id format 99
col text format a10

select *
from destination.t1;

ID TEXT
--- ----------
4 Text 4
1 Text 1
2 Text 2
As you can see it's important to prepare the instantiation so that the capture can capture. There is many more things to understand from this example and we'll detail them in the coming weeks. For now, delete your Streams configuration:
sqlplus /nolog @drop-no-prepare.sql

1 comment:

  1. Capture_message_number is only the scn of last scannned. scn not the last built CLR. The capture process read any scn (either from log_buffer or from old archives) and oppose the SCN to all available rules of the capture, including the first and start scn as well as the ignore_scn and rules defined into dbms_rules_adm. Only if the SCN pass all these checks, it is transformed into an LCR. Thus if you don't have a table instantiation (or schema instantiation) then the SCN fails the very first check and is never transformed into a LCR. You could have spared yourself this demo.

    ReplyDelete