ORA-12034: “SCOTT＂.＂USER_TABLE” 上的實體化視圖日誌比上次重新整理後的內容新

ORA-12034: “SCOTT＂.＂USER_TABLE” 上的實體化視圖日誌比上次重新整理後的內容新

最近同事在交接工作時，發現有幾個schedule job沒有執行成功，我這邊給看了下，其中一個是由於資料庫遷移，調用dblink的host主機IP在tnsnames中沒有變更導致，還有一個是無法視圖的報錯，即報錯資訊如下：

一、錯誤記錄檔

通過查看schedual job報錯日誌，具體報錯資訊如下

ORA-12034:"SCOTT"."USER_TABLE" 上的實體化視圖日誌比上次重新整理後的內容新
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: 在 line 2

二、錯誤原因

一般出現這個錯誤是在重新整理物化視圖，方式為fast的時候會出現（ORA-12034 is a timing issue that occurs when performing a fast refresh of a materialized view.）

When a materialized view log is created for a master table, and a materialized view has been created with the REFRESH FAST option, the following timestamps will be used when validating log age.
At the materialized view site:
- Information about the last refresh time for each materialized view. The last refresh time is recorded as the timestamp when the last refresh completed successfully.
At the master table site:
- Information about the last refresh time for every materialized view using a materialized view log on that site.
The timestamps at the master site are used for two purposes:
- To maintain information concerning which rows are needed to fast refresh each individual registered materialized view.
- To maintain information concerning which rows can be purged from the materialized view log.
When a fast refresh starts, the last refresh timestamp from the materialized view site for the refreshing materialized view is compared to the oldest timestamp of ANY materialized view using the same materialized view log as the one currently being refreshed. If the oldest timestamp is newer than the materialized view site timestamp, ORA-12034 is raised. By doing this it is ensured that all changed rows since the last refresh will be refreshed, and if this can't be ensured, a complete refresh is forced. There's no exception to this behavior, and violating this main rule will result ORA-12034.

1、Dropping / recreating the materialized view log on the master table.（在主表上刪除或重建物化視圖日誌）

If a materialized view was created at time T1 and materialized view log was created at time T2, we can't ensure that all changes made between T1 and T2 will be in the materialized view after fast refresh. Therefore complete refresh is mandatory.

2、Creating the materialized view before the materialized view log.（物化視圖建立早於物化視圖日誌）

 The explanation here is the same as in Section 2.1.

3、The previous refresh for the materialized view did not complete successfully.（之前的物化視圖重新整理沒有成功）

When a refresh starts, the last refresh time of the materialized view is set to '01-JAN-1950'. This guarantees that if the refresh fails for any reason, then an ORA-12034 error will be signaled and a complete refresh will be forced. When the refresh succeeds, this date is updated to the proper time. If it doesn't get updated because of some failure during the refresh, the next time the refresh runs, '01-JAN-1950' is used to validate the log age.

4、 Creating a materialized view takes longer than the time it takes all other materialized views currently using the materialized view log to refresh.

If there are other materialized views using the materialized view log on the master table, and all of these other materialized views start their refreshes AFTER the new materialized view creation has started but complete their refreshes BEFORE the new materialized view creation has completed, then fast refreshes will fail with ORA-12034. Materialized view registration is based on the starting time of the creation, but as the last step of the operation. If that start time is older than the oldest timestamp currently registered, the new materialized view will not be registered. A complete refresh is required to register the materialized view, but it may not avoid the ORA-12034 error the next time a fast refresh is attempted.

There are three ways to resolve this problem:

- Stop the refresh of at least one other materialized view that is using the materialized view log before
creating the new one.
- In production system the previous option might not be possible. For this situation, a temporary materialized view can be created which uses the same log. If this temporary materialized view is not refreshed while the new materialized view is created, the new materialized view creation can complete successfully.
- Use deployment templates to create the materialized view environment at materialized view sites. This problem will not occur if deployment templates are used. See the Advanced Replication documentation for information about deployment templates.

5 、Certain DDL changes to the master table have been performed.

6、 Master table reorganization.

7、 Materialized view registration failed at the master site.

 8、Incorrect conversion of a materialized view log from ROWID to primary key.

9、Manual deletion of sys.slog$ entry for the materialized view.

三、解決方案

1、全量重新整理物化視圖

exec dbms_mview.refresh('SCOTT.USER_TABLE','C');

exec dbms_mview.refresh('SCOTT.USER_TABLE');

2、調整快速舒心日誌內容

select * from sys.slog$
SELECT SOWNER, VNAME, MOWNER, MASTER, to_char(SNAPTIME,'yyyy-mm-dd hh24:mi:ss') FROM SYS.SNAP_REFTIME$;
insert into sys.slog$ values('DHSH','USER_BASIC',NULL,171,NULL,to_date('2014-01-07 15:44:18','yyyy-mm-dd hh24:mi:ss'),null,null);
commit;

四、附錄

1、MOS方案

    Diagnosing ORA-12034 Materialized View Log Younger Than Last Refresh (文檔 ID 204127.1)

（1）Error Definition and Description

Error Definition

Oracle 8i and below: ORA-12034: "snapshot log on "%s"."%s" younger than last refresh"
Oracle 9i and above: ORA-12034: "materialized view log on "%s"."%s" younger than last refresh"

Cause: The materialized view log was younger than the last refresh.
Action: A complete refresh is required before the next fast refresh.

Note: A complete refresh can be done using the command:

execute dbms_mview.refresh('"CORP"."NM_SV_RANGE"','C');

2、全量重新整理物化視圖

https://www.bkjia.com/topicnews.aspx?tid=12

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-03/151121.htm