Here’s a mistake you only do once: delete Oracle’s flash_recovery_area thinking that you can save space. You see, to quickly configure Oracle Backups (here is a great and brief how-to), it’s best to turn on “archivelog”, which instructs Oracle to create transaction logs, and allows you to make live backups. After making a couple of backups, you’ll find that the directory “flash_recovery_area” starts filling up with multiple backup copies. Since that directory started empty, you may think that you can blast it away. Uh… NO! (I can now see anyone familiar with Oracle administration smacking their hands to their foreheads.)
What happens is not only does flash_recovery_area contain the backups but also the archive log files. With those missing Oracle immediately jumps into read only mode, and worse, upon restart, fails to come up. After Googling a bit, I found this absolutely incredible walk-through on Oracle recovery from O’Reilly. The problem is that I got stuck in the lower left corder of the walk-through: I was without redo logs, and, since I was working on the first backup procedures, I didn’t have a remote backup copy yet.
While it wasn’t a production system, my colleagues spent some time setting up the environment, and I didn’t want to lose some hours of configuration work.
Amazingly, akpraveen had a similar situation and posted his solution in a blog entry. The solution is to reconstruct the control file to trick Oracle that it doesn’t have redo logs and to simply trust the dbf files. Because we were using XE on Linux, the resulting commands were (NOTE: Include all your dbf files in the CREATE CONTROLFILE COMMAND):
oracle$ sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL>
CREATE CONTROLFILE REUSE DATABASE “XE” RESETLOGS NOARCHIVELOG
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE
‘/usr/lib/oracle/xe/oradata/XE/system.dbf’,
‘/usr/lib/oracle/xe/oradata/XE/undo.dbf’,
‘/usr/lib/oracle/xe/oradata/XE/sysaux.dbf’,
‘/usr/lib/oracle/xe/oradata/XE/users.dbf’
LOGFILE
GROUP 1 SIZE 51200K,
GROUP 2 SIZE 51200K;
SQL> alter database mount;
SQL> alter database open resetlogs;
And then we were back in business! Thank you to all the smart Oracle gurus out there who allowed me to save lots of time.