Yadu's Oracle Junction

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

2009-03-20T11:54:00.000-07:00

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

2008-12-04T05:38:00.000-08:00

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.

2008-11-14T10:47:00.000-08:00

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.

Oracle11g SNAPSHOT standby database

2007-09-20T00:58:00.000-07:00

Till now you have come across two types of standby databases namely Physical standby database and Logical standby database. From Oracle 11g onwards there is one more addition to this. It is called Snapshot standby database.

Snapshot standby is updatable version of the standby database. It is created from the existing standby database .You can modify the database and again you can convert it back to the physical standby database. The snapshot standby database receives the archive logs(redo) from the primary database but does not apply the redo in standby .It is automatically applied when you convert the snapshot standby database back to physical standby database and the conversion can be done at any point of time! So data from the primary database is always protected .It gives data protection and disaster recovery exactly like the physical standby database.

Why you need a snapshot standby database??

As told earlier snapshot standby database is updatable. It provides the replicated version of the primary database which can be used for development, testing and reporting purposes. You can always refresh the snapshot standby database to be in sync with the primary by converting it to physical standby database. Again you can convert to snapshot version do the testing and resynchronize with primary. This cycle can be repeated any number of times!

The conversion from physical standby to snapshot standby database and vice versa can be done in three ways.
1.Dataguard Broker(If the physical standby is managed by DGMGRL)
2.Enterprise Manager(If the physical standby is managed by DGMGRL)
3.Manual method.

Let us see how you can convert the physical standby database to snapshot standby database manually. This can be done by issuing the below SQL command in the physical standby database.

Sql>alter database convert to snapshot standby;

If the standby database is a RAC setup then you need to shutdown all the instances except one on which you will be using the conversion commands.

You may wonder how you will be able to get back to the original state .Here Oracle uses its own features within, nothing else! Oracle uses restore point and flashback feature to accomplish this. If you remember, restore point and flashback database concepts were first introduced in Oracle10g.

When you convert the physical standby database to snapshot standby database a guaranteed restore point is created. This guaranteed restore point is used to flashback a snapshot standby database to its original state. The name of this guaranteed restore points begins with ‘SNAPSHOT_STANDBY_REQUIRED_’. The database is dismounted during this conversion and you have to restart the database (which will be the snapshot standby database).

Once it is converted the database can be opened in read/write mode, you can update the database. Meanwhile you will be receiving the redo from the primary, which will be automatically applied when snapshot standby database is converted back to physical standby database.

Now, to convert the snapshot standby back to the physical standby state use the following SQL command:

Sql>alter database convert to physical standby;

Using the guaranteed restore point the snapshot standby database will be flashed back to its original state and subsequently the restore point will be dropped. After the conversion the redo will be applied which were received by the snapshot standby database.

Note:

When you convert a physical standby database to snapshot standby database, Then

• Redo gap detection and resolution will work as before.
• You cannot perform switchover or failover on a snapshot standby database. To perform role transitions you have to convert the snapshot standby database back to physical standby database.
• If the primary database has a new incarnation (because of Flashback database or Open resetlogs ),the snapshot database continue to receive the redo from the primary.
• In case of role transition of one of the standby database (like failover or switchover), the snapshot standby database continues to receive the redo from the new primary.

Comments are always welcome :-)

Listener hangs in Oracle 10g and no new connections are allowed

2007-08-17T09:43:00.000-07:00

Just when I was thinking how boring my job as DBA was (doing the same routine work and nothing new to implement until you convince everyone and take approvals from them!!) I was made to feel that DBA work is not at all boring if you are at the receiving end :-) . I was suddenly bombarded with mails from the users saying that they are not able to connect to the database. As usual I checked the listener status and replied to some of them saying that there is no problem with the listener as such but would get back to them with a solution. It was a production database, the pressure on me was mounting every second. I was not sure what to do? Reply to the users or solve the problem :-)

The database version was of Oracle 10.2.0.1 running on Linux RHEL 4..The listener seemed to be in hung state and moreover there were no errors in the listener log file.

I don’t know what came to my mind I thought of checking the listener process using the ps command. I was surprised to see a child process forked automatically with the same name of the current listener.

$ ps -ef |grep tns
oracle 2310 1 0 Jul 17 ? 72:00 /oracle/ora10g/db/bin/tnslsnr oprem -inherit
oracle 6573 2310 0 14:19:23 ? 0:00 /oracle/ora10g/db/bin/tnslsnr oprem –inherit

I killed the child process and then reloaded the listener. Phew!!...it worked.The users were able to connect to the database now.

Later I found that this is an Oracle bug (No.4518443).

Some of the possible solutions for the above problem are:

1. Kill the child process using kill command and then reload the listener process.

2. Add the following parameter in the listener.ora file and restart the listener process.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_NAME=OFF

Where listener_name is the listener name (here it would be oprem)

3. Apply Patch.

4. Rename the ons.config file and restart the listener. (path:$ORACLE_HOME/opmn/conf)

This problem is fixed in 10.2.0.3

Note: If you add the above parameter for a listener.ora in RAC setup, then Fast Application Notification (FAN) will not work.

For patch and more details on this refer to metalink note: 340091.1

Hope this was useful to you guys.

Sometimes something goes unnoticed...

2007-08-13T06:54:00.000-07:00

ORA-01194: File 5 needs media recovery to be consistent

When you get the above error if you decide that you have to recover the datafile (or database), think twice. Always you may not have to recover the file. I had faced this scenario in one of the client places. Though the initial plan was to restore the datafile from the backup and recover, I had to change my mind.

Now the scenario…I was informed that a guy at the client site had restarted the database (on windows) and they could not open the database because of the datafile inconsistency. After some searching I could find that the problem was with the backup process!!

It happened so that when the hot backup (user-managed) of the database was being taken, that guy had shutdown the database. Now, while restarting the database a particular datafile was asking for media recovery. When checked I found that the datafile was still in backup mode. i.e. the database was shutdown when the backup was going on. Now, the solution is simple. Mount the database and use alter database datafile 5 end backup command and open the database. There is absolutely no need to recover the datafile!! J

So what exactly happened here?!? Well, nothing strange happened. It is a normal oracle behavior, nothing else. When the instance was restarted, the datafile which was in the backup mode will look old because the datafile header is freezed with older SCN. So it will (and it shouldJ) ask for recovery. That’s it!

Generally on unix servers if you issue normal shutdown commands (except abort!!) on the database when the hot backup is going on, shutdown wont happen. It will throw an error saying that the datafile is in backup mode. So there is very less chance of facing this scenario on unix servers.

But in windows, it is general practice to shutdown the database using the OracleService on the services window. In this case even if the backup is happening database goes down with shutdown abort command internally. You won’t come to know that shutdown abort has happened.

You can even face this scenario when the instance crashes during backup (hot backup).You try to restart the instance without knowing that backup was active during the instance crash and end up with the same error.

So, don’t panic… Take it easy!!!

How to recover a table using RMAN backup

2007-08-13T06:51:00.000-07:00

This article is about recovering a particular table (which was dropped or truncated) using RMAN backup. I assume that logical backup is not planned as the size of the database is in Terabytes (TB) and RMAN is used for backing up the database. Here tablespace point in time recovery (TSPITR) does not come into picture because we want to recover a single table and not all the objects in the tablespace.

It is assumed that

- The target database is on host A and the RMAN full backup was taken before the table TEST which is to be recovered was dropped.
- The database is to be restored onto host B
- The directory structure of host B is different to host A
- The ORACLE_SID will not change for the restored database
- The backups were carried out to disk
- TEST table to be recovered is in the tablespace TEST_DATA

The following steps are required:

- make the backup available to host B
- make a copy of the init.ora available to host B
- edit the init.ora to reflect directory structure changes
- set up a password file for the duplicated database
- mount the database
- restore and rename the datafiles
- recover and open the database
-export and import the table to the target database

These steps are explained further below.

1.Backup the latest controlfile

sqlplus>alter database backup controlfile to ‘/oracle/control.ctl’;

Note: Backup the archived logs
Move all the archive logs to the Host B from the time backup was taken.

2. List Datafile Locations on Host A

The datafile numbers and location on host A are required. These datafile locations will change on host B

sqlplus> select file#, name from v$datafile;

file# name
----- ------------------------------
1 /oracle/orcl/oradata/system01.dbf
2 /oracle/ orcl/oradata/users..dbf
3 /oracle/orcl/oradata/undo01.dbf
4 /oracle/orcl/oradata/tools01.dbf
5 /oracle/orcl/oradata/test01.dbf
6 /oracle/orcl/oradata/test02.dbf
7 /oracle/orcl/oradata/undo02.dbf
8 /oracle/orcl/oradata/rcvcat.dbf

3. Make the Backups Available to Host B

During restore, RMAN will expect the backup sets to be located in the same directory as written to during the backup. For disk backups, this can be accomplished in many ways:

- set up an NFS directory, mounted on both host A and host B
- use of symbolic links on host B

4. init.ora on Host B

The "init.ora" needs to be made available on host B. Any location specific
parameters must be ammended. For example,
- *_dump_dest
- log_archive_dest*
- control_files=(control file backup taken on Host A)

5. Setup PASSWORD File

In order to allow RMAN remote connections, a password file must be setup for the duplicated database. For example,

$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle

6. Recover the Database

On Host B perform the following steps.

6.1 Startup nomount the database

sqlplus> startup nomount pfile=

6.2 Mount the database

sqlplus> alter database mount;

6.3 Rename and restore the datafiles, and perform database recovery

RMAN can be used to change the location of the datafiles from the location on host A to the new location on host B. Here rename the datafiles of SYSTEM,UNDOTBS1 and TEST_DATA tablespaces only.

Note: If you have two undo tablespaces in your database and you keep switching between these undo tablespaces it is necessary to restore both the undo tablespaces.

RMAN> run {

allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;

set newname for datafile 1 to '/oracle/datafiles/system01.dbf';
set newname for datafile 3 to '/oracle/datafiles/undo01.dbf';
set newname for datafile 5 to '/oracle/datafiles/test01.dbf';
set newname for datafile 6 to '/oracle/datafiles/test02.dbf';
set newname for datafile 7 to '/oracle/datafiles/undo02.dbf';

restore tablespace SYSTEM;
restore tablespace UNDOTBS1;
restore tablespace TEST_DATA;
switch datafile all;
}

6.4 Recover and open the database

Perform incomplete recovery and take the datafiles of the tablespaces other that SYSTEM,UNDOTBS1,TEST_DATA to offline. This makes our restore work easier. i.e. you don’t have to restore the whole database backup. When you issue offline drop command, controlfile assumes that it does not need these files for recovery(so need to restore!!).This is helpful when you have a database of say 1 TB and the tablespace in which the table to be recovered is present is of say 10 GB. By skipping the restoration of other tablespaces you save lot of time and space also.

sqlplus>alter database datafile 2 offline drop;
alter database datafile 4 offline drop;
alter database datafile 8 offline drop;

sqlplus> recover database using backup controlfile until cancel; (or until time)

Forward the database applying archived redo log files to the point just before the table was dropped and stop the recovery process by typing cancel at the prompt (assuming that you have required archived redo log files in the log_archive_dest directory)

6.5 Rename the logfiles prior to opening the database

sqlplus> alter database rename file ' /oracle/orcl/oradata/redo1.log' to '/oracle/redologs/redo1.log';

sqlplus> alter database rename file ' /oracle/orcl/oradata/redo2.log' to '/oracle/redologs/redo2.log';

sqlplus> alter database open resetlogs;

Now you can query the table TEST to find out the data.

Once you are confirmed that the table TEST is recovered, export the table and import to the target database.