18th September 2021

What is Table Point In Time Recovery (PITR) in Oracle?

In the past, when a table was deleted or had to come back after a certain period of time for any reason, it did a lot of unnecessary work. With Oracle Database 12C, we had the chance to bring the table back to our lives with Table Point In Time Recovery, in other words, with a single action from the backup.

In today’s article, we will recognize this feature that can be used in Oracle Database 12C and higher versions. The process logically opens a new instance for you and registers a database with your table on this instance. After registration, the table is taken out and imported into your existing database. You can do this by going to a certain date or according to the SCN or Sequence number, but there are some situations that we need to pay attention to. When you use the RECOVER command to recover tables or table partitions contained in an RMAN backup, the following limitations exist.

  • The database must be on the archivemod.
  • Tables and table partitions belonging to the SYS scheme cannot be recovered.
  • Tables and table partitions cannot be recovered from SYSTEM and SYSAUX table fields.
  • Tables and table partitions in standby databases cannot be recovered.
  • Tables with NOT NULL constraints cannot be recovered with the REMAP option.

Recovering tables or table partitions in RMAN backup with example

We will explain with an example of the test database.

Recovery process by date

Now we will prepare a suitable environment for recovery. For example, we will recover according to the date. We will check our database clock and delete a sample table. We want to return our database clock ”04/11/2020 22:59:12” to this hour. There is a directory named ”/u01/tempdata/bgrdb” on the file system. The database will temporarily use this location.

LEARN MORE  Docker Installation on Ubuntu

Delete table

First of all, we delete our table according to the scenario.

Starting recovery

We start recovery with history.

Using SCN

If you want to do it with SCN, you can use the code below.

Recovery startup

We’re starting the recovery.

Added instance

Transactions are completed. As you can see, Oracle is doing what we used to do manually in the past. As an example, you can see the added instance while performing the process on the screen below.

LEARN MORE  Migrating from MS-SQL Server to PostgreSQL Database
added instance
added instance

 

Export for table

Our table has been recovered with the two examples we have given successfully. If you want, you can do not recover but export for this table.

Recovery under a different name

Or you can recover the table with a different name.

Different tablespace

Finally, as you can recover the table with a different name, you can get it on different tablespaces.

 

Leave a Reply

Your email address will not be published. Required fields are marked *