Who, me? Welcome back, dear reader, to “Who, Me?” – Catharsis Corner of The Register. Once a week, he provides information to readers like you to help ease the burden when things don’t go as planned.
This week’s confession comes from a reader we identify again as “Larry.” Several years ago, Larry worked as an Oracle DBA for a healthcare provider in the United States. He hadn’t been his DBA that long, so he still didn’t “know everything or have it all,” in his words, but he was getting by. was knowledgeable enough to ask intelligent questions of his older DBA.
And he was given a really important mission™️. The industry he worked in produced monthly reports on membership numbers based on the current month and the previous month. Its monthly reports were automated, but for some reason the annual report still required a manual process. Larry said who, me? He said, “These days he backs up two months, rounds down the month, For this Reconfigure the job to generate reports and finally restore from backup. ”
Obviously I needed to make this more efficient.
During this time, Larry became familiar with Oracle’s Data Pump utility, which is ideal for one-time table backups. He also knew how to back up a particular partition, so he thought what if he created a parameter file that he imported to handle the annual backups using Data Pump.
Now, obviously when building this file I didn’t have to back up the entire table and the 10+ years of data stored on it. All he needed was the last two months. Enter the useful parameters. TABLE_EXISTS_ACTION. This is run against the import file and if it finds a table that already exists, it takes the necessary action.
Larry was an eager learner and always loved learning new things.
Since the export of the relevant partitions ran perfectly, he assumed that the import would be perfect as well. Typically, the process requires manually truncating a partition to restore it from a backup, but the main goal of this exercise was to eliminate manual intervention.Therefore, Larry added: TABLE_EXISTS_ACTION=TRUNCATE Copy it to the import parameter file.
His vision was that “the database would automatically truncate these two partitions and gracefully reload the data, saving everyone time and energy.” Cue praise from upper management and a nod of approval from his coworker, his DBA.
However, when I ran the process, I discovered that the backup file was much smaller than I expected. a lot. For example, he made it 1GB when it should have been 75GB or more.
That piqued his curiosity.
It was discovered TABLE_EXISTS_ACTION As the name more or less suggests, this is a table-level parameter, not a partition-level operation. And what he essentially did was cut out all of that, not just the tables that were needed for the report.
Over 10 years of customer data. It was gone within seconds.
Of course, thankfully I had a backup, so it didn’t take long to restore all my data. In fact, no one reported any broken links or service interruptions. All I knew so far was Larry. And he learned the important lesson of properly understanding the commands he just learned before using them on production data.
If you’ve had a learning experience like Larry’s, we want to hear about it. And so will your fellow Residents. Please let me know about this by emailing Who, Me? And we will all share a catharsis some Monday. ®
