RMAN Table Point in Time Recovery (PITR) Oracle 12c

Following are the steps involved in recovery of accidental drop table with rman Point in time recovery feature.

1. Create new table and insert data

CREATE TABLE test (id NUMBER);
INSERT INTO test VALUES (1);
COMMIT;


2. Note the SCN number upto which restore test

select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2800000


3. Insert the data into the table

INSERT INTO test VALUES (2);
COMMIT;
SELECT * FROM test;
        ID
----------
         1
         2

  
4. Start the recovery of table with RMAN point in time recovery feature.
Make sure you have enough space in auxiliary destination to make all datafiles.

rman target=/
RECOVER TABLE 'HR'.'TEST' UNTIL SCN 2800000 AUXILIARY DESTINATION '/u01/db2'
REMAP TABLE 'HR'.'Test':'Test_PREV';


Note:
auxiliary destination: is created an auxiliary database
remap table : give alternative name to that table and restore.
 
5. Check the new restored table.

SELECT * FROM test_prev;
ID
----------
    1