oracle flashback 知识点

Oracle闪回数据库实战

最新推荐文章于 2025-09-10 19:11:59 发布

weivi001

最新推荐文章于 2025-09-10 19:11:59 发布

阅读量415

点赞数

CC 4.0 BY-SA版权

文章标签： oracle

本文链接：https://blog.youkuaiyun.com/weivi001/article/details/45330819

本文演示了Oracle数据库中使用闪回技术恢复数据的过程。包括启动数据库到mount状态、设置归档模式、启用闪回功能、查询闪回 SCN 和时间、执行数据库及表级别的闪回操作等关键步骤。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 318769536 bytes
Database Buffers 96468992 bytes
Redo Buffers 6094848 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8

SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------

891640 27-APR-15

SQL> flashback database to scn 891640; 闪回到scn891640

Flashback complete.

alter database open resetlogs;

练习

SQL> conn scott/weiwei;
已连接。
SQL> create table dept2 as select * from dept;

表已创建。
SQL> select * from dept2;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

21:51:57 SQL> delete from dept2 where deptno=40;

已删除 1 行。

21:52:30 SQL> commit;

提交完成。

21:52:38 SQL> alter table dept2 enable row movement;

表已更改。

21:55:19 SQL> flashback table dept2 to timestamp to_timestamp('2015-04-28 21:51:56','yyyy-mm-ddhh24:mi:ss');

闪回完成。

21:57:31 SQL> select * from dept2;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON 删除记录回来了