Ora 12048 error encountered while refreshing materialized view

Materialized View Refresh Error ORA-12048/ORA-00001 (Doc ID 1330883.1)

Last updated on FEBRUARY 01, 2022

Applies to:

Symptoms

On : 11.2.0.1 version, Materialized Views

When attempting to refresh mview group, the following errors occur:

ERROR
————————
ORA-12048: error encountered while refreshing materialized view
ORA-00001: UNIQUE CONSTRAINT (constraint name) violated

STEPS
————————
The issue can be reproduced at will with the following steps:
1. DBMS_REFRESH.REFRESH(» «);

BUSINESS IMPACT
————————
The issue has the following business impact:
Due to this issue, users cannot refresh mview group properly.

Cause

To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

MSRFWOR Refresh Collection Snapshot Program Failed with ORA-12048 and ORA-01410 Errors (Doc ID 1484720.1)

Last updated on JANUARY 30, 2022

Applies to:

Oracle Advanced Supply Chain Planning — Version 12.1.3 and later
Information in this document applies to any platform.

When attempting to launch data collections, the Refresh Collection Snapshots (MSRFWOR) Program is failing with the following:

ERROR
————————
The degree of parallelism for Refreshing snapshots is set to: В 0
ORA-12048: error encountered while refreshing materialized view «APPS».»MTL_DEMAND_SN»
ORA-01410: invalid ROWID

How do we resolve this issue?

Solution

To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Monday, March 3, 2014

Materialized View Issues

Reference for Read Only Snapshots

Materialized Views Overview
The data in a materialized view is updated either by complete or by incremental refresh.

Log Table: To support Incremental Refresh, Oracle maintains a log table to keep track of changes on the master table.

Materialized View Related Objects

ALL_MVIEWS

USER_MVIEWS, ALL_MVIEWS, DBA_MVIEWS
Data exists on The Remote Site.
It got data about all the Materialized Views in the schema, when they were last refreshed, refresh type, SQL that was used to populate the MV, and more.

USER_MVIEWS or USER_SNAPSHOTS
SELECT mview_name,
query,
master_link,
refresh_mode,
refresh_method,
last_refresh_type,
last_refresh_date
FROM USER_MVIEWS;

is same as:
SELECT name,
query,
master_link,
refresh_method,
type,
last_refresh
FROM USER_SNAPSHOTS;

REFRESH_MODE — Can be DEMAND,COMMIT,NEVER

REFRESH_METHOD — Can be C-COMPLETE, F-FAST, N-NEVER, ?-FORCE

REFRESH_MODE
The Frequency of Refresh can be configured to run on-demand or at regular time intervals.

Lists the Name of the table where the changes to the master table or master materialized view are logged.

SELECT log_owner, master, log_table
FROM DBA_MVIEW_LOGS;

ALL_MVIEW_REFRESH_TIMES
— Lists the last refresh time per Materialized View.

MATERIALIZED VIEW LOG
These are the MLOG$_

DBMS_REFRESH Package
See Oracle Documentation: DBMS_REFRESH

DBMS_REFRESH enables you to create groups of materialized views that can be refreshed together as a single transaction.
The methods for DBMS_REFRESH :
MAKE(), CHANGE(), DESTROY() — Create, Modify and Delete the Refresh Group.
ADD(), SUBTRACT() — Add and Remove materialized views Refresh Group

DBA_RGROUP
See Oracle Documentation: DBA_RGROUP
A View that lists all the Refresh Groups.

Each entry in DBA_RGROUP has an entry in DBA_JOBS, linked by DBA_RGROUP.job.

SELECT *
FROM DBA_JOBS
WHERE job IN (SELECT job FROM DBA_RGROUP)

DBMS_SNAPSHOT Package
See Oracle Documentation: DBMS_SNAPSHOT

DBMS_MVIEW is a synonym for DBMS_SNAPSHOT
Package that handles refresh snapshots that are not part of the same refresh group and purge logs.

DBMS_MVIEW Package
DBMS_MVIEW is a synonym for DBMS_SNAPSHOT
It got several useful procedures:
REFRESH — Refreshes materialized views that are not members of the same refresh group
PURGE_LOG — Purges rows from the materialized view log.
ESTIMATE_MVIEW_SIZE — This procedure estimates the size of a materialized view that you might create
EXPLAIN_MVIEW — That would give some info about the existing Materialized View.
and more.

DBA_REGISTERED_SNAPSHOTS
This table lists Remote snapshots of local tables.
This table exists in the Master DB.

SELECT owner, name, snapshot_id
FROM DBA_REGISTERED_SNAPSHOTS

WHERE owner like ‘ REMOTE_DB %’
ORDER BY snapshot_id

OWNER NAME SNAPSHOT_ID
————- ————- ————
REMOTE_DB_A TABLE_A_SS 43661
REMOTE_DB_A TABLE_B_SS 43662
REMOTE_DB_A TABLE_C_SS 43663
REMOTE_DB_B TABLE_A_SS 43664
REMOTE_DB_B TABLE_B_SS 43665

If There are N Remote sites — There should be N Entries in per each table, in DBA_REGISTERED_SNAPSHOTS.

Join with USER_SNAPSHOT_LOGS, to see last refresh date.
SELECT DBA_REGISTERED_SNAPSHOTS.owner,
DBA_REGISTERED_SNAPSHOTS.name,
DBA_REGISTERED_SNAPSHOTS.snapshot_id,
USER_SNAPSHOT_LOGS.current_snapshots
FROM USER_SNAPSHOT_LOGS,
DBA_REGISTERED_SNAPSHOTS
WHERE DBA_REGISTERED_SNAPSHOTS.snapshot_id =
USER_SNAPSHOT_LOGS.snapshot_id
AND DBA_REGISTERED_SNAPSHOTS.owner = ‘ REMOTE_DB_A ‘

What if there are several Remote sites with same Materialized View looking at the same Master Table, and all are doing Fast Refresh?

With Fast Refresh, rows are deleted from Log Table once the data was refreshed.
Multiple simple snapshots can use the same snapshot log, in that case already used to refresh one snapshot would still be needed to refresh another snapshot.
Oracle does not delete rows from the log until all snapshots have used them.

MLOG$ Tables
Applicable only to Refresh Fast option.
The MLOG% Tables exist only in the Master DB.

SELECT *
FROM USER_TABLES
WHERE TABLE_NAME LIKE ‘MLOG%’

Per each table that participates in a materialized view there is one MLOG $ table, with the name MLOG$_ .
These MLOG$ tables serve to store all DML changes that will be transported to a materialized view.

SQL> SELECT * FROM MLOG$_TABLE_A
WHERE rownum
ORDER BY 2 DESC;

CDR_FORWARD_NAME SNAPTIME$$ DMLTYPE$$ OLD_NEW$$
CHANGE_VECTOR$$
————————- ———— ——— ——— ——
REMOTE_DB-01_TABLE_A 08/08/2012 I N FEFF
REMOTE_DB-01_TABLE_A 05/08/2012 U U 400C
REMOTE_DB-01_TABLE_A 05/08/2012 U U 600C
REMOTE_DB-02_TABLE_A 05/08/2012 U U 600C
REMOTE_DB-03_TABLE_A 05/08/2012 U U 400C
REMOTE_DB-03_TABLE_A 26/06/2012 I N FEFF

RUPD$ Tables
There is one RUPD$ table per MLOG$ table
This is a temporary updatable snapshot log created for Java Rep API.
When the user creates a PK snapshot Log on a Master Table, or does an ‘ ALTER SNAPSHOT LOG ADD PRIMARY KEY ‘, in
addition to MLOG$_ , a temporary table by name RUPD$_ is also automatically created.

SYS.SNAP$
Lists the existing snapshots on the consumer site

SYS.SLOG$
On the Master side.
Lists the Consumer snapshots that are using entries in SYS.SLOG$ at the master site.
This table might grow up to quite large, having tens thousands records.

SQL> SELECT mowner, master, snapid, snaptime
2 FROM SYS.SLOG$
WHERE ORDER BY 2 DESC ;

MOWNER MASTER SNAPID SNAPTIME
—————— ———— ——- ———-
MASTER_USER TABLE_A 95 31/03/2014
MASTER_USER TABLE_A 375 18/12/2008
MASTER_USER TABLE_A 655 30/12/2012
MASTER_USER TABLE_B 961 07/01/2014

MASTER_USER TABLE_B 1335 15/03/2009

The problem might arise if a Snapshot on remote DB was dropped, but the Master DB is not aware of this change.
For example — bring down a test environment that had a Snapshot from Production DB.
The problem is Oracle would not purge old records from DBA_SNAPSHOT_LOG , and the table would grow bigger and bigger.

When deciding whether to purge snapshot log records, Oracle compares SYS.SLOG$.snaptime for the table with MLOG$_.snaptime$$ .
The rows with a MLOG$_. snaptime $$ equal to or older than the oldest SYS.SLOG$. snaptime for the table are purged from the log.
If an orphan entry exists in SYS.SLOG$ at the master site for a deleted snapshot, the SNAPTIME in SLOG$ will not be updated.
Consequently, any records in the snapshot log will never be purged during a refresh.

The following query can be useful in identifying situations where a snapshot entry exists in SLOG$ but is not registered and has not been updated in a long time.

SELECT
REGISTERED_SNAPSHOTS.name AS snapname,
snapid,
NVL(REGISTERED_SNAPSHOTS.snapshot_site, ‘not registered’) AS snapsite,
snaptime
FROM
SYS.SLOG$ SYS_SLOG,
DBA_REGISTERED_SNAPSHOTS REGISTERED_SNAPSHOTS
WHERE SYS_SLOG.snapid=REGISTERED_SNAPSHOTS.snapshot_id(+)
AND mowner LIKE UPPER(‘&owner’)
AND master LIKE UPPER(‘&table_name’);

To remove orphaned entries and Free up space:
Remove orphaned entries:
DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG

Reset the high watermark of the materialized view log:
ALTER MATERIALIZED VIEW LOG ‘&snapshot_log’ SHRINK SPACE.

DBMS_REFRESH.refresh and USER_JOBS
Per Oracle documentation:
«Some job queue requests are created automatically.
An example is refresh support for materialized views.
If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.»

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 SCOPE=BOTH;

USER_SNAPSHOT_LOGS

Important Tables for querying Refresh issues
On Consumer side

USER _REFRESH /ALL _REFRESH /DBA_REFRESH
List of Refresh Groups

USER_REFRESH_CHILDREN/ ALL_REFRESH_CHILDREN/ DBA_REFRESH_CHILDREN
List of Materialized Views per Refresh Group

SELECT REFRESH.rowner,
REFRESH.rname,
REFRESH.job,
REFRESH_CHILDREN.name,
REFRESH.next_date next_refresh
FROM ALL_REFRESH REFRESH,
ALL_REFRESH_CHILDREN REFRESH_CHILDREN
WHERE REFRESH.job = REFRESH_CHILDREN.job

ORDER BY name;

USER_MVIEW_LOGS and USER_SNAPSHOT_LOGS
Both Tables exist on Master side.

USER_MVIEW_LOGS
List Master Table and their MLOG$ table.
One record per Owner, Master Table, Log Table (MLOG$)

USER_SNAPSHOT_LOGS
Lists same data as in USER_MVIEW_LOGS plus data per each snapshot_id.
One record per Owner, Master Table, Log Table (MLOG$), Snapshot ID.

USER_SNAPSHOT_LOGS Reference.

Each time a table in Master is refreshed, an entry is updated in this table.

To see the refreshed tables:
SELECT UNIQUE MASTER FROM LOG_TABLE.

The rows looks like:
SQL> SELECT log_owner, master, log_table, current_snapshots
FROM USER_SNAPSHOT_LOGS WHERE rownum

LOG_OWNER MASTER LOG_TABLE CURRENT_SNAPSHOTS
——— ———— ——————— —————-
USER_A TABLE_A MLOG$_TABLE_A 18/12/2008 09:28:
USER_A TABLE_A MLOG$_TABLE_A 15/03/2009 08:54:
USER_A TABLE_A MLOG$_TABLE_A 15/03/2009 08:54:
USER_A TABLE_B MLOG$_TABLE_B 25/03/2009 15:35:
USER_A TABLE_B MLOG$_TABLE_B 26/03/2009 15:35:

Источник

Error ORA-01031 When Refresh Materialized View (Doc ID 1496817.1)

Last updated on AUGUST 23, 2022

Applies to:

Symptoms

On production database 11.2.0.2 version when attempting to refresh the following error occurs.

ERROR
————————
Cannot refresh materialized view group:

ORA-12048: error encountered while refreshing materialized view » «.»TEST_MV»
ORA-01031: insufficient privileges
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2566
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2779
ORA-06512: at «SYS.DBMS_IREFRESH», line 685
ORA-06512: at «SYS.DBMS_REFRESH», line 195

Changes

When refreshing a MV with XML type В columns and the MV log is in another schema, if the «insufficient privileges» error is encountered, it’s probably due to this bug.

Cause

To view full details, sign in with your My Oracle Support account.

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

In this Document

tables listed in USER_MVIEW_LOGS and in USER_SNAPSHOT_LOGS. They should be created explicitly. CREATE MATERIALIZED VIEW LOG ON MASTER_OWNER.MASTER_TABLE

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Reference for Read Only Snapshots


Materialized Views Overview
The data in a materialized view is updated either by complete or by incremental refresh.  

Log Table: To support Incremental Refresh, Oracle maintains a log table to keep track of changes on the master table.  

Materialized View Related Objects


ALL_MVIEWS

USER_MVIEWS, ALL_MVIEWS, DBA_MVIEWS
Data exists on The Remote Site.
It got data about all the Materialized Views in the schema, when they were last refreshed, refresh type, SQL that was used to populate the MV, and more.

USER_MVIEWS or USER_SNAPSHOTS
SELECT mview_name, 
       query,
       master_link,
       refresh_mode, 
       refresh_method, 
       last_refresh_type, 
       last_refresh_date
FROM USER_MVIEWS;


is same as:
SELECT name,
       query,
       master_link,
       refresh_method,
       type,
       last_refresh
FROM USER_SNAPSHOTS;




REFRESH_MODE — Can be DEMAND,COMMIT,NEVER


REFRESH_METHOD — Can be C-COMPLETE, F-FAST, N-NEVER, ?-FORCE


REFRESH_MODE
The Frequency of Refresh can be configured to run on-demand or at regular time intervals.

REFRESH_METHOD
Complete Refresh: The materialized view is build from scratch


Incremental (or Fast Refresh): Only the changes, or delta, from master table are copied to Materialized View.

If you perform a complete refresh of a master materialized view, then the next refresh performed on any materialized views based on this master materialized view must be a complete refresh. 
If a fast refresh is attempted for such a materialized view after its master materialized view has performed a complete refresh, then Oracle returns the following error:

===================
Error ORA-12034 mview log is younger than last refresh