Flashback database


[oracle@test]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 18 10:21:38 2014
 
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.
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.
I a going to create a  table with 1000 row.
CREATE TABLE anar.table_x 
as select * from dba_objects where rownum < 1001; 
Table created.
SQL> select count(1) from anar.table_x;
 
 COUNT(1)
----------
 1000
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-14 11.03.47.000000000 AM 2880 100687872 5256462336 
 
SQL> truncate table anar.table_x;
 
Table truncated. 
 
SQL> select count(1) from anar.table_x;
 
 COUNT(1)
----------
 0
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.
SQL> alter database open resetlogs;
 
Database altered.

SQL> select count(1) from anar.table_x;
 
 COUNT(1)
----------
 1000

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: