How to Recover Your Oracle 12c Database

By Chris Ruel, Michael Wessler

Many types of failures can befall your database. The Oracle 12c Recovery Manager (RMAN) is a tool that can help you get back on your feet after many of these failures. Sometimes it is the only option, sometimes it is the best approach of several, and sometimes it isn’t the right approach at all.

RMAN can really help with two types of failures:

  • Media failure: Loss of files

  • User error: Mistakes that lead to damaged databases or data

Whether RMAN can always help you when it comes to user error depends on what type of problem has been created. For example, if a user accidentally removes a file or a tablespace, RMAN can help very easily.

However, if a user accidentally drops a table or corrupts data, RMAN can help, but it might not be the quickest approach. If a user drops a table, it might be quicker to retrieve it from the database Recyclebin or Flashback Database.

However, if the user has purged the Recyclebin or the Flashback Database isn’t configured, Recovery Manager is your only choice.

RMAN can do two types of recoveries:

  • Complete: All files are brought back to the time the database failed. No data is lost.

  • Incomplete: The database is recovered but stopped short of a full recovery. There may be data loss. Sometimes this is what you want. For example, if a user drops a table at 10:13 a.m. sharp, you do an incomplete recovery to 10:12 a.m. to get the database back before the drop occurs.

Complete recovery is what usually happens. However, be prepared for anything.

How to verify the problem with your Oracle 12c database recovery

Finding out what went wrong with your database isn’t always an easy task. Sometimes you get lucky (if you want to use the term lucky in the face of a broken database). For example, maybe you know what happened:

  • A system administrator told you a disk croaked.

  • A user told you they dropped a table.

  • You caused the error and you know what happened and why.

These might not be the problem, however. Sometimes you’re presented with sneaky problems, in which case you take on the role of a detective. Say you start the database or access the data while the database is still open and get an error similar to this one:

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/dev12c/users01.dbf'

You look for the file in the location that it gives. Lo and behold, it is gone (or maybe the whole disk is gone). You had more than one file on that disk.

Why is Oracle telling you only that it can’t find one of your files? Because when you start the database, Oracle reads the data file list in the control file. As soon as it can’t find one in the list, it stops opening and presents the error.

Or, if the database is already open, Oracle tells you only about the error that you’re experiencing as a result of your specific action. Unfortunately, this is a little misleading; you might restore and recover the file only to find another error just like it for a different data file.

Incomplete recovery with your Oracle 12c database

Incomplete recovery is usually a very unfortunate position to be in. Typically, it means you will be losing data (hence incomplete). Also, the Data Recovery Advisor (DRA) cannot help at all in this situation. In an incomplete recovery scenario, the database has not actually failed. Someone has done something to put the database in a state that requires you to go back in time.

Here are the steps to incomplete recovery:

  1. Shut down the database.

  2. Start up the database in mount mode.

  3. Set the time for the restore to work from.

  4. Restore the database.

  5. Recover the database.

  6. Open the database with RESETLOGS.

    The control files won’t match the data files. You have to re-sync the control files with the data files.

  7. Open a prompt to your OS command line.

  8. Log in to your database with RMAN:

    <rman target /
  9. Put the database in mount mode:

    <shutdown immediate>
    <startup mount>
  10. Use the following RMAN command to recover your database to the appropriate time (11:44 a.m. in this case):

    RMAN> run {
    set until time =
    "to_date('30-JUN-2013:11:44:00','DD-MON-YYYY:HH24:MI:SS')";
    restore database;
    recover database;
    sql "alter database open resetlogs";
    }

    When the command completes, you should see something like this:

    executing command: SET until clause
    Starting restore at 30-JUN-2013 11:58:52
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=20 device type=DISK
    …output snipped…
    Finished recover at 30-JUN-2013 12:02:56
    sql statement: alter database open resetlogs
  11. Make sure the table you were trying to recover has indeed been recovered.

    There’s nothing more embarrassing than telling everybody you recovered data only to have someone else discover that the data still isn’t there. If you discover it isn’t there, do the recovery again, going back a little farther in time.