Databases Reference
In-Depth Information
3.
Similar to the second solution, you can open the database in read-only mode to check
whether you have flashed back to a correct place in time:
SQL> alter database open read only;
Database altered.
4.
After the database is opened, you can check the data and determine whether the flashback
was done to a time far back enough. If not, you can flash it back once more by repeating
the steps: shut down, start up in mount mode, flash back, and open as read-only.
5.
When you want the database to be at a certain point in time, follow the “common
poststep” to open the database for normal use.
The database is now flashed back and ready for use.
How It Works
The SQL approach works exactly like the RMAN approach described in Recipe 13-4. Refer to the “How It Works”
section of that recipe for details.
Finding Out How Far Back into the Past You Can Flash Back
Problem
You want to flash back the database, and you want to find out how far into the past you can go.
Solution
Query the V$FLASHBACK_DATABASE_LOG view to find out how far into the past you can flash back. For example:
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE CON_ID
------------------------ ----------
2193903 21-JUL-12 1440 367001600
20570112 0
The value of the column OLDEST_FLASHBACK_SCN is 2193903, which indicates you can flash back to the SCN
up to that number only, not before that.
The column OLDEST_FLASHBACK_TIME shows the earliest time you can flash back to when you use the
timestamp approach shown in Recipe 13-4 and Recipe 13-5. The default display format of a datetime column is just
a date, and it does not yield enough information. To see the exact time, you issue the following SQL statement:
SQL> select to_char(oldest_flashback_time,'mm/dd/yy hh24:mi:ss')
2 from v$flashback_database_log;
TO_CHAR(OLDEST_FL)
-----------------
07/21/12 17:39:12
