Oracle case 02--ora-12034: "SCOTT". User_table "The solid View Log on" is newer than what was last refreshed

Recently colleagues in the handover work, found that there are several schedule job did not succeed, I looked at the next, one is due to the database migration, call Dblink host IP in TNSNames no change caused, there is no view of the error, that is, the error message is as follows:

First, error log

By viewing the Schedual job error log, the specific error message is as follows

ORA-12034: "SCOTT". " User_table "The solid View Log on" is newer than what was last refreshed
ORA-06512: In "SYS." Dbms_snapshot ", Line 2563
ORA-06512: In "SYS." Dbms_snapshot ", Line 2776
ORA-06512: In "SYS." Dbms_snapshot ", Line 2745
ORA-06512: On line 2

Ii. Causes of Errors

This error generally occurs when the materialized view is refreshed, in the form of fast ( ora-12034 is a timing issue That occurs when performing a fast refresh of a materialized view. )

When a materialized view log was created for a master table, and a materialized view have been created with the REFRESH FAST option, the following timestamps would be a used when validating log age. At the materialized view site:-information, the last refresh time for each materialized view. The last refresh time was recorded as the timestamp when the last refresh completed successfully. At the master table site:-information on the last refresh time for every materialized view using a materialized view L OG on that site. The timestamps at the master site is used for both purposes:-to maintain information concerning which rows is needed to Fast refresh each individual registered materialized view.-to maintain information concerning which rows can be purged fr Om the materialized view log. When a fast refresh starts, the last refresh timestamp from the materialized view site for the refreshing materialized vie W is compared to the oldest timestamp of any materialized view using the sameMaterialized 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's ensured, all changed rows since the last refresh would 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 would result ORA-12034.

1. dropping/recreating The materialized view log on the Master table. (deleting or rebuilding materialized view logs on the primary table)

2, Creating the materialized view before the materialized view log. (materialized views are created earlier than materialized view logs)

3. The previous refresh for the materialized view does not complete successfully. (Previous materialized view Refresh was unsuccessful)

4, Creating a materialized view takes longer than the time it takes all other materialized views currently using the Mater ialized view Log to refresh.

If There is other materialized views using the materialized view Log on the master table, and all of the these other material ized views start their refreshes after the new materialized view creation have started but complete their refreshes before The new materialized view creation has completed and then fast refreshes would 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 won't be register Ed.  A complete refresh was required to register the materialized view, but it could not avoid the ORA-12034 error the next time a Fast Refresh is attempted. There is three ways to resolve this problem:-Stop the "the" at least one other materialized view. is using the Materialized view Log beforecreating the new one.-in production system the previous option might isn't be possible. For this situation, a temporary Materialized view can be created which uses the same log.  If This temporary materialized view isn't 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 would not occur if deployment templates is used.  See the "Replication documentation for information" about deployment templates.

5. Certain DDL changes to the master table has 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.

Third, the solution

1. Full volume refresh materialized view

exec Dbms_mview.refresh (' SCOTT. User_table ', ' C ');

exec Dbms_mview.refresh (' SCOTT. User_table ');

2. Adjust the content of fast and comfortable log

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

Iv. Appendices

1. MOS scheme

Diagnosing ORA-12034 materialized View Log Younger Than last Refresh (document ID 204127.1)

(1) Error Definition and Description

Error definitionoracle 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 is younger than the last refresh. Action:a complete Refresh is required before the next fast refresh. Note:a Complete refresh can is done using the command:

Execute Dbms_mview.refresh (' "CORP". Nm_sv_range "', ' C ');

2. Full volume refresh materialized view

Oracle case 02--ora-12034: "SCOTT". User_table "The solid View Log on" is newer than what was last refreshed