I am a SQL Server DBA and recently been given responsibility for an Oracle environment. (11g)
Now i do timed backups of my transaction logs on SQL Server and then migrate them to a different server. I would like to do this with Oracle as well.
Oracle's redo logs appear to be size based before they switch and allow the server to archive them.
Can someone provide some tips / best practices on how to achieve this.
I would also appreciate any advice on sizing the redo logs and how to handle changes in usage patterns during the day.
I forgot to say this is on a Redhat 5 Enterprise server
Best Answer
First suggestion is to never mess with them manually unless you really know what you're doing.
What you want to do is read up on
rman
, the Oracle tool for performing backups (including redo log backups). I would highly recommend researching this pretty thoroughly to make sure you fully understand how the various aspects of Oracle backups work before you take any actions.Now, generally the Oracle redo/archive logs should remain where they're written until after you've performed a backup. The backup will usually be setup to include the archive logs that are required along with the database backup.
As for the size of redo logs, it's going to be based directly on the volume of database change transactions. The more changes, the greater the log volume. This will be highly individual to your applications and usage, so I'd recommend you start recording statistics on it (transaction volume, transaction log size, database size, etc., all with timestamps). Once you've got a few weeks worth of data you can start correlating activity with logs and distill it to some valid estimates.
Edit: I think I partially misunderstood the original question. I think you're asking for a way to basically checkpoint your redo logs to right now when you run a backup, so that you are fully consistent with your backup right up to when you issued the backup start.
And, rman will actually handle all that dirty work for you. From some rman documentation:
Another little bit that provides a little more detail:
The rman documentation should be able to provide more details for you. I would give the URL I pulled that from (online Oracle rman docs) but the URL has already changed from when I bookmarked it, so I don't trust it to stay around. Googling for rman docs should be able to find it, though.
Edit: One more thing I wanted to add. . . you mentioned something about sizing. Oracle 11g does support compressed redo logs. I haven't used them myself, but I know it supports them. Additionally, Oracle 10g and 11g support compressed backups. If you aren't doing compressed backups yet, you should be. The size reduction is huge, and on top of that, we got a significant performance increase in backup runs, too, when we enabled compressed backups.