Home » RDBMS Server » Backup & Recovery » Configuring retention policy not for archive logs (Oracle 10g)
Configuring retention policy not for archive logs [message #403440] Fri, 15 May 2009 11:40 Go to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
I am configuring rman to disk backup and since I have to make room for future backup I want to delete the old backups. Since retention redundancy policy also mark archive logs as obsolete, is there any way that I can set redundacy only for database backup but not for archivelogs?

Re: Configuring retention policy not for archive logs [message #403441 is a reply to message #403440] Fri, 15 May 2009 11:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> but not for archivelogs?
Could you explain why would like to keep just archives?
I would rather tag archives along with backup of datafiles and delete all archives after backup.
This way for restore, I can just bring everything back.

Look into keep options.
Not sure it would help you
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta035.htm#i97063

Quote:

LOGS Specifies that all of the archived logs required to recover this backup or copy must remain available as long as this backup or copy is available.
Re: Configuring retention policy not for archive logs [message #403442 is a reply to message #403441] Fri, 15 May 2009 12:05 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
Here is what I want to setup:

1 full backups every other day of the week and daily archive logs backup to disk and then to tape

Based on the policy of having atleast 5 old archive logs I will be deleting these logs by saying "delete noprompt archivelog until time 'sysdate - 5' backed up 2 times"

This will take care of archive logs but I want to make space for database backup and trying to find a way to automate deletion of database backup only.

Keep option cannot be used for archive logs

[Updated on: Fri, 15 May 2009 12:10]

Report message to a moderator

Re: Configuring retention policy not for archive logs [message #403443 is a reply to message #403442] Fri, 15 May 2009 12:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Then you have to rely on manual methods to delete backups.
***Not so recommended***.

Write script that would list
the backupsets to be made unavailable.

Something like this (spool the output and run separately against catalog).
select 'change backupset '||bs_key||' unavailable;' 
from rc_backup_set a, rc_database b 
where   a.db_key=b.db_key and 
        status = 'A'      and 
        trunc(completion_time) < trunc(SYSDATE-yourNumberOfDays);


Use OS methods to delete the files.
Something like
find . -mtime +yourNumberOfdays -name "*.bak" -exec /bin/rm {} \;



Warning: Untested code.

[Updated on: Fri, 15 May 2009 12:21]

Report message to a moderator

Re: Configuring retention policy not for archive logs [message #403444 is a reply to message #403443] Fri, 15 May 2009 12:29 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
Thanks for the quick reply but I am not using the catalog and since I will be backing them to tape and deleting it I would also want to have their records in the control file for restore.

Is there any way that I can query the oldest backup in rman and delete all those backup pieces at OS level?
Re: Configuring retention policy not for archive logs [message #403445 is a reply to message #403444] Fri, 15 May 2009 12:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You are not supposed to just delete the datafiles.

whether you are using catalog or not,
you can just mark them UNAVAILABLE and then delete them.
During restore, mark them AVAILABLE again and restore.


>>Is there any way that I can query the oldest backup in rman and delete all those backup pieces at OS level?
List command should work, but will be hard to script.
There are views in database that will provide you the same information and I just do not remember on top of my head.
Re: Configuring retention policy not for archive logs [message #403446 is a reply to message #403440] Fri, 15 May 2009 12:52 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
I see what you mean. Let me ask you this - Is there a better way to do disk backup and then copying them to tape, maintaining the disk space at the same time? What do you recommend?
Re: Configuring retention policy not for archive logs [message #403448 is a reply to message #403446] Fri, 15 May 2009 13:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
IMHO,
Disk is very very cheap in $$ and I would never worry about that when it comes to backups.
If no local disks are available, look into NFS options. You can push the backups to a remote drive.
If you are still constrained,
you can compress the backups and save a little (but it is CPU intensive. Nothing is free Smile).


Re: Configuring retention policy not for archive logs [message #403450 is a reply to message #403440] Fri, 15 May 2009 13:13 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
I am using NFS mount, I have 2 TB of disk space and I am using the compress option, this is not an issue. the issue is deleting the old disk backup and performing new backup. How can I achive that better?
Re: Configuring retention policy not for archive logs [message #403451 is a reply to message #403440] Fri, 15 May 2009 13:17 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
> How can I achive that better?
Better based upon which metric?
You can have it good, fast, or cheap. Pick any two.
Re: Configuring retention policy not for archive logs [message #403454 is a reply to message #403450] Fri, 15 May 2009 13:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You will to define "better" then.
We discussed the options already.
If you want a custom retention policy, you have to delete it manually. You can easily script the process and schedule them.
I do not understand what the issue is here.
Re: Configuring retention policy not for archive logs [message #403456 is a reply to message #403440] Fri, 15 May 2009 13:30 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
Sorry if I am confusing you guys.

Let me start over:

We were having tape backups before and now we want to implement disk backup and tape backup both.
Our current backup policy is
Full backup plus archivelog every Mon, Wed and Fri
Daily archive logs backup every 6 hrs

Now, considering the same for disk backup, how can I achieve this in a better way keeping in mind that I have to delete the old backup to make room for next backup
Re: Configuring retention policy not for archive logs [message #403460 is a reply to message #403456] Fri, 15 May 2009 13:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>now we want to implement disk backup and tape backup both
By that, my understanding is
1). Before you were doing tape only backup.
2). You want to do a direct tape backup.
3). You would also want disk backup.

Just backup to disk every day and let the tape libraries pick
the RMAN backupsets.
Have sufficient window between the two.
Maintain disk retentions (manually, through scheduled jobs).
Maintain Tape retentions/de-dupes using the vendor specific methods.
Re: Configuring retention policy not for archive logs [message #403463 is a reply to message #403440] Fri, 15 May 2009 13:58 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
Thats what I am trying to do. How can I maintain disk backups and their retentions manually?
Re: Configuring retention policy not for archive logs [message #403464 is a reply to message #403463] Fri, 15 May 2009 14:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>How can I maintain disk backups and their retentions manually?
Already talked about it in my second post.
Use v$backup_files instead of rc_backup_set.
No need of rc_database.


http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1.htm#REFRN30022

Test it rigorously and use it at your own risk.
I do not have an environment with nocatalog option to test validity of using v$backup_files.
Re: Configuring retention policy not for archive logs [message #403476 is a reply to message #403440] Fri, 15 May 2009 15:04 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
Let me ask you this - Do the rman maintains its backup records in control file or v$backup_file or both?
What is the difference?
Re: Configuring retention policy not for archive logs [message #403492 is a reply to message #403476] Fri, 15 May 2009 17:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
rman stores info in control file.
v$backup_file is a view that will display the information from control file.
V$BACKUP_FILES is same as RC_BACKUP_FILES (in catalog).


Re: Configuring retention policy not for archive logs [message #403744 is a reply to message #403440] Mon, 18 May 2009 07:31 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
Thanks, one more doubt, what is the use of making the backup set unavailable from v$backup_file. Why do we want to do that?
Re: Configuring retention policy not for archive logs [message #403751 is a reply to message #403744] Mon, 18 May 2009 08:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Because you are not preferring to use default RMAN retention mechanism.
http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10735/maint.htm#1007675
Re: Configuring retention policy not for archive logs [message #403895 is a reply to message #403440] Tue, 19 May 2009 07:07 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
One more question - Full database and Archive logs backup creates 5 backup pieces each in separate backupset. As I will be deleting the files from the OS, is there any way that I can cross reference rman that I will be deleting only the old backup files.
For e.g full backups created 5 backup pieces:-
bckuppiece1
bckuppiece2
bckuppiece3
bckuppiece4
bckuppiece5

Now since bckuppiece1 is the oldest one how can I reference rman the rest of the backuppieces holding that full backup?
Re: Configuring retention policy not for archive logs [message #403943 is a reply to message #403895] Tue, 19 May 2009 12:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>s I will be deleting the files from the OS, is there any way that I can cross reference rman that I will be deleting only the old backup files.

Already answered.
Look into the simple sql script.
You can get all information based on completion_time.
>>Now since bckuppiece1 is the oldest one how can I reference rman the rest of the backuppieces holding that full backup?

Hope you are not looking to delete a specific backuppiece within a particular backupset.
I would just drop all backuppieces that will belong to a backupset on a given day.

Re: Configuring retention policy not for archive logs [message #403950 is a reply to message #403943] Tue, 19 May 2009 15:41 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
Thats the problem, I am having every backupset is unique for each backuppiece. It would be lot easier if those backuppieces belong to only 1 backupset.

Is there a way that I can set to have all the backuppieces for a particular full backup to be in 1 backupset?
Re: Configuring retention policy not for archive logs [message #403951 is a reply to message #403950] Tue, 19 May 2009 15:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I see no problem here.
You are just going to delete the files that were backed up N days before. An example is already been given.
It does not matter how many files you have per backupset.

>>Is there a way that I can set to have all the backuppieces for a particular full backup to be in 1 backupset?
use filesperset.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmconc1.htm#458507

It is not going to change anything for you.
Infact, 1 file per backupset is better unless you worry too much about network roundtrips.
Re: Configuring retention policy not for archive logs [message #404108 is a reply to message #403951] Wed, 20 May 2009 07:10 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
Deleting files before N days doesnt guarantee that it will delete only those files. What if n days ago backup didnt completed and its been backed up next day? what if backup runs before and after midnight then we will have 2 dates for 1 complete backup. So there must be a way that we can cross reference rman to delete only those backuppieces which are the oldest and belong to one full backup only
Re: Configuring retention policy not for archive logs [message #404111 is a reply to message #404108] Wed, 20 May 2009 07:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>So there must be a way that we can cross reference rman to delete only those backuppieces which are the oldest and belong to one full backup only
Already explained.
V$backup_file will give you the list of files backed up based on completion_time and bs_key. crosscheck with v$backup_set and v$backup_set_details.
>> What if n days ago backup didnt completed and its been backed up next day?
As said above (many times),
you have to generate the list of files to deleted based on v$views .
If you are talking about pushing the backupsets to tape within a window,
You have to take care of it.
Provide enough window for tape backups.

[Updated on: Wed, 20 May 2009 07:47]

Report message to a moderator

Re: Configuring retention policy not for archive logs [message #404129 is a reply to message #404111] Wed, 20 May 2009 09:06 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
Could you give me an example where you query views not based on date or n number of days but based on the oldest backup taken and get the list of only those backuppieces for that full backup (Note: Each backuppiece is contained in unique backupset with unique bs_key)

V$backup_file will give you the list of files backed up based on completion_time and bs_key. crosscheck with v$backup_set and v$backup_set_details

>>Considering the example that i gave earlier 1 full backup creates 3 backuppieces on the OS with unique backupset and bs_key. Could you give me an example how i can crossceck with v$backup_set and v$backup_set_details
Re: Configuring retention policy not for archive logs [message #404134 is a reply to message #403440] Wed, 20 May 2009 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
Do you see the spot on the road?
That is where the horse died which had been beaten to death.
Since the carcass has been removed, perhaps we should just move on.
Re: Configuring retention policy not for archive logs [message #404139 is a reply to message #404134] Wed, 20 May 2009 09:29 Go to previous messageGo to next message
raj12
Messages: 15
Registered: May 2009
Junior Member
Do you see the spot on the road?
That is where the horse died which had been beaten to death.
Since the carcass has been removed, perhaps we should just move on.

>> Do you have anything else to do beside commenting like this. if you dont have any technical knowledge why dont you just shut up and do your own business.

Do you even understand what we are talking about? lol...

I appreciate Mahesh's help but I am not forcing him to answer all my queries. If you dont want to answer why bother. Move on.. someone else may have the answer or better understanding .. thats what we have forums for...

[Updated on: Wed, 20 May 2009 09:34]

Report message to a moderator

Re: Configuring retention policy not for archive logs [message #404143 is a reply to message #404129] Wed, 20 May 2009 09:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
This is going nowhere.

>>where you query views not based on date or n number of days but based on the oldest backup taken

Define oldest.
Conventionally, oldest is based on date.

As said before, I do not have a nocatalog env with a hybrid retention like this. I will have to set one and will take quite a while to crosscheck everything. Cannot afford right now.

All information you need is in v$views.
Good luck with trying to re-invent the wheel.


All this is because you are trying to use RMAN retention for archives and manual retention for datafiles.

Regards
Re: Configuring retention policy not for archive logs [message #404144 is a reply to message #404143] Wed, 20 May 2009 10:10 Go to previous message
raj12
Messages: 15
Registered: May 2009
Junior Member
As I said I appreciate your help Mahesh

Thank you very much.

Regards
Previous Topic: Retention Policy
Next Topic: media recovery
Goto Forum:
  


Current Time: Wed Dec 07 14:19:28 CST 2016

Total time taken to generate the page: 0.11183 seconds