SEO

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.

On Jun 8, 11:21 pm, Joe <joe1...@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.

the words that you're looking for are "delete input".
you may want to set retention policies such that archived redo logs
are included in at least 2 backup sets prior to being eligible for
deletion.

you could temporarily set the log_archive_dest to another location
that has free space and after grooming the logs, switch it back (no
instance restart required).

-bdbafh

On Jun 8, 11:21 pm, Joe <joe1...@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.

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...@yahoo.com> wrote:
> On Jun 8, 11:21 pm, Joe <joe1...@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 -

On Sat, 09 Jun 2007 09:04:46 -0700, Joe <joe1977@tlen.pl> wrote:

>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?

It is *only* backing up the archivelogs, it is NOT backing up the
database etc.

--
Sybrand Bakker
Senior Oracle DBA

On Jun 9, 12:53 pm, sybra...@hccnet.nl wrote:
> On Sat, 09 Jun 2007 09:04:46 -0700, Joe <joe1...@tlen.pl> wrote:
> >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?
>
> It is *only* backing up the archivelogs, it is NOT backing up the
> database etc.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

That could make it difficult for the OP to recover from problems. The
10g R2 Enterprise Manager Database Control makes it easy to set up
RMAN so that it backs up the database, archived redo log files, and
control file, while keeping X number of database backups in the flash
recovery area. It is a good idea to force log file switches
occasionally, for instance every 30 or 60 minutes.

If no new archived redo logs are making it into the flash recovery
area, check the Alert log to determine if the database instance still
believes that the flash recovery area is full.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

it created one flash recovery area file couple hours ago, alert log
does not say anything, last message was:

Severity=Unreachable Clear
Message=Agent Unreachability is cleared. The current status of the
target is UP.
Rule Name=Database Availability and Critical States
Rule Owner=SYSMAN

I read somewhere that once make full backup with rman, all archivelogs
can be removed. After reading more about rman, and still being
confused with number of options and switches it got, I dont think that
the above is even a question.

I am just trying to get to the point where my ora10gR2 is backed up
fully (everything) and I dont have to worry about it (old archives are
removed from disk device). I have 50gb partition for that logs and
backups. My tablespace is 200mb, archive logs about 50 mb each about
250 mb a day.
What would be the likely best settings for backup? should one backup
each day just controlfile and datafiles, and keep archivelogs until
that backup happened and then remove them, or also back them up (what
is the reason for that?)




On Jun 9, 4:39 pm, Charles Hooper <hooperc2...@yahoo.com> wrote:
> On Jun 9, 12:53 pm, sybra...@hccnet.nl wrote:
>
>
>
>
>
> > On Sat, 09 Jun 2007 09:04:46 -0700, Joe <joe1...@tlen.pl> wrote:
> > >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?
>
> > It is *only* backing up the archivelogs, it is NOT backing up the
> > database etc.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> That could make it difficult for the OP to recover from problems. The
> 10g R2 Enterprise Manager Database Control makes it easy to set up
> RMAN so that it backs up the database, archived redo log files, and
> control file, while keeping X number of database backups in the flash
> recovery area. It is a good idea to force log file switches
> occasionally, for instance every 30 or 60 minutes.
>
> If no new archived redo logs are making it into the flash recovery
> area, check the Alert log to determine if the database instance still
> believes that the flash recovery area is full.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

On Jun 8, 11:21 pm, Joe <joe1...@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.

Is this a real useful test or production database environment or one
that you are just playing with?

I would recommend that you purchase and read the Hart/Freeman rman 10g
book. Trying reading it a couple of times cover to cover.

On Jun 9, 5:33 pm, Joe <joe1...@tlen.pl> wrote:
> it created one flash recovery area file couple hours ago, alert log
> does not say anything, last message was:
>
> Severity=Unreachable Clear
> Message=Agent Unreachability is cleared. The current status of the
> target is UP.
> Rule Name=Database Availability and Critical States
> Rule Owner=SYSMAN
>
> I read somewhere that once make full backup with rman, all archivelogs
> can be removed. After reading more about rman, and still being
> confused with number of options and switches it got, I dont think that
> the above is even a question.
>
> I am just trying to get to the point where my ora10gR2 is backed up
> fully (everything) and I dont have to worry about it (old archives are
> removed from disk device). I have 50gb partition for that logs and
> backups. My tablespace is 200mb, archive logs about 50 mb each about
> 250 mb a day.
> What would be the likely best settings for backup? should one backup
> each day just controlfile and datafiles, and keep archivelogs until
> that backup happened and then remove them, or also back them up (what
> is the reason for that?)

It is preferred in this forum that reponses to posts appear below the
pre-existing text - bottom-posting is preferred.

hpuxrac provided a good suggestion for a book. While I have not read
that specific book (the RMAN documentation from Oracle is reasonably
good), it is a good idea to make certain that all pieces are in place
to allow for recovery.

You might want to retain not only a couple days worth of backups, but
also the archived redo logs for a couple days. Ideally, the multiple
online RMAN backups (data files, control file, and archived redo log
files) should be transferred to tape or other backup media in addition
to those files remaining in the flash recovery area. In the event
that one of the backups is bad and you need to recover the database,
you could use an earlier backup and all archieved redo logs since the
last good backup.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jun 9, 10:04 pm, Charles Hooper <hooperc2...@yahoo.com> wrote:

>
> hpuxrac provided a good suggestion for a book. While I have not read
> that specific book (the RMAN documentation from Oracle is reasonably
> good), it is a good idea to make certain that all pieces are in place
> to allow for recovery.
>
> You might want to retain not only a couple days worth of backups, but
> also the archived redo logs for a couple days. Ideally, the multiple
> online RMAN backups (data files, control file, and archived redo log
> files) should be transferred to tape or other backup media in addition
> to those files remaining in the flash recovery area. In the event
> that one of the backups is bad and you need to recover the database,
> you could use an earlier backup and all archieved redo logs since the
> last good backup.

The restore database validate check logical is also a good option to
think about for tape content validation but requires a lot of time and
access to tape drive(s).

>From my point of view though the OP needs to invest the time in
reading the documentation, getting familiar with the basic concepts
etc before throwing in specific limited questions. Whether that's the
oracle rman documentation that is free or the Hart/Freeman book is not
as important.