0

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

5 Answers 5

3

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
  • Thanks Christopher, i didn't intend to keep change them during the day. What i was looking for was a method of limiting the time between what i would refer to as a transaction log backup to a set period of about 15 minutes. One thought i did have was to to issue a switch log file wait for a minute and then run an RMAN backup to archive them. However this does seem to be a rather clunky aproach and i just wanted to know if anyone else had found a solution.
    – OilyRag
    Jun 5, 2009 at 19:35
  • Ahhhh! I see what you're asking. I misunderstood the original question. I'm editing my answer above to address what I think you're asking. Jun 6, 2009 at 1:28
  • Oracle 11G documentation is available at oracle.com/pls/db112/homepage
    – Benoit
    Nov 24, 2010 at 10:28
1

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
  • Db-eh, thanks for that, i'll look into it
    – OilyRag
    Oct 10, 2010 at 14:05
0

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

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

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .