1

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?

migrated from serverfault.com Sep 3 '14 at 10:46

This question came from our site for system and network administrators.

  • 2
    I have never worked with O11g but if it is as difficult as you believe then surely trying to do it without suitable education is a foolish thing to do surely? Either attend the relevant Oracle classes or hire someone with suitable experience to do it and document if fore you.You know it makes sense. – user2501 Sep 3 '14 at 6:09
  • That's not an option: we are not getting education till 2015 (budget issues) and can't get specialist for same reasons. DB maintenance is on me alone and I've been looking for advice here hoping someone experienced would help me sort things out. – user2838376 Sep 3 '14 at 6:20
  • 2
    I suggest a migration to dba SE. – peterh Sep 3 '14 at 6:25
  • 1
    That's not a good position to be in. You really need to discuss this with your management and explain to them that the task is too complex for you without suitable education and it is likely to put the business data at risk without their taking action to educate you. – user2501 Sep 3 '14 at 6:25
  • There is a graphical tool called Oracle Enterprise Manager Database Control. This could be used for managing the database including backup and restore. I have no expierience with backup/restore using Database Control but this is the tool Oracle delivers with its database to facilitate user the managing of their databases. There are no additional licenses necessary for using it for backup/restore. – miracle173 Sep 4 '14 at 0:36
4

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

If your "full cold backup" is really a full backup of all database files while the database is not running, you only have to copy them back to the original place. The files needed are the control files, online redo logs, init.ora or spfile, database files, password file and so on. Some of them are located in $ORACLE_HOME/dbs, some are in other locations (depending on OS), the rest is where you created them :-)

The most simple cold backup is:

  • Shutdown database and listener
  • Create a full filesystem backup of the whole machine including ALL(!) filesystems with any database files
  • Startup database and listener

To restore:

  • copy ALL the database files back.
  • If you lost $ORACLE_HOME restore it.
  • Restore all other oracle files you need on windows.

This should be working for a REAL COLD backup.

If you need a better way: use RMAN

With RMAN you can do all the cool stuff like online and incremental backups, you can clone your database, have blockchecking while doing backups and so on.

As you do not get education before 2015 (buying Oracle without education is near to suicide!) have a look at the "2 Day DBA" book (http://docs.oracle.com/cd/E11882_01/index.htm).

Cloning database has to wait until you realy know backup and restore with RMAN.

Remember: TEST your restores!

  • You see, the thing I call full cold backup actually IS RMAN backup made through Oracle Enterprise Manager. It is a set of three files: (1) Datafile backup, (2) Controlfile+SPFile, (3) Control File only. I Actually was able to restore Oracle database from it using command-line RMAN, but now facing different problem: enterprise manager dies after such restore – user2838376 Sep 23 '14 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.