Wednesday, September 30, 2015

ON COMMIT Materialized View Causes ORA-02050 and ORA-02051

This example replicates a problem I discovered today. I tested it in an Oracle 11.2 database. First I created an empty table called tab1:

SQL> create table tab1
  2  (col1 varchar2(30),
  3   constraint con1 primary key (col1))
  4  /
 
Table created.
 
SQL>

Then I set up a materialized view on the table. Note that it is refreshed on commit:

SQL> create materialized view log on tab1
  2  /
 
Materialized view log created.
 
SQL> create materialized view mv1
  2  refresh fast on commit
  3  as select * from tab1
  4  /
 
Materialized view created.
 
SQL>

I added some rows to the table FROM THE LOCAL DATABASE:

SQL> insert into tab1
  2  select table_name
  3  from dba_tables
  4  where owner = 'SYS'
  5  and table_name like 'A%'
  6  /
 
97 rows created.
 
SQL>

I checked that the rows did not appear in the materialized view:

SQL> select count(*) from mv1
  2  /
 
  COUNT(*)
----------
         0
 
SQL>

Then I did a commit to refresh the materialized view:
 
SQL> commit
  2  /
 
Commit complete.
 
SQL>

… and checked that I could see the rows in the table via the materialized view:

SQL> select count(*) from mv1
  2  /
 
  COUNT(*)
----------
        97
 
SQL>

I inserted some more rows into the table FROM A REMOTE DATABASE:

SQL> insert into tab1
  2  select table_name
  3  from dba_tables@remote_database
  4  where owner = 'SYS'
  5  and table_name like 'S%'
  6  /
 
142 rows created.
 
SQL>
 
I checked that I could not see these new rows via the materialized view:
 
SQL> select count(*) from mv1
  2  /
 
  COUNT(*)
----------
        97
 
SQL>
 
If you have an on commit materialized view, you cannot insert rows into any of its master tables from a remote database. (I read this on My Oracle Support.) So when I tried to refresh the materialized view, I got an ORA-02050 and an ORA-02051:
 
SQL> commit
  2  /
commit
*
ERROR at line 1:
ORA-02050: transaction 6.25.157910 rolled back, some
remote DBs may be in-doubt
ORA-02051: another session or branch in same
transaction failed or finalized
 
SQL>
 
… and the number of rows in the materialized view did not change:
 
SQL> select count(*) from mv1
  2  /
 
  COUNT(*)
----------
        97
 
SQL> 

I also noticed the following messages in the alert log: 

Wed Sep 30 17:41:31 2015
Following on-commit snapshots not refreshed :
ANDREW.MV1
Error 2051 trapped in 2PC on transaction 6.25.157910. Cleaning up.
Error stack returned to user:
Wed Sep 30 17:41:31 2015
DISTRIB TRAN BUSDPT1.WORLD.2e3bf2d1.6.25.157910
ORA-02050: transaction 6.25.157910 rolled back, some remote DBs may be in-doubt
  is local tran 6.25.157910 (hex=06.19.268d6)
ORA-02051: another session or branch in same transaction failed or finalized
  insert pending collecting tran, scn=1759205570084 (hex=199.98d2b624)
Wed Sep 30 17:41:31 2015
DISTRIB TRAN BUSDPT1.WORLD.2e3bf2d1.6.25.157910
  is local tran 6.25.157910 (hex=06.19.268d6))
  delete pending collecting tran, scn=1759205570084 (hex=199.98d2b624) 

To isolate the exact cause, I created another table called tab2:

SQL> create table tab2
  2  (col2 varchar2(30),
  3   constraint con2 primary key (col2))
  4  /
 
Table created.
 
SQL>
 
Then I set up an on demand materialized view on the table:
 
SQL> create materialized view log on tab2
  2  /
 
Materialized view log created.
 
SQL> create materialized view mv2
  2  refresh fast on demand
  3  as select * from tab2
  4  /
 
Materialized view created.
 
SQL>
 
I inserted the same rows into the table FROM THE LOCAL DATABASE:
 
SQL> insert into tab2
  2  select table_name
  3  from dba_tables
  4  where owner = 'SYS'
  5  and table_name like 'A%'
  6  /
 
97 rows created.
 
SQL>
 
… and checked that I could not see them via the materialized view:
 
SQL> select count(*) from mv2
  2  /
 
  COUNT(*)
----------
         0
 
SQL>
 
I did a commit:
 
SQL> commit
  2  /
 
Commit complete.
 
SQL>
 
… but this had no effect on the materialized view as it was refreshed on demand:
 
SQL> select count(*) from mv2
  2  /
 
  COUNT(*)
----------
         0
 
SQL>
 
… so I did an on demand refresh:
 
SQL> exec dbms_mview.refresh('mv2');
 
PL/SQL procedure successfully completed.
 
SQL>
 
… and checked that I could see the rows I had added to the table:
 
SQL> select count(*) from mv2
  2  /
 
  COUNT(*)
----------
        97
 
SQL>
 
I added the same rows FROM THE REMOTE DATABASE:
 
SQL> insert into tab2
  2  select table_name
  3  from dba_tables@remote_database
  4  where owner = 'SYS'
  5  and table_name like 'S%'
  6  /
 
142 rows created.
 
SQL>
 
… but when I did a commit, this time it did not fail:
 
SQL> commit
  2  /
 
Commit complete.
 
SQL>
 
Of course, as the materialized view had to be refreshed on demand, the new rows did not appear in it:
 
SQL> select count(*) from mv2
  2  /
 
  COUNT(*)
----------
        97
 
SQL>
 
… so I did another on demand refresh:
 
SQL> exec dbms_mview.refresh('mv2');
 
PL/SQL procedure successfully completed.
 
SQL>
 
… and then I could see them:
 
SQL> select count(*) from mv2
  2  /
 
  COUNT(*)
----------
       239
 
SQL>

1 comment:

Dominic Brooks said...

Moaned about here:
https://orastory.wordpress.com/2013/01/15/fun-with-distributed-transactions/

Discussed here:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3339188000346289695