Backups and a Clever Student

During my one year stint as a Sr. Instructor at Oracle University I got to meet with a lot of current and aspiring DBAs. While I’m most at home in front of a computer with thirty clients breathing down my neck, a fat mug of coffee and a pile of metrics spilling off the desk, teaching a class is a heck of a lot of fun. You get to meet a lot of interesting people and hear war stories from all over the Oraclosphere.

But this story isn’t about teaching in general. I want to tell you about

The Most Clever Student I Ever Had

While teaching at Oracle, there were many courses I enjoyed teaching: 10g New Features, 10g RAC (9i not so much), 9i and 10g Security, etc. But my favorites were the 9i and 10g Workshop II. The reason is that in those classes, we had a full day of backup and recovery exercises. The students got a bit of time to prepare for their trials before I was free to unleash unholy terror on their poor, unsuspecting databases.

Now I would help them out a bit by putting up a list of recommended practices. Set your backup parameters properly, assemble the necessary pieces, take a backup, that sort of thing. But I also let them know they were welcome to add whatever personal touches they wanted to feel comfortable in the face of my onslaught. Once they finished we would begin. Twenty eager students versus me, Master of Destruction and Patron Saint of ORA-600s.

In one particular class I had a student who I could tell from day one was a cut above. He asked the truly difficult questions. He finished the labs in record time. He told me about his experiments, ideas, and plans. So I knew he would be a ton of fun when the final lab day came along. I had something special ready for him.

So everyone made their preparations, and I could see a cocky grin on the guy as I told them it was time. I started logging into student machines and tricking up their databases in various ways… delete a controlfile here, drop a redo log there, write some data to a datafile elsewhere. I won’t bore you with all the different predicaments. You and I both know only one story matters. The showdown was on. An epic battle, you see, between myself and the upstart who thought he could beat a teacher. A veritable David and Goliath in the works. And I would be victorious. Oh yes.

I started off light, testing his weaknesses. Dropped the USERS tablespace and TEMP. I figured he’d notice USERS but I wondered about TEMP… no dice. Within a minute or two he slapped his finger down on the Enter key, looked up, and grinned.

Round two. Two members of a log group down, one member of a different log group toast, overwrote one of the controlfiles so they were out of sync, and renamed a datafile. A little overboard so early on, but I needed to see how quick he was. He ended up going for a recovery until logseq, got it back with an incomplete recovery, and cleaned up after himself to make it look squeaky clean. Nothing too impressive, mind you. But he did it fast, ending the round with the loud clack of the Enter key and a smug smile. This time he put his hands behind his head for a final flourish. No more mister nice guy.

I opted for deletion of all his archive logs this time. Just to show him who’s boss, mind you. Two datafiles got bad blocks written to them. You might call it petulant but for good measure I deleted the RMAN binary from his $ORACLE_HOME. Seriously folks, it was for his own good. I stood up from my seat, relinquishing the protection afforded me by the screen that blocks every student’s view. Our eyes met. If it hadn’t been so sunny outside, thunder would have crashed.

Five minutes later he was done. Not just finished, but done. Everything was recovered and back in place. He smiled like nothing every happened. He knew, the guy knew I deleted his rman binary. He didn’t say a word. He just smiled. The file was back like nothing had happened.

Dropping back into my seat like an Acme anvil over a coyote on rocket powered rollerskates I furiously started typing. Controlfiles gone. Datafiles gone. Redo logs gone. Archive logs gone. His secret stash of RMAN scripts (sneaky) gone. ORACLE_HOME gone. No, seriously. I deleted his entire Oracle installation. Don’t judge me. You would have done the same thing.

Seven minutes go by and he clacks and smiles. No. No way. I logged into his machine. Everything was back to normal. The ORACLE_HOME was back, the database was back, even the RMAN scripts directory. It was all there. He had beat the trials. I smiled and nodded like the proud dad at the end of a movie about little league baseball.

So what did he do?

This student was obviously prepared. In fact, he was hilariously over-prepared. If I had bothered to take a better look at the RMAN scripts he had created I would have seen the extremes he went to. Here is what he did at the beginning of every exercise:

• Took a tar backup of all scripts, the ORACLE_HOME, and other special files like oratab.
• Used RMAN to back up his database, archive logs, controlfiles (before and after), and spfile.
• Backed up controlfile to trace and spfile to pfile.
• Exported the database.

But that’s not all. He also put a copy of every single backup on at least three other students’ PCs. That time when I just deleted the rman binary, he scp’d the rman binary from another student’s installation to his own machine.

The biggest thing is that he was prepared. All theatrics and pseudo-gladiatorial commentary aside, he really did make sure he was prepared for any eventuality. Even though most of the things I inflicted his database with were outlandish and unlikely, he was ready anyways. That readiness is usually the mark of someone who has either been bitten (which we all have at some point) and someone who has vowed to treat their database as they would their own kin.

The fact of the matter is that your backups are production. No matter where you store them, how you manage them, or what tool you use to take them, backups should be treated like solid gold. In the event you lose a database, all that is between you and a really truly terribly bad day is a good backup plan. In fact, many companies opt to back up any database, even development and QA (which are also production to some folks). If there is one task that you should pay the utmost attention to, it is ensuring a solid and safe backup strategy for your company’s most critical asset: its data.