How to restore a table to a point in time in Oracle Database with RMAN

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

In this article we will see how in Oracle Database we can restore a table at a specific time. The data to perform the process comes from RMAN backups and during the process they are temporarily restored automatically to a clone of the database (auxiliary database).

The prerequisites

The database should be located at Archive Log Mode, if it is not found, connect from a terminal with the oracle user (with which the Oracle Database has been installed):

sqlplus / as sysdba

To make the change you will need to restart the database by running the following:

shutdown immediate;

alter database archivelog mode;

startup;

We should also be application aware RMAN database backups and keep them Archive Logs.

We connect to RMAN:

rman target /

To get the backup of the database we run the following:

backup database;

The example

For example we have created a tablespace, we have given permission to the user to write to this tablespace and we have created a table with some records in it:

CREATE TABLESPACE corrupt_ts 
   DATAFILE '/oracle/app/oracle/oradata/ORACLEDB/datafile/corrupt_ts_data1.dbf'size 10m autoextend on next 64m maxsize 32767m,
   '/oracle/app/oracle/oradata/ORACLEDB/datafile/corrupt_ts_data2.dbf'size 10m autoextend on next 64m maxsize 32767m;

ALTER USER stratos quota unlimited on CORRUPT_TS;

CREATE TABLE stratos.users(
   id INT GENERATED ALWAYS AS IDENTITY, 
   username VARCHAR2(50)
) TABLESPACE corrupt_ts;


insert into stratos.users (username) values ('Stratos');
insert into stratos.users (username) values ('Giorgos');
insert into stratos.users (username) values ('Nikos');
insert into stratos.users (username) values ('Vasilis');
commit;

We check to see that the table contains the entries:

select * from stratos.users;

How do we reset the table to a specific point in time

Let's say that someone has run a query and updated all the entries in the table with a wrong value:

update stratos.users set username='Oups!!';
commit;

How do we restore the table with another name

We connect to RMAN:

rman target /

And we do recover the table at a specific time:

• As auxiliary destination we define the space where a clone of the database will be temporarily created until the process is complete.
• By command remap table we set it to bring the table with another name.

RECOVER TABLE stratos.users
  UNTIL TIME "TO_DATE('08-02-2022 14:23', 'DD-MM-YYYY HH24:MI')"
  AUXILIARY DESTINATION '/oracle/app/oracle/oradata/AUX/'
  REMAP TABLE stratos.users:stratos.users2;

As soon as the process is completed, we will see that the table has returned with the new name:

select * from stratos.users2;

How do we restore the table with the same name

We can replace the table directly after first deleting the old one:

sqlplus / as sysdba

drop table stratos.users;

rman target /

And we do recover the table at a specific time:

• As auxiliary destination we define the space where a clone of the database will be temporarily created until the process is complete.

RECOVER TABLE stratos.users
  UNTIL TIME "TO_DATE('08-02-2022 16:00', 'DD-MM-YYYY HH24:MI')"
  AUXILIARY DESTINATION '/oracle/app/oracle/oradata/AUX/';

select * from stratos.users;

How to export point in time table to Export Dump File

We can do recover the table at a specific point in time, not in the database but in a export dump file:

• As auxiliary destination we define the space where a clone of the database will be temporarily created until the process is complete.
• As datapump destination we define the location where the dump file will be exported.
• As dump file we define the name of the export dump file.
• With notableimport we only set the table to be searched in the export dump file and not imported.

RECOVER TABLE stratos.users
  UNTIL TIME "TO_DATE('08-02-2022 15:40', 'DD-MM-YYYY HH24:MI')"
  AUXILIARY DESTINATION '/oracle/app/oracle/oradata/AUX/'
  DATAPUMP DESTINATION '/home/oracle/Documents'
  DUMP FILE 'users.dmp'
  NOTABLEIMPORT;

How do we reset the entire tablespace to a specific point in time

We can do recover the entire tablespace at a specific point in time:

• As auxiliary destination we define the space where a clone of the database will be temporarily created until the process is complete.

RECOVER TABLESPACE corrupt_ts
  UNTIL TIME "TO_DATE('08-02-2022 14:23', 'DD-MM-YYYY HH24:MI')"
  AUXILIARY DESTINATION '/oracle/app/oracle/oradata/AUX/';

Sources:

• Recovering Tables and Table Partitions from RMAN Backups
• Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)
• Getting Started with RMAN
• Changing the Database Archiving Mode