Home / server / Questions / 19944
Accepted
OilyRag
Asked: 2009-06-05 10:37:08 +0800 CST

archive log backups in oracle

  • 772

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

oracle-11g

5 Answers

  1. 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.

    • 3

  2. Oracle's redo logs appear to be size based before they switch and allow the server to archive them.

    Look at the parameter archive_lag_target for Oracle 9i and above.

    • 1

  3. It's a tradeoff. I've heard that big log files are more efficient, but we don't use them because A) the bigger the loss of a file, the bigger the loss of your data and B) our bandwidth is crap

    This leads to us spooling files for a few hours then transferring and running updates on the various standby machines. We're actually using Oracle8i still, and because the database was designed a long time ago by someone who wasn't a DBA, I still have to manually create new datafiles and control files. /sigh

    • 0

  4. I will show you how to do migration from sql server to oracle after that you can move data. For the migration, you need to program SQL Developer with JDK1.5 to move data from Ms sql to oracle. If you want to know clear, please read help of SQL Developer.

    For the backup database, I advise you to read rman command in oracle and you will know clearly how to backup and restore.

    You can see this website for download and read help for migration. And You can download any documents related to oracle database.

    http://www.oracle.com/technology/products/database/sql_developer/index.html

    • 0

  5. It been a while since i asked this but i have recently found the answer. From amout Oracle 10G R2 onwards there has been a parameter that will ensure a log switch will take place no longer than the time specified.

    If you go alter system set archive_lag_target=900;

    this will ensure the switch occurs every 15 minutes, regardless of the amout of log used.

    • 0