Recently we had following error in one of our production databases which uses Materialized view and I could see following ORA messages in its alert log and we noticed that it is occurring due to a privilege issue.
Tue Mar 19 11:14:00 2013
Errors in file /orasw/pmqc1/app/oracle/diag/diag/rdbms/pmqc1/pmqc1/trace/pmqc1_j007_22675516.trc:
ORA-12012: error on auto execute of job 103
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Tue Mar 19 11:16:00 2013
Contents of trace file
Trace file /orasw/pmqc1/app/oracle/diag/diag/rdbms/pmqc1/pmqc1/trace/pmqc1_j007_22675516.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /orasw/pmqc1/app/oracle/product/11.2.0/db
System name: AIX
Node name: prdmqcx001
Release: 1
Version: 6
Machine: 00F65A334C00
Instance name: pmqc1
Redo thread mounted by this instance: 1
Oracle process number: 109
Unix process pid: 22675516, image: oracle@prdmqcx001 (J007)
*** 2013-03-19 11:13:59.952
*** SESSION ID:(2063.11133) 2013-03-19 11:13:59.952
*** CLIENT ID:() 2013-03-19 11:13:59.952
*** SERVICE NAME:(SYS$USERS) 2013-03-19 11:13:59.952
*** MODULE NAME:() 2013-03-19 11:13:59.952
*** ACTION NAME:() 2013-03-19 11:13:59.952
Failed REFRESH STATEMENT
/* MV_REFRESH (DEL) */ DELETE FROM "MV_ADMIN"."TEST" SNA$ WHERE "ROW_ID" IN (SELECT /*+ NO_MERGE HASH_SJ */ * FROM (SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "ENTERPRISE_TMO_PRODUCTION_NEW
_"."MLOG$_TEST" "MAS$" WHERE "MAS$".SNAPTIME$$ > :B_ST0 ) AS OF SNAPSHOT(:B_SCN) MAS$) AND "MARKER" = 1
*** 2013-03-19 11:13:59.992
*** 2013-03-19 11:14:00.001
ORA-12012: error on auto execute of job 103
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
In our case, MV_ADMIN.TEST materialized view is created with UNION of following tables.
Enterprise_2012_enterprise_tes.TEST
Enterprise_2013_enterprise_tes.TEST
Enterprise_tmo_production_new_.TEST
From the following query you could see that MV_ADMIN schema user does not have select privileges to Enterprise_tmo_production_new_ MLOG$_TEST mview log table. This could be the reason for failures. Now you might ask why it did not fail yesterday or even today after I manually refreshed it from command line. I need to assume that most of the time system is using full refresh and we get error when it try to do a fast refresh.
select grantee,owner,table_name,privilege from dba_tab_privs where table_name like 'MLOG$_TEST' and grantee='MV_ADMIN';
GRANTEE OWNER TABLE_NAME PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
MV_ADMIN ENTERPRISE_2012_ENTERPRISE_TES MLOG$_TEST SELECT
MV_ADMIN ENTERPRISE_2010_ENTERPRISE_TES MLOG$_TEST SELECT
MV_ADMIN ENTERPRISE_2011_ENTERPRISE_TES MLOG$_TEST SELECT
MV_ADMIN ENTERPRISE_2013_ENTERPRISE_TES MLOG$_TEST SELECT
MV_ADMIN PRODUCT_DEV_QC10 MLOG$_TEST SELECT
MV_ADMIN SEATTLE_PRODUCT_DEV_DB MLOG$_TEST SELECT
Just to follow Oracle’s recommendation, I granted select privileges and I did not get the ORA error anymore
OPS$ORACLE@pmqc1> grant select on ENTERPRISE_TMO_PRODUCTION_NEW_.MLOG$_TEST to MV_ADMIN;
Grant succeeded.
OPS$ORACLE@pmqc1> select grantee,owner,table_name,privilege from dba_tab_privs where table_name like 'MLOG$_TEST' and grantee='MV_ADMIN';
GRANTEE OWNER TABLE_NAME PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
MV_ADMIN ENTERPRISE_TMO_PRODUCTION_NEW_ MLOG$_TEST SELECT
MV_ADMIN ENTERPRISE_2012_ENTERPRISE_TES MLOG$_TEST SELECT
MV_ADMIN ENTERPRISE_2010_ENTERPRISE_TES MLOG$_TEST SELECT
MV_ADMIN ENTERPRISE_2011_ENTERPRISE_TES MLOG$_TEST SELECT
MV_ADMIN ENTERPRISE_2013_ENTERPRISE_TES MLOG$_TEST SELECT
MV_ADMIN PRODUCT_DEV_QC10 MLOG$_TEST SELECT
MV_ADMIN SEATTLE_PRODUCT_DEV_DB MLOG$_TEST SELECT
No comments:
Post a Comment