How to Recover Your Oracle 12c Database with Copies

By Chris Ruel, Michael Wessler

Copies allow for superfast recovery of your Oracle 12c databases and fewer technical recoveries when you’ve lost a disk and the file has to go to a different location.

Some Database Administrators (DBAs) would argue that it’s incorrect to use the copy in the place you backed it up. Good DBAs subscribe to the mantra “Everything has its place; everything in its place.” However, times are changing. For example, the popularity of large storage area networks (SANs), where all your files go to the same place, is growing.

DBAs don’t have as much responsibility to organize, separate, stripe, and label data, nor do they have as much time. The SAN does all the protection and striping for you. Furthermore, Oracle has even released, in essence, its own volume manager: Automatic Storage Management (ASM). ASM can help you:

  • Relax your regimented file and naming conventions.

  • Find more time to make better use of the features that Oracle has to protect and manage data.

You get a call from a user who is getting the following error:

SQL> select *
 2 from emp;
from emp
 *
ERROR at line 2:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u02/app/oracle/oradata/dev12c/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

After some investigation, you see that someone has removed the data file from the USERS tablespace. This tablespace is critical and must be recovered immediately. You decide to recover with RMAN using a COPY of the data file.

  1. Log in to your target with RMAN.

  2. Make sure you have a copy of your USERS tablespace data file:

    <list copy of tablespace users;>

    You should see something like this:

    List of Datafile Copies
    =======================
    Key   File S Completion Time   Ckp SCN  Ckp Time
    ------- ---- - -------------------- ---------- --------------------
    19   4  A 30-JUN-2013 12:11:31 2365031  30-JUN-2013 12:11:30
        Name: /u01/app/oracle/fast_recovery_area/DEV12C/datafile/o1_mf_users_8x0p5lyh_.dbf
        Tag: LEVEL0_COPY
    12   4  A 30-JUN-2013 11:12:05 2356203  30-JUN-2013 11:12:05
        Name: /u01/app/oracle/fast_recovery_area/DEV12C/datafile/o1_mf_users_8x0lp5mo_.dbf
        Tag: LEVEL0_COPY
    7    4  A 30-JUN-2013 11:08:20 2355686  30-JUN-2013 11:08:20
        Name: /u01/app/oracle/fast_recovery_area/DEV12C/datafile/o1_mf_users_8x0lh47j_.dbf
        Tag: USERS_COPY
    6    4  A 30-JUN-2013 11:08:08 2355658  30-JUN-2013 11:08:08
        Name: /u01/app/oracle/fast_recovery_area/DEV12C/datafile/o1_mf_users_8x0lgrkr_.dbf
        Tag: TAG20130630T110808
    5    4  A 30-JUN-2013 11:08:04 2355632  30-JUN-2013 11:08:03
        Name: /u01/app/oracle/fast_recovery_area/DEV12C/datafile/o1_mf_users_8x0lgmrf_.dbf
        Tag: TAG20130630T110430
  3. Take the tablespace offline (because the database is open):

    < sql "alter tablespace users offline";>

    You see this:

    sql statement: alter tablespace users offline
  4. Switch to the copy:

    <switch tablespace users to copy;>

    You see something like this:

    datafile 4 switched to datafile copy "/u01/app/oracle/fast_recovery_area/DEV12C/datafile/
    o1_mf_users_8x0p5lyh_.dbf"
  5. Recover the copy that was taken earlier:

    <recover tablespace users;>

    You see something like this:

    Starting recover at 30-JUN-2013 12:21:52
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=48 device type=DISK
    starting media recovery
    media recovery complete, elapsed time: 00:00:00
    Finished recover at 30-JUN-2013 12:21:53
  6. Alter the tablespace to put it back online:

    <alter tablespace users online;>

    You see this:

    Statement processed

All done! Do you see how quick that was without having to restore the file? Of course, you have to come to terms with it being in your Fast Recovery Area. If that really bugs you, you can do one of the following:

  • You can rename the file and move it later when you have a maintenance window.

  • When you take the copy, you can copy the file to an auxiliary area outside your Fast Recovery Area (where you don’t mind it being) in case you have to use it.