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);
2. Note the SCN number upto which restore test
select dbms_flashback.get_system_change_number from dual;
3. Insert the data into the table
INSERT INTO test VALUES (2);
SELECT * FROM test;
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.
RECOVER TABLE 'HR'.'TEST' UNTIL SCN 2800000 AUXILIARY DESTINATION '/u01/db2'
REMAP TABLE 'HR'.'Test':'Test_PREV';
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;