Oracle Database 12c introduced a significant enhancement to Recovery Manager (RMAN) with the ability to perform Table Point-in-Time Recovery (PITR). This feature simplifies the process of recovering a table or table partition to a specific point in time. It eliminates the need for manual steps such as creating a clone database. You no longer need to export data using Data Pump or clean up the clone. In this article, we will explore how to perform Table PITR in Oracle Database 12c Release 1 and 2 (12.1 and 12.2), including setup, execution, and advanced features like remapping and space checks.
Overview of Table Point-in-Time Recovery (PITR)
In previous Oracle Database releases, recovering a table or table partition to a specific point in time required a cumbersome process. This process involved cloning the database, exporting the table, and then importing it back into the original database. Oracle 12c simplifies this process by introducing a single RMAN command that automates all these steps. This feature is particularly useful when you need to recover a table to a specific System Change Number (SCN), timestamp, or log sequence number.
Setup for Table PITR
To demonstrate Table PITR, we need to create a sample table and ensure the database is running in archivelog mode with adequate backups. While flashback queries are often more appropriate for recent changes, this example serves to illustrate the Table PITR process.
Step 1: Create a Test User and Table
First, connect to the database as a privileged user (e.g., SYSDBA) and create a test user and table.
CONN / AS SYSDBA
CREATE USER demo_user IDENTIFIED BY demo_password
QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO demo_user;
CONN demo_user/demo_password
CREATE TABLE sales_data (sale_id NUMBER);
INSERT INTO sales_data VALUES (1001);
COMMIT;
Step 2: Check the Current SCN
Next, check the current System Change Number (SCN) to establish a recovery point.
CONN / AS SYSDBA
SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1853267
Step 3: Add More Data
Insert additional rows into the table after capturing the SCN.
CONN demo_user/demo_password
INSERT INTO sales_data VALUES (1002);
COMMIT;
SELECT * FROM sales_data;
SALE_ID
----------
1001
1002
Performing Table Point-in-Time Recovery (PITR)
Step 4: Log in to RMAN
Exit SQL*Plus and log in to RMAN as a user with SYSDBA or SYSBACKUP privileges.
$ rman target=/
Step 5: Execute the RECOVER TABLE Command
Use the RECOVER TABLE command to restore the table to the specified SCN. The AUXILIARY DESTINATION clause specifies the location for the auxiliary database files. The REMAP TABLE clause allows you to rename the recovered table for comparison.
Recovery Using SCN
RECOVER TABLE DEMO_USER.SALES_DATA
UNTIL SCN 1853267
AUXILIARY DESTINATION '/u01/auxiliary'
REMAP TABLE 'DEMO_USER'.'SALES_DATA':'SALES_DATA_PREV';
Recovery Using Timestamp
Alternatively, you can specify a timestamp for recovery.
RECOVER TABLE DEMO_USER.SALES_DATA
UNTIL TIME "TO_DATE('01-JAN-2023 15:00', 'DD-MON-YYYY HH24:MI')"
AUXILIARY DESTINATION '/u01/auxiliary'
REMAP TABLE 'DEMO_USER'.'SALES_DATA':'SALES_DATA_PREV';
Step 6: Verify the Recovered Table
Once the recovery is complete, connect to the database and verify the contents of the recovered table.
CONN demo_user/demo_password
SELECT * FROM sales_data_prev;
SALE_ID
----------
1001
Table PITR to a Data Pump Dump File
Instead of importing the recovered table directly into the database, you can export it to a Data Pump dump file for manual import later. Use the DATAPUMP DESTINATION, DUMP FILE, and NOTABLEIMPORT clauses to achieve this.
RECOVER TABLE DEMO_USER.SALES_DATA
UNTIL SCN 1853267
AUXILIARY DESTINATION '/u01/auxiliary'
DATAPUMP DESTINATION '/u01/export'
DUMP FILE 'sales_data_prev.dmp'
NOTABLEIMPORT;
After the operation, the dump file will be available 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 sales_data_prev.dmp
Table PITR in a Pluggable Database (PDB)
The process for performing Table PITR in a Pluggable Database (PDB) is similar to that in a non-CDB database. Simply add the OF PLUGGABLE DATABASE clause to the RECOVER TABLE command.
RECOVER TABLE DEMO_USER.SALES_DATA OF PLUGGABLE DATABASE pdb1
UNTIL SCN 1853267
AUXILIARY DESTINATION '/u01/auxiliary'
REMAP TABLE 'DEMO_USER'.'SALES_DATA':'SALES_DATA_PREV';
Advanced Features
Remapping in Oracle 12.1
In Oracle 12.1, you can remap the table name and tablespace during recovery. For example:
RECOVER TABLE DEMO_USER.SALES_DATA
UNTIL SCN 1853267
AUXILIARY DESTINATION '/u01/auxiliary'
REMAP TABLE 'DEMO_USER'.'SALES_DATA':'SALES_DATA_PREV'
REMAP TABLESPACE 'USERS':'EXAMPLES';
Remapping in Oracle 12.2+
Oracle 12.2 enhances remapping capabilities by allowing schema remapping. For example:
RECOVER TABLE DEMO_USER.SALES_DATA
UNTIL SCN 1853267
AUXILIARY DESTINATION '/u01/auxiliary'
REMAP TABLE 'DEMO_USER'.'SALES_DATA':'DEMO_USER2'.'SALES_DATA_PREV'
REMAP TABLESPACE 'USERS':'EXAMPLES';
Space Check in Oracle 12.2+
In Oracle 12.2, RMAN performs a space check in the auxiliary destination before starting the recovery operation. This prevents failures due to insufficient space.
Conclusion
Oracle Database 12c’s Table Point-in-Time Recovery (PITR) feature simplifies the process of recovering tables to a specific point in time. By automating the creation of auxiliary instances and Data Pump operations, RMAN reduces the complexity and time required for recovery. Whether you’re working with a non-CDB or a PDB, Table PITR provides a powerful tool for data recovery, with advanced features like remapping and space checks enhancing its flexibility and reliability.
By following the steps outlined in this article, you can effectively use RMAN to perform Table PITR in Oracle Database 12c Release 1 and 2, ensuring your data remains consistent and recoverable in the event of accidental changes or corruption.
Home 