Oracle 12c Recover table from RMAN backup

Oracle has introduced a new feature from Version 12c which will enable us to recover tables from RMAN backups using RECOVER TABLE command. In this article I will demonstrate how to recover tables to a specified point in time using RMAN.

Refer the below URL to get more information,

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-recovering-tables-partitions.html

Below informations are required to recover tables from an RMAN backup,

• Names of the table that has to be recovered
• Point in time/SCN to which the table has to be recovered
• Whether the recovered tables must be imported into the target database

Below are the steps which I am testing here:

1. Creation of test table and import some records
2. Get the current SCN for PITR recovery
3. Backup the database and archivelogs using RMAN
4. Drop the table which has to be recovered
5. Recover the table,
   • with the same name
   • with different name
6. Verify the recovered tables

1. Create test table and import some records

Connect to database schema to create two tables tab1 and temp1 and insert some records

SQL> conn faheem/faheem
Connected.

create table faheem.tab1 (id number(7), name varchar2(20), name_ar varchar2(30));

insert into faheem.tab1(id, name, name_ar)
select rownum, 'Employee ' || to_char(rownum), 'اسم'|| to_char(rownum)
from dual
connect by level <= 100000;
commit;

create table temp1 as select * from tab1;

2. Get the current SCN for PITR recovery

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1699874

3. Backup the database and archivelogs using RMAN

RMAN> run
{
BACKUP AS COMPRESSED BACKUPSET DATABASE 
TAG 'FULLDB'
format '/home/oracle/rmanbackup/db2/db_level1_%t_%s_p%p';
BACKUP AS COMPRESSED BACKUPSET 
ARCHIVELOG ALL NOT BACKED UP 1 TIMES 
TAG 'ARCHBKP'
format '/home/oracle/rmanbackup/db2/al_%t_%s_p%p';
backup current controlfile
TAG 'CTL'
format '/home/oracle/rmanbackup/db2/cf_%t_%s_p%p';
}

4. Drop the tables which has to be recovered and purge recyclebin

SQL> conn faheem/faheem
Connected.
SQL> drop table temp1;
Table dropped.

SQL> drop table tab1;
Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE     CLUSTERID
------------------------------ ---------   ---------
BIN$sUOvFIk8I2TgU9E4qMCInw==$0 TABLE

BIN$sUOvFIk9I2TgU9E4qMCInw==$0 TABLE

SQL> purge recyclebin;
Recyclebin purged.

SQL> select * from tab;
no rows selected

SQL>

5. Recover the table

Here I am testing the table recovery with two ways,

1. Recover the table with the same name
2. Recover the table with different name

Recover the table table temp1 with the same name

Connect to RMAN and run the RECOVER TABLE command

rman target /	

recover table faheem.temp1 until scn 1699874
auxiliary destination '/home/oracle/backup';

Click here to see the logflie

Recover the table tab1 with different name

Connect to RMAN and run the RECOVER TABLE command with remap table,

rman target /

recover table faheem.tab1 until scn 1699874 
auxiliary destination '/home/oracle/backup'
remap table 'FAHEEM'.'TAB1':'TAB1_09102020';

Click here to see the log for the above comand.

6. Verify the recovered tables

conn faheem/faheem
SQL> select * from tab;

TNAME                     TABTYPE  CLUSTERID
------------------------- ------- ----------
TEMP1                     TABLE
TAB1_09102020             TABLE

Hope this helps…