Flashback in oracle

Flashback
Steps involved are mainly
Enable archiving.
Set Flashback parameters in database and init.ora
Enable Flashback
Grants and Permissions
Test flashback manually.

Enable archiving.
alter system set log_archive_dest_1='location=’/u05/app/clvs1app/db/apps_st/data/ flashback '
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 481259520 bytes
Fixed Size 1314212 bytes
Variable Size 427819612 bytes
Database Buffers 37748736 bytes
Redo Buffers 14376960 bytes
Database mounted.
SQL> !echo $ORACLE_SID
CLVS1
SQL> alter database archivelog;
Database altered.
Set Flashback parameters in database and init.ora
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=10080;
System altered.
Note : this is set for 7 days as discussed with Jason.
SQL> alter system set db_recovery_file_dest_size=10g;
System altered.
SQL> alter system set db_recovery_file_dest='/u05/app/clvs1app/db/apps_st/data/flashback';
System altered.
Set the both the above envirments in init.ora file also.
Enable Flashback
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL>
/u05/app/clvs1app/db/apps_st/data/flashback/CLVS1/flashback

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

We can also disable supplemental log by
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Grants and Permissions

SQL> grant execute on dbms_flashback to test_user;
Grant succeeded.
SQL> grant select any transaction to test_user;
Grant succeeded.
Test flashback manually .
SQL> create table scott.emp1 as select * from scott.emp;
Table created.
SQL> select count(*) from scott.emp1;
COUNT(*)
----------
14
SQL>delete from scott.emp1 where rownum <= 8; SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2009/09/08 07:57:44
SQL> delete from scott.emp1;
6 rows deleted.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
-------------------
2009/09/08 07:58:09
SQL> select count(*) from scott.emp1;
COUNT(*)
----------
0
SQL> alter table scott.emp1 enable row movement;
Table altered.
SQL> flashback table scott.emp1 to timestamp TO_TIMESTAMP('2009/09/08 07:57:44','YYYY/MM/DD HH24:MI:SS');
Flashback complete.
SQL> select count(*) from scott.test;
SQL> select count(*) from scott.emp1;
COUNT(*)
----------
6