利用闪回恢复truncate表的数据

最新推荐文章于 2025-07-05 16:19:10 发布

原创最新推荐文章于 2025-07-05 16:19:10 发布 · 6.8k 阅读

7 ·

CC 4.0 BY-SA版权

Oracle-Backup/Recovery 专栏收录该内容

11 篇文章

订阅专栏

当误操作使用truncate导致数据丢失时，由于truncate属于DDL，无法通过闪回查询恢复。本文介绍了如何通过数据库闪回功能，结合一系列步骤，包括创建测试表、设置闪回、检查条件、模拟数据丢失、闪回至数据存在状态、导出数据、重启并恢复到闪回前状态，最终成功恢复被truncate的表数据。

因为truncate不是DML语句，是DDL语句，不能使用闪回查询的方式恢复表数据，这里介绍一种通过flashback database的方式恢复数据的方法。

1. 创建测试表

SQL> create table t as select * from all_objects where rownum<11;

Table created.

SQL> select object_id from t where rownum<11;

 OBJECT_ID
----------
       100
       116
       117
       280
       365
       367
       368
       370
       371
       373

10 rows selected.

2. 开始数据库闪回

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

Total System Global Area  776646656 bytes
Fixed Size                  2257272 bytes
Variable Size             486542984 bytes
Database Buffers          285212672 bytes
Redo Buffers                2633728 bytes
Database mounted.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   4
Current log sequence           4
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

3. 检查闪回数据库的必要条件

SQL> show parameter db_recovery

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
/u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size           big integer
4182M
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   4
Current log sequence           4
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
YES

4. 模拟truncate丢失数据

SQL> select count(*) from t;

  COUNT(*)
----------
        10

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-01-07 15:00:18

SQL> truncate table t;

Table truncated.

5. 闪回数据库到数据仍然存在的时间点

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

Total System Global Area  776646656 bytes
Fixed Size                  2257272 bytes
Variable Size             486542984 bytes
Database Buffers          285212672 bytes
Redo Buffers                2633728 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2017-01-07 15:00:18','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

6. 闪回数据库之后，可以通过alter database open resetlogs的方式打开，但闪回时间之后的数据就会全部丢失。这里用read only的模式打开数据库

SQL> alter database open read only;

Database altered.

7. 导出被truncate表的数据

[oracle@centos6 ~]$ exp test_fb/oracle file=t.dmp tables=t;

Export: Release 11.2.0.4.0 - Production on Sat Jan 7 15:07:10 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T         10 rows exported
Export terminated successfully without warnings.

8. 重启数据库，并恢复到闪回之前的时间点

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

Total System Global Area  776646656 bytes
Fixed Size                  2257272 bytes
Variable Size             486542984 bytes
Database Buffers          285212672 bytes
Redo Buffers                2633728 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.
SQL> select count(*) from t; #此时t表仍然没有数据

  COUNT(*)
----------
         0

9. 导入t表的数据

[oracle@centos6 ~]$ imp test_fb/oracle file=t.dmp tables=t ignore=y;

Import: Release 11.2.0.4.0 - Production on Sat Jan 7 15:09:54 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing TEST_FB's objects into TEST_FB
. importing TEST_FB's objects into TEST_FB
. . importing table                            "T"         10 rows imported
Import terminated successfully without warnings.

10. 验证数据已经恢复

SQL> select count(*) from t;

  COUNT(*)
----------
        10