otak-otak | favorite evening food

September 4, 2008

streams printing error queue ORA-01403 related

Filed under: replication — ora62 @ 11:44 am

Definitely we have to know/care about application behavior for the future debugging, tracing and error handling when configuring oracle streams environment.

I believe that after configuring oracle streams environment 99% we have to monitor for the future ‘fun’ :)

1. Configuration

Configure oracle streams environment is quite handy and easy for us. Oracle given us a package to generate necessary scripts that needed for configuration to take place. (I have used this facility in oracle 10gR2). Streams also can communicate to cross platform with cross oracle version…this is the feature that really useful for my life :)

2. The game starting now

After configuration has been done. Error queue always happening at destination database, when user updating data let say master form, even when application server which point to destination database was killed, sadly synchronization was not happened, it always give me error message “ORA-01403: no data found”.

3. Printing error

Look at stream documentation, we can find script to print all error message with particular data either by specifying local transaction ID or can grab all from the DBA_APPY_ERROR. The script  simply extract and print LCR vertically.

4. Resolve error

Let me check each error and do manual resolution to know the pattern of the erroneous  by updating data on destination, using this method until my hand cannot accept again and bore to press the keyboard :)

5. Manual error handling

Based on the pattern that I got from previous step, I developed pl/sql package to handle the error in the streams destination due to apply process is in the destination. This package simply extract LCR and find the row in the particular table’s row updating it to match with the OLD LCR’s value. Interestingly this approach can handle for some tables (actually table with few column). For a lot of column’s tables this solution cannot accomplished.

6. Trace apply error queue

To trace streams apply error to see what is going on:

alter session set events=’10308 trace name context forever, level 8′ ;
alter session set events=’26700 trace name context forever, level 15999′;
alter session set events=’1403 trace name errorstack level 1′;

Find interesting for the tables which has lot of column, which is not get printed by print_lcr as of point 3, event if I changed my packaged to accommodate the hidden column, it always skipped by print_lcr (my pl/sql also utilize print_lcr to extract LCR), so… is the game over? :)

7. Final solution

Finally by searching document for the comparability between OLD value in the LCR and the existing record at the destination’s table found interesting package to do it…..

DECLARE
cols DBMS_UTILITY.LNAME_ARRAY;
BEGIN
cols(1):=’COLUMN1′;
cols(2):=’COLUMN2′;
cols(3):=’COLUMN3′;
DBMS_APPLY_ADM.COMPARE_OLD_VALUES(
object_name  => ‘SCHEMA.TABLENAME’,
column_table => cols,
operation    => ‘*’,
compare      => false);
END;

The above package really safe my life :), it simply will not performs comparation for the listed columns, since streams when applying LCR will compare all columns even PK has been added to the supplemental log. This behavior as I understand aimed to do synchronization for tables without PK, Unique indexes.

now the game paused…waiting for next fun :)

Ujang| Indonesia | Jakarta

Advertisements

Create a free website or blog at WordPress.com.