Perform Table Recovery With Oracle 12c Using RMAN

Import Consideration:

  • An rman backup containing the missing table must exist; it cannot rely on a backup from before the table was created alongside the application of archived redo.
  • The table cannot belong to SYS or SYSTEM and cannot reside in SYSTEM or SYSAUX.

Create Test Tables

SQL> create table test1 as select * from dba_tables;

Table created.

SQL> select count(*) from test1;

  COUNT(*)

———-

      6352

Make sure to perform Incremental or Full Backup

Drop test Tables

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> drop table umair.test1;

Table dropped.

Set Recovery Destination Area

> Mkdir –p /zfssa/dbm01/backup1/fra

> cd /zfssa/dbm01/backup1/fra

oracle@exadbadm01.gain.tcprod.local:dbm01:/zfssa/dbm01/backup1/fra

Recover table using time or SCN

RMAN> recover table umair.test1 until time “to_date(’09/16/2017 21:01:15′,’mm/dd/yyyy hh24:mi:ss’)” auxiliary destination ‘/zfssa/r360pdimg/imgbackup1/fra’;

Starting recover at 17-SEP-17
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=509 instance=dbm01 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Tablespace UNDOTBS2
Tablespace UNDOTBS3
Tablespace UNDOTBS4
Tablespace UNDOTBS5
Tablespace UNDOTBS6

auxiliary instance file tspitr_plti_80445.dmp deleted
Finished recover at 18-SEP-17

Validate Table Recovery

SQL> select count(*) from test1;

  COUNT(*)

———-

      6352