To the untrained eye, SQL statements and Unix commands look like so much heiroglyphics. Alas, even to the trained eye, such arcana is not so simple to keep track of. Oracle DBA Brian Bientz relates this harrowing tale of what happens when one confuses a ' with a `...
"One evening before leaving work, I remembered that I needed to add some space to a tablespace in our Oracle Applications environment. So I proceeded to add a datafile to the tablespace and placed it in a newly created file system on the server. At that time, we were doing cold backups nightly and the backups were driven off a configuration file that contained the list of file systems to backup. The next morning before I left for work, I realized that I forgotten to include the new file system in the configuration file. After realizing that the newly created datafile would not have been backed up by our nightly process, I dialed in remotely, put the tablespace in backup mode and issued the following command to make a backup of the datafile to a remote system:
tar -cvf -
| rsh `cd /orabackup/prod/ ; tar -xvf -'
"The command generated an error since the quotes were mismatched. I pulled the command back up and instead of changing the first ` to a ', I changed the trailing ' to a `. When the second command ended with a bizarre tar error, I suddenly realized what I had done. Since I changed the trailing ' to a `, the cd and tar -x was executed before doing the rsh ON THE LOCAL SYSTEM. Since the /orabackup/prod directory did not exist on that system, the cd failed and the tar command proceeded to untar the file into the same directory. The datafile was truncated to the size of the tar buffer!
"After realizing the error of my ways, I immediately updated my resume and then contacted Oracle support and begged for a solution. The initial response was to restore a backup of the datafile. But I had no backup of the datafile; that's what I was trying to do. So then we thought that maybe, just maybe, nothing had been written to the datafile yet, in which case we would be OK. Well, we weren't so lucky. Extents had been created in the newly created datafile and we were getting ugly error messages since the datafile was truncated. By this time, people were beginning to arrive at the office and some remote sites were complaining that they couldn't get there shipping orders, etc.
"Finally, after escalating our call through the support channels at Oracle, we finally got the answer we were looking for. Just issue the following command and do a database recovery:
alter database create datafile '
' as ' ' ;
"We recovered the database and everyone was happy. Disaster averted! Later that afternoon, I found the following statement in the Oracle documentation in reference to this command:
'Use the CREATE DATAFILE clause to create a new empty datafile in place of an old one. You can use this clause to re-create a datafile that was lost with no backup.'
"The moral of this story: mind your `s and 's!"
For more true DBA bloopers, click:
Have your own tale of woe to share? Submit your backup/recovery snafus, tuning disasters and ugly upgrades. Stories of good intentions gone bad, over-ambitious and under-trained newbies, clueless consultants, and even more clueless managers will all be accepted. The submitter of the most amusing or wince-inducing blooper of the month will receive a free copy of Craig Mullins' new book Database administration: The complete guide to practices and procedures. Send your bloopers to us today!