At Oracle OpenWorld 2018 a few of the EMEA Oracle ACEs got together to do a lighthearted joint presentation, where we had four minutes each to speak about a mistake we had made and what we learned from it. Not surprisingly the session was called, “Even the ACEs make Mistakes – What did they learn?” Some of us said we would blog our stories, so here’s mine…
I had performed some maintenance on a production database, moving some objects into new tablespaces and purging a lot of old data. It meant that some tablespaces now contained a lot of free space, that would never be reused, so I decided to do a manual tablespace reorganisation to reclaim the unused disk space.
Having moved all the objects out of one tablespace I preceded to drop it, but forgot to add the “including contents and datafiles” clause. That meant I had to manually delete the datafiles from the file system. The only problem was I deleted the wrong ones! Here’s a graphical representation of what happened.
Luckily Oracle has a really good backup/recovery tool called Recovery Manager (RMAN), and I had backups of the database from before I messed up, so I restored and recovered the missing datafiles and everything was working again.
What did I learn?
- Always have a backup! Luckily I did.
- If you are going to drop a tablespace, make sure it is the correct one (I did) and make sure you add the “including contents and datafiles” clause (I didn’t), so Oracle will delete the datafiles for you. That way you won’t delete the wrong files by accident.
- Don’t be me. I’m a dick! 🙂
Cheers
Tim…