Skip to main content
Delphix

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

 

KBA 3797

 

Issue

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

BEGIN DBMS_MVIEW.REFRESH('HOOCN99_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/HOEPM99-HODLX01D1/datafile/+P3_CP3_DATAEPM12/XOEPM00D1/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109' 
[‎22/‎03/‎2019 14:12] CADOR Franck (Prestataire): 
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

5.3 5.3.0.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

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

4.0

4.0.0.0, 4.0.0.1, 4.0.1.0, 4.0.2.0, 4.0.3.0, 4.0.4.0, 4.0.5.0, 4.0.6.0, 4.0.6.1

3.2

3.2.0.0, 3.2.1.0, 3.2.2.0, 3.2.2.1, 3.2.3.0, 3.2.4.0, 3.2.4.1, 3.2.4.2, 3.2.5.0, 3.2.5.1, 3.2.6.0, 3.2.7.0, 3.2.7.1

3.1

3.1.0.1, 3.1.1.0, 3.1.2.0,  3.1.2.1, 3.1.3.0 , 3.1.3.1, 3.1.3.2, 3.1.4.0, 3.1.5.0, 3.1.6.0

3.0

3.0.0.3, 3.0.0.4, 3.0.1.0, 3.0.1.1, 3.0.1.2, 3.0.1.3, 3.0.2.0, 3.0.2.1, 3.0.3.0, 3.0.3.1, 3.0.4.0, 3.0.4.1, 3.0.5.0, 3.0.6.0, 3.0.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:

HOEPM99(4):MV HOOCN99_OCN_TO_EPM.MVIC_PS_HIER_PRD was not refreshed successfully. 
HOEPM99(4):Number of MV refresh failures: 1. 
HOEPM99(4):Encountered error ORA-12008. 
HOEPM99(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/HOEPM99-HODLX01D1/datafile/+P3_CP3_DATAEPM12/XOEPM00D1/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109

-rw-r----- 1 oracle asmadmin 10737426432 Mar 22 10:33 /mnt/delphix/HOEPM99-HODLX01D1/datafile/+P3_CP3_DATAEPM12/XOEPM00D1/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109

[oracle@ppln0013 ~]$ file /mnt/delphix/HOEPM99-HODLX01D1/datafile/+P3_CP3_DATAEPM12/XOEPM00D1/7147C0847FF47DE7E0535106760A0B83/DATAFILE/dtmref.342.981823109

/mnt/delphix/HOEPM99-HODLX01D1/datafile/+P3_CP3_DATAEPM12/XOEPM00D1/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 /@HODLX01D1
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: HO_DLX01 (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=HODLX01D1_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=HODLX01D1_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/HOEPM99-HODLX01D1/datafile/+P3_CP3_DATAEPM12/XOEPM00D1/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/HOEPM99-HODLX01D1/datafile/+P3_CP3_DATAEPM12/XOEPM00D1/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:

  • link
  • link
  • link