How to restore operational Oracle 11g Database from full backup


Imagine that things gone bad and all we have is a fresh Windows Server and full cold backup of our Oracle database.

In case of SQL Server 2000/2005/2008/2012 it was really a matter of few mouse clicks: copy backup files, install db instance, restore files with few clicks, recreate users and we are good to go.

But now we are setting up Oracle 11g DB Standard as our database solution and from what I've seen over Internet it's a really complicated issue up to the point where this cold backup files might become completely useless without "actions" taken outside "usual" backup procedure.

While looking for answers, I've ran across certain topics like "db duplication form backup without access to flash recovery area" and etc which looks like to be close to what we were doing in SQL Server. Problem is all this things are far outside of my own experience (SQL Server, remember?) so I can't even tell if I'm looking in a right direction.

So my question is (I'll split it in two, because I think it matters in Oracle):

1) What should I do in order to properly "backup" my production database and restore it lately on different machine in case of emergency if we have not got yet any emergency instance installed?

2) What should I do in order to clone my production database and restore it on prepared instance (to clone production db).

What's most important: is there a common approach to both this tasks like in SQL Server?

Best Answer

In short - yes, there are standard operating procedures for doing all of this with Oracle. You should start by looking into RMAN (Recovery MANager). I have put together a high level overview of RMAN as well as an introduction to Oracle backups for SQL Server DBAs. I suggest watching both of those and then heading over to the Oracle Database Backup and Recovery User's Guide to learn more about the options available to you.

Basically, though - RMAN can perform all of these tasks for you. If you need additional tooling, RMAN + Oracle Enterprise Manager can provide you with automation, cloning, and other reporting capabilities. Oracle Enterprise Manager Database Control (dbcontrol) will also be very helpful, it will give you additional web-based GUI tools.

To get to a point where you can get to restoring from a bare bones Windows server and cold backup file is going to take significant training. You need to work with the business to define effective RPO/RTO plans, budget training for the future, and then do what you can right now to mitigate the risks before you're stuck with an empty server.

Improving the original answer slightly...

  1. You should start using Oracle Recovery Manager (RMAN) to back up your databases. Depending on how you configure RMAN, you may be sending this to NFS or to a set of disks on a different SAN. The way you configure this has a lot to do with your RPO/RTO policies. If you need to send this to a cool standby server, you should look into Oracle Data Guard. Depending on the configuration, this may take the place of either log shipping or an AlwaysOn Availability Group.
  2. You can clone a database using RMAN, just make sure you have everything in place and you practice it.