Trouble with Oracle 11.2.0.3 redo logs

Question

I have a table in oracle 11.2.0.3 that I want to capture in the redo logs. The issue is that it has an sdo_geometry field. This is a legacy table that I can not change. But the good news is I do not need that sdo_geometry field. So I have created a materialized view as shown below.

CREATE MATERIALIZED VIEW LOG ON LEGACY_TABLE_NAME
WITH PRIMARY KEY
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LEGACY_TABLE_NAME_MV
  NOLOGGING
  NOCACHE
  BUILD IMMEDIATE 
  REFRESH FAST ON COMMIT 
  WITH PRIMARY KEY
  AS
    SELECT <List of non sdo_gemoetry columns> FROM LEGACY_TABLE_NAME;

The issue shows up when I do an update and look at the redo logs. Instead of seeing an update statement, I see a delete and insert statements. Since I am using a primary key, I would expect to see the update statement.

Does anyone know what I need to do to ensure that I see an update statement in the redo logs.

Thanks

Answer 1

I think you are misunderstanding what a redolog stores with what a materiliazed view log does.

Let's try to make a test for both scenarios:

• LogMiner to verify the content of the redo logfiles, something we can see using v$LOGMNR_CONTENTS.
• Example of Materialized view Log and Update operations

Oracle version: 12.2

RedoLog Contents

SQL> create table cpl_rep.test_redo_logs ( c1 number primary key , c2 number ) ;

Table created.

SQL> insert into cpl_rep.test_redo_logs values ( 1 , 1 );

1 row created.

SQL> insert into cpl_rep.test_redo_logs values ( 2 , 2 );

1 row created.

SQL> commit ;

Commit complete.

SQL> update cpl_rep.test_redo_logs set c1=3 , c2=3 where c1 = 2 ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> select * from cpl_rep.test_redo_logs ;

        C1         C2
---------- ----------
         1          1
         3          3

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit 
Production
$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 8 21:53:05 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system switch logfile ;

System altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit 
Production

Now let's start a LogMiner session by loading the redo log files into LogMiner:

SQL> exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo1/redo11.ora' , 1);
SQL> exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo2/redo21.ora' , 1);
SQL> exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo1/redo12.ora' , 1);
SQL> exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo2/redo22.ora' , 1);
SQL> exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo1/redo13.ora' , 1);
SQL> exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo2/redo23.ora' , 1);

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents where seg_name = upper('test_redo_logs') ;

COUNT(*)
----------
         4

SQL> select sql_redo , seg_name from v$logmnr_contents where seg_name = upper('test_redo_logs') ;

SQL_REDO
--------------------------------------------------------------------------------
SEG_NAME
--------------------------------------------------------------------------------
create table cpl_rep.test_redo_logs ( c1 number primary key , c2 number ) ;
TEST_REDO_LOGS

insert into "CPL_REP"."TEST_REDO_LOGS"("C1","C2") values ('1','1');
TEST_REDO_LOGS

insert into "CPL_REP"."TEST_REDO_LOGS"("C1","C2") values ('2','2');
TEST_REDO_LOGS


SQL_REDO
--------------------------------------------------------------------------------
SEG_NAME
--------------------------------------------------------------------------------
update "CPL_REP"."TEST_REDO_LOGS" set "C1" = '3', "C2" = '3' where "C1" = '2' an
d "C2" = '2' and ROWID = 'AAGKh2AAAAAJIH1AAB';
TEST_REDO_LOGS

As you can see above, the UPDATE appears normally as any other DML operation in the SQL_REDO column of V$LOGMNR_CONTENTS. So, obviously the REDO files store any update operation as long as the operation is done in Logging mode, or the database is in FORCE LOGGING MODE, in which case it doesn't matter what mode the operation is done, because it will always be stored.

Materialized View Log

Let's create a materialized view log and a materialized view as you did in your question. However, to verify the content of the MLOG$ tables, I will put the refresh on demand, instead of on commit.

SQL> create table x ( c1 number primary key , c2 number ) ;

Table created.

SQL> insert into x values ( 1 , 1 ) ;

1 row created.

SQL> insert into x values ( 2 , 2 );

1 row created.

SQL> commit ;

Commit complete.

SQL> create materialized view log on x with primary key including new values ;

Materialized view log created.

SQL> create materialized view mv_x nologging nocache build immediate refresh fast on demand with primary key as select c1 , c2 from x ;

Materialized view created.

SQL> select * from x ;

        C1         C2
---------- ----------
         1          1
         2          2

SQL> select * from mv_x ;

        C1         C2
---------- ----------
         1          1
         2          2

SQL> insert into x values ( 3 , 3 );

1 row created.

SQL> commit ;

Commit complete.

SQL> update x set c1=4 , c2=4 where c1=3 ;

1 row updated.

SQL> commit ;

Commit complete.

As we did create the materialized view with refresh on demand, now let's the content of the MLOG$ table

SQL>  select * from x ;

        C1         C2
---------- ----------
         1          1
         2          2
         4          4

SQL> select * from mv_x ;

        C1         C2
---------- ----------
         1          1
         2          2

SQL> select * from mlog$_x

        C1 SNAPTIME$ D O CHANGE_VEC                        XID$$
---------- --------- - - ---------- ----------------------------
         3 01-JAN-00 I N FE                    39406677128122001
         3 01-JAN-00 D O 00                    44473269658586765
         4 01-JAN-00 I N FF                    44473269658586765

Then I refresh

SQL>  select * from x ;

        C1         C2
---------- ----------
         1          1
         2          2
         4          4

SQL> select * from mv_x ;

        C1         C2
---------- ----------
         1          1
         2          2

SQL> exec dbms_mview.refresh('MV_X') ;

PL/SQL procedure successfully completed.

SQL> select * from mv_x ;

        C1         C2
---------- ----------
         1          1
         2          2
         4          4

SQL> select * from mlog$_x
  2  ;

no rows selected

The reason why you don't see UPDATE on DMLTYPE$$ is because you choose Primary Key as WITH clause in your Materialized View creation. In that case, only D or I will appear in the column DMLTYPE$$ , but when it is an update, you will get two rows with the same transaction ID ( XID$$ field in the example above has the same value )

However, check what happen when I use ROWID instead of PRIMARY KEY

SQL> create materialized view log on x with rowid including new values ;

Materialized view log created.

SQL> create materialized view mv_x nologging nocache build immediate refresh fast on demand with rowid as select c1 , c2 from cpl_rep.x ;

Materialized view created.

SQL> select * from cpl_rep.mv_x ;

        C1         C2
---------- ----------
         1          1
         2          2
         3          3

SQL> select * from x ;

        C1         C2
---------- ----------
         1          1
         2          2
         3          3

SQL> insert into x values ( 4 , 4 ) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> insert into x values ( 5 , 5 );

1 row created.

SQL> commit ;

Commit complete.

SQL> update x set c1=6 , c2=6 where c1=5 ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> select * from x ;

        C1         C2
---------- ----------
         1          1
         2          2
         3          3
         4          4
         6          6

SQL> select * from mv_x ;

        C1         C2
---------- ----------
         1          1
         2          2
         3          3

Let' see the content of the M$LOG table now

SQL> col m_row$$ for a18
SQL> select * from mlog$_x ;

M_ROW$$            SNAPTIME$ D O CHANGE_VEC                        XID$$
------------------ --------- - - ---------- ----------------------------
AAGKh/AAAAAJJWnAAD 01-JAN-00 I N FE                     3659458165104006
AAGKh/AAAAAJJWnAAE 01-JAN-00 I N FE                    44754731750395757
AAGKh/AAAAAJJWnAAE 01-JAN-00 U U 06                    12948119511653544
AAGKh/AAAAAJJWnAAE 01-JAN-00 U N 06                    12948119511653544

I have now 4 rows, 2 for the inserts, 1 update for the field C1 and another for the field C2, which are in fact the same transaction ( field XID$$ )

I hope it clarifies how the MLOG$ tables are populated when you choose ROWID or PRIMARY KEYY. Note that materialized view log tables using primary keys also have rupd$_ tables. The rupd$_ table supports updateable materialized views, which are only possible on log tables with primary keys.