In order to utilize Flashback you'll need to put your database in ARCHIVELOG mode. Then you can set the DB_FLASHBACK_RETENTION_TARGET parameter that defines the period of time which we want to retain flashback logs, and finally turn Flashback on with an ALTER DATABASE statement. Lets look at the setup.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST='/u02/fra';
System altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
Okey, Flashback is now enabled for this database. We've defined a flasback retension of 4320 minutes (or 72 hours), a recovery file size of 512MB and defined the location for the file recovery area (FRA) as /u02/fra.
Lets see Flashback in action now. You can look at the contents of the recyle bin by querying select DBA_RECYCLEBIN the table.
oracle@nexus6 ~$ sqlplus ben/passwd
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Nov 4 00:41:36 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table test_table(
2 id number(2),
3 name varchar2(30)
4 );
Table created.
SQL> insert into test_table values (1, 'Ben Rockwood');
1 row created.
SQL> insert into test_table values (2, 'Tamarah Rockwood');
1 row created.
SQL> insert into test_table values (3, 'Nova Rockwood');
1 row created.
SQL> insert into test_table values (4, 'Hunter Rockwood');
1 row created.
SQL> select * from test_table;
ID NAME
---------- ------------------------------
1 Ben Rockwood
2 Tamarah Rockwood
3 Nova Rockwood
4 Hunter Rockwood
SQL> drop table test_table;
Table dropped.
SQL> select * from test_table;
select * from test_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> flashback table "test_table" to before drop;
flashback table "test_table" to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL> flashback table "TEST_TABLE" to before drop;
Flashback complete.
SQL> select * from test_table;
ID NAME
---------- ------------------------------
1 Ben Rockwood
2 Tamarah Rockwood
3 Nova Rockwood
4 Hunter Rockwood
SQL>
In this example we logged in as a user (ben) that by default writes to the users tablespace. Alternately, we could have specified the tablespace explicitly and used the sys user for testing (ie: users.table_test instead of table_test). I've created a simple table and populated it with some data. Then we drop the table and verify that it's really gone. To restore the table we simply use the flashback "to before drop" statement. Another query verifies that is was properly restored.
分享到:
相关推荐
ORACLE flashback database测试,主要进行ORACLE flashbackup 闪回整个数据库的实现。
全面总结和解释了oracle flashback技术的使用,优势以及限制
Oracle Flashback 技术总结
NULL 博文链接:https://shihuan830619.iteye.com/blog/1485908
ORACLE FLASHBACK综述.pdf
主要详细讲解了oracle10g以来对于闪回机制的类型及相关的使用方法和场景的介绍,很有用,尤其对于无意中对表数据删除了的恢复较有价值
Oracle Flashback技术总结.比较先进一些,这些内容很丰富的~
Oracle Flashback在医院数据恢复中的应用.pdf
oracle flashback特性(闪回语句,闪回表,闪回数据库).doc
Oracle 10g High Availability-Flashback技術介紹
FLASHBACK DATABASE flashback data1base闪回到过去的某一时刻 闪回点之后的所有工作都将丢失 必须使用resetlogs创建新的场景并打开数据库(一旦resetlogs之后,将不能再闪回至resetlogs之前的时间点) .......
必须设定undo保留时间足够大以能够重构需要闪回的数据 ALTER SYSTEM SET UNDO_RETENTION=; seconds值是undo数据保持的秒数。 Flashback view是由undo retention interval来限制的。
Oracle_Flashback_技术_总结.pdf
NULL 博文链接:https://bestxiaok.iteye.com/blog/1129883
如何正确删除Oracle归档日志,oralce归档日志清理.
oracle数据库FLASHBACK系列功能介绍
Flashback 技术是以Undo segment中的内容为基础的, 因此受限于UNDO_RETENTON参数。要使用flashback 的特性,必须启用自动撤销管理表空间。
Work from the command line or Oracle Enterprise Manager, automate the backup process, perform Oracle Flashback recoveries, and integrate cloud computing technology. This authoritative resource also ...
基本信息 出版社: 清华大学出版社; 第1版 (2009年1月1日) 平装: 594页 语种: 简体中文 开本: 16 ... 使用最新的Oracle Database 11g工具——Oracle Total Recall和Oracle Flashback Data Archive等。
基本信息 出版社: 清华大学出版社; 第1版 (2009年1月1日) 平装: 594页 语种: 简体中文 开本: 16 ... 使用最新的Oracle Database 11g工具——Oracle Total Recall和Oracle Flashback Data Archive等。