Oracle 12c New Features

Online Oracle Training

LESSON 20

Glory of Flashback

Why flashback

If you have enough space, make sure to turn on your flashback. It will save you lots of time in times of crises. Make enough space based on your retention time. Also, when you perform flashback do not resetlogs unless you are convince that you are where you want to be. 

In this lesson, we show you how to use flashback and use the resetlogs option when you want to open your database.

Here we will talk about flashback at the database level not table. Assuming that your user wants you to go back to a specific date and time for a reason, also, assuming that all users are willing to lose data and start their work from the time you have plan to flashback. If those assumptions are okay with your users, then you can perform the flashback.

Also, make sure that you have snapshot from the time your user requested to flashback.

The following are some examples:

SQL> select timestamp_to_scn(sysdate-7) SCN from v$database;

select timestamp_to_scn(sysdate-7) from v$database

       *

ERROR at line 1:

ORA-08180: no snapshot found based on specified time

ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

This output shows, I have no snapshot going 7 days back to perform flashback

SQL> select timestamp_to_scn(sysdate) SCN from v$database;

This output shows my current time snapshot.

SQL> select timestamp_to_scn(sysdate-1) SCN from v$database;

This output shows the snapshot from one hour ago.

SQL> select timestamp_to_scn(to_date('03-04-2014 08:31:00','MM-DD-YYYY HH24:MI:SS')) SCN from v$database;

This output shows the snapshot from a specific date and time ('03-04-2014 08:31:00').

That"s all you need to perform the flashback.

How to turn on your database flashback

Step 1:

Check to see if your flashback is on using the V$DATABASE view

SQL> SELECT flashback_on FROM v$database;

Notice that my flashback is not on.

If not, then turn on your flashback.

To turn on your flashback, you need to shut down, startup your database with the MOUNT option and then turn on your flashback.

SQL> CONNECT / as sysdba

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

Check to see if your flashback is on using the V$DATABASE view

SQL> SELECT flashback_on FROM v$database;

Notice that my flashback is YES.

Check your flashback retention

SQL> show parameter flashback_retention

Notice that your retention is only for 24 hours. The value is on minutes.

To increase the value

SQL> ALTER SYSTEM SET db_flashback_retention_target=14400;

Check your flashback retention again

SQL> show parameter flashback_retention

Notice that your retention is for 10 days. Notice more retention requires more recovery area. You may have to increase your recovery area.

Check you recovery area

SQL> show parameter recovery_file

To increase the value

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=150G;

Check you recovery area again

SQL> show parameter recovery_file

Notice that you increased the size from 4800M (default) to 150G.

Now, you are ready to flashback.

Relax and wait for an opportunity to come to flashback if a user make a mistake and you need to flashback the whole database to a specific time.

Okay, you got a call from your user and he/she deleted or drop objects that it is critical to their daily operations. They are willing to go back to that time and re-enter the lost data.

Let us assume that the date and time, they want you go back is 03-04-2014 08:31:00

Find the SCN number the snapshot to that time. If you do not have the snapshot for that time, there is no way you can flashback.

To get the SCN, perform the following query.

SQL> select timestamp_to_scn(to_date('03-04-2014 08:31:00','MM-DD-YYYY HH24:MI:SS')) SCN from v$database;

This output shows you can flashback to that SCN (System Change Number).

It is possible that you have received a wrong time so do not resetlogs unless you are sure.

To flashback, you need to shut down and start the database with the MOUNT option.

SQL> shutdown immediate;

SQL> startup mount;

SQL> FLASHBACK DATABASE TO SCN 2733999;

Check the database before you open it with the RESETLOGS option.

Open the database in the READ ONLY mode.

SQL> ALTER DATABSE OPEN READ ONLY;

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;

Make sure that what your user wants.

If the user is happy, perform the flashback again but this time open the database with the RESETLOGS option.

SQL> shutdown immediate;

SQL> startup mount;

SQL> FLASHBACK DATABASE TO SCN 2733999;

SQL> ALTER DATABSE OPEN RESETLOGS;

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Once you use the resetlogs option, you want to make sure to perform a full backup.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG delete all input;

Good Luck!