Home » RDBMS Server » Backup & Recovery » Flash Recovery Area (Windows,10.2.0.3)
Flash Recovery Area [message #471733] Tue, 17 August 2010 01:39 Go to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
Hi I have configured a flash recovery area in my database and it is the only place where my archived log files are stored.

Now the flash recovery area is full and no space for archiving so database hangs.How to automate the deletion of archive log files from flash recovery area after a particular time(say 2 days).

And suppose if I have tow log archive destinations 1 and 2.Suppose dest_1 becomes full and there is still space in dest_2 will the archiving continue or will the database hang?
Re: Flash Recovery Area [message #471738 is a reply to message #471733] Tue, 17 August 2010 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ If you use RMAN to backup your FRA then Oracle will do it automatically for you. Otherwise there is (hopefully) no way.

2/ It depends on the parameter you give for dest_1: mandatory or optional?

Regards
Michel
Re: Flash Recovery Area [message #471743 is a reply to message #471738] Tue, 17 August 2010 02:32 Go to previous messageGo to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
Am not using RMAN and I am doing it manually I mean the FRA.So is there no way as you said?

Can I turn the archiving in FRA to optional and mandatory ?

Please look at the following

SQL> alter system set log_archive_dest_10='USE_DB_recovery_file_dest optional';
alter system set log_archive_dest_10='USE_DB_recovery_file_dest optional'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_10" not allowed with SPFILE


SQL> alter system set log_archive_dest_10='USE_DB_recovery_file_dest';
alter system set log_archive_dest_10='USE_DB_recovery_file_dest'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_10" not allowed with SPFILE


SQL> alter system set log_archive_dest_10='';

System altered.

SQL> alter system set log_archive_dest_10='USE_DB_recovery_file_dest optional';
alter system set log_archive_dest_10='USE_DB_recovery_file_dest optional'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_10" not allowed with SPFILE


SQL> alter system set log_archive_dest_10='USE_DB_RECOVERY_FILE_DEST';
alter system set log_archive_dest_10='USE_DB_RECOVERY_FILE_DEST'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_10" not allowed with SPFILE


When I put alter system set log_archive_dest_10=''; doesnt it disable archiving in the FRA?In this case will the flashback logs still be generated?On what conditions will the flashback logs generated like commit etc?

[Updated on: Tue, 17 August 2010 02:42]

Report message to a moderator

Re: Flash Recovery Area [message #471745 is a reply to message #471743] Tue, 17 August 2010 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-16179: incremental changes to \"%s\" not allowed with SPFILE
 *Cause:  Incremental changes to a log_archive_dest_n parameter cannot
          be made when using an SPFILE.
 *Action: Specify either LOCATION or SERVICE plus all other attributes
          to be set in one ALTER SYSTEM/SESSION SET command.

So 'LOCATION=USE_DB_RECOVERY_FILE_DEST ...'

Regards
Michel
Re: Flash Recovery Area [message #471747 is a reply to message #471745] Tue, 17 August 2010 02:43 Go to previous messageGo to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
When I put alter system set log_archive_dest_10=''; doesnt it disable archiving in the FRA?In this case will the flashback logs still be generated?On what conditions will the flashback logs generated like commit etc?
Re: Flash Recovery Area [message #471748 is a reply to message #471747] Tue, 17 August 2010 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In this case will the flashback logs still be generated?

Yes, I think so, but you can just check it.

Quote:
what conditions will the flashback logs generated like commit etc?

I don't understand the question, please precise.

Regards
Michel
Re: Flash Recovery Area [message #471751 is a reply to message #471748] Tue, 17 August 2010 02:50 Go to previous messageGo to next message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
On what conditions will the flashback logs be generated like archive log files are created when online log files are full or on alter system switch logfile.Similarly when are the flashback logfiles created??

unlike archive logfiles in FRA,are the flashback logfiles cleared automatically when they become obselete(Based on retention set) even if we dont use RMAN?
Re: Flash Recovery Area [message #471767 is a reply to message #471751] Tue, 17 August 2010 04:31 Go to previous message
sekar52
Messages: 141
Registered: May 2010
Location: Mumbai
Senior Member
unlike archive logfiles in FRA,are the flashback logfiles cleared automatically when they become obsolete(Based on retention period set) even if we dont use RMAN?
Previous Topic: Incremental Backup
Next Topic: Facing Deadlock error while restore using TSPITR
Goto Forum:
  


Current Time: Fri Mar 29 11:01:29 CDT 2024