HOME

"Recover Table" feature in Oracle 12c (in a pluggable DB)

Suppose you lost a table's contents, either via drop, truncate or delete. Now you need to recover tables with its contents.

You have following options to recover it:

  1. Flashback table: Very easy and simple option, but unfortunately, it cannot span DDL operations, i.e. cannot recover dropped or truncated table. Can recover deleted contents, but only upto the limit of availablility of data in UNDO segments, which can vanish anytime. Always prefer this whenever possible. It's the least draconian method of all other options.
  2. Flashback database: Requires maintenance of flashback logs and recovers entire DB, not the particular table, which is not what we need here in this scenario.
  3. TSPITR: It will rollback entire tablespace, not just the individual table.
  4. RMAN Restore and Recovery process: This will too rollback the whole DB.

Oracle 12c provides "recover table" option with RMAN, just like "recover tablespace" for TSPITR, with the difference that recover table will recover only the specified table(s) to a particular time/SCN specified.

Make sure you have a backup of targeted pdb, preferrably taken while in Root cdb.
Suppose tables HR.T_T and HR.TEAM were truncated/deleted somehow.
You know the time/SCN when this table disaster happened.
While recovering, we have option to specify alternate table name via remap option. We cannot remap to an existing table name. So have to provide some different table name, later on we can rename that to original table after dropping original one or can just insert into original table.

SET ORACLE_HOME=C:\oracle\product\12.1.0\dbhome_1
SET ORACLE_SID=orcl12c  (this is your CDB)
Start RMAN, be in root CDB only, and fire following command to recover tables present in pluggable DB pdborcl.
C:\windows\system32>C:\oracle\product\12.1.0\dbhome_1\bin\Rman target /
		
recover table hr.t_t,hr.team OF PLUGGABLE DATABASE pdborcl until time "to_date('08/29/2014 15:30:00','mm/dd/yyyy hh24:mi:ss')"
auxiliary destination 'C:\or_backup\usr_hot_bkp\aux1' REMAP TABLE hr.team:team1,hr.t_t:t_t1 ;

Here are the operations performed by above command during the process, its a long listing.

RMAN> run {
2>  recover table hr.t_t,hr.team OF PLUGGABLE DATABASE pdborcl until time "to_date('08/29/2014 15:30:00','mm/dd/yyyy hh24:mi:ss')"
3> auxiliary destination 'C:\or_backup\usr_hot_bkp\aux1' REMAP TABLE hr.team:team1,hr.t_t:t_t1 ; }

Starting recover at 29-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=139 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

Creating automatic instance, with SID='feAf'

...........................
Starting recover at 29-AUG-14
using channel ORA_AUX_DISK_1

starting media recovery

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

.....
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2410864 bytes
Variable Size                297797264 bytes
Database Buffers             763363328 bytes
Redo Buffers                   5365760 bytes

sql statement: alter database mount clone database

....................
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''C:\or_backup\usr_hot_bkp\aux1''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''C:\or_backup\usr_hot_bkp\aux1''

..................


Performing import of tables...
...............
Import completed


Removing automatic instance
Automatic instance removed
.........
Finished recover at 29-AUG-14

RMAN>
Thanks...

TOP