Perform Flashback via Enterprise Manager

You can perform a Flashback Database operation via Enterprise Manager as well.
Go to Tab: Maintenance -> Section: Backup/Recovery -> Link: Perform Recovery


Dealing with Excluded Tablespaces in case of Flashback Database Operations

If some of your tablespaces are excluded from flashback logging it means that they are not with critical importance for your system and the database is allowed to be open without the data contained within them.
If you want to perform a Flashback Database operation with excluded tablespaces, try to follow these steps:
1. Find the desired point in time for the flashback operation. Usually it is the time just before the time when some human error caused a logical damage of the database data. You can calculate this as SCN or a Date (TIMESTAMP expression). Additionally, you can use a restore point or a time just before last RESETLOGS operation. A Log Sequence Number can be used in 10g R1 but it is not available as an option in 10g R2.
2. Find all datafiles that are with disabled flashback logging:
SQL> SELECT NAME tbs_name,file_name db_file,flashback_on fb_logging, online_status status FROM v$tablespace,dba_data_files WHERE NAME=tablespace_name;


3. Ensure that you have available backups for all datafiles that have disabled flashback logging:
RMAN> LIST BACKUP BY FILE;
If you have a RMAN catalog, you can query the view: RC_BACKUP_DATAFILE_SUMMARY

4. Ensure that all needed archived and redo logs (covering the whole time period between oldest_flashback_time column value and the time of failure) are physically available:
4.1 Check the physical availability:
RMAN> CROSSCHECK ARCHIVELOG FROM SCN oldest_flashback_scn;
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.
4.2 Check the logical availability:
RMAN> REPORT UNRECOVERABLE;
REPORT UNRECOVERABLE command reports all datafiles that cannot be recovered from existing backups because redo may be missing.
You can check the logical availability of archived redo logs with the following SQL as well:
SQL> SELECT * FROM v$archived_log WHERE first_change# > = oldest_flashback_scn AND status = 'A';
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.

5. Find and write down the current SCN (or create a normal restore point if you are using 10g R2). Don’t forget that the Flashback Database is a change on the database and it must be recorded if some failure occurs within this operation.
SQL> SELECT current_scn FROM v$database;
or
SQL> CREATE RESTORE POINT before_flashback_operation;
To map a time value and SCN, you can use the built-in SQL functions (available since 10g):
SCN_TO_TIMESTAMP – to find associated timestamp with this SCN
TIMESTAMP_TO_SCN – to find associated SCN with this timestamp
SQL> SELECT current_scn, scn_to_timestamp(current_scn) FROM v$database;
This timestamp mapping information is recorded in the SMON_SCN_TIME table.
Oracle keeps the information in SMON_SCN_TIME table for a period of 5 days.

6. Restart the database in MOUNT stage:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

7. Put the datafiles with disables flashback logging in OFFLINE mode but with the DROP option.
If you try to use immediately the Flashback Database command, it will return an error. You should put all excluded from flashback logging datafiles in OFFLINE mode:

Now, if you try to do Flashback, the Flashback Database operation will succeed but another error will be returned:

You must again put the needed datafiles in OFFLINE mode but with the DROP option. After this command the offlined datafiles cannot be recovered without performing of a datafile media recovery.

If you want to perform a Flashback Database operation (when some tablespaces are excluded from flashback logging), you should use this command:
SQL> ALTER DATABASE DATAFILE ‘E:\ORACLE\ORADATA\ORCL102\FB_TEST01.DBF’ OFFLINE FOR DROP;
This will let the flashback operation to complete successfully and the database to be opened as fast as possible.

8. Execute the FLASHBACK DATABASE command

9. Open the Database with RESETLOGS clause:
SQL> ALTER DATABASE OPEN RESETLOGS;

10. Recover offline tablespaces.
At this moment, when all important data is already available and accessible to the end users, you can try to perform media recovery of all offlined datafiles while the database is open. You can do this with RMAN:
RMAN> RESTORE TABLESPACE fb_users;
RMAN> RECOVER TABLESPACE fb_users;
Now you can bring recovered datafiles in ONLINE mode:
SQL> ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\ORCL102\FB_TEST01.DBF' ONLINE;
SQL> ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\ORCL102\FB_TEST02.DBF' ONLINE;

It is possible SYSTEM tablespace to be excluded from flashback logging:

In this case you will not be able to use the Flashback Database operation to perform fast database point-in-time recovery because you cannot open the database without to perform media recovery of the whole database:

Disabling the SYSTEM tablespace will make the Flashback Database feature useless because the only way a DBITR to be performed is by using the standard media recovery with restore of datafile and applying of redo logs.
According to all tests – excluding of tablespaces from flashback logging can safe some space on your storage but in many cases will lead to problems and serious difficulties of using of the Flashback Database feature. Exclude a tablespace from flashback logging only if you are sure that you will be able to perform fast database point-in-time recovery in case of need.

Thanks.