SQL> show parameter db_recovery
NAME TYPE VALUE
--------------------------- ----------- ------------------------------
db_recovery_file_dest string c:\oracle\flash_recovery_area
db_recovery_file_dest_size big integer 4977M
SQL> show parameter flashback;
NAME TYPE VALUE
----------------------------- ----------- -----
db_flashback_retention_target integer 1440
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> select instance_name, archiver from v$instance;
INSTANCE_NAME ARCHIVE
---------------- -------
demo STARTED
SQL> conn demo/demo
Connected.
SQL> alter session set nls_date_format = 'YYYY-MM-DD:HH24:MI:SS';
Session altered.
SQL> select table_name from user_tables;
TABLE_NAME
---------------
PREZ
SQL> desc prez
Name Null? Type
------------------------------------------ -------- -----------
NAME VARCHAR2(25)
ELECTED VARCHAR2(4)
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2008
George W. Bush 2013
Bill Clinton 2013
George H. W. Bush 2013
Ronald Reagan 2013
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-11-24:22:02:36
SQL> create table viceprez (name varchar2(25), elected varchar2(4));
Table created.
SQL> insert into viceprez values ('Joe Biden', '2008');
1 row created.
SQL> insert into viceprez values ('Dick Cheney', '2000');
1 row created.
SQL> insert into viceprez values ('Al Gore', '1992');
1 row created.
SQL> insert into viceprez values ('Dan Quayle', '1988');
1 row created.
SQL> insert into viceprez values ('George H. W. Bush', '1980');
1 row created.
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Joe Biden 2008
Dick Cheney 2000
Al Gore 1992
Dan Quayle 1988
George H. W. Bush 1980
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-11-24:22:09:07
SQL> quit
Note the timestamp here. At the time that the timestamp is shown the rows have not been committed to the database. If we do not explicitly issue a commit statement, the commit does not happen until we quit from SQL*Plus, where the commit happens implicitly. We will see the relevance of this particular note later in the demo.
SQL> delete from viceprez where elected< 2000;
3 rows deleted.
SQL> delete from prez where elected = 2013;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2008
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Joe Biden 2008
Dick Cheney 2000
SQL> quit
C:\>rman target /
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DEMO 3543868088 CURRENT 1 19-NOV-12
RMAN>quit
C:\> sqlplus / as sysdba
SQL> flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS');
flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS')
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
C:\> sqlplus / as sysdba
SQL> startup mount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 415236560 bytes
Database Buffers 113246208 bytes
Redo Buffers 5804032 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS');
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> connect demo/demo
Connected.
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2008
George W. Bush 2013
Bill Clinton 2013
George H. W. Bush 2013
Ronald Reagan 2013
SQL> select * from viceprez;
no rows selected
SQL> quit
C:\>rman target /
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DEMO 3543868088 PARENT 1 19-NOV-12
2 2 DEMO 3543868088 CURRENT 849611 24-NOV-12
RMAN>quit
This is where the earlier comment about the placement of the commit came into play. Because the changes had not been committed at the timestamp specified, when the flashback database was performed, those rows are missing from the resulting database after the flashback is complete.
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-11-24:22:22:11
SQL> delete from viceprez where elected < '2000';
3 rows deleted.
SQL> delete from prez where elected = '2013';
4 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2008
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Joe Biden 2008
Dick Cheney 2000
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 415236560 bytes
Database Buffers 113246208 bytes
Redo Buffers 5804032 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2012-11-24:22:22:11','YYYY-MM-DD:HH24:MI:SS');
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> connect demo/demo
Connected.
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2008
George W. Bush 2013
Bill Clinton 2013
George H. W. Bush 2013
Ronald Reagan 2013
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Joe Biden 2008
Dick Cheney 2000
Al Gore 1992
Dan Quayle 1988
George H. W. Bush 1980
SQL> quit
C:\>rman target /
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DEMO 3543868088 PARENT 1 19-NOV-12
2 2 DEMO 3543868088 PARENT 849611 24-NOV-12
3 3 DEMO 3543868088 CURRENT 849931 24-NOV-12
RMAN>
The DBA is the hero of the hour, having managed to successfully recover the database back to pre-delete state without the large time investment of a complete database restore.
SQL> create restore point flashback_table;
Restore point created.
Again, let's assume that someone has run rampant over some table in the database. The table is stand alone, with no other dependencies on other objects, so it has been determined that it's safe to roll this one table back to a point in time in the past.
SQL> conn demo/demo
Connected.
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Joe Biden 2008
Dick Cheney 2000
Al Gore 1992
Dan Quayle 1988
George H. W. Bush 1980
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-11-24:22:32:14
SQL> delete from viceprez where elected = '2008';
1 row deleted.
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Dick Cheney 2000
Al Gore 1992
Dan Quayle 1988
George H. W. Bush 1980
SQL> flashback table viceprez to restore point flashback_table;
flashback table viceprez to restore point flashback_table
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
Oops! What does that mean? Row movement is a setting that tells Oracle it's OK to generate new ROWID's for rows being recovered in this manner (since there's no guarantee that the old ROWID is still unique).
SQL> alter table viceprez enable row movement;
Table altered.
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Dick Cheney 2000
Al Gore 1992
Dan Quayle 1988
George H. W. Bush 1980
SQL> flashback table viceprez to restore point flashback_table;
Flashback complete.
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Joe Biden 2008
Dick Cheney 2000
Al Gore 1992
Dan Quayle 1988
George H. W. Bush 1980
I won't go into examples for this particular flashback method for UNTIL SCN and UNTIL TIMESTAMP since the same principles apply for FLASHBACK TABLE as they do for FLASHBACK DATABASE above.
SQL> insert into viceprez values ('The Man in the Moon', '2013');
1 row created.
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Joe Biden 2008
Dick Cheney 2000
Al Gore 1992
Dan Quayle 1988
George H. W. Bush 1980
The Man in the Moon 2013
6 rows selected.
SQL> drop table viceprez;
Table dropped.
SQL> select * from viceprez;
select * from viceprez
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
PREZ
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
VICEPREZ BIN$bsEpu+DLQZWxoPngfpkZJQ==$0 TABLE 2012-11-24:22:40:18
SQL> flashback table viceprez to before drop;
Flashback complete.
SQL> show recyclebin;
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
PREZ
VICEPREZ
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Joe Biden 2008
Dick Cheney 2000
Al Gore 1992
Dan Quayle 1988
George H. W. Bush 1980
The Man in the Moon 2013
6 rows selected.
SQL> delete from viceprez where elected = '2013';
1 row deleted.
SQL> select * from viceprez;
NAME ELEC
------------------------- ----
Joe Biden 2008
Dick Cheney 2000
Al Gore 1992
Dan Quayle 1988
George H. W. Bush 1980
There's not a lot of discussion needed with this one. Dropped tables remain in the DBA_RECYCLEBIN and can be recovered from there. For more information about the Recyclebin, refer to the Oracle Documentation.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)