Skip to main content

Refreshing a Materialized View for Oracle VDB fails with ORA-12008, ORA-06512, ORA-00376 (KBA3797)



KBA 3797



When attempting to refresh a materialized view on an Oracle VDB, the following errors are encountered:

ERROR at line 1: 
ORA-12008: error in materialized view or zonemap refresh path 
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2952 
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2370 
ORA-00376: file 2067 cannot be read at this time 
ORA-01110: data file 2067: '/mnt/delphix/VDB124-VDB125/datafile/+XX_XX_XXXX/VDB126/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109' 
[‎22/‎03/‎2019 14:12] xxx xxx (xxx): 
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85 
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245 
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2352 
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2908 
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3191 
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3221 
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 15 
ORA-06512: at line 1

Applicable Delphix Versions

This article applies to the following versions of the Delphix Engine:

Major Release

All Sub Releases




In this instance, the root cause was determined to be a number of datafiles needing media recovery:

SQL> select FILE#,STATUS from v$datafile where upper(STATUS)='RECOVER'; 
---------- ------- 
2007       RECOVER 
2014       RECOVER 
2023       RECOVER 

After media recovery was completed, the operation completes without issue.



In this instance, the following entries in Oracle alert log are observed:

VDB124(4):MV VDB123_OCN_TO_EPM.MVIC_PS_HIER_PRD was not refreshed successfully. 
VDB124(4):Number of MV refresh failures: 1. 
VDB124(4):Encountered error ORA-12008. 
VDB124(4):kkzifr3g: Encountered error ORA-12008.

As the Oracle events indicate datafile cannot be read, the file is sanity checked for access issues from the operating system level, but no issues found:

[oracle@ppln0013 ~]$ ls -l /mnt/delphix/VDB124-VDB125/datafile/+XX_XX_XXXX/VDB126/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109

-rw-r----- 1 oracle asmadmin 10737426432 Mar 22 10:33 /mnt/delphix/VDB124-VDB125/datafile/+XX_XX_XXXX/VDB126/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109

[oracle@ppln0013 ~]$ file /mnt/delphix/VDB124-VDB125/datafile/+XX_XX_XXXX/VDB126/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109

/mnt/delphix/VDB124-VDB125/datafile/+XX_XX_XXXX/VDB126/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109: data

Further verification is performed using RMAN backup validate, which indicates no issue with datafile content:

[oracle@ppln0013 ~]$ rman target /@VDB125
Recovery Manager: Release - Production on Wed Mar 27 11:13:29 2019 
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. 
connected to target database: DLPXDB (DBID=1142528944)
RMAN> backup validate check logical datafile 2067; 
Starting backup at 27-MAR-19 
using target database control file instead of recovery catalog 
allocated channel: ORA_SBT_TAPE_1 
channel ORA_SBT_TAPE_1: SID=62 instance=VDB125_1 device type=SBT_TAPE 
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 
allocated channel: ORA_SBT_TAPE_2 
channel ORA_SBT_TAPE_2: SID=92 instance=VDB125_1 device type=SBT_TAPE 
channel ORA_SBT_TAPE_2: Data Protection for Oracle: version 
channel ORA_SBT_TAPE_1: starting full datafile backup set 
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set 
input datafile file number=02067 name=/mnt/delphix/VDB124-VDB125/datafile/+XX_XX_XXXX/VDB126/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109 
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:02:25 
List of Datafiles 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN 
---- ------ -------------- ------------ --------------- ---------- 
2067 OK     0              44317        1310720         6183359774785 
File Name: /mnt/delphix/VDB124-VDB125/datafile/+XX_XX_XXXX/VDB126/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109 
Block Type Blocks Failing Blocks Processed 
---------- -------------- ---------------- 
Data       0              1255980 
Index      0              4147 
Other      0              6276
Finished backup at 27-MAR-19




Related Articles

The following articles may provide more information or related information to this article: