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

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

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. The point in time can be specified using UNTIL SCN, UNTIL TIME or UNTIL SEQUENCE. In the following example the REMAP TABLE clause is included to give the recovered table a new name so we can compare the before and after.

# SCN
RECOVER TABLE TEST.T1
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/aux' 
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

# TIME
RECOVER TABLE TEST.T1
  UNTIL TIME "TO_DATE('01-JAN-2013 15:00', 'DD-MON-YYYY HH24:MI')"
  AUXILIARY DESTINATION '/u01/aux' 
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

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;

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
$

◉ Table Point In Time Recovery (PITR) in a Pluggable Database (PDB)

The process for performing a point in time recovery of a table in a PDB is similar to that of a non-CDB database. You just have to add the OF PLUGGABLE DATABASE clause.

RECOVER TABLE TEST.T1 OF PLUGGABLE DATABASE pdb1
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/aux' 
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

◉ Remapping (12.1)

In a previous section we saw an example of remapping a table name. In the following example the table point in time recovery of T1 results in a new table called T1_PREV.

RECOVER TABLE TEST.T1
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/aux' 
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

It is also possible to remap the tablespace, either independently or in conjunction with the remap of the table name. In the following example the previous table point in time recovery is repeated, but the resulting table is created in the EXAMPLES tablespace, rather than the USERS tablespace.

RECOVER TABLE TEST.T1
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/aux' 
  REMAP TABLE 'TEST'.'T1':'T1_PREV'
  REMAP TABLESPACE 'USERS':'EXAMPLES';

◉ Remapping (12.2+)

In addition to the remapping operations of 12.1, Oracle 12.2 allows you to remap the schema of the table during a tablespace point in time recovery. The remap table syntax now allows the inclusion of the destination schema. The following example switches the schema from TEST to TEST2.

RECOVER TABLE TEST.T1
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/aux' 
  REMAP TABLE 'TEST'.'T1':'TEST2'.'T1_PREV'
  REMAP TABLESPACE 'USERS':'EXAMPLES';

◉ Space Check (12.2+)

Table and partition point in time recovery (PITR) requires the creation of an auxiliary instance, created in the location specified by the AUXILIARY DESTINATION clause. In Oracle 12.1 the recovery operation would begin regardless of the space available in the auxiliary destination. If there wasn't enough space the operation would fail.

In Oracle 12.2 RMAN checks the space available for the auxiliary instance before beginning the operation.