Oracle streams - errors and fixes

Hit a problem with the Streams capture process aborting with a duplicate value in the index I_STREAMS_APPLY_SPILL_TXN on the table sys.STREAMS$_APPLY_SPILL_TXN. Quick look on the interweb revealed the answer.. which was to delete old rows from the table.
The view dba_capture tells you the process has aborted.

To start and stop the process use:-
BEGIN
dbms_capture_adm.start_capture(capture_name => 'capture_e1strm');
END;

BEGIN
dbms_capture_adm.stop_capture(capture_name => 'capture_e1strm');
END;



A second error involved the propagation process. The error in the logs was
ORA-25307: Enqueue rate too high, flow control

the view dba_propagation showed the same error but the process
was still enabled
The answer turned out to be to
restart the propagation process.
i.e.

BEGIN
dbms_propagation_adm.stop_propagation(propagation_name => 'PROP_E1DB_TO_E2DB');
END;

BEGIN
dbms_propagation_adm.start_propagation(propagation_name => 'PROP_E1DB_TO_E2DB');
END;

Useful site is :


http://download.oracle.com/docs/cd/B28359_01/server.111/b28321/strms_trouble.htm



enabled

Oracle Streams - streaming two users into one

Hit and (hopefully) resolved an number of issues with Oracle Streams recently.

1) to stream two tables with the same structure owned by two different users into one destination user i.e. user1.tab and user2.tab on the source instance into user1.tab on the destination instance.

The answer seems to be to set up a normal schema stream for user1and user2 on the destination. Then modify the capture progress at the destination for user2 as follows:-

CREATE OR REPLACE PROCEDURE user1.write_reh4_lcrs
(v_any IN sys.AnyData)
IS
v_lcr SYS.LCR$_ROW_RECORD;
v_rc pls_integer;
v_command VARCHAR2(10);
v_old_values SYS.LCR$_ROW_LIST;
BEGIN
v_rc := v_any.GETOBJECT(v_lcr);
v_lcr.SET_OBJECT_owner('user1');
v_lcr.EXECUTE(TRUE);
END;



and then mod the capture process to use the proc

BEGIN
dbms_apply_adm.set_dml_handler(
object_name => 'user2.reh4'
,object_type => 'TABLE'
,operation_name => 'INSERT'
,error_handler => FALSE
,user_procedure => 'user1.write_reh4_lcrs'
,apply_database_link => NULL
,apply_name => NULL
);
END;

BEGIN
dbms_apply_adm.set_dml_handler(
object_name => 'user2.reh4'
,object_type => 'TABLE'
,operation_name => 'UPDATE'
,error_handler => FALSE
,user_procedure => 'user1.write_reh4_lcrs'
,apply_database_link => NULL
,apply_name => NULL
);
END;

BEGIN
dbms_apply_adm.set_dml_handler(
object_name => 'user2.reh4'
,object_type => 'TABLE'
,operation_name => 'DELETE'
,error_handler => FALSE
,user_procedure => 'user1.write_reh4_lcrs'
,apply_database_link => NULL
,apply_name => NULL
);
END;

Oracle streams - lost archive logs

We recently had a problem when we lost the archive logs from the source database before they could be applied to the destination database..... don't ask how or why OK... enough already!

I spent ages trying to figure out how to reset the scn on the capture process to get the whole thing up and running again ( having manually resynced the tables). Turn out the way to do it was to drop and recreate the capture. I used drop_unused_rule_sets => true which meant I had to recreate all the associated rule but you may be able to get away with just dropping and recreating the capture.
i.e.

BEGIN
dbms_capture_adm.drop_capture(capture_name => 'capture_e1strm'
, drop_unused_rule_sets => FALSE
);
END;

followed by

BEGIN
dbms_capture_adm.create_capture(queue_name => 'capture_e1q'
,capture_name => 'capture_e1strm'
,source_database => 'ecit1'
,use_database_link => TRUE
);
END;

Gather Stats – the importance of granularity

I got caught out today when using dbms_stats.gather_table_stats. I specified a partition name in the statement because I just wanted to gather stats for a particular partition. I did not however specify the granularity. The default value for the parameter will also cause dbms_stats.gather_table_stats to generate the global table stats. The partition I was trying to get stats for was only 60 millions rows so it should only have taken a few hours, the full table however contained 700 million rows so gathering the stats for the partition and the global table stats took 14 hours!

For reference the table below option for granularity.

GRANULARITY	Table Global	Partition Global	Partition Statistics	Subpartition Statistics
GLOBAL	YES	NO	NO	NO
PARTITION	NO	YES	YES	NO
DEFAULT	YES	YES	YES	NO
SUBPARTITION	NO	NO	YES	YES
ALL	YES	YES	YES	YES

This was obtained from the following webpage

http://www.dbazine.com/oracle/or-articles/nanda5

Moving a partitioned table’s tablespace

Moving the partitions with a table is straight forward e.g.

alter table reh_temp move partition p200705

tablespace TSD_Q_BLB_P200705;

This does not however move the table ‘header’ for that you need to do

alter table reh_temp modify default attributes tablespace tsd_q_blb;

Reference

alter table reh_temp modify default attributes tablespace tsd_q_blb; see

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:416718680907

about half way down the page search for alter table invoices modify default attributes tablespace manual;

The same is true of indexes but I do not know what the syntax is for moving indexe headers

Splitting a table partition

If speed at which this operation occurs is dependent on the number of rows in the partition. Empty partition split immediately. An example of the command is :-

ALTER TABLE reh_temp

SPLIT PARTITION pmax AT (TO_DATE('01-AUG-2007','DD-MON-YYYY'))

INTO (PARTITION p200707

,PARTITION pmax)

UPDATE GLOBAL INDEXES;

Exchanging a table for a table partition

An example of this command is

ALTER TABLE reh_temp

exchange partition p200609 with TABLE blb_200610

including indexes;

If the indexes on the ‘donor’ table are all valid etc and the partitioned table has the same indexes AND THEY ARE LOCAL INDEXES then the index should be carried across into the new partition and remain valid. Any global indexes will go invalid.