oacle ORA-12034 Materialized View Log Younger Than Last Refr

Diagnosing ORA-12034 Materialized View Log Younger Than Last Refresh (

文档

ID 204127.1)

转到底部

In this Document

Purpose

Troubleshooting Steps

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later

Information in this document applies to any platform.

PURPOSE

This

troubleshooting

guide

is

intended

to

introduce

the

reader

to

the

reasons

and

ORA-12034

can

be

raised

when

refreshing

a

materialized

view,

and

to

present

the

most

common

causes

of

the

error.

It

also

gives

solutions

for

many

of

these

problems

and some troubleshooting queries.

TROUBLESHOOTING STEPS

1. Error Definition and Description

Error Definition

Oracle 8i and below: ORA-12034:

Oracle 9i and above: ORA-12034:

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_h('

Like

the

error

text

above

indicates,

ORA-12034

is

a

timing

issue

that

occurs

when

performing

a

fast

refresh

of

a

materialized

view. To troubleshoot it, it's important to get some background information regarding the refresh mechanism.

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.

2. Potential Causes of ORA-12034

There are a number of potential reasons that the last refresh time for a materialized view at the materialized view site

can be older than the oldest timestamp at the master site. These are described below.

2.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.2 Creating the materialized view before the materialized view log.

The explanation here is the same as in Section 2.1.

2.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.

2.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.

2.5 Certain DDL changes to the master table have been performed.

DDL changes that modify the master table data are not recorded in the materialized view log (truncate, alter partition,

drop partition, truncate partition, exchange partition, etc).

Truncation

of

a

materialized

view

master

table

should

be

made

specifying

the

'purge

materialized

view

log'

clause

to

force

a complete refresh, otherwise the data will be inconsistent between the master table and the materialized view. However,

specifying this clause will cause the ORA-12034 error if an attempt is made to fast refresh before complete refreshing.

This is Oracle's way of forcing the complete refresh. See

Note 179469.1

for a more detailed description of this.

Note that for Partition

Maintenance

Operations (PMOPS) such

as alter

partition,

drop

partition,

truncate partition,

etc,