Oracle

HOW TO RESOLVE ORA-00257 IN 10G ?

Errors:
ORA-00257

Symptoms:
Connections to the database Receive ORA-00257

Cause:
The max limit for flash recovery area(db_recovery_file_dest_size) is reached.

Fix:

1. Check whether the database is in archive log mode and automatic archivingis enabled.SQL> archive log list;
2. If Archive destination is USE_DB_RECOVERY_FILE_DEST, find the archive destination by :SQL> show parameter db_recovery_file_dest;Check what is the value set for db_recovery_file_dest_size
3. Find the space used in flash recovery area by :SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
4. If SPACE_USED is equal to SPACE_LIMIT, move the archive logs to differentdestination.
5. Archive all the log filesSQL> alter system archive log all;
6. If ORA-16020: less destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST is received for step 5, thenFor each destination give correct the archivelogpath and issue:SQL>alter system set LOG_ARCHIVE_DEST_.. = 'location=/archivelogpath reopen';
7. Just switch the logs to verify:SQL> alter system switch logfile;

NOTE:
If you want to increase the flash recovery area,

STEPS:

1. Note down the path of flash recovery area.SQL> show parameter db_recovery_file_dest;
2. Disable the Flash Recovery AreaSQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';
3. Increase the Flash Recovery AreaSQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10g SCOPE=BOTH SID='*';
4. Enable the Flash Recovery Area SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/dir1' SCOPE=BOTH SID='*';If the flash recovery area location is an Automatic Storage Management disk group named disk1, for example, then you can do the following:SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*';

posted by nixhacks @ 7:22 AM