Patchwork Psychology

Due to a recent fiasco at my work with developers wiping out data in databases, I’ve decided it’d be a good idea to export the database so that we could recover individual tablespaces and tables if necessary. The answer doesn’t immediately present itself, however, because I don’t have the ’sys’ user password. I’m ok, though, because I can still log in using OS authentication by using sqlplus connect / as sysdba. It does, however, befuddle the ORACLE export command exp, as it won’t accept / for the username and password (the basic syntax is exp scott/tiger, where scott is the username and tiger is the password).

Using the basic syntax, exp will prompt the user for the parameters. It is possible to automate this process through the use of key=value parameters on the command-line or in a parfile. Help is available for these parameters by typing exp help=y. On the command line, separate the key/value pairs with spaces. In the parfile, newlines can also be used.

I had a heck of a time figuring out how to put / as sysdba on the command line as the username. Putting quotes around it didn’t help. Finally, after I had given up and decided to put the username in a parfile, I found this website:

troygeek.com: Using SQLPLUS, Import, and Export Without a Password
http://www.troygeek.com/article/20/

There I found the elusive syntax, which in hindsight makes perfect sense. The userid must be contained in escaped single quotes.

So I run exp with my options

exp \'/ as sysdba\'      \
full=y                   \
grants=y                 \
rows=y                   \
consistent=y             \
direct=y                 \
statistics=NONE          \
file=full_export.dat     \
log=full_export.log

and watch exp export my worries away! All I need to do now is make a cron entry and I’ll have a day’s worth of data available. But chances are that I will want to have a few days’ worth of data available, so I could either run the exp command from a script that will rename the data file after the export (which isn’t a bad idea, as it would be a very convenient place to insert some cleanup scripting to remove exports beyond a certain age) or I could automatically append a timestamp to the filename right on the command line:

exp \'/ as sysdba\'      \
full=y                   \
grants=y                 \
rows=y                   \
consistent=y             \
direct=y                 \
statistics=NONE          \
log=full_export.log      \
file=full_export-`date +%Y.%m.%d.%H`.dmp

This would export the database to full_export-YEAR.MO.DY-HR.dmp, so if I ran it while writing this, it would produce the filename

full_export-2007.01.12.17.dmp

Then I should come up with some scripting to clean up the old exports as mentioned above, as I don’t want to check this regularly to clean up the data, nor do I want to start receiving text messages at 3 AM saying the database has crashed.

After this I can rest easy knowing that my data is safe from developers…well, sort of. I guess I can at least know that I’ll be able to bail out a wreckless developer from a data disaster.