ORA-19665: Size in File Header Does Not Match Actual File Size of String

Posted in: Oracle, Technical Track
ORA19665

I received the above message related to an ORA-7445 on my 11.2.0.4 database: ORA-07445: exception encountered: core dump [kcflfi()+1016] [SIGFPE] [Integer divide by zero] [0x10053AD10] [] []

 

 

After some checks, I noticed the following:

SQL> select file_name, bytes from dba_data_files where file_id=106;

FILE_NAME                                         BYTES 
------------------------------------------------ --------------
+DATA/MYDB/DATAFILE/DATAFILE_XX.558.1015447173   14529069056

SQL> select name, bytes from v$datafile where file#= 106;

NAME                                             BYTES 
------------------------------------------------ --------------
+DATA/MYDB/DATAFILE/DATAFILE_XX.558.1015447173   14529067281

Does this mean  the database dictionary has different sizes for the data file?

I’d expect to have the same size for both queries, as the dba_data_files should be based on v$datafile.

Looking at MOS (My Oracle Support), it seems to be a match to ORA-07445: Exception Encountered (Doc ID 1958870.1).

So, what did resolve my case? After following  the MOS Doc, dropping and restoring the datafile fixed the views:

SQL> alter database datafile 106 offline to drop;
RMAN> restore datafile 106;
RMAN> recover datafile 106;
SQL> alter database datafile 106 online;

This process actually fixed the inconsistency in my views:

SQL> select file_name, bytes from dba_data_files where file_id=106;

FILE_NAME                                         BYTES 
------------------------------------------------ --------------
+DATA/MYDB/DATAFILE/DATAFILE_XX.558.1015447173   14529069056

SQL> select name, bytes from v$datafile where file#= 106;

NAME                                             BYTES 
------------------------------------------------ --------------
+DATA/MYDB/DATAFILE/DATAFILE_XX.558.1904729421   14529069056

There are a few things you should be careful with when using this method:

  • Make sure you have a backup before dropping the data file.
  • Make sure you can put the data file offline or proceed during non-business hours.
  • And of course, follow change procedures for production; things could get wild!

What if you don’t have a backup?

  1. First of all, you may want to create one. The backup may fail however, considering the original mismatch.
  2. Consider using Export/Import logically (Data Pump is recommended):
  • Export the data from the related tablespace (Data Pump or Legacy Export—check for limitations and data types).
  • Drop the tablespace and recreate it
  • Import the data back.

I hope this helps—if you have any questions or thoughts, please leave them in the comments.

 

Make sure to sign up for more updates here.

 

 

email
Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Lead Database Consultant
Well known in the Oracle community in Latin America and Europe where he participates regularly in technology events, Matheus is actually the youngest Oracle ACE Director in the world. Lead Database Consultant at Pythian, Matheus is a Computer Scientist by PUCRS and has been working as an Oracle DBA for the last 10 years.

No comments

Leave a Reply

Your email address will not be published.