Refreshing a Materialized View for Oracle VDB fails with ORA-12008, ORA-06512, ORA-00376 (KBA3797)
Issue
When attempting to refresh a materialized view on an Oracle VDB, the following errors are encountered:
BEGIN DBMS_MVIEW.REFRESH('VDB123_OCN_TO_EPM.MVIC_PS_HIER_PRD'); END;
*
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 |
| 6.0 |
6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0 |
|
5.3 |
5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0 |
| 5.2 | 5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1 |
Resolution
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'; FILE# STATUS ---------- ------- 2007 RECOVER 2014 RECOVER 2023 RECOVER …
After media recovery was completed, the operation completes without issue.
Troubleshooting
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 12.2.0.1.0 - 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 8.1.0.0 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 8.1.0.0 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 RMAN>
Related Articles
The following articles may provide more information or related information to this article: