Archive log backups in oracle

oracle-11g

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:

When taking a backup of archived redo logs that includes the most recent log (that is, a BACKUP ... ARCHIVELOG command is run without the UNTIL or SEQUENCE option) if the database is open, then before beginning the backup, RMAN will switch out of the current online redo log group, and all online redo logs that have not yet been archived, up to and including the redo log group that was current when the command was issued. This ensures that the backup contains all redo that was generated prior to the start of the command.

Another little bit that provides a little more detail:

You can add archived redo logs to a backup of other files by using the BACKUP ... PLUS ARCHIVELOG clause. Adding BACKUP ... PLUS ARCHIVELOG causes RMAN to do the following:

  1. Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command.
  2. Runs BACKUP ARCHIVELOG ALL. Note that if backup optimization is enabled, then RMAN skips logs that it has already backed up to the specified device.
  3. Backs up the rest of the files specified in BACKUP command.
  4. Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command.
  5. Backs up any remaining archived logs generated during the backup.

This guarantees that datafile backups taken during the command are recoverable to a consistent state.

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.

Related Topic