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

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.