📋Steps in this guide1/7

Recovery Manager (RMAN) Table Point In Time Recovery (PITR) in Oracle Database 12c Release 1 and 2 (12.1 and 12.2)

Oracle Database 12c makes Point In Time Recovery (PITR) of individual tables as easy as a single Recovery Manager (RMAN) command.

oracle 12cconfigurationintermediate

by OracleDba

13 views

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. Check the current SCN. Add some more data since the SCN was checked. Exit from SQL*Plus and log in to RMAN as a root user with SYSDBA or SYSBACKUP privilege.

Code/Command (click line numbers to comment):

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;

CONN / AS SYSDBA

SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1853267

SQL>

CONN test/test

INSERT INTO t1 VALUES (2);
COMMIT;

SELECT * FROM t1;

        ID
----------
         1
         2

SQL>

Table Point In Time Recovery (PITR)

Log in to RMAN as a user with SYSDBA or SYSBACKUP privilege. Issue the command, giving a suitable location for the auxiliary database. The point in time can be specified using , or . In the following example the clause is included to give the recovered table a new name so we can compare the before and after. 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.

Code/Command (click line numbers to comment):

$ rman target=/

# 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';

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 , and clauses to achieve this. The output from this command is shown here . Once the operation is complete, we can see the resulting dump file in the specified directory.

Code/Command (click line numbers to comment):

RECOVER TABLE TEST.T1
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/aux'
  DATAPUMP DESTINATION '/u01/export'
  DUMP FILE 'test_t1_prev.dmp'
  NOTABLEIMPORT;

$ 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 clause. You can read about it here .

Code/Command (click line numbers to comment):

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. 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.

Code/Command (click line numbers to comment):

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

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.

Code/Command (click line numbers to comment):

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 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. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!