How to purge logs by using Flashback feature in Oracle Datab

All posts relating to Oracle database administration.

Moderator: Tim...

How to purge logs by using Flashback feature in Oracle Datab

Postby ssadoglu » Sat Apr 27, 2013 4:33 pm

Hi Tim,

I’ve configured flash recovery area for Oracle Database. My question is: how can I delete Flashback Database log?or is there any retention policy for that?

2- After I’ve enabled archive log mode and flash recovery area ,does purging automatically archived logs which are located archive log mode folder(not FRA logs)?or do I have to backup them via RMAN?

Thanks,
ssadoglu
Senior Member
 
Posts: 58
Joined: Wed Feb 02, 2011 10:59 am

Re: How to purge logs by using Flashback feature in Oracle D

Postby Tim... » Sat Apr 27, 2013 9:09 pm

Hi.

The fast recovery area is self managing. Flashback logs will be kept as long as possible, but once the FRA is full, the contents will be managed to make room for new logs.

Archived redo logs outside the FRA must be managed manually. You can do this using a delete within rman, delete then manually and do a crosscheck, or delete as part of an archivelog backup using DELETE INPUT.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: How to purge logs by using Flashback feature in Oracle D

Postby ssadoglu » Sat Apr 27, 2013 9:47 pm

First of all, thanks for your answer.But I have some questions which are related on your mentioned above.
1- What do you mean by 'Flashback logs will be kept as long as possible'? Because I've configured as 3GB size of fast recovery area. So is there any specific time? if so how can I set that?
2- Can you please give me exactly all commands which are needed to delete archived logs ?

Thanks in advance.
ssadoglu
Senior Member
 
Posts: 58
Joined: Wed Feb 02, 2011 10:59 am

Re: How to purge logs by using Flashback feature in Oracle D

Postby Tim... » Sun Apr 28, 2013 8:23 am

Hi.

1) I can't give you an exact time because it depends on the workload of your database. When your database processes DML is generates redo and therefore archived redo logs and flashback logs. If your database does very little DML, then a 3G FRA will allow you to store flashback logs for a long time. If your database is generating massive amounts of redo, then 3G might only last a few hours. This might help:

http://docs.oracle.com/cd/B19306_01/bac ... m#BABJJCHF

2)

a) If you have a recovery window set, you can just do:

Code: Select all
RMAN> DELETE OBSOLETE;


b) If you want to manually delete files you can do something like this:

Code: Select all
RMAN> DELETE ARCHIVELOG UNTIL TIME ‘SYSDATE-7′;


c) If you want to delete archived logs as soon as they are backed up you can do this:

Code: Select all
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;


d) If you want to manually delete the files from the OS (UNIX, Linux, Windows), once you've done the delete you need to run this to remove them from the catalog:

Code: Select all
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE OBSOLETE;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: How to purge logs by using Flashback feature in Oracle D

Postby ssadoglu » Sun Apr 28, 2013 9:31 am

Thanks so much for your reply. I have a question too.

Let's say I've been performing a hot backup with RMAN. As I mentioned above I've been FRA at the same time. My question is : How can I delete obsolete autobackup and Backup Sets from the FRA? or is there any retention policy which can be deleted as automatically obsolete backup sets and autobackup? if so , can you give me the commands?

Thanks & Regards,
ssadoglu
Senior Member
 
Posts: 58
Joined: Wed Feb 02, 2011 10:59 am

Re: How to purge logs by using Flashback feature in Oracle D

Postby Tim... » Sun Apr 28, 2013 12:04 pm

Hi.

I've already told you. If you have a retention policy you use 'delete obsolete' to clean up any old stuff, including archived redo logs, and backups.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: How to purge logs by using Flashback feature in Oracle D

Postby ssadoglu » Mon Apr 29, 2013 10:23 am

Hi ,

thanks you so much for your all help.
ssadoglu
Senior Member
 
Posts: 58
Joined: Wed Feb 02, 2011 10:59 am

Re: How to purge logs by using Flashback feature in Oracle D

Postby Tim... » Mon Apr 29, 2013 11:14 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest