Materialized View Log Not Registering/Purging in 11.2.0.1 Master; SLOG$ Entry Missing (Doc ID 1155843.1)

Last updated on JULY 29, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.1 [Release 11.2]
Information in this document applies to any platform.

Symptoms

  1. Creating a distributed materialized view in a mview database < 11.2 with an 11.2.0.1 master database seems to succeed (no errors), but the mview logs are no longer purging in the 11.2.0.1 master database.
  2. Manually registering the materialized view has no effect.
  3. When the following query is run, it does not show that the materialized view is registered:
COLUMN snapshot_id HEADING 'SnapshotID' FORMAT b9999999999
COLUMN owner HEADING 'Owner' FORMAT A6
COLUMN name HEADING 'Mview Name' FORMAT A30
COLUMN snapshot_site HEADING 'Mview Site' format a30
COLUMN current_snapshots HEADING 'Last Time Refresh' format a21

select l.snapshot_id, owner, name, substr(snapshot_site,1,30) snapshot_site,
to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots
from dba_registered_snapshots r, dba_snapshot_logs l
where r.snapshot_id = l.snapshot_id (+)
and l.master='&table';


>> no rows selected
>>

Changes

Master was upgraded to 11.2.0.1.   Mview database is 10.2.0.4, but bug indicates the issue will occur with mview databases < 11.2.  The materialized view log was purging until the materialized view was dropped and recreated.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms