Using restore points

Hello folks.

In many cases you probably need to restore a database to a point in the past.
Errors that won’t be reversed only with a flashback table or perhaps you do not have
the flashback feature configured for the database.

You can use restore point with a specific time in the past or using the SCN number.
To capture the SCN of the database you can the query below:

SQL> select current_scn from v$database;

Now to create a restore point run the command:

SQL> create restore point before_fail;
Restore point created.
To create a restore point for a particular SCN, use the AS OF syntax:

SQL> create restore point good_for_now as of scn 2354674668;
Restore point created.

Oracle will keeps the restore points at least as long as the time specified in the
CONTROL_FILE_RECORD_KEEP_TIME initialization parameter
If you would like to keep for more long time you can use the PRESERVE keyword

SQL> create restore point before_fail preserve;
Restore point created.

To drop a restore point:

SQL> drop restore point before_fail;
Restore point dropped.

Well, during a maintence at the software application someone drop very important tables of the production database

SQL> drop table usr_app.vendas;
Table dropped.
SQL> drop view usr_app.compras_mes;
View dropped.

Let’s start to use the restore point:

First shutdown the database:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Now do the startup

SQL> startup mount
ORACLE instance started.

Total System Global Area 876102608 bytes
Fixed Size 2791732 bytes
Variable Size 470734273 bytes
Database Buffers 1384122378 bytes
Redo Buffers 5652480 bytes
Database mounted.

Log into the RMAN and create a run block

RMAN> run
set until restore point before_fail;
restore database;
recover database;

executing command: SET until clause
starting full resync of recovery catalog
full resync complete


On the next step open the database with RESETLOGS:

SQL> alter database open resetlogs;
Database altered.

Check the table in the database:

SQL> select * from usr_app.vendas;

—- —– ——— ————
42 05/04/2015 22 TV LED
43 05/04/2015 90 NOTEBOOK 14
44 05/04/2015 31 SOFA 4 LUGARES
45 05/04/2015 72 LUSTER 127V
With the flashback feature you are able to go back the table in the past
with database online or retrieving the tables from the recycle bin.
But each case will depend of your conditions of environment.

I hope that you enjoy
Thanks for reading


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s