OCP Blog
Super DBA
Table level recovery in Oracle 12c
by Vazha Mantua Monday, October 7, 2013 5:44 PM

Good Day,

One major new features in Oracle 12c in new RMAN feature named table level recovery . With 12c R1, you can recover a particular table or partition to a point-in-time or SCN from RMAN backups in the event of a table drop or truncate.

I test new feature on Linux x64_86 platform.  As I understood procedure of restore is

1. Backup database

2. Create an auxiliary database and recovers the specified tables or table partitions, until the specified point in time, into this auxiliary database

3. Make export of table from auxiliary database with data pump.

4. Make import to Source DB with data pump

5. Drop temporary auxiliary database .


Let’s Begin, we have  made this example for : Oracle Database 12c Enterprise Edition Release - 64bit Production, Single instance, archive mode.

set DB_RECOVERY_FILE_DEST parameter:

SQL> alter system set DB_RECOVERY_FILE_DEST=’/u1/oradata/backup’ scope=both;

create table, which we should restore from RMAN backup:

SQL> create table vazha.clients_12c as select * from dba_users; (table should not be in system and sysaux TBS!)

now we should make backup of DB:


rman target /

RMAN> backup database;

After finishing backup you can drop table from source DB ORA12C with command

SQL> drop table vazha.clients_12c;



So now we can restore table from RMAN backup:

find last SCN which is on RMAN backup, you can find it with command list backup

RMAN> List backup;

List of Backup Sets

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1.20G      DISK        00:01:32     07-OCT-13     
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20131007T165002
        Piece Name: /u1/oradata/backup/ORA12C/backupset/2013_10_07/o1_mf_nnndf_TAG20131007T165002_955cj05c_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1735782    07-OCT-13 /u1/oradata/ORA12C/system01.dbf
  3       Full 1735782    07-OCT-13 /u1/oradata/ORA12C/sysaux01.dbf
  4       Full 1735782    07-OCT-13 /u1/oradata/ORA12C/undotbs01.dbf
  6       Full 1735782    07-OCT-13 /u1/oradata/ORA12C/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.64M      DISK        00:00:05     07-OCT-13     
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20131007T165002
        Piece Name: /u1/oradata/backup/ORA12C/backupset/2013_10_07/o1_mf_ncsnf_TAG20131007T165002_955cm0jr_.bkp
  SPFILE Included: Modification time: 07-OCT-13
  SPFILE db_unique_name: ORA12C
  Control File Included: Ckp SCN: 1735826      Ckp time: 07-OCT-13


So you can restore table until SCN: 1735826.

For restoring table you should do:


rman target /

RMAN>recover table vazha.clients_12c until scn 1735826 auxiliary destination '/install/oracle/temp';

Oracle OS user should have privileges on directory /install/oracle/temp. on this destination RMAN create temporary database for exporting table.


That’s ALL.

Tags: , , ,

Filter by APML


<<  August 2020  >>

View posts in large calendar