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

                 It doesn’t matter, whether you working in large or small organization. Space allocation is the common issue. Management wants all prod databases should be in archive mode, archive log should not be deleted before backup and at least 3 day archive should be there on server. Yes it should be like this, but the problem is they provide that much space which is capable to maintain 3-4 days logs. If backup fails, any bat job, scheduler runs from application server which generates high archive generation and you doesn’t aware of it and the archive destination gets full. You will get error like.

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

Delete archive logs and make sufficient space on archive destination and then switch logs. That’s all; it will solve your problem.

SQL> alter system archive log all;

You are well aware to solve this issue, yes you have to free space in archive destination else no transaction will process on database server. Delete archive logs which are backed up and sync with standby. If the archive generation is much high and you can’t stop the job. In this case there is chance that you have to delete archive logs which are not backed up also which are not transferred to standby. You can bring standby database in sync later. By taking incremental backup of Production. Before that you should inform to your manager because he should be aware of this. Not to worry, you haven’t done anything wrong. unless application team informs you about their activity. As a core DBA You can’t control on archive generation. Use below query to find pattern of archive generation. This report will work like RCA forward it whomever the concern.

select trunc(first_time) on_date,

       thread# thread,

       min(sequence#) min_sequence,

       max(sequence#) max_sequence,

       max(sequence#) - min(sequence#) nos_archives,

       (max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb

from   v$log_history,

       (select avg(bytes/1024/1024) log_avg_mb

        from   v$log)

group  by trunc(first_time), thread#, log_avg_mb

order by trunc(first_time) desc.

Don’t forget to fire full database backup.