Flashback database

1.PURPOSE AND SCOPE

It is posible to fix the logical errors, with using flashback database technology but for sure time period of this kind of operations depends of flashback time of database.While using flashback technology we can use time,log sequence or SCN.

P.S: Disk failure and deletion of datafile/redo/control file  cant be restored with technology.

2.PRACTICE

2.1. We are going to connect to closed db and open flashback option.

[oracle@sb trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 18 10:21:38 2013

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 4.0353E+10 bytes
Fixed Size            2235176 bytes
Variable Size         2.1072E+10 bytes
Database Buffers     1.9193E+10 bytes
Redo Buffers           85233664 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.

2.2.   First of all we need to check Db_flashback_retention_target parameter , it will show us flashback time ,default of this parameter is  1140 minute. I am going to set it to 2 days.

SQL> show parameter flashback

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

SQL> alter system set db_flashback_retention_target=2880;

System altered.

SQL> alter database open;

Database altered.

2.3.  I a going to create a dummy table with 1000 row.

 CREATE TABLE bcanbakal.table_x 
as select * from dba_objects where rownum < 1001;    Table created. SQL> select count(1) from bcanbakal.table_x;

  COUNT(1)
----------
      1000

2.4. For flashback operation i am going to SCN number of DB.Thats the reason first of all i am going to check SCN number of db ,after of it going to truncate table.

SQL> select a.oldest_flashback_scn,(select scn_to_timestamp(a.oldest_flashback_scn) from dual) oldest_flashback_time,(select 'Current scn: '|| current_scn||' .Current scn to date: '|| scn_to_timestamp(current_scn) from v$database) current_scn,a.retention_target,a.flashback_size,a.estimated_flashback_size from v$flashback_database_log a;

OLDEST_FLASHBACK_SCN     OLDEST_FLASHBACK_TIME                 CURRENT_SCN                                                                       RETENTION_TARGET   FLASHBACK_SIZE  ESTIMATED_FLASHBACK_SIZE
--------------------     ----------------------------------   --------------------------------------------------------------                    ----------------    --------------  ------------------------
         4363163         30-OCT-12 11.03.32.000000000 AM         Current scn: 5243411 .Current scn to date: 18-JAN-13 11.03.47.000000000 AM         2880            100687872       5256462336     

SQL> truncate table bcanbakal.table_x;

Table truncated.      

SQL> select count(1) from bcanbakal.table_x;

  COUNT(1)
----------
     0

2.5.  And now i am going to close database and use flashback technology to restore my table using SCN.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4.0353E+10 bytes
Fixed Size            2235176 bytes
Variable Size         2.1072E+10 bytes
Database Buffers     1.9193E+10 bytes
Redo Buffers           85233664 bytes
Database mounted.
SQL> flashback database to scn 5243411;

Flashback complete.

2.6. We need to open database with resetlog option.It is wise to take full backup after using resetlog option.

SQL> alter database open resetlogs;

Database altered.

2.7.  Now lets check the table.

SQL> select count(1) from bcanbakal.table_x;

  COUNT(1)
----------
      1000