Questions on flashback feature

Reference: http://www.iselfschooling.com/mcd12_ora10g/Oracle10g_Answers.htm

Q: What is the flashback?
A: The flashback gives users the capability to query past version of schema objects, query historical data, and perform change analysis.

Q: How it works?
A: Every transaction logically generates a new version of the database. You can navigate through these versions to find an error and its cause.

Q: Why do need to use the flashback?
A: It eliminates restore process and it is faster than traditional point-in-time recovery.

Q: What is its architecture?
A: Now, one more log was added as Flashback Database log. The Oracle database server regularly logs before images of data blocks in the Flashback Database logs from Flashback buffer in the SGA Oracle memory. The Flashback Database must be enabled. When it is enabled, the new RVWR (Recovery Version Writer) background process will be started. The RVWR background process sequentially writes Flashback Database data from the flashback buffer to the Flashback Database logs which are circularly reused.

Q: How do you configure Flashback Database?
A:
Assuming:
The database is in archive mode.
The database flash recovery area was configured.
Set the database flashback retention time target.
SQL> ALTER SYSTEM SET db_flashback_retention_target = 2880; -- Ex: for two days.
Enable Flashback Database. Before altering your database, the database must be in MOUNT EXCLUSIVE mode, ARCHIVELOG mode, and the Flashback be enabled. To check whether it is enable do the following SQL statement.
SQL> SELECT flashback_on FROM v$database;
SQL> ALTER DATABASE FLASHBACK ON;
If you disable the flashback (OFF), all existing Flashback Database logs are deleted automatically.

Q: How do you Flashback a database?
A: The FLASHBACK DATABASE command force the database back to a past time or SCN. See the following examples:
SQL> FLASHBACK DATABASE TO TIMESTAMP (sysdate-5/24); -- Go back 5 hours from now.
SQL> FLASHBACK DATABASE TO SCN 65473;

Q: How do you monitor Flashback Database?
A: Use the V$FLASHBACK_DATABASE_LOG view to display the approximate lowest SCN and time to which you can flash back your database.
SQL> SELECT oldest_flashback_scn, oldest_flashback_time
FROM v$flashback_database_log;

Q: How do you use the V$FLASHBACK_DATABASE_LOG view to determine how much disk space is needed to meet the current flashback retention target?
A:
SQL> SELECT estimated_flashback_size, flashback_size
FROM v$flashback_database_log;

Q: How do you use the V$FLASHBACK_DATABASE_STAT view to monitor the overhead of logging flashback data?
A: You can use this to adjust the retention time or the flash recovery area size.
SQL> SELECT * FROM v$flashback_database_stat;

Q: How do you exclude a tablespace from flashback database?
A: If you do not want the USER tablespace to be included to log Flashback Database data, do the following SQL statement.
SQL> ALTER TABLESPACE users FLASHBACK OFF;

Q: When are you not able to Flashback Database?
A:
The control file has been restored or recreated,
A tablespace has been dropped,
A data file has been shrunk, and
A RESETLOSG operation is required.

Q: How can you query the content of the recycle bin by using the DBA_RECYCLEBIN view?
A:
SQL> SELECT * FROM dba_recyclebin WHERE can_undrop = ‘YES’;
SQL> SHOW RECYCLEBIN

Q: How do you restore from recycle bin?
A: Use the FLASHBACK TABLE command to recover a table and all its possible dependent objects form the recycle bin.
SQL> DROP TABLE iself.emp;
SQL> SELECT original_name, object_name, type, ts_name,
dropttime, related, space
FROM dba_recyclebin
WHERE original_name = ‘EMP’;
SQL> FLASHBACK TABLE emp TO BEFORE DROP;
SQL> FLASHBACK TABLE emp
TO BEFORE DROP RENAME TO employee;
SQL> FLASHBACK TABLE emp
TO TIMESTAMP to_timestamp (’14:45’,’HH24:MI’);

Q: How do you reclaim the recycle bin?
A: By using PURG option.
SQL> PURGE TABLE emp; -- Purges the specified table.
SQL> PURGE TABLESPACE scott_ts USER scott; -- All the Scott’s objects.
SQL> PURGE RECYCLEBIN; -- Purges all user objects.
SQL> PURGE DBA_RECYCLEBIN; -- Purges all the objects.

Q: How can you perform queries on the database as of a certain clock time or SCN?
A:
SQL> SELECT versions_xid, sal, versions_operation
FROM emp
VERSIONS BETWEEN TIMESTAMP sysdate-10/24 AND sysdate
WHERE empno = 100;

Q: How can you use the CURRENT_SCN column in the V$DATABASE view to obtain the current SCN?
A:
SQL> SELECT current_scn FROM v$database;

Q: How can you enforce to guaranteed UNDO retention?
A: You can do one of the following SQL statements.
SQL> CREATE UNDO TABLESPACE my_undotbs1
DATAFILE ‘my_undotbs01.dbf’ SIZE 10G AUTOEXTEND ON
RETENTION GUARANTEE;
SQL> ALTER TABLESPACE my_undotbs1
RETENTION GUARANTEE;

Q: How can you check the UNDO retention?
A:
SQL> SELECT tablespace_name, retention FROM dba_tablespaces;

Q: How can you recover deleted file?
A:
Connect as sysdba and flashback the table.
SQL> CONNECT / AS SYSDBA
Use the FLASHBACK TABLE command to recover a table and all its possible dependent objects form the recycle bin.
Check what do you have in your recycle bin.
SQL> SELECT original_name, object_name, type, ts_name,
dropttime, related, space
FROM dba_recyclebin
WHERE original_name = ‘FLASHBACK_TABLE’
/
SQL> FLASHBACK TABLE iself.emp TO BEFORE DROP;

Q: How do you test that your recovery was successful?
A:
SQL> SELECT count(*) FROM flashback_table;