How I solved the ORA-08103: object no longer exists problem

Applies to:

Oracle Enterprise Edition 9.2.0.7.0
Any platform

Problem:

I had migrated an Oracle database from one server to another using the cold backup. The database came up clean. But the user had complaints saying his select count (*) on some tables gives the error ORA-08103: object no longer exists. But the select * for the same tables was giving the rows! There were around 3000 tables out of which 30 tables where giving the error. All these tables belonged to the same tablespace.

I tried the following.

SQL>Select * from test;
NAME
--------------
India
Australia
England
Srilanka
USA
5 rows selected

SQL> Select count (*) from test;
Select count (*) from test
*
ERROR at line 1:
ORA-08103: object no longer exist

Initially I thought there could be some problem with the indexes of these tables. I picked up one of the tables. And I tried validating the indexes by the below command

SQL>Analyze table test validate structure cascade;
Analyze table test validate structure cascade
*
ERROR at line 1:
ORA-08103: object no longer exists


And then I tried skipping the indexes by using the following command.
I used “no index” hint to execute “select count (*) from table”, command failed with the same error.
SQL>Select /*+NO INDEX*/ count (*) from test;
Select /*+NO INDEX*/ count (*) from test
*
ERROR at line 1:
ORA-08103: object no longer exists


Then I did the following.

1) Got the DDL of the table.
SQL>SELECT dbms_metadata.get_ddl(‘TABLE’, ‘TEST’,’USER’) FROM DUAL;

2) Create new table using above DDL

3) Execute the following statement.

SQL>INSERT INTO testr SELECT * FROM test;
INSERT INTO testr SELECT * FROM test;
*
ERROR at line 1:
ORA-08103: object no longer exists

Even this failed with the error ora-08103.
One of the solutions I tried was.

-For a table, truncate or drop it and reload the data from export dump

I dropped the tables having this error and took an export dump from the server (A) and imported the tables back into the server (B).For my surprise this time I faced the same problem with some other tables.

Cause:

Here, I had copied the database files (Cold backup) from Solaris server (A) ->windows server Solaris server (B) in the binary mode. This was done because I had a better network speed this way. This method had worked me for the same kind of restoration of the databases before. But this time it failed with the corruption of the data blocks. This could be because of

- The header block has an invalid block type or the block type inside the block is not expected. i.e. A data block (Type=6) was expected but the actual block information is not a data block.

Or

- The data_object_id (seg/obj) stored in the block is different than the data_object_id stored in the segment header.

Solution:

This time I copied the database files (cold backup) from solaris server (A) to solaris server (B) directly without the windows server in the middle. And I restored the whole database again. I had no problems with any of the tables and the database is running fine now.

The solution was to copy the files directly from the UNIX server to the UNIX server to avoid block corruptions of the data blocks.

References:

Note.268302.1 ORA-8103 Diagnostics and Solution