Oracle : Redo / Undo for nologging table + Append + archivelog

Question

I'm trying to replicate a table from an external Oracle DB to my system using a DB Link.

First i tried doing it using an mview but it caused alot of redo/undo(I did the same steps as below but it didn't work for some reason).

Next, I went on a different strategy and created a self implementation of an mview with a table.

• Table was set as nologging when created. (verified it in dba_tables)
• Index on table is set as nologging when created. (verified it in dba_indexes).
• Database log_mode is set to ARCHIVELOG. (verified in v$database).

process of replication is being done like:

1. Set index as unusable.
2. Truncate table.
3. Insert with append hint.(INSERT /*+append*/ into tbl_name SELECT * FROM v_tbl)
4. Rebuild index nologging.

The code is written in a package which is being run with a scheduler_job on an interval.

v_tbl - a view that encapsulate the original external table structure(view is being built dynamically when compiled due to different sites external DB table names and columns) and give constant names for the columns(by the view columns aliases).

v_Tbl :

 SELECT column1 col1, 
        column1 col2 
 FROM ext_tbl@external_db;

When examining the archives using dbms_logmnr package I found a lot of redolog is being written on this table even though I'm using the append hint and no logging options for table and index.

In v$logmnr_contents view I see alot of rows for this table with operation = "DIRECT INSERT" and I can see all the data which was inserted in the redo/undo columns.

Is it related maybe to the DB link?

Am I doing anything else wrong?

Answer 1

Consider taking another look at mviews since a properly set up mview with a corresponding mview log on the parent table (so that fast refreshes are possible, which works over a db link) will be very efficient for both performance and redo generation (as only the changes to the parent table are refreshed into the mview instead of the entire table). This setup works over a database link.

As for your insert /* +APPEND */ specific question:

What is the value of: select force_logging from v$database;? Table level nologging is ignored if the database is set to force_logging. You can turn off force_logging via: ALTER DATABASE NO FORCE LOGGING;

In addition: Insert /* +APPEND */ is ignored if there are constraints or triggers on the table. Simply disabling the constraints and triggers is sufficient to allow the direct path load. You can verify whether a direct path load was done if you receive a "ORA-12838: cannot read/modify an object after modifying it in parallel" error when selecting from the table after a direct path load, but before a commit is issued.

See AskTom for a more detailed description of insert /* +APPEND */: https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484

Provided your table is set to nologging (and your database isn't set to force_logging), and you drop your indexes, disable triggers and constraints on the table, then do a direct path load, that really is all you can do to suppress redo generation.

Answer 2

It looks like you set your indexes to unusable. I believe that just means the index won't be used if you query the table.

However, during your loading, the index will be maintained with inserted and updated values, thus generating redo even when nologging is used.

"nologging" for index will only work when used with create index statement.

There may be other reasons but this is one of them.

Try the following and see if it generates less or no redos:

1. Drop indexes
2. Load table with /*+APPEND*/(will be faster too without indexes on the table)
3. Create index with "nologging" option

Answer 3

Some additional information to go along with the other answers that relates to your question (although not to your specific issue).

When you can't build a fast refresh-able materialized view and sometimes even when you can but the same values are being updated in the same rows many times, you need a different technique to keep the replication current while limiting the undo and redo volume. One possible solution is to do a merge statement from your source table to your replicated table. Since the merge is base on the current values without regard to how many times they have changed, a compromise can be made between how up to date the data is and how much redo/undo is generated to maintain the relevancy of the data.

For example, if a particular value in a particular row changes an average of three times an hour, a full refresh run once an hour will generate redo/undo for that row even if there are no changes to it. A fast refresh will maintain all three changes and apply them sequentially, but a merge would only apply the latest change and only if the data changed, yet still be as up to date as the other solutions. As a caveat, it may take longer to run, so you will have to test it to ensure it meets required refresh window.