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

ORA-00257 Archiver Error

If you come across this error it means that your database is in archivelog mode and it is not possible to archive the current online redo log file. This is usually because the archive log drive has become full and needs to be cleared down.

Step 1: make some more space on the archivelog drive

You can find out where you are archiving your logs to by looking at the init.ora or spfile and checking the log_archive_dest_n parameter. Normally log_archive_dest_1 is the local archiving location.

You may also make an attempt to archive your REDO logs using the following command and you will receive an error:

SQL> alter system archive log current;
alter system archive log current
ERROR at line 1:
ORA-16038: log 3 sequence# 192 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 3 thread 1: 'D:\ORADATA\REDO\REDO03.LOG'

This error shows up when you have no space available to archive the current log file.

I can’t log on to the Database as SYS

If you are having trouble actually logging on to the database to check the value for the archiving location just open up the SPFILE or INIT.ora file in a notepad and search for the parameter in there.

You also have the option to change the location of where the archivelogs are being written to, using the following command:

ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=D:\Oracle\ArchLogs\' SCOPE=both;

If you are having issues logging onto the DB check out the strategy below which should get you logged on if your normal way of logging on doesn’t work.

How to log into the DB as SYS

Once you have cleared down some space for new archive logs, you will need to log on to the database and clear the archive logs. You may not be able to log in to the database as you normally would. That being the case, this time you need log on using the following sequence:

set oracle_sid = TESTDB
sqlplus /nolog
conn sys/ as sysdba
archive log all;

Once complete, you should see the archive logs being created on the archive log drive and operation of your database should continue as normal, hopefully… If not, check the alert log and see what other errors you have.

Archive Log Backups

It should be noted that if you need to keep the archive logs and they haven’t been backed up you should perhaps move them somewhere temporarily until you can be sure they have been backed up before you delete them.

If you are wondering how to put your database into archivelog mode, have a read through my post on how to do that. Also, I have written a much more detailed article on archive log mode which you should have a read through and it also covers some other interesting and related archive log topics.

Have fun!


Like it, share it...

Category: Database Config

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *