Recovering Tables from RMAN Backups

Starting with Oracle 12c, RMAN enables you to recover one or more tables or table partitions to a specified point in time without affecting the remaining database objects. You can use previously-created RMAN backups to recover tables and table partitions to a specified point in time.

Oracle Database version: 12.1.0.1

Oracle Database patch level: Bundle #12:

SQL> alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss'; Session altered. --Get date and time SQL> select sysdate from v$database; SYSDATE ------------------- 08/24/2014 14:19:14

SQL> select * from boris.emp;     EMPNO ENAME      JOB              MGR HIREDATE         SAL          COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 17-DEC-80        800                    20      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300    30      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500    30      7566 JONES      MANAGER         7839 02-APR-81       2975                    20      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400   30      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10      7788 SCOTT      ANALYST         7566 19-APR-87       8000                    20      7839 KING       PRESIDENT          17-NOV-81       5000                    10      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20      7900 JAMES      CLERK           7698 03-DEC-81        950                    30      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 14 rows selected. --Truncate EMP table SQL> truncate table boris.emp; Table truncated. --Verify EMP table SQL> select count(*) from boris.emp;  COUNT(*) ----------         0 --Drop EMP table SQL> drop table boris.emp; Table dropped.

--Restore EMP table with the original name rman target / recover table boris.emp until time "to_date('08/24/2014 14:20:00','mm/dd/yyyy hh24:mi:ss')" auxiliary destination 'D:\temp'; --Restore EMP table with a new name (bkp_emp) rman target / recover table boris.emp until time "to_date('08/24/2014 14:20:00','mm/dd/yyyy hh24:mi:ss')" auxiliary destination 'D:\temp' remap table boris.emp:bkp_emp;

Note: if tables you are trying to restore are already in the schema, you will get an error. You need to rename or drop these tables before you restore them via RMAN. You can also use RMAN to REMAP parameter to recover the tables to a different schema or table name.

For more information see:

Oracle® Database Backup and Recovery User's Guide

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.