Improved Flashback with Oracle 11g
It was great that the database do not have to be shutdown and then be mounted to just enable the flashback on feature in Oracle 11G, at least 11.2 later.
Below is a short testing case to utilize this feature to prepare some Application release just in case a rollback is requested, and the flashback could be faster than the Rman backup & restore.
Check and Change the DB working mode:
oracle@dbinterest ORADB112:/oracledb/ora> sqlplsu / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 23 03:47:59 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> select log_mode,flashback_on from v$database; LOG_MODE FLASHBACK_ON ------------ ------------------ NOARCHIVELOG NO
Put the DB at the mounted state, then change the log mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2234960 bytes
Variable Size 880805296 bytes
Database Buffers 155189248 bytes
Redo Buffers 5656576 bytes
Database mounted.
SQL> alter database archive log;
alter database archive log
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracledb/ora/archivelog01/ORADB112/
Oldest online log sequence 21
Current log sequence 24
SQL>
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracledb/ora/archivelog01/ORADB112/
Oldest online log sequence 21
Next log sequence to archive 24
Current log sequence 24
SQL> alter database open;
Database altered.
Switch a log file and see where it goes.
SQL> alter system switch logfile; System altered. SQL> !ls -lrth /oracledb/ora/archivelog01/ORADB112/ total 29M -rw-r----- 1 oracle dba 29M Nov 23 05:39 arch_0001_0794845279_0000000024.arc
Prepare the Flashback enabling….
SQL> show parameter db_rec NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 db_recycle_cache_size big integer 0 SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38709: Recovery Area is not enabled. SQL> alter system set db_recovery_file_dest='/oracledb/ora/fra/'; alter system set db_recovery_file_dest='/oracledb/ora/fra/' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=300m; System altered. SQL> alter system set db_recovery_file_dest='/oracledb/ora/fra/'; System altered. SQL> alter database flashback on; Database altered. SQL> SQL> SQL> select log_mode,flashback_on from v$database; LOG_MODE FLASHBACK_ON ------------ ------------------ ARCHIVELOG YES
See what files might be generated from after the flashback being set up… 2 new files with the same size 
oracle@dbinterest ORADB112:/oracledb/ora/fra/ORADB112/flashback> ls -lrth total 101M -rw-r----- 1 oracle dba 51M Nov 23 05:43 o1_mf_8by38gj0_.flb -rw-r----- 1 oracle dba 51M Nov 23 05:43 o1_mf_8by38kq6_.flb
See what new processes might be started after the flashback being set up…
oracle@dbinterest ORADB112:/oracledb/ora/fra/ORADB112/flashback> ps -ef | grep ora_ oracle 19851 1 0 05:37 ? 00:00:00 ora_pmon_ORADB112 oracle 19855 1 0 05:37 ? 00:00:00 ora_psp0_ORADB112 oracle 19859 1 0 05:37 ? 00:00:00 ora_vktm_ORADB112 oracle 19865 1 0 05:37 ? 00:00:00 ora_gen0_ORADB112 oracle 19869 1 0 05:37 ? 00:00:00 ora_diag_ORADB112 oracle 19873 1 0 05:37 ? 00:00:00 ora_dbrm_ORADB112 oracle 19877 1 0 05:37 ? 00:00:00 ora_dia0_ORADB112 oracle 19881 1 0 05:37 ? 00:00:00 ora_mman_ORADB112 oracle 19885 1 0 05:37 ? 00:00:00 ora_dbw0_ORADB112 oracle 19889 1 0 05:37 ? 00:00:00 ora_lgwr_ORADB112 oracle 19893 1 0 05:37 ? 00:00:00 ora_ckpt_ORADB112 oracle 19897 1 0 05:37 ? 00:00:00 ora_smon_ORADB112 oracle 19901 1 0 05:37 ? 00:00:00 ora_reco_ORADB112 oracle 19905 1 0 05:37 ? 00:00:00 ora_mmon_ORADB112 oracle 19909 1 0 05:37 ? 00:00:00 ora_mmnl_ORADB112 oracle 19975 1 0 05:38 ? 00:00:00 ora_arc0_ORADB112 oracle 19980 1 0 05:38 ? 00:00:00 ora_arc1_ORADB112 oracle 19984 1 0 05:38 ? 00:00:00 ora_arc2_ORADB112 oracle 19988 1 0 05:38 ? 00:00:00 ora_arc3_ORADB112 oracle 19992 1 0 05:38 ? 00:00:00 ora_qmnc_ORADB112 oracle 20023 1 0 05:38 ? 00:00:00 ora_q000_ORADB112 oracle 20027 1 0 05:38 ? 00:00:00 ora_q001_ORADB112 oracle 20107 1 0 05:43 ? 00:00:00 ora_rvwr_ORADB112 oracle 20138 4155 0 05:45 pts/0 00:00:00 grep ora_
Just a quick test to turn the flashback off 
SQL> alter database flashback off; Database altered.
Then the flashback logs were all cleared
oracle@dbinterest ORADB112:/oracledb/ora/fra/ORADB112/flashback> ls -lrth total 0
Enable the flashback again and check the logs and processes…
SQL> alter database flashback on; Database altered. oracle@dbinterest ORADB112:/oracledb/ora/fra/ORADB112/flashback> ls -lrth total 101M -rw-r----- 1 oracle dba 51M Nov 23 05:48 o1_mf_8by3k0dk_.flb -rw-r----- 1 oracle dba 51M Nov 23 05:49 o1_mf_8by3jzoz_.flb oracle@dbinterest ORADB112:/oracledb/ora/fra/ORADB112> ps -ef | grep ora_ oracle 19851 1 0 05:37 ? 00:00:00 ora_pmon_ORADB112 oracle 19855 1 0 05:37 ? 00:00:00 ora_psp0_ORADB112 oracle 19859 1 0 05:37 ? 00:00:00 ora_vktm_ORADB112 oracle 19865 1 0 05:37 ? 00:00:00 ora_gen0_ORADB112 oracle 19869 1 0 05:37 ? 00:00:00 ora_diag_ORADB112 oracle 19873 1 0 05:37 ? 00:00:00 ora_dbrm_ORADB112 oracle 19877 1 0 05:37 ? 00:00:00 ora_dia0_ORADB112 oracle 19881 1 0 05:37 ? 00:00:00 ora_mman_ORADB112 oracle 19885 1 0 05:37 ? 00:00:00 ora_dbw0_ORADB112 oracle 19889 1 0 05:37 ? 00:00:00 ora_lgwr_ORADB112 oracle 19893 1 0 05:37 ? 00:00:00 ora_ckpt_ORADB112 oracle 19897 1 0 05:37 ? 00:00:00 ora_smon_ORADB112 oracle 19901 1 0 05:37 ? 00:00:00 ora_reco_ORADB112 oracle 19905 1 0 05:37 ? 00:00:00 ora_mmon_ORADB112 oracle 19909 1 0 05:37 ? 00:00:00 ora_mmnl_ORADB112 oracle 19975 1 0 05:38 ? 00:00:00 ora_arc0_ORADB112 oracle 19980 1 0 05:38 ? 00:00:00 ora_arc1_ORADB112 oracle 19984 1 0 05:38 ? 00:00:00 ora_arc2_ORADB112 oracle 19988 1 0 05:38 ? 00:00:00 ora_arc3_ORADB112 oracle 19992 1 0 05:38 ? 00:00:00 ora_qmnc_ORADB112 oracle 20023 1 0 05:38 ? 00:00:00 ora_q000_ORADB112 oracle 20027 1 0 05:38 ? 00:00:00 ora_q001_ORADB112 oracle 20198 1 0 05:48 ? 00:00:00 ora_rvwr_ORADB112 oracle 20205 1 0 05:48 ? 00:00:00 ora_smco_ORADB112 oracle 20212 1 0 05:48 ? 00:00:00 ora_w000_ORADB112 oracle 20233 4155 0 05:49 pts/0 00:00:00 grep ora_
Flashback feature testing …
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2012-11-23 06:16:09
SQL>
SQL> create table t as select sysdate, o.* from dba_objects o where rownum < 11;
create table t as select sysdate, o.* from dba_objects o where rownum < 11
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias
SQL> create table t as select sysdate time, o.* from dba_objects o where rownum < 11;
Table created.
SQL> select to_char(time, 'yyyy-mm-dd hh24:mi:ss') from t;
TO_CHAR(TIME,'YYYY-
-------------------
2012-11-23 06:18:23
2012-11-23 06:18:23
2012-11-23 06:18:23
2012-11-23 06:18:23
2012-11-23 06:18:23
2012-11-23 06:18:23
2012-11-23 06:18:23
2012-11-23 06:18:23
2012-11-23 06:18:23
2012-11-23 06:18:23
10 rows selected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
492802
Delete several records and test the flashback.
SQL> delete from t where rownum < 3;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
----------
8
SQL> flashback database to scn 492802;
flashback database to scn 492802
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SQL>
SQL>
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2234960 bytes
Variable Size 880805296 bytes
Database Buffers 155189248 bytes
Redo Buffers 5656576 bytes
Database mounted.
SQL> flashback database to scn 492802;
Flashback complete.
SQL> select count(*) from t;
select count(*) from t
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from t;
COUNT(*)
----------
10
Lastly check on the flashback log, it seems some new records was there, this might generated in the time database side activities are performing something, in this case, some delete and commit …
oracle@chidatiod751 ORADB112:/oracledb/ora/fra/ORADB112/flashback> ls -lrth total 101M -rw-r----- 1 oracle dba 51M Nov 23 05:48 o1_mf_8by3k0dk_.flb -rw-r----- 1 oracle dba 51M Nov 23 06:29 o1_mf_8by3jzoz_.flb ORADB112:/oracledb/ora/fra/ORADB112/flashback> strings o1_mf_8by3k0dk_.flb | wc -l 4 ORADB112:/oracledb/ora/fra/ORADB112/flashback> strings o1_mf_8by3jzoz_.flb | wc -l 21582
References:
http://uhesse.com/2010/06/25/turning-flashback-database-on-off-with-instance-in-status-open/
