Full Flash Recovery Area on Oracle 11g
Upon accessing Enterprise Manager for an Oracle 11g instance recently I was unable to login. I was greeted instead with a dashboard message indicating that the database was up but that “Archiver is unable to archive a redo log because the output device is full or unavailable.” The following error prevented EM from establishing an Oracle Net connection: “ORA-00257: archiver error. Connect internal only, until freed.”
I determined that this was due to a full flash recovery area. This was odd because in the past month, I’ve left this instance alone without running any transactions against it. Apparently the flash recovery area size is defaulted to an extremely low number. As this is not a production database, I decided against simply expanding the size; I instead wanted to completely disable flash recovery logs so that this wouldn’t occur again.
Note: this was not a case of a lack of disk space. 50GB is available to the instance, 23% of which was in use. This was solely an issue with Oracle 11g configuration parameters.
To resolve this issue, navigate to the flash recovery directory for the Oracle 11g instance you are working to fix. For me, this directory was:
I deleted all of the archive log directories from the above directory:
$ rm -rf *
You must use Oracle Recovery Manager to inform the instance that the archive logs are no longer present on the filesystem. Use the
rman binary to connect to the target database in the following manner:
rman TARGET SYS/<password_for_SYS>@<name_of_database>
rman indicates that you are connected to the database, crosscheck the instance’s knowledge of all log files with their physical presence on disk:
RMAN> crosscheck archivelog all;
I then chose to delete all of the archive logs from the
rman respository using:
RMAN> delete expired archivelog all;
After verifying that
rman deleted all logs (94 in my case), I returned to Enterprise Manager and attempted to establish a (non-internal) connection to the instance. The attempt succeeded.
To disable the instance’s logging of flash files, I clicked on the “Availability” tab in EM, then on “High Availability Console,” then on the link to the right of “Flashback Database,” then scrolled to the section entitled “Media Recovery”. I unchecked the checkbox next to “ARCHIVELOG Mode”. After clicking “Save,” then typing in my credentials to restart the database instance, I waited for the database instance to come back online. I then returned to Enterprise Manager, logged in, and verified that the database is now in NOARCHIVELOG mode, which should prevent this issue from occurring again.
After completing this process, Enterprise Manager should indicate that Flashback Database Logging is Disabled; see the bottom right corner of the screenshot below: