RE: Middle-Tier Inflicted Corruption
- From: David Wagoner <dwagoner@xxxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Feb 2004 11:53:46 -0500
Thanks for sharing this, Ian. I forwarded it to our developers, who
sometimes try to convince me that RI should be in the application layer.
They haven't won that discussion yet, thankfully, and they are starting to
see the light :-).
We are currently developing a data collection tool to grab data from a
3rd-party PostgreSQL DB and load it into our Oracle DB. The 3rd-party has
no RI in the DB, only 1 NOT NULL in the entire DB, and uses mostly VARCHAR
datatypes (length not defined) even for *dates* (sometimes a date field has
other text in it). Needless to say, it's been challenging.
Best regards,
David B. Wagoner
Database Administrator
-----Original Message-----
From: MacGregor, Ian A. [mailto:ian@xxxxxxxxxxxxxxxxx]
Sent: Monday, February 02, 2004 7:27 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: Middle-Tier Inflicted Corruption
I haven't had much sleep lately. The other day someone came to ask why our
Financials Peoplesoft database thought it was 1998. I checked to be sure,
and the database returned the correct date. I asked them to check the
client, which in this case is a Citrix farm. Some of those servers showed
the 1998 dates. The maintainer of that system was queried and replied:
"The problem with the clock was due to the old domain controller not
correctly synchronizing its time with the new domain controller. Which is
another good reason for building a new domain controller. Because the
clocks never properly synchronized, when the new domain controller came
online to backup the failing primary it came up with a time that was out of
date. This has caused the domain time to be out of sync. It was a last
vestige of the old domain controller 'OVERLORD'. I apologize for the
problems it has caused you. If you have any questions about this please let
me know."
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
Peoplesoft does not use database enforced referential integrity. However it
does employ unique indexes and calls them primary keys. These keys can
include dates. If the date of a parent table is corrected in this situation
and the children are missed, those children are now orphans. If a child's
record is changed and the parent's missed, same thing. If the dates are not
changed then reports are incorrect. Perhaps a capital asset get's an
incorrect receipt date and the depreciation schedule is thrown off.
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
Ian MacGregor
Stanford Linear Accelerator Center
ian@xxxxxxxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
