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.

• Setup
• 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)

Related articles.

• Table Point In Time Recovery (PITR) in PDBs

Setup

To 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 TABLE command, giving a suitable AUXILIARY DESTINATION location for the auxiliary database. In the following example the REMAP TABLE clause 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 File

Rather 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 DATAPUMP DESTINATION, DUMP FILE and NOTABLEIMPORT clauses 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
$