pat98's always & forever

Applies to:

Oracle Server - Standard Edition - Version: 10.1.0.0
This problem can occur on any platform.

Symptoms

Connections to the database Receive ORA-00257

Cause

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

Solution

SOLUTION:
========

1. Check whether the database is in archive log mode and automatic archiving is 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 different destination.

5. Archive all the log files

SQL> alter system archive log all;

NOTE:  See Note 315098.1 for pointers on backing up files from the flash recovery area to decrease space usage

6. If ORA-16020: less destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEED_DEST

is received for step 5, then

For each destination give the correct archivelog path and issue:

SQL>alter system set LOG_ARCHIVE_DEST_.. = 'location=/archivelogpath reopen';

NOTE: 

If you have configurated Flash Recovery Area (default in 10gR2)--meaning that you have set the two initialization parameters:
-- DB_RECOVERY_FILE_DEST_SIZE
-- DB_RECOVERY_FILE_DEST:
you can not use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters any more. You must always use the LOG_ARCHIVE_DEST_n parameters in case you have configured flash recovery area. LOG_ARCHIVE_DEST_10 is implicitly set to USE_DB_RECOVERY_FILE_DEST if you create a recovery area and do not set any other local archiving destinations.

If you try to use LOG_ARCHIVE_DEST with a Flash Recovery Area configured you will receive errors like

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

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 Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';

3. Increase the Flash Recovery Area

SQL> 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='*';