RE: Middle-Tier Inflicted Corruption

We had a similar problem with sequence generated primary and foreign keys.

We copied the child tables to temporary tables with no relational integrity, generated good sequence numbers for the primary keys, saved the new primary key with the old key that it was replacing in a pairs-table, updated the temporary tables with the new keys, truncated the child tables and reloaded them from the temporary tables. About six months of planning, analysis, and practice and 48 hours of implementation over one weekend. Using the pairs-table we could always ungenerate our fix and recover the original state of the tables. Unfortunately, since you're using dates you may update a bad date to a good date that may already exist. You won't be able to undo that unless you capture additional data to uniquely identify all changed rows.

                                                                                                                                       
                      "Thater,                                                                                                         
                      William"                 To:      "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>                            
                      <William.Thater          cc:      "'MacGregor, Ian A.'" <ian_at_SLAC.Stanford.EDU>                                  
                      @carrier.utc.com         Subject: RE: Middle-Tier Inflicted Corruption                                           
                      >                                                                                                                
                      Sent by:                                                                                                         
                      oracle-l-bounce                                                                                                  
                                                                                                                                       
                                                                                                                                       
                      02/03/2004 07:40                                                                                                 
                      AM                                                                                                               
                      Please respond                                                                                                   
                      to oracle-l                                                                                                      
                                                                                                                                       
                                                                                                                                       

MacGregor, Ian A. scribbled on the wall in glitter crayon:

> Peoplesoft (in-the-head) in their ultimate wisdom decided not to use
> the date on the database server, but that on the client. I now have
> these incorrect dates sprinkled through the system. Furthermore some
> have propagated from parent to child. I spent most of the weekend
> mining redo logs and believe I have come up with a complete list of
> the effected rows. One cannot ever be 100% sure. The project
> leaders for each Peoplesoft module have these. They will be
> responsible for implementing any corrections

welcome to my world... well the world at my old job. been there, done that,
it sux.

> With database enforced RI I can find the lineage of a key through all
> generations, but that is not so easy when it is program based.
>
> I am open to suggestions as to how to best remedy this stituation

well there are two data dictionaries to worry about, Oracle's and PeopleSoft's. so make sure they're in sync. in my case we had dates that were way out of range, so i could scan the dates and find the wrong ones, then work on correcting them. [in my case the project leads wouldn't touch it with a ten foot pole.;-)]

write me off line with any questions if i can help.

--
Bill "Shrek" Thater     ORACLE DBA
"I'm going to work my ticket if I can..." -- Gilwell song
                william.thater_at_carrier.utc.com
------------------------------------------------------------------------
Politics for the moment while an equation is for eternity. - Albert
Einstein
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------




----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------