Perform a Complete Recovery of an Oracle 12c Database without the Database Recovery Advisor - dummies

Perform a Complete Recovery of an Oracle 12c Database without the Database Recovery Advisor

By Chris Ruel, Michael Wessler

You shouldn’t rest on your laurels too much, so let’s look at a recovery without Oracle 12c’s Data Recovery Advisor (DRA). The DRA won’t help you in every situation. What if the file you need to restore has to go somewhere else? The DRA won’t know where to put it.

Plus, it’s good to understand how to recover without the DRA. You might find yourself in a non-12c database someday. The following method works all the way back to the dawn of Recovery Manager (RMAN).

This example has you losing a data file but being unable to put it back in the same place. (In this case you means RMAN.) You have to tell RMAN where to put the file; then RMAN will restore it to the proper location.

  • You lost a disk with a data file on it.

  • The disk won’t be replaced, and you have to restore the data file elsewhere.

  • You tell RMAN where to put the data file.

  • You tell RMAN to restore the data file.

  • You tell RMAN to recover the data file.

  • If the database was closed, you open it. If the database was open, you online the data file.

This example using Oracle on Linux starts with the database closed; you open it and then fix the error. To create this error, simply renam the users01.dbf data file while the database was down.

  1. Start the database and read this error:

    ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
    ORA-01110: data file 4: '/u01/app/oracle/oradata/dev12c/users01.dbf'
  2. See if that is the only missing file:

    <select * from v$recover_file;>
  3. Determine whether this is the only file affected and whether it is a critical file.

    You also determine that it must be restored to a different disk.

  4. Launch RMAN and take the data file offline:

    <alter database datafile 4 offline;>

    You see this:

    using target database control file instead of recovery catalog
    Statement processed
  5. Open the database:

    <alter database open;>
  6. Tell RMAN to restore to the correct location:

    <run {
    set newname for datafile 4 to '/u02/app/oracle/oradata/dev12c/users01.dbf';
    restore tablespace users;
    switch datafile all;
    recover tablespace users;
    }>

    In this case, it is disk u02, a different location. The output indicates that the file is being restored and recovered in the new location:

    executing command: SET NEWNAME
    Starting restore at 30-JUN-2013 11:48:46
    using target database control file instead of recovery catalog
    channel ORA_DISK_1: SID=7 device type=DISK
    channel ORA_DISK_1: restoring datafile 00004
    input datafile copy RECID=12 STAMP=819457925 file name=/u01/app/oracle/
    fast_recovery_area/DEV12C/datafile/o1_mf_users_8x0lp5mo_.dbf destination for restore of datafile 00004: /u02/app/oracle/oradata/dev12c/users01.dbf channel ORA_DISK_1: copied datafile copy of datafile 00004 output file name=/u02/app/oracle/oradata/dev12c/users01.dbf RECID=13 STAMP=819460128 Finished restore at 30-JUN-2013 11:48:49 datafile 4 switched to datafile copy Starting recover at 30-JUN-2013 11:48:49 using channel ORA_DISK_1 starting media recovery ...output snipped... media recovery complete, elapsed time: 00:00:01 Finished recover at 30-JUN-2013 11:48:52
  7. When the recovery finishes, alter the tablespace to put it back online:

<alter tablespace users online;>