ORA-00257: archiver error. Connect internal only, until freed.(Archiver Destination Full)

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

ARCHIVE DESTINATION FULL ORA-00257

ps -ef|grep pmon

ORACLE_SID=`ps -ef | grep asm_smon | grep -v 'grep' | grep -v 'sed' | awk '{printf $8}' | awk 'BEGIN{FS="_"} {printf $3}'`

ASM Tablespace Utilization Scripts

http://select-star-from.blogspot.in/2013/03/asm-tablespace-utilization-scripts.html

show parameter recovery;

1. DB_RECOVERY_FILE_DEST_SIZE (Specifies max space to use for FRA)

2. DB_RECOVERY_FILE_DEST (Location of FRA)

The DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.

col name format a40

select

   name,

   to_char(space_limit, '999,999,999,999') as space_limit,

   to_char(space_limit - space_used + space_reclaimable,

   '999,999,999,999') as space_available,

   round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full

from

   v$recovery_file_dest;

select * from V$FLASH_RECOVERY_AREA_USAGE;(see what kind of files are available in the Flash Recovery Area)

select * from V$RECOVERY_FILE_DEST; (determine actual values)

ALTER SYSTEM SET db_recovery_file_dest_size=10G scope=both;

ALTER SYSTEM SET db_recovery_file_dest='/oradata/FRA';

For example, If FRA is in an Automatic Storage Management (ASM) disk group

ALTER SYSTEM SET db_recovery_file_dest='+FLASH’ scope=both;

RAC

ALTER SYSTEM set db_recovery_file_dest_size=60G scope=both sid='*' ;

ALTER SYSTEM SET db_recovery_file_dest='+FLASH' sid='*';

In a RAC database, all instances must have the same values for these parameters. Even though there are multiple nodes they all share the same controlfiles.

Here,if there is no space available. DBA can take the archive backup to free the space.

ls -ltr *.cmd

nohup rman target / cmdfile=archivebackup.cmd log=archivebackup_dbname_DDMONYY.log &

nohup: appending output to `nohup.out'

tail -f archivebackup_dbname_DDMONYY.log

archivebackup.cmd

-----------------

run {

DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-1' BACKED UP 1 TIMES TO SBT_TAPE;

allocate channel ch1 type 'sbt_tape';

allocate channel ch2 type 'sbt_tape';

allocate channel ch3 type 'sbt_tape';

allocate channel ch4 type 'sbt_tape';

BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;

}

ps -ef| grep rman

col name format a40

select

   name,

   to_char(space_limit, '999,999,999,999') as space_limit,

   to_char(space_limit - space_used + space_reclaimable,

   '999,999,999,999') as space_available,

   round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full

from

   v$recovery_file_dest;

select * from V$FLASH_RECOVERY_AREA_USAGE;(see what kind of files are available in the Flash Recovery Area)

select * from V$RECOVERY_FILE_DEST; (determine actual values)

NOTE:

In order to solve the above error the solutions are

1) Increase the free space where archiver archives the archivelog. The location where archiver archives the log is determined by parameter file pfile or spfile.

This can be determined by loging into sqlplus and issuing

SQL> show parameter log_archive_dest

2) In case it is not possible to increase free space at the same location but if free space is available at other location then the parameter log_archive_dest (or log_archive_dest_1 in some cases) can be changed so that the new archives are produced at new location specified which has free space.

this can be done by modify init.ora file or using alter system if spfile is present

SQL> alter system set log_archive_dest_1=’

3) We can use following steps for this

   1.find the location of Archive destination by

   show parameter archive_dest

   lets say it provide  LOCATION=/u10/oradata/mydb/arch

   2.move some files to some other location using os command

   cd /u10/oradata/mydb/arch

   mv /u10/oradata/mydb/arch/* /u11/oradata/mydb/arch-bkp/

4) The option which is often used is to take a backup of the archives from the existing place and delete those archives from that place so that new archives can generated at that place .

the backup can be OS level backup and OS level del;etion but the recommended method which is compulsory to be used with ASM in place is taking any RMAN backup and delete using RMAN. as shown

rman target sys/sys

RMAN> backup archive log all device type disk format ‘/oracle/arch_%U’;

RMAN> delete archive until time ‘trunc(sysdate)’;

This will delete all the archive logs until today and space will freed and the archiver will start archiving redo logs

------------------------------------------------------------------------------------------------------------

The views v$recovery_file_dest and v$flash_recovery_area_usage does not always give the true picture of exact space used due to BUG Bug 4911954 in Oracle 10g and the versions which is confirmed to be affected is 10.2.0.2. (Reference Metalink Doc Id 4911954.8 ).

V$FLASH_RECOVERY_AREA_USAGE provides information about the flash recovery area disk space usage. Following is its main columns:

FILE_TYPE - the type of the file and can have any of the following values:

controlfile, onlinelog, archivelog, backuppiece, imagecopy, flashbacklog

PERCENT_SPACE_USED - This represents the disk space used by the file type, in percentage.

PERCENT_SPACE_RECLAIMABLE - this represents the percentage of disk space reclaimable from the file type after deleting any obsolete or redundant files, and files backed up to a tertiary device.

OTN Notes that you can see the actual space used by joining into v$recovery_file_dest:

A new view, V$FLASH_RECOVERY_AREA_USAGE, shows what's available in the flashback area.