Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
есть множество базовых таблиц, на которых висят mv-логи (MLOG$_BaseTbl1, MLOG$_BaseTbl2 ... MLOG$_BaseTblN ) и от которых зависят разные всякие MViews ( с опциями REFRESH FAST ON DEMAND !). И некий джоб, который "рефрешивает" все эти вьюхи каждые 15 минут. Всё отлично и быстро работает.
Проблема однако начинается, когда кто-то изменяет (DDL) какую-то из базовых таблиц BaseTbX (ну напр. новая колонка на базовой таблице (которая к MLOG$ и MVs) не имеет отношения. Потом рефреш с опцией Fast ("F" или "?") уже нормально не работает.
автор
(ORA-12048: error encountered while refreshing materialized view , ORA-00904: "MYSCHEM$"."CHANGEDDATETIME" ORA-06512: in "SYS.DBMS_SNAPSHOT", row 2821)

Работает только с "C"(=complete).
Если правильный универсальный подход для решения этой проблемы? (а то пока, после каждого DDL на одной из базовых таблиц, о приходится полностью удалять(drop) все MLOG$_ и MViews и пересоздавать их снова)

Заранее всем спасибо за конструктивные советы!

____________________________________________________________
сейчас работаю на: Oracle 12.1.0.2.0, 64bit, standard edition, OU-Linux.
3 июн 16, 17:59    [19256291]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
и ещё чисто DBA-шный вопрос: как отключить (в Oracle-12c) запись в alert.log и trc-ы сообщений о проблемах с MViews?

автор
Thu Jun 02 18:30:02 2016
Errors in file /azt/opt/oracle/admin/aztpro/diag/rdbms/aztpro/aztpro/trace/aztpro_j003_20407.trc:
ORA-00904: invalid identifier: "MAZ$"."KOSTENART":


а то для нас это некоторый overkill! Ошибки мы ловим в свой лог! (которые пишется из нашего JOB-a)
3 июн 16, 18:13    [19256331]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
UDW
Member

Откуда: Самара
Сообщений: 1096
ORAngutang,

Если вставляемые столбцы не отражены в матвьюхе, то после операции DDL надо выполнить dbms_repcat.generate_replication_support, чтобы обновить триггеры для имененной родительской таблицы. Все должно работать.
3 июн 16, 19:11    [19256485]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
UDW,

Cпасибо, но пока не идёт! Пробую:

автор
begin dbms_repcat.generate_replication_support( sname => 'MYSCHEMA', oname => 'MLOG$_BaseTblX', type => 'table' ); end;


получаю:
ORA-23308 object does not exist or is invalid



а что в "oname =>" подставлять то? Базовую таблицу? Её MLOG$?(cм выше)? Таблицу самой MV? Пробывал и то и другое и третее! Во всех 3х случаях огребаю ORA-23308 !

P.S. это случайно не EE-фича? А то у нас Standard Edition! ;-)
3 июн 16, 20:26    [19256671]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
UDW
Member

Откуда: Самара
Сообщений: 1096
ORAngutang,

Имя объекта надо указывать, а не имя журнала.
Кстати, журнал строится по первичному ключу.
Все записи в журнале делаются триггерами реп.поддержки.
3 июн 16, 21:53    [19256896]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
ORA__SQL
Member

Откуда: Moscow
Сообщений: 1774
ORAngutang,
А что если прикрутить:
drop materialized view preserve table и затем create materialized view on prebuilt table
3 июн 16, 22:04    [19256928]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
ma1tus
Member

Откуда:
Сообщений: 752
ORAngutang
и ещё чисто DBA-шный вопрос: как отключить (в Oracle-12c) запись в alert.log и trc-ы сообщений о проблемах с MViews?

автор
Thu Jun 02 18:30:02 2016
Errors in file /azt/opt/oracle/admin/aztpro/diag/rdbms/aztpro/aztpro/trace/aztpro_j003_20407.trc:
ORA-00904: invalid identifier: "MAZ$"."KOSTENART":


а то для нас это некоторый overkill! Ошибки мы ловим в свой лог! (которые пишется из нашего JOB-a)


интересно, а откуда ваш job берет информацию о таких ошибках?
4 июн 16, 06:50    [19257350]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
UDW
ORAngutang,

Все записи в журнале делаются триггерами реп.поддержки.


А с какого перепугу ты решил у ORAngutang'а advanced replication с мастерами, триггeрами и прочее? Скорее всего у него банально basic replication: таблица + журнал + матпредставление с fast refresh. И он должен работать:

SQL> select  banner
  2    from  v$version
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> create table tbl(ename varchar2(20))
  2  /

Table created.

SQL> alter table tbl
  2    add constraint tbl_pk
  3      primary key(ename)
  4  /

Table altered.

SQL> create materialized view log on tbl
  2  /

Materialized view log created.

SQL> create materialized view tbl_mv
  2    refresh fast
  3    on demand
  4    as
  5      select  *
  6        from  tbl
  7  /

Materialized view created.

SQL> insert
  2    into tbl
  3    select  ename
  4      from  emp
  5  /

14 rows created.

SQL> commit
  2  /

Commit complete.

SQL> exec dbms_mview.refresh('tbl_mv','f');

PL/SQL procedure successfully completed.

SQL> alter table tbl
  2    add job varchar2(20)
  3  /

Table altered.

SQL> exec dbms_mview.refresh('tbl_mv','f');

PL/SQL procedure successfully completed.

SQL> update tbl
  2     set job = (select job from emp where emp.ename = tbl.ename)
  3  /

14 rows updated.

SQL> commit
  2  /

Commit complete.

SQL> exec dbms_mview.refresh('tbl_mv','f');

PL/SQL procedure successfully completed.

SQL> select  *
  2    from  tbl_mv
  3  /

ENAME
--------------------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT

ENAME
--------------------
SMITH
TURNER
WARD

14 rows selected.

SQL>


Т.e. добавление полей в базовую таблицу на MV не влияет - матпредставление просто их не видит ведь контейнер матпредставления создается на базе select'a уазанного в матпредставлении.

SY.
4 июн 16, 16:02    [19258073]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
UDW
Member

Откуда: Самара
Сообщений: 1096
SY,

Перепугу нет, но AR основана на механизме MV.
4 июн 16, 18:31    [19258382]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
UDW
AR основана на механизме MV.


И? Что теперь все должны использовать AR для простого MV?

SY.
4 июн 16, 18:49    [19258421]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
UDW
Member

Откуда: Самара
Сообщений: 1096
SY,

Ok. Ваше предложение?
4 июн 16, 21:20    [19258656]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
UDW
Ok. Ваше предложение?


Так я вроде показал что все работает. Пока ORAngutang не приведет тест кейс предложений не будет.

SY.
4 июн 16, 21:44    [19258692]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
SY
UDW
Ok. Ваше предложение?


Так я вроде показал что все работает. Пока ORAngutang не приведет тест кейс предложений не будет.

SY.


Вообще-то cитуация очень похожа на "кашу маслoм можно и испортить". Тот кто модифицирует таблицу "заодно" переcоздает и журнал при этом теряя все изменения с момента последнего обновления (даже если их и не было - Oracle этого ведь не знает и руководствуется только сравнением дат создaния журнала и последнего обновления матпредставления):

SQL> alter table tbl
  2    add sal number
  3  /

Table altered.

SQL> drop materialized view log on tbl
  2  /

Materialized view log dropped.

SQL> create materialized view log on tbl
  2  /

Materialized view log created.

SQL> exec dbms_mview.refresh('tbl_mv','f');
BEGIN dbms_mview.refresh('tbl_mv','f'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."TBL" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2802
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3039
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2998
ORA-06512: at line 1


SQL>


SY.

Сообщение было отредактировано: 4 июн 16, 21:58
4 июн 16, 21:57    [19258714]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
UDW
Member

Откуда: Самара
Сообщений: 1096
SY,

Вобще есть компромисс - force refresh.
4 июн 16, 22:51    [19258890]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
UDW
Вобще есть компромисс - force refresh.


Вообще проще не удалять/переcоздавать журнал. Конечно force refresh в таком случае ошибки не выдаст но выполнит complete refresh который может длится дoлго, потребует кучу UNDO (взaвисимости от ATOMIC_REFRESH) и нагенерирует кучу redo.

SY.
5 июн 16, 00:44    [19259039]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
чё-то реально непонятное происходит:

cоздаю м-лог на одной из старых наших базовых таблиц:

 create materialized view log on  MYSCH.MYTBLX WITH ROWID INCLUDING NEW VALUES;


потом пытаюсь создать м-вью:

CREATE MATERIALIZED VIEW MYSCH.kbortil_fi_agg_mv
REFRESH FAST ON DEMAND AS

...

огребаю:

ORA-12033: cannot use filter columns from materialized view log on "MYSCH"."MYTBLX"

потом создаю клон базовой таблицы:

create table MYSCH.MYTBLX2
as select * from MYSCH.MYTBLX where 1 = 0 


делаю всё тоже самое на клоне MYTBLX2 (mlog + mv). Всё получается!!! Может это что-то системное? Как это проверить? Куда копать???
6 июн 16, 20:26    [19264778]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
ORAngutang,

Возможно 11g Virtual Columns and Fast Refreshable Materialized Views (What In The World)

SY.
6 июн 16, 21:40    [19264988]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
SY
ORAngutang,

Возможно 11g Virtual Columns and Fast Refreshable Materialized Views (What In The World)

SY.


Cпасибо огромнейшее за наводку! Благодаря ней мы нашли workaround. Вообщем-то что Оракл там за кулисами делает, это просто праздник какой-то:

select * --table_name, column_name, data_default, virtual_column, hidden_column
from dba_tab_cols
where owner = 'MYSCH'
and virtual_column = 'YES'
and Table_Name = 'MYTBLX'
and Column_Name like 'SYS_ST%'

SYS_STSR9JGQU6238JVT6LX3ZIY9MA SYS_OP_COMBINED_HASH("ID","PARENTFIRMAID")

оказывается, он лепит похоже некую extended statistics в некие созданые за кулисами hidden-сolumns, и вот они то с fast-refreshable MVs как раз таки и не дружат! Баг старый (MOS.10281402) с 11g-времён (c 2010) протянутый аж до 12с: :-(
https://blogs.oracle.com/sql/entry/ora_54033_and_the_hidden

если на базовых таблицах эту хрень (extended statistics) удалить:

begin dbms_stats.drop_extended_stats( ''MYSCH', ''MYTAB', '(ID,PARENTFIRMAID)'); end;

то проблема решается! Т.е. после этого создаются без ошибок (ORA-12033) все MV и их можно без проблем (fast-)рефрешевать:

begin dbms_mview.refresh( list => 'mysch.kportail_myviewsoandso22', method => 'F' ); end;

P.S. остаётся надеяться, что удаление extended statistics-и не подпортит нам performance в других местах! ;-)

P.P.S. а SYS_OP_COMBINED_HASH вообще "is an undocumented feature"! Во как! :-))))
7 июн 16, 17:16    [19268090]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
part351
Member

Откуда:
Сообщений: 11
ORAngutang,

Добрый вечер.
Хочу уточнить - вы не разобрались с проблемой записи в алект лог?

автор
и ещё чисто DBA-шный вопрос: как отключить (в Oracle-12c) запись в alert.log и trc-ы сообщений о проблемах с MViews?
7 окт 19, 17:26    [21988590]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
-2-
Member

Откуда:
Сообщений: 15330
part351
с проблемой записи в алект лог
when others then null
8 окт 19, 07:19    [21989012]     Ответить | Цитировать Сообщить модератору
 Re: падающий dbms_mview.refresh( ..., method => 'F' ) после DDL на базовых таблицах!  [new]
part351
Member

Откуда:
Сообщений: 11
-2-,

Боюсь это не то...
В 12 версии оракл пишет не только ошибку в алерт лог, но и здоровенную портянку снимков, которые он не смог обновить.
И это жутко засоряет лог. Пытаюсь найти как это отключить.
8 окт 19, 13:05    [21989360]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить