data integrity problem of Oracle 8.1.6 Standby Database

From: James <cheungchung_at_hotmail.com>
Date: Mon, 24 May 2004 23:57:22 +0800
Message-ID: <c8t613$91p292_at_imsp212.netvigator.com>



Hi ,

 I have an oracle 8.1.6 running on solaris platform. This database have a standby DB of same version and OS platform for query or report generation. At day time the standby DB is in read-only mode and the archived logs are applied at night.

Last week, this production DB have a maintenance and added 4 datafiles to different tablespaces and this physical changes have been re-structed at the standby DB also. The standby DB have been successfully recovered through the archived logs and I can turn back it to read only mode the next for users to query.

However, this week some users complained that their query report return "no rows selected" for selecting the column A ( which is a unix timestamp) on a table named SERVICE . Then I checked it with simple query like below:

select columnB from SERVICE where columnA = 10283848;

Table : SERVICE

 columnA                                            NUMBER(38)
 columnB                                            NUMBER(38)
 columnC                                            VARCHAR2(255)
 columnD                                            NUMBER(38)
 columnE                                            NUMBER(38)
 columnF                                            NUMBER(38)
 columnG                                            NUMBER(38)


It returned " no rows selected" ONLY if the timestamp number ( columnA ) I choosed was after the day of prod DB maintenance. And I found that not only columnA , but also all the columns with datatype NUMBER(38) got this problem

If I tried the SQL like this :
select columnB from SERVICE where columnA like '10283848'; or apply 'tochar' on columnA

It will returned the rows I needed !!

The total number of rows of this table is the same as the one in production DB

Can anyone help to give some hint to troubleshoot what's happening in this table ?

Thanks in advance

James Received on Mon May 24 2004 - 17:57:22 CEST

Original text of this message