Re: ORA-00257 archiver error. connect internal only, until freed. Well, how can I securely "freed"

On Jun 8, 11:21 pm, Joe <joe1..._at_tlen.pl> wrote:
> my archivelog on 10gR2/Win2k3 has just filled out.
> I still can connect through SYS into my instance.
>
> Obviously, I would like to increment db_recovery_file_dest_size which
> is simple, however this database did not have full backup since couple
> weeks, and I wish to keep it fully working with data up to the moment
> when archivelog has been filled out (couple hours ago). I can still
> run SELECT queries.
>
> 1. What should one do in order to securly increment recovery dest
> size, and restart oracle instance?
> should one just simply execute "Alter system set
> db_recovery_file_dest_size=20G" and restart oracle as a service in
> Windows 2k3, or try to backup with rman before? Or maybe there's
> another more secure way to deal with this situation?
>
> 2. Can one set up db_recovery_file_dest_size as unlimited? I have also
> tried to move 3/4 of my flash content (the oldest part) into different
> place to make room for the fresh archivelog, but that did not change a
> thing.
> In the future, how can one successfully "release" unwanted archive log
> after rman backup in order to make space for the new archivelog
> content?
>
> Thanks for all your help.

If you have move the content of the flash recovery area, you need to tell Oracle that the space is now available. Fire up RMAN from a DOS command prompt and execute the following commands that appear after > (it might be a good idea to look up the meaning of these commands first):
RMAN> crosscheck backup;
RMAN> delete expired backup;
Are you sure? yes

To fix the problem without the need to restart the database instance, from a DOS command prompt, execute the following (replace my_db_sid_here with the appropriate SID):

Z:\>SET ORACLE_SID=my_db_sid_here
Z:\>SQLPLUS / AS SYSDBA
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE='20480M' SCOPE=BOTH;

System altered.

Give the Oracle 15 to 20 minutes and it should be ready for use again. It is better to make certain that DB_RECOVERY_FILE_DEST_SIZE is set to a realistic value than to allow Oracle to completely fill a hard drive volume until 0 bytes are free on the volume.

It would be a good idea to determine why all available space in the flash recovery area was exhausted, and correct that problem.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Jun 09 2007 - 06:21:36 CDT