Pages

Tuesday, February 23, 2016

ORA-00257: Database freeze, restart archiving

ORA-00257: Database freeze, restart archiving
                                               
Summary

If you have enable archive log mode and run out of space at the archive log destination filesystem, then it is a matter of time to get the database freeze. You'll get the error:

ORA-00257 archiver error. Connect internal only, until freed.

The workaround is very simple. First option is to release some space by taking an rman backup with delete input for the archives, or change the archive log destination to a filesystem with enough free space.

alter system set log_archive_dest_1='location=/filesystem_with_free_space' scope=memory;

If you don't have time to wait for the backup move the archives to another destination, or the final solution if you don't have any free space at all, just delete them!

In case you delete archives, bear in mind that you have to take a full database backup afterwards. Don't wait for the next scheduled backup next week! Take it at the same day you delete the archives.

Now that you have free space at the archive log destination. Restart the archiver.

sqlplus "/ as sysdba" OR svrmgrl>CONNECT internal

ARCHIVE LOG STOP;

ARCHIVE LOG START;

ARCHIVE LOG ALL;

ALTER SYSTEM SWITCH LOGFILE;

One good way to avoid restarting archiver manually, is setting reopen at the archive log destination. For example reopen=60 will automatically in 60 seconds retry to start archiving (so you only have to get free space at the archive log destination)

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/genevamig/oradata/GNVTMG1/archives reopen=60';

Tip:If you move or delete archives, then the Rman will fail the next time because of the missing logs.

channel t1: starting piece 1 at 29-APR-04
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 04/29/2004 18:01:05
ORA-19505: failed to identify file "/oradmp/archive/PRODDB/PRODDB_1_161663.arc"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Recovery Manager complete.

In that case you should tell Rman to check which archived logs are still available on disk. The workaround is: Crosscheck archives.

No comments:

Post a Comment