数据被不小心truncate，是一件麻烦的事情。但如果开启了闪回功能，则可以使用这个特性快速恢复被truncate的数据
首先，开启闪回数据库：
sys@ABSEED> show parameter flashback
NAME TYPE VALUE
----------------------- -----------------------------------
db_flashback_retention_target integer 1440
sys@ABSEED> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------
NO
1 row selected.
打开数据库闪回功能
sys@ABSEED> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ABSEED> startup mount
ORACLE instance started.
Total System Global Area 488534016 bytes
Fixed Size 2214616 bytes
Variable Size 364905768 bytes
Database Buffers 117440512 bytes
Redo Buffers 3973120 bytes
Database mounted.
sys@ABSEED> alter database flashback on;
Database altered.
sys@ABSEED> alter database open;
Database altered.
sys@ABSEED> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------
YES
1 row selected.
插入一点测试数据
sys@ABSEED> connect testfb/asp_889
Connected.
testfb@ABSEED> select * from cat;
TABLE_NAME TABLE_TYPE
--------------- ---------------------------------
TEST1 TABLE
TEST2_BAK TABLE
2 rows selected.
testfb@ABSEED> select count(*) from TEST2_BAK;
COUNT(*)
----------
11
1 row selected.
testfb@ABSEED> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
30-AUG-16 01.46.47.947331 AM +08:00
1 row selected.
testfb@ABSEED> truncate table TEST2_BAK;
Table truncated.
testfb@ABSEED> insert into TEST2_BAK select * from all_objects;
55625 rows created.
testfb@ABSEED> commit;
Commit complete.
testfb@ABSEED> shutdown immediate
ORA-01031: insufficient privileges
testfb@ABSEED> connect as sysdba
Connected.
sys@ABSEED> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ABSEED> startup mount
ORACLE instance started.
Total System Global Area 488534016 bytes
Fixed Size 2214616 bytes
Variable Size 364905768 bytes
Database Buffers 117440512 bytes
Redo Buffers 3973120 bytes
Database mounted.
sys@ABSEED> flashback database to timestamp to_timestamp('2016-08-30 01:46:47','yyyy- mm-dd hh24:mi:ss');
Flashback complete.
sys@ABSEED> alter database open read only;
Database altered.
sys@ABSEED> connect testfb/asp_889
Connected.
testfb@ABSEED> select * from cat;
TABLE_NAME TABLE_TYPE
--------------- ---------------------------------
TEST1 TABLE
TEST2_BAK TABLE
2 rows selected.
testfb@ABSEED> select count(*) from TEST2_BAK;
COUNT(*)
----------
11
1 row selected.
被truncate的表的数据恢复了，还是11条
testfb@ABSEED> connect as sysdba
Connected.
sys@ABSEED> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ABSEED> startup
ORACLE instance started.
Total System Global Area 488534016 bytes
Fixed Size 2214616 bytes
Variable Size 364905768 bytes
Database Buffers 117440512 bytes
Redo Buffers 3973120 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Completed: ALTER DATABASE MOUNT
Tue Aug 30 01:51:32 2016
ALTER DATABASE OPEN
Errors in file /oracle/diag/rdbms/abseed/abseed/trace/abseed_ora_7516.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: ALTER DATABASE OPEN...
Tue Aug 30 01:52:29 2016
sys@ABSEED> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/archive/abseed/
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
sys@ABSEED> alter database open RESETLOGS;
Database altered.
sys@ABSEED> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/archive/abseed/
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
纯干货，非盈利，爱分享
DBA实战技术，欢迎您！
DBA实战群-Oracle 586444631
DBA实战群-db2 586444547
DBA实战群-MySQL 299903198
DBA实战群-OS-云 299379704
加群注明：微信
长按以下二维码关注我们
