DBA made ten years of data disappear with one misplaced parameter
- Reference: 1711355050
- News link: https://www.theregister.co.uk/2024/03/25/who_me/
- Source link:
This week's confession comes from a reader we'll Regomize as "Larry". Some years ago Larry was working as an Oracle DBA for a healthcare provider in the US. He hadn't been a DBA for very long so, in his words, he was not yet "all knowing or all powerful" – but he knew enough to get by and could ask intelligent questions of the older DBAs.
Then he was handed a Really Important Task™️. The biz he worked for produced monthly reports on its membership numbers, based on the current and previous months. That monthly report was automated, but for some reason the annual report still required a manual process. Larry told Who, Me? that it involved "backing up the most recent two months, truncating said months, running an ad hoc job to repopulate it so a report can be generated, then finally restoring from the backup."
[1]
Obviously this needed to be more efficient.
[2]
[3]
Larry had around this time become familiar with Oracle's Data Pump utility, which was great for one-off table backups. H also knew how to back up specific partitions, so – he thought – why not use the Data Pump to write an import parameter file to handle the yearly backups?
Now, obviously there was no need, in building this file, to backup the entire table and the more than ten years of data it stored. All he needed was the last two months. Enter a handy parameter called TABLE_EXISTS_ACTION . This would run on the import file and, if it found a table that already existed, perform whatever actions were desired.
[4]
Larry was a keen little learner, and he had learned a new thing.
[5]Yes, I did just crash that critical app. And you should thank me for having done so
[6]Intern with superuser access 'promoted' himself to CEO
[7]If we plug this in without telling anyone, nobody will know we caused the outage
[8]Self-taught-techie slept on the datacenter floor, survived communism, ended a marriage
His export of the relevant partitions ran flawlessly, so – he thought – the import would be equally flawless. Normally, the process would involve manually truncating the partitions in order to restore them from the backup, but the whole point of the exercise was to do away with manual intervention. Thus, Larry added TABLE_EXISTS_ACTION=TRUNCATE to the import parameter file.
His vision was that "the database would automatically truncate those two partitions and successfully reload the data, saving everyone time and energy." Cue praise from the higher-ups and knowing nods of approval from his fellow DBAs.
Except that when he ran the process, he found that the backup file was considerably smaller than he had expected it to be. Like, a lot. Like, 1GB when it should have been more than 75GB.
Which aroused his curiosity.
[9]
It transpired that TABLE_EXISTS_ACTION is, as its name more or less suggests, a table-level parameter, not a partition-level operation. And what he had essentially done was truncate not just the tables required for the report, but all of them.
Over ten years worth of customer data. Gone in seconds.
Thankfully, of course, there were backups and it didn't take long to restore all of the data. Indeed, no-one even reported broken links or any disruption of service. Only Larry ever knew – and he learned an important lesson about properly understanding a command you've just learned before you go using it on production data.
If you've ever had a learning experience like Larry's, we want to know about it – and so do your fellow Regizens. Tell us about it in [10]an email to Who, Me? and we'll all share in the catharsis some future Monday. ®
Get our [11]Tech Resources
[1] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/databases&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=2&c=2ZgFZSSHeYyCbgUbBYCa6mwAAABc&t=ct%3Dns%26unitnum%3D2%26raptor%3Dcondor%26pos%3Dtop%26test%3D0
[2] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/databases&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=4&c=44ZgFZSSHeYyCbgUbBYCa6mwAAABc&t=ct%3Dns%26unitnum%3D4%26raptor%3Dfalcon%26pos%3Dmid%26test%3D0
[3] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/databases&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=3&c=33ZgFZSSHeYyCbgUbBYCa6mwAAABc&t=ct%3Dns%26unitnum%3D3%26raptor%3Deagle%26pos%3Dmid%26test%3D0
[4] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/databases&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=4&c=44ZgFZSSHeYyCbgUbBYCa6mwAAABc&t=ct%3Dns%26unitnum%3D4%26raptor%3Dfalcon%26pos%3Dmid%26test%3D0
[5] https://www.theregister.com/2024/03/18/who_me/
[6] https://www.theregister.com/2024/03/11/who_me/
[7] https://www.theregister.com/2024/02/26/who_me/
[8] https://www.theregister.com/2024/02/19/who_me/
[9] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/databases&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=3&c=33ZgFZSSHeYyCbgUbBYCa6mwAAABc&t=ct%3Dns%26unitnum%3D3%26raptor%3Deagle%26pos%3Dmid%26test%3D0
[10] mailto:whome@theregister.com
[11] https://whitepapers.theregister.com/
That smell has sailed.
Little Larry Tables
https://xkcd.com/327
Re: Little Larry Tables
We've all been Little Larry Tables at some point.
This is why we ALWAYS test new procedures on a COPY of the production database
Even if we are old and grizzled DBAs. Because anyone can screw up.
Re: This is why we ALWAYS test new procedures on a COPY of the production database
Not even it but because. We also COUNT the number of rows that will be affected. But is Oracle so slow that two month's worth of rows couldn't be SELECTed?
"Thankfully [..] there were backups"
With actual DBA's around, I should hope so. This was not the modern days of "move fast and break things" which, funnily enough, I don't they use too much as far as critical data is concerned.
At least, I hope so.
Appropriate Mis-quote
"You keep using that keyword. I do not think it means what you think it means."
Re: Appropriate Mis-quote
Inconceivable!
Walking on the live rail
If testing new procedures on the live database is normal in "Larry's" company, I expect the database backup/restore process gets a lot of use.
Let's hope for their victims customers sake, someone doesn't live-test a flaky modification to the backup process.
As a wise man once said, if you don't have a test system, what you actually don't have is a production system... Truncate was one of the things that caused a sharp intake of breath whenever I hit the return key. I've done worse though.
Ouch!
One more case of "saved by the backup". Last time I lost some important data (only my own LaTeX sources of slides I use for teaching, not other people's work) was when my laptop was stolen and BOTH recent backup disks failed. I had some older back-ups (missing the more recent additions to the sources), and the PDF files of the slides, but it does go to show you can never have too many backups. I now do my LaTeX editing in Overleaf, make regular copies to my (backed up) work machine, AND on my personal laptop AND copy those to two solid-state disks regularly. I do not doubt I will at some point lose stuff.
sad thing is the IT world is full of idiots, they might not lose 75G of data on adaily basis but they are stupid in so many ways.
At my work, theres a non prod environment. Genius keep moving application and clusters around to "save" aws resources. Im not sure how much they "save" but its far less than the "time" they waste because stuff is down, people costing thousands a day are waiting, and i havent mentioend the time wasted because urls and more "change". Naturally they then "change" things again a few weeks later.
If they didn't have all that busy-work to do somebody might notice that they're not really needed.
Daft?
《 the annual report ... required .... backing up the most recent two months, truncating said months, running an ad hoc job to repopulate it so a report can be generated, then finally restoring from the backup.》
The last bit is a tad confused but the gist seems to be that annual report was generated two months after end-of-year.
So I would guess that the code generating report was repurposed from a specified start-of-year to current report so that removing the last two months is to make "current" two months prior (ie end of year.)
While not a DBA I can grok enough sql to select .... from .... where rec-date between ... and ... and would wonder why database views of the required tables restricted to required period viz omitting the final two months weren't used.
Dropping views is slightly less dangerous I would have thought.
I would it would be damn sight safer to parameterize the report generating script with a period starting/ending argument.
Any data that has been collected, validated and stored (especially in a dbms) is best treated as read only. Even when amending a record to correct an error it is far more desirable to just supersede the erroneous record in order to keep an obvious audit trail. I imagine modern rdbms do this anyway.
I have been fortunate in my BOFH exertions not to have anything to do with production databases other than very short stints with Unify 4.2 and then Oracle 5 at the outset. I suspect had it had been otherwise the company carpark would have been full of ex-DBAs not so much fallen from grace as from a convenient open window.
SO much or Oracle dos not work as you expect
I was also working with my team of Oracle DBA's. They had set up a backup programme using the Sequent supplied backup system. We were running on Sequent compuyters with Sequent disks. This is important. This was back in the 1990's
We also had a MASSIVE table. Gazzilions of rows that grew at a rate of round about a million per day. So we had a regime. Each month we create a new partition which we distributed over the disks. Older months tables were set to READ-ONLY, thus we knew queries would not take a lock. This all ran fine. Backups happened automatically. New partitions were created at month end and everyone was happy.
Right up until the Sequent engineer came in and wanted to takle a list of disk serial numbers. We thought he would know what he was doing, this being a vitally important (and not cheap) production data warehouse. He staretd at the front, and noted all the disks. We went round the back, opened the cabinet doors and knelt down to take the lower level disk IDs. And that was when he knocked the master power switch.
The disks all dropped off line, red lights flashed on the consoles, but he very quickly restored the power. The active partition was trashed, but thats OK, because we could restore from tape and roll forward. But the pervious months partitiions were not so lucky. But hey, we had Sequents backup program. Simply restore the damaged data and we are good, right?
WRONG! Most of our data was missing. It seems that Sequent does not backup a read only partition. We only kept about a months worths of backup because tapes were expensive, so when we restored and rolled forward we got tables spaces with loads of empty space. Major panic ensued because there was a regulatory reason for us having the data.
Sequent\'s did look into this. They scanned and scoured our backup tapes and eventually came up with a solution. Without any form of a lie, their solution to the missing data was to hand me, formally, an insert to our manual set that stated the backup solution they had sold us "Did not support readonly tablespaces".
And that was when we threw the Sequents out, ditdched Oracle and moved to DB2 on RS/6000 SP'2.
An Oracle DBA regomized as "Larry"? Somehow that smells suspect.