Flashback database

Objectives:
a.)  Do we need an image copy of datafiles in Fast Recovery Area?
b.)  Can flashback database run in NOARCHIVELOG mode?
So that I can estimated least space needed for project development databases.

1. ensure no image copy in FRA

orarac2poc:TTS:/home/oracle> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Mar 27 09:21:07 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TTS (DBID=1769654909)

RMAN> list backup;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> list copy;

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository

RMAN> exit


Recovery Manager complete.

2. Note down the SCN and enable flashback database

orarac2poc:TTS:/home/oracle> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 27 09:21:54 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


SYS@TTS> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SYS@TTS> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
SYS@TTS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@TTS> startup mount
ORACLE instance started.

Total System Global Area  663908352 bytes
Fixed Size                  2229440 bytes
Variable Size             281021248 bytes
Database Buffers          373293056 bytes
Redo Buffers                7364608 bytes
Database mounted.
SYS@TTS> alter system set db_recovery_file_dest='/u02/oradata/TTS/FRA' ;
alter system set db_recovery_file_dest='/u02/oradata/TTS/FRA'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory

--due to the subdir FRA was not pre-created. created it accordingly in other session.

SYS@TTS> alter system set db_recovery_file_dest='/u02/oradata/TTS/FRA' ;
alter system set db_recovery_file_dest='/u02/oradata/TTS/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


SYS@TTS> alter system set db_recovery_file_dest_size=3000m ;

System altered.

SYS@TTS> alter system set db_recovery_file_dest='/u02/oradata/TTS/FRA' ;

System altered.

SYS@TTS> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

SYS@TTS> select 1440/24 from dual;

   1440/24
----------
        60
       
#default 24 hours

SYS@TTS> alter database flashback on;

Database altered.

SYS@TTS> alter database open;

Database altered.


SYS@TTS> set time on
09:34:27 SYS@TTS> select count(*) from tranp.idx03;

  COUNT(*)
----------
      3491

09:34:38 SYS@TTS> select current_scn from v$database;

CURRENT_SCN
-----------
    1009053

09:34:43 SYS@TTS>
09:34:56 SYS@TTS>
09:34:58 SYS@TTS>
09:35:00 SYS@TTS> select current_scn from v$database;

CURRENT_SCN
-----------
    1009061

09:35:03 SYS@TTS> drop table tranp.idx03;

Table dropped.

09:35:21 SYS@TTS> select current_scn from v$database;

CURRENT_SCN
-----------
    1009099

09:35:43 SYS@TTS> alter system checkpoint;

System altered.

09:35:48 SYS@TTS> desc v$flashback_database_log;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 OLDEST_FLASHBACK_SCN                                                              NUMBER
 OLDEST_FLASHBACK_TIME                                                             DATE
 RETENTION_TARGET                                                                  NUMBER
 FLASHBACK_SIZE                                                                    NUMBER
 ESTIMATED_FLASHBACK_SIZE                                                          NUMBER


09:36:41 SYS@TTS> @/home/oracle/scripts/performance/set_df

Session altered.

09:37:05 SYS@TTS> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- -------------------- ---------------- -------------- ------------------------
             1008730 27-mar-2013 09:33:19             1440       16384000                        0




09:38:38 SYS@TTS> select count(*) from tranp.idx03;
select count(*) from tranp.idx03
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


3. Flashback database and verify

09:38:45 SYS@TTS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
09:38:59 SYS@TTS> startup mount
ORACLE instance started.

Total System Global Area  663908352 bytes
Fixed Size                  2229440 bytes
Variable Size             281021248 bytes
Database Buffers          373293056 bytes
Redo Buffers                7364608 bytes
Database mounted.
09:39:27 SYS@TTS> select current_scn from v$database;

CURRENT_SCN
-----------
          0

09:39:47 SYS@TTS> flashback database to scn 1009053;

Flashback complete.

09:40:39 SYS@TTS> alter database open read only;

Database altered.

09:41:13 SYS@TTS> select count(*) from tranp.idx03;

  COUNT(*)
----------
      3491

09:42:10 SYS@TTS> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

09:43:04 SYS@TTS> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01531: a database already open by the instance


09:43:29 SYS@TTS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
09:43:43 SYS@TTS> startup mount
ORACLE instance started.

Total System Global Area  663908352 bytes
Fixed Size                  2229440 bytes
Variable Size             281021248 bytes
Database Buffers          373293056 bytes
Redo Buffers                7364608 bytes
Database mounted.
09:44:06 SYS@TTS> alter database open resetlogs;

Database altered.

09:44:20 SYS@TTS> select count(*) from tranp.idx03;

  COUNT(*)
----------
      3491



orarac2poc:DGS:/u02/oradata/TTS/FRA/TTS/flashback> ls -l
total 16040
-rw-r----- 1 oracle asmadmin 8200192 Mar 27 09:39 o1_mf_8o4m2z8r_.flb
-rw-r----- 1 oracle asmadmin 8200192 Mar 27 09:33 o1_mf_8o4m2zm9_.flb
orarac2poc:DGS:/u02/oradata/TTS/FRA/TTS/flashback> ls -l
total 16040
-rw-r----- 1 oracle asmadmin 8200192 Mar 27 09:40 o1_mf_8o4m2z8r_.flb
-rw-r----- 1 oracle asmadmin 8200192 Mar 27 09:33 o1_mf_8o4m2zm9_.flb


4. check relationship with ARCHIVELOG mode

10:17:29 SYS@TTS> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

10:17:36 SYS@TTS> show parameter archive_log
10:17:51 SYS@TTS> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string
...

10:18:01 SYS@TTS> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

10:18:57 SYS@TTS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:19:25 SYS@TTS> startup mount
ORACLE instance started.

Total System Global Area  663908352 bytes
Fixed Size                  2229440 bytes
Variable Size             281021248 bytes
Database Buffers          373293056 bytes
Redo Buffers                7364608 bytes
Database mounted.
10:21:36 SYS@TTS> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled


10:21:44 SYS@TTS> alter database open;

Database altered.

10:22:07 SYS@TTS> alter system switch logfile;

System altered.


Findings:

1. No image copy is required in order to use flashback database.
2. "The database must be running in ARCHIVELOG mode.
   -- To rewind the database to a guaranteed restore point, the FLASHBACK DATABASE command needs the archived redo logs starting from around the time of the restore point.
   -- A Fast Recovery Area must be configured because the Oracle Database server stores the required logs in the Fast Recovery Area.


Note that we cannot use Flashback Database in the following situations:
– The control file has been restored or re-created.
– A tablespace has been dropped.
– A data file has been reduced in size.