RMAN Table Point In Time Recovery (PITR) in Oracle Database 12c Release 1 (12.1)In previous releases point in time recovery of a table or table partition was only possible by manually creating a point in time clone of the database, retrieving the table using data pump, then removing the clone. Oracle 12c includes a new RMAN feature which performs all these steps, initiated from a single command.
- Table Point In Time Recovery (PITR)
- Table Point In Time Recovery (PITR) to Dump File
- Table Point In Time Recovery (PITR) in a Pluggable Database (PDB)
SetupTo demonstrate this, we need to create a table to do a PITR on. This example assumes you are running in archivelog mode and have adequate backups in place to allow a recovery via a point in time clone. For such a recent modification, using a flashback query would be more appropriate, but this serves the purpose for this test.
CONN / AS SYSDBA CREATE USER test IDENTIFIED BY test QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE TABLE TO test; CONN test/test CREATE TABLE t1 (id NUMBER); INSERT INTO t1 VALUES (1); COMMIT;Check the current SCN.
CONN / AS SYSDBA SELECT DBMS_FLASHBACK.get_system_change_number FROM dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 1853267 SQL>Add some more data since the SCN was checked.
CONN test/test INSERT INTO t1 VALUES (2); COMMIT; SELECT * FROM t1; ID ---------- 1 2 SQL>Exit from SQL*Plus and log in to RMAN as a root user with SYSDBA or SYSBACKUP privilege.
Table Point In Time Recovery (PITR)Log in to RMAN as a user with SYSDBA or SYSBACKUP privilege.
$ rman target=/Issue the
RECOVER TABLEcommand, giving a suitable
AUXILIARY DESTINATIONlocation for the auxiliary database. In the following example the
REMAP TABLEclause is used to give the recovered table a new name.
RECOVER TABLE 'TEST'.'T1' UNTIL SCN 1853267 AUXILIARY DESTINATION '/u01/aux' REMAP TABLE 'TEST'.'T1':'T1_PREV';
RMAN> RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…' AUXILIARY DESTINATION '/u01/tablerecovery' DATAPUMP DESTINATION '/u01/dpump' DUMP FILE 'tablename.dmp' NOTABLEIMPORT REMAP TABLE 'username.tablename': 'username.new_table_name';
The output from this command is shown here. It's rather long, but it clearly shows the creation of the clone and the data pump export and import operations. Once the operation is complete, we can see the T1_PREV table has been created and contains the data as it was when the SCN was captured.
sqlplus test/test SELECT * FROM t1_prev; ID ---------- 1 SQL>
Table Point In Time Recovery (PITR) to Dump FileRather than completing the whole recovery, you can just stop at the point where the recovered table is in a data pump dump file, which you can import manually at a later time. The following example uses the
NOTABLEIMPORTclauses to achieve this.
RECOVER TABLE 'TEST'.'T1' UNTIL SCN 1853267 AUXILIARY DESTINATION '/u01/aux' DATAPUMP DESTINATION '/u01/export' DUMP FILE 'test_t1_prev.dmp' NOTABLEIMPORT;The output from this command is shown here. Once the operation is complete, we can see the resulting dump file in the specified directory.
$ ls -al /u01/export total 120 drwxr-xr-x. 2 oracle oinstall 4096 Dec 26 17:33 . drwxrwxr-x. 5 oracle oinstall 4096 Dec 26 12:30 .. -rw-r-----. 1 oracle oinstall 114688 Dec 26 17:34 test_t1_prev.dmp $