Wednesday , May 29 2024

Table Point In Time Recovery in 12c(PITR)

This article contains information about table point in time recovery in 12c and later versions and examples.

As database administrators, we used to do a lot of superfluous work when it was necessary to return a deleted table. With Table Point In Time Recovery in 12C, we had the chance to return the table from the backup with a single action. Yes it is an automatic table recovery in oracle 12c.

In today’s article, we will recognize this feature that can be used in Oracle Database 12C and above versions.

The process works as follows; It opens a new instance for you and registers a database with your table on that 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 things you should 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 in archivemode.
  • Tables and table partitions belonging to the SYS scheme cannot be recovered.
  • Tables and table partitions cannot be recovered from SYSTEM and SYSAUX tablespaces.
  • Tables and table partitions in standyby databases cannot be recovered.
  • Tables with NOT NULL constraints cannot be recovered with the REMAP option.

Table Point in Time Recovery Example

Let’s explain with a small example. Since I am working on the test database, I will take a fresh backup and perform the process. If you have a backup available, you can skip this step.

Now let’s prepare a suitable environment for recovery. For the example, I will recover according to the date. I will check my database time and delete a sample table.

My database time is “16/09/2020 10:59:12”. I want to return to this hour. There is a directory named “/ u01 / tempdata / bgrdb” on file system. The database will temporarily use this location.

First of all, I delete my table according to the scenario.

How To Recover Dropped Table in Oracle 12c

Now we deleted our test table. Lets start to recover dropped table in oracle 12c with date.

If you want to do it with SCN;

Recovery begins.

The whole process is complete. As you can see, Oracle itself handles what we used to do manually. And with IMPDP, which we don’t like :))

As an example, the instance added while processing;

Our table has been successfully recovered with the two examples we have given. If you want, you can export for this table instead of performing recovery.

Or you can recover the table with a different name.

We have come to the end of this article where we discuss the issue of recovering a table in Oracle Database with the help of RMAN.

Hope to see you again,

Source :


About Buğra PARLAYAN

Burgra Parlayan is an experienced Database and Weblogic Administrator. After completing his technical / relevant training he has got involved with a serious amount of projects. He successfully managed database upgrade, database migration, database performance tuning projects for various public institutions.Currently he has been employed by one of the leading financial institutions called Turkiye Hayat & Emeklilik as responsible administrator for Oracle Database and Oracle Middleware. He has been sharing his experience and knowledge by face to face training, personal blog and various social networking accounts to support the Oracle ecosystem continuously since 2010.

Leave a Reply

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