Monday 27 April 2009

Controlling Controlfile Backups

Did you know that you can control the name of your controfile trace backups? I didn't, until Bob Morgan of Stonehill College in Easton, MA pointed out some additional syntax in the ALTER DATABASE command.


I already knew that you could control the filename and location when backing up the controlfile in binary, via:


ALTER DATABASE BACKUP CONTROLFILE TO '<pathame>';


And I knew that you could get a text copy of the commands necessary to rebuild the controlfiles, via:


ALTER DATABASE BACKUP CONTROLFILE TO TRACE;


The resulting text commands are placed in the user's trace file for the session. To find the proper trace file requires knowing the session number; it's in the filename.  (Though I usually cheat, and go to the directory pointed to by the user_dump_dest parameter, sort by modification time, and my session is near the top.)


What Bob pointed out was the variation:


ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '<filename>';


This still writes to the backup_dump_dest directory, but names the tracefile with your choice of filename instead of the default one that includes the session number.  Much easier to find.


Thanks, Bob!