Oracle误删数据的恢复

背景

在数据库管理中，误删数据是一个常见且令人头疼的问题。无论是因为操作失误还是脚本错误，误删的数据可能会导致业务中断和数据丢失。然而，Oracle数据库提供了一系列强大的恢复功能，包括Undo表空间、回收站和闪回数据库
，帮助DBA快速恢复误删的数据。

前提条件

Flashback需要打开最小补全日志.
alter database add supplemental log data;  

使用如下命令来查看补全日志是否被打开.
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;  
  
SUPPLEME SUP SUP SUP SUP  
-------- --- --- --- ---  
YES  NO  NO  NO  NO  

使用Flashback 需要当前用户具有select any transaction 权限.

基于undo

Flashback Transaction Query

-- Flashback Transaction Query可以查出事务的操作时间, xid等关键信息.

SQL> conn scott/tiger
Connected.

SQL> delete from scott.emp1 where EMPNO<7600;

4 rows deleted.

SQL> commit;

Commit complete.


-- 通过时间
select eid,eitime,eutime,numday, versions_xid, versions_startscn, versions_endscn,  
to_char(versions_starttime,'yy/mm/dd hh24:mi:ss') as startime,   
to_char(versions_endtime,'yy/mm/dd hh24:mi:ss') as endtime,   
versions_operation  
from scott.loguser versions BETWEEN timestamp to_timestamp('2024-05-14 19:50:00','yyyy-mm-dd hh24:mi:ss') and to_timestamp('2024-05-14 20:00:00','yyyy-mm-dd hh24:mi:ss') where eid > 0 and versions_operation is not null;

-- 通过scn
select current_scn from v$database;

CURRENT_SCN
-----------
  256991992

select eid,eitime,eutime,numday, versions_xid, versions_startscn, versions_endscn,  
to_char(versions_starttime,'yy/mm/dd hh24:mi:ss') as startime,   
to_char(versions_endtime,'yy/mm/dd hh24:mi:ss') as endtime,   
versions_operation  
from scott.loguser versions between scn 256991992 and 256999992 where eid > 0 and versions_operation is not null;


-- 查看误删除语句的VERSIONS_XID
     EMPNO VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN STARTIME          ENDTIME           V
---------- ---------------- ----------------- --------------- ----------------- ----------------- -
      7566 0700170014480200         256991454                 24/05/14 19:55:10                   D
      7521 0700170014480200         256991454                 24/05/14 19:55:10                   D
      7499 0700170014480200         256991454                 24/05/14 19:55:10                   D
      7369 0700170014480200         256991454                 24/05/14 19:55:10                   D


-- 根据VERSIONS_XID找出回滚sql,指向所有的undo sql
select operation, undo_sql from flashback_transaction_query   
where xid ='0700170014480200' ;

OPERATION
--------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DELETE
insert into "SCOTT"."EMP1"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO","SEX") values ('7566','JONES','MANAGER','7839',TO_DATE('1981-04-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'2975',NU
LL,'20','female');

DELETE
insert into "SCOTT"."EMP1"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO","SEX") values ('7521','WARD','SALESMAN','7698',TO_DATE('1981-02-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1250','5
00','30','female');

DELETE
insert into "SCOTT"."EMP1"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO","SEX") values ('7499','ALLEN','SALESMAN','7698',TO_DATE('1981-02-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1600','
300','30','female');

DELETE
insert into "SCOTT"."EMP1"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO","SEX") values ('7369','SMITH','CLERK','7902',TO_DATE('1980-12-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'800',NULL,
'20','male');

flahback query

-- flahback query查看60分钟前的数据（利用多版本读一致性从undo tablesapce 读取操作前的记录数据）
SQL> select * from SCOTT.EMP1 as of timestamp sysdate-20/(24*60) where empno<7640;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO SEX
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20 male
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30 female
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30 female
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20 female

SQL> select * from SCOTT.EMP1 as of timestamp TO_TIMESTAMP('2024-05-14 19:55:10', 'YYYY-MM-DD HH24:MI:SS') where empno<7640;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO SEX
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20 male
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30 female
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30 female
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20 female

-- 回滚数据
insert into SCOTT.EMP1 select * from SCOTT.EMP1 as of timestamp to_timestamp('2024-05-14 19:55:10', 'YYYY-MM-DD HH24:MI:SS') where empno<7640;

flashback table 恢复delete

-- delete全表恢复（基于回滚数据(undo data)来实现，ddl和统计信息不能恢复）
SQL> select * from t;

         C
----------
         0
         0

SQL> delete from t;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table t enable row movement;

Table altered.

SQL> flashback table t to timestamp to_timestamp('2024-05-14 19:00:00','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> select * from t;

         C
----------
         0
         0

基于回收站

恢复drop的表

SQL> select OBJECT_NAME,ORIGINAL_NAME,OPERATION from recyclebin where ORIGINAL_NAME='T';

OBJECT_NAME
--------------------------------------------------------------------------------
ORIGINAL_NAME
--------------------------------------------------------------------------------
OPERATION
---------------------------
BIN$7WWDt6uVTczgU3QQzQoM2Q==$0
T
DROP


SQL> select * from t;
select * from t
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> flashback table t to before drop;

Flashback complete.

SQL> select * from t;

         C
----------
         0
         0

## 还需要单独创建索引，主键等

基于闪回恢复区

-- 配置闪回恢复区：
-- 设置闪回恢复区的路径和大小，以存储闪回日志。

ALTER SYSTEM SET db_recovery_file_dest = '/path/to/recovery/area';
ALTER SYSTEM SET db_recovery_file_dest_size = 10G;  -- 设置闪回恢复区大小

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 SCOPE=BOTH; -- 设置保留时间为1440分钟（1天）

-- 启用闪回数据库功能
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

-- 查看最久的scn和时间
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
           256452918 2024-05-14 11:56:26

-- 启动到mount
STARTUP MOUNT;
-- 闪回到10分钟前
FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);
-- 闪回到指定scn
flashback database to scn 256452918;

总结

误删数据是数据库管理中的常见问题，但通过Oracle提供的Undo表空间、回收站和闪回数据库等恢复功能，我们可以有效地应对和恢复误删的数据。这不仅提高了数据安全性，还保障了业务的连续性和可靠性。掌握这些恢复技巧，DBA可以更加从容应对意外的发生，确保数据库系统的稳定运行。

「欢迎关注我们的公众号，获取更多技术分享与经验交流。」