Wednesday, January 16, 2013

Resolve ORA-00257 error

Purpose: This tutorial provides the basic steps for resolving an “ORA-00257 archiver error. Connect internal only, until freed.”. This error is basically telling you that you have run out of room to write additional archive logs. This may be a disk space issue or a database limit issue.

Step 1. Log on to server
sudo su – oracle
. oraenv
<db_sid>
Step 2. Connect to Oracle as sys
sqlplus / as sysdba
Step 3. Determine size and location
select name, value from v$parameter where name in  ('db_recovery_file_dest');
select name, trunc(value/1024/1024/1024) || 'GB' from v$parameter where name in  ('db_recovery_file_dest_size');
Step 4. Check host if room is available increase dest_size
host
df –h
exit
Step 5. Increase size if disk is available.
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = <new_size>G scope=both;

Step 6. Check Available area
select name, trunc(space_limit/1024/1024/1024) || 'GB', trunc
(space_used/1024/1024/1024)  || 'GB'
from v$recovery_file_dest
order by name
/