I have an environment in which backup is performed of Oracle 10 databases with the use of RMAN and Tivoli Storage Manager (Data Protection for Oracle).
There are several databases and for every one there is a daily full backup and hourly archive logs backup.
Sometimes when full db backup takes longer (up to 4 hours) archive logs backups are missed - as TSM node cannot perform two backups at a time. I would like not to have those missed backups.
Option A was to delete association of the arch log scheduler during full backup. But when removing association we lose historical data about backup. And we need historical data to be able to create weekly / monthly / quarterly statistics of completed backups. We need to have 99% completed.
Option B was to create two nodes in TSM (TDPO) and one will do full backup only and another one only arch logs backup. So the problem is moved to RMAN. But from RMAN specialist I heard that this may cause problems with full backup. During full backup also archive logs are backed up (at the start and end) so there might be a problem with accessing the file that is used by another process. And this may cause problem with full backup - which we want to avoid especially.
Do you find this reasoning correct? Do you have suggestions how this should be done properly?
Your help will be appreciated.
>Option A was to delete association of the arch log scheduler during full backup. But when removing association we lose historical data about backup.
I don't understand what this means. What "association" would be deleted. As long as you use RMAN to backup database files and archivelog files, it is RMAN that tracks the backups and can identify the archivelogs that are require for a RECOVERy of a database backup.
>During full backup also archive logs are backed up (at the start and end)
So your full backup "script" also includes BACKUP ARCHIVELOG commands. Does it DELETE INPUT ? If not, the separate Archivelog backup "script" will also be backing up the same archivelogs.
>so there might be a problem with accessing the file that is used by another process
I'm not sure about this. But I can't test it. How about testing it out ? Run two backup scripts concurrently.
Hemant K Chitale
First why do you need to have archivelog backup running every hour? When the full backup runs it will baxkup the archivelogs if specified so during that full backup you should not need a archivelog backup. If you are backing up the archivelogs hourly because of storage you should get more storage. If it part of your recovery strategy you many need to rethink the strategy.
Note if you try to run run an archivelog and full backup at the same time you will get some errors that archivelog no longer exists of controlfile errors.
Schedule your backups according to the full backup time.
Do a test of full backup and fine the time that it takes to complete the full backup. You can include "plus archivelog" if you want your archive logs to be backed up along with the full backup. This is most preferred as this makes restoration easy too.
If your full backup takes, say, 4 hrs to complete and if you schedule your full backup at 00 00 hrs on your scheduler, then schedule the archive backups after 04 00 hrs. This will solve your problems with missing archive backups.
>> "But from RMAN specialist I heard that this may cause problems with full backup. During full backup also archive logs are backed up (at the start and end) so there might be a problem with accessing the file that is used by another process. And this may cause problem with full backup - which we want to avoid especially."
This will throw out errors if your input to full backup consists "delete input" for your archived logs, else the process will not have any problem and succeed.
Also when one or more of backups run concurrently, "ORA-00230: operation disallowed: snapshot control file enqueue unavailable" error may occur but this is rare case.
Sounds like you have an SLA of 1 hour have you tested your recovery to see if it will b e able to recover in 1 hour. Backing up the archivelogs every hour does not mean your recovery will take one hour. You save your full backup takes 6 hours lets say you tune the system well the recover could take about 3 hours but what if your database crashes 5 minutes before the next full backup? This means the database would require to restore all the archive logs you were backing up ever hour and then do the restore.
If you have an SLA of 1 hour you should look at a standby solution. You should also make sure you are following best practice and have the archivelogs on separate LUNs/disk from all other files.