An Oracle DBA regomized as "Larry"? Somehow that smells suspect.
DBA made ten years of data disappear with one misplaced parameter
Welcome once again, dear reader, to Who, Me? – the cathartic corner of The Register wherein, once a week, we hand over to our readers, such as yourself, so that they may unburden themselves about times when things did not quite go according to plan. This week's confession comes from a reader we'll Regomize as "Larry". Some …
COMMENTS
-
-
Monday 25th March 2024 17:26 GMT Mage
Re: on a COPY of the production database
Ideally on a separate computer that's accessed directly with its own keyboard and mouse (No KVM switch etc) and the network unplugged, because you never know :(
Thinks, am I accessing the test server in the basement of the office in another country, or the live co-located server in the data centre? I know, i'll copy the data to a VM on my own laptop just to be sure to be sure.
-
-
Monday 25th March 2024 12:49 GMT phuzz
Re: "Thankfully [..] there were backups"
"If you can't restore from a backup, then your data isn't backed up", so by testing their restore procedures, Larry was actually helping.
(Tongue partially in cheek, because in my last job, if I had to restore from a backup for someone, I'd also tick off my "tested restores" task for the month)
-
Sunday 31st March 2024 19:04 GMT Shalghar
Re: "Thankfully [..] there were backups"
Dont call yourself a jockey or cowboy when (schlock) mercenary rules apply surprisingly well.
17. The longer everything goes according to plan, the bigger the impending disaster.
41. “Do you have a backup?” means “I can’t fix this.”
43. If it's stupid and it works, it's still stupid and you're lucky.
https://schlockmercenary.fandom.com/wiki/The_Seventy_Maxims_of_Maximally_Effective_Mercenaries
-
-
-
-
Monday 25th March 2024 09:52 GMT Michael H.F. Wilkinson
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.
-
Monday 25th March 2024 10:15 GMT CowHorseFrog
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.
-
Monday 25th March 2024 10:20 GMT Bebu
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.
-
Monday 25th March 2024 11:53 GMT Prst. V.Jeltz
Re: Daft?
" backing up the most recent two months, truncating said months"
My eyes glazed over at that point and i decided not to try to digest the exact specifics and just carry on with the story, even though that is currently my exact stock in trade . I import / store present data to the dept next door which makes all the pretty pictures out of it, usually via a "view" . These views can easily get last week / month / year / fin year at any point of the current week / month / year / fin year without all that jiggery pokery in the story .
Like this for example
WHERE (CAST(payday AS Date) BETWEEN DATEADD(DAY, 1, EOMONTH(DATEADD(MONTH, - 13, GETDATE()))) AND EOMONTH(DATEADD(MONTH, - 1, GETDATE())))
I just assumed there must be other factors / complications , and skipped to the "lost 75gb" part
-
Monday 25th March 2024 15:17 GMT yetanotheraoc
Re: Daft?
Views are ideal but if the report is slow could just save the monthly reports in a table and query whatever 12 months for the annual report. -- "other factors / complications" -- If the annual report needs extra detail just capture the extra in the monthly process as well.
Truncate... Whoever devised that works with spreadsheets not databases. Have suffered a few hiccoughs myself and sadly one time was not saved by the backups. In my opinion it's best to keep restore for emergencies and find another way for routine business.
-
-
-
Monday 25th March 2024 10:29 GMT I Am Spartacus
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.
-
Monday 25th March 2024 13:43 GMT Gene Cash
Re: SO much or Oracle dos not work as you expect
> We only kept about a month's worth of backup because tapes were expensive
I guess tapes don't seem quite so expensive now.
I've seen people reuse tapes until you could see through them, despite warnings that they didn't have good backups. "What? We're not getting any errors in our backup process"
-
-
Monday 25th March 2024 17:35 GMT Mike 137
Fundamental misunderstanding
"He hadn't been a DBA for very long so, in his words, he was not yet "all knowing or all powerful""
Anyone (in any discipline) who thinks they're "all knowing or all powerful" isn't by definition. Someone who actually was as close as possible to either wouldn't think like that -- they'd (in the words of Dirty Harry) know their limitations.
-
Tuesday 26th March 2024 15:37 GMT DBA-ONE
Hundreds of years ago I was a production Oracle and SQL Server DBA. The Oracle side required less work as that was a pending prod system. I spent the majority of my time doing data conversions from SQL Server and what not. I had a process down that would drop existing objects and then import data and recreate things like views, procedures, etc. It all worked very well and was efficient. Until one day. We had dev, test and prod instances of course and while I thought I was in the dev instance I ran a script that dropped all sequence objects. I ran the script as the Sys user in the Prod instance. Once that happens, you cannot login anymore and this means the instance had to be rebuilt, and it was. If that system were live it would have been a resume generating event. After that I modified glogin to show the instance and user and any time I was going to run a script I logged in to a new connection.