Posted by Alisher Yuldashev on Feb 10, 2010
There are a lot of discussions going on in the Internet regarding whether we can trust third parties to look after our data. I am not going to add fuel to the fire. I am going to show you how simple it is to backup an Oracle database to the Storage Cloud using the Oracle Secure Backup (OSB) Cloud Module.
First, you need to sign up for an Amazon S3 account and get an Access Key ID and a Secret Access Key. Check AWS pricing first.
Next, download the OSB Cloud Module and unzip it. Read the rest of this entry . . .
Posted by Sheeri Cabral on Oct 1, 2009
Firstly, I have to thank my co-workers Singer Wang and Gerry Narvaja for doing a lot of the work that resulted in this comparison.
After running both InnoDB Hot Backup and Xtrabackup, we have found that there is a measurable but not large difference between the resources that Xtrabackup and InnoDB Hot Backup consume.
Xtrabackup:
Posted by André Araujo on Sep 2, 2009
Have you ever been asked to restore a database and wondered which backup files were available? And if many were available, which ones you should use, and in which order?
Getting familiar with the msdb schema, especially the backupset and backupmediafamily tables, helps to answer that question since all the backup history is stored in that database. But you would still have to check if the backups are still available on disk (tape?) and figure out what are the commands to restore, and the order in which to restore each file.
Okay, this is not a difficult thing to do, but when you do it over and over again, it becomes tedious, and the automation bug in you starts to look for a better and quicker way to handle it. The bug in me found the following answer.
Read the rest of this entry . . .
Posted by Sheeri Cabral on Jun 30, 2009
A short time ago I posted how I was Using XtraDB Backup to backup InnoDB. Overall, the blog post was positive, but experiences that others have had (and commented to on that blog post) have made me want to put out another short article about using XtraDB backup.
The first few points remain the same — the backup process is stable, we were able to use the binaries without compiling, and using Innobackupex as the wrapper script, analogous to Innobackup.pl.
However, we did figure out why Xtrabackup had to be run as the mysql user:
Xtrabackup writes to the data dictionary file (ibdata1, for example). We have not examined if it also writes to the data and index files (still ibdata1 by default, or the .ibd files when using innodb_file_per_table). [EDIT: The authors of Xtrabackup have commented below as to why the write occurs:
xtrabackup is kind of very small and restricted instance of InnoDB, and reuses a lot of InnoDB code.
InnoDB by default requires O_RDWR option on ibdata1 files at start, and xtrabackup therefore also did that. In the latest push to trunk it was fixed, now xtrabackup opens file with O_RDONLY flag.
When the new version is released, we will be sure to test it so that we can run the backup as a read-only user, and report back.]
On the one hand, Xtrabackup is a free tool. On the other hand, modifying InnoDB’s underlying files risks corrupting all the InnoDB tables in the system. Which is a tricky situation when it is your backup tool that might cause corruption that is beyond repair, as you do not know if you can trust your backups.
Read the rest of this entry . . .
Posted by Sheeri Cabral on Jun 12, 2009
This is the 150th edition of Log Buffer, the weekly review of database blogs. Someone accidentally left Dave Edwards’ cage unlocked, and he escaped, thus leaving me with the pleasurable duty of compiling the 150th weekly Log Buffer.
Many people other than Dave are finding release this week. Read the rest of this entry . . .
Posted by Sheeri Cabral on Jun 9, 2009
As Baron points out, Percona’s Xtrabackup tool can be used just like InnoDB Hot Backup.
Many are wondering, “is it good enough?” In fact, I wondered the same thing, and after a few weeks of using and testing Xtrabackup (on machines that have MyISAM and InnoDB tables), I can say:
0) We have not run into any problems with backing up InnoDB tables; on the machines we’ve run it on (RHEL 5.3 and Debian) it has been stable.
1) We were able to use one of the binaries provided by Percona — we did not have to compile anything. Binaries are provided for 64-bit versions of Linux
2) Innobackupex is the script analogous to Innobackup. Basically, this is a wrapper script for Xtrabackup that will copy the non-InnoDB tables.
3) Usually we run InnoDB Hot Backup from a non-privileged user. We had to run Xtrabackup as the mysql user; we think this is because it writes a state file in the datadir, but we did not dig too hard — once we saw that running it from the mysql user worked, we just left it like that.
4) Xtrabackup does not have a –compress option like InnoDB hot backup does. Having the option would be useful, but I believe this option will come soon.
Currently, for clients who want a hot backup solution but do not want to pay for InnoDB Hot Backup, Pythian recommends Xtrabackup.
Posted by Alex Gorbachev on May 13, 2009
Please feel free to submit the backup and recovery and DR topics you want to discuss – just mention in the comments and I’ll make sure they are on the agenda!
What: Sydney Oracle Meetup #4 – Backups, Recovery and Disasters
When: May 15, 2009 5:30 PM
Where: Our usual location in CBD see even page for details.
Meetup Description:
SOM #4 is about Oracle backup and recovery. As usual, Pizza and drinks arrive at 5:30PM and we start the presentation at 6PM. We should be out by 8:30PM with optional post-event program. It’s Friday night in the end! Read the rest of this entry . . .
Posted by Don Seiler on Mar 4, 2009
The story you are about to read is based on actual events. Names and paths have been changed to protect the innocent. I call this scenario “The Perfect Storm” because it took just the right combination of events and configurations. Sadly, this doesn’t make it an unlikely occurrence, so I’m posting it here in hopes that you’ll be able to save yourselves before it’s too late.
I have always had a preternatural dislike for using REDUNDANCY as a retention policy for Oracle RMAN, greatly preferring RECOVERY WINDOW instead, simply because REDUNDANCY doesn’t really guarantee anything valuable to me, whereas RECOVERY WINDOW guarantees that I’ll be able to do a point-in-time recovery to anytime within the past x days. Plus, I had already been burned once by a different client using REDUNDANCY. With the story I’m about to tell, this dislike has turned into violent hatred. I’m going to be light on the technical details, but I hope you’ll still feel the full pain.
Read the rest of this entry . . .
Posted by Chris Presley on Jan 12, 2009
Let’s say for you want to move a database from one environment (possibly production) to another (possibly development) on a semi-regular basis. After about the 10th time doing it manually, you’re ready to automate the process. But how?
That’s what I intend to help with in this post. For this example, I’m running two Virtual servers, both running Windows Server 2003 and SQL Server 2005 SP2. We’ll use the AdventureWorks OLTP database, available for download from http://www.codeplex.com/SqlServerSamples. I’m running SQL Server and SQL Agents with local users that have permissions on both machines. This should work for SQL Server 2005 or 2008, and the Windows version level isn’t important.
There are a couple ways you could go about this task. One way would be to write stored procedures and call them with a batch file using osql.exe and SQL Agent job or your enterprise scheduler. There’s nothing wrong with that approach, but I think that for this task, SSIS packages give more options, flexibility, and simplicity for ongoing management.
At a high level, the SSIS package we are about to create will do the following:
Read the rest of this entry . . .
