Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-00257 archiver error. connect internal only, until freed. Well, how can I securely "freed"

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

From: Joe <joe1977_at_tlen.pl>
Date: Sat, 09 Jun 2007 09:04:46 -0700
Message-ID: <1181405086.311906.75450@q69g2000hsb.googlegroups.com>


thank you all for your help.

after doing some research online (whole night long), it turns out that the database did not hang (cause physically there was 50gb more space, its just DB_RECOVERY_FILE_DEST_SIZE param was limiting oracle instance from adding data). After doing "Alter" mentioned by Charles, system got back to normal.
I know way more about backing up with rman now, but of course it is oracle and I know maybe 2% of all :)

my current settings:

RMAN> show all;

using target database control file instead of recovery catalog RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT \10.2.0\DB_1\DATABASE\S
NCFGGG.ORA'; # default

I would like to backup entire system: control files, archive logs, datafile copy, basically - everything in order to get zero problems putting ora online after major crash.
should one (ccording to my show all settings) use the following command:

RMAN> BACKUP ARCHIVELOG ALL DELETE ALL INPUT; is there anything this command is not backing up? also, as you can see once archivelogs have been backed up, they are removed from recovery flash area. I have backed up yesterday with the command mentioned below; I had some users running queries, but it is odd that not a single archivelog file has been created in my flash_recovery_area since then. Is it something I should be worry about??

Thank you.

On Jun 9, 7:21 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Received on Sat Jun 09 2007 - 11:04:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US