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

Read Write Oracle10g Standby database

Of all the database features of Oracle,the one which I feel interesting is the STANDBY or DATAGUARD feature.Long back I had posted a topic on Oracle11g standby database,which enables us to open a standby database in read write mode and put it back to standby(open read-only) mode.

I was going through a blog by one Mr.Syed Jaffar Hussain(http://jaffardba.blogspot.com) who has explained how we can open read write a Oracle10g physical standby database and put it back to the standby mode(open read-only).Below is the link to the document.
http://jaffardba.blogspot.com/search/label/Standby%20Database

The blog by Mr.Hussain is itself very informative.And thanks Hussain for sharing with us.

Cheers,

Yadu

VNI-4026 : Discovery failed : Error while transferring services.ora from agent : 4009.

I am BACK after a LOOOOOOONG break...!!

I was not able to update my blog all these days but not anymore..Anyways,let me come to the point..

We have been migrating databases from one datacenter to another..Migration of the databases were pretty simple..Just take the cold backup of the database and restore it.We had to build a new server for the migration and the Unix consulatnt had installed the Oracle for us on the new server..Actually,he had not installed Oracle as such..He copied the Oracle home from the old server to the new server.Since the volumes on the server were identical,we had no issues in bringing up the database on the other server.

But when we tried to discover the new server on the OEM,there was this error

VNI-4026 : Discovery failed : Error while transferring services.ora from agent : 4009.

The intelligent agent was up and running.We stopped the agent and started the Intelligent agent after deleting *.q files in $ORACLE_HOME/network/agent/ location.Even then we had the same problem.

The above issue got resolved only after REINSTALLING the entire Oracle Home on the new server.It is recommended not to copy the Oracle Home from one server to another.Some components may work and some may not!!So always go with the fresh installations while taking such kind of activities.