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)
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=/
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';
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
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 $
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 can read about it here.
For more information see:
- Introduction to the Multitenant Architecture
- Overview of the Multitenant Architecture
- Managing a Multitenant Environment
- Recovering Tables and Table Partitions from RMAN Backups
- Table Point In Time Recovery (PITR) in PDBs
Hope this helps. Regards Tim...