Oracle 备份恢复体系 二 闪回技术

1.误删表的闪回

查询当前用户的回收站是否开启

#切换到用户
conn 用户/密码
#查询当前用户的回收站是否开启
show parameter recyclebin  #value 为on 就是开启

create table t1 as select * from emp;
select * from tab;
drop table t1;
#表删除后进入回收站，名字会发生变化
show recyclebin
#会发现原始名字和回收站内的名字

接下来就开始闪回

flashback table "回收站内的表名” to before drop;

#再次查看用户的表
select * from tab;
#会发现表已经回来了

但是如果在删表的时候加上purge参数，就不会进入回收站

drop table t1 purge;
#purge 是清空回收站的命令，你加上purge就是删表并清空。

2.查询闪回 –从undo中将数据带回来了

flashback table t1 to timestamp to_date('2022-07-24 16:10:00','yyyy-mm-dd hh24:mi:ss');
#将表闪回到某个时间节点

3.闪回数据库–前提归档开启
闪回数据库需要依赖闪回日志

show parameter db_flash
select flashback_on from v$database;#查看是否打开闪回

#设置打开闪回
sqlplus> shutdown immediate;
sqlplus> startup mount;
sqlplus> alter system set db_recovery_file_dest_size=4G;
sqlplus> alter system set db_reovery_file_dest='路径';
sqlplus> alter system set db_flashback_retention_target=2880 scope=both; #默认是1440 一天
sqlplus> alter database flashback on;
sqlplus> alter database open;

SYS@orcl>select flashback_on from v$database;

FLASHBACK_ON
——————
NO

SYS@orcl>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl>startup mount;
ORACLE instance started.

Total System Global Area 6714322944 bytes
Fixed Size 2265944 bytes
Variable Size 3556773032 bytes
Database Buffers 3137339392 bytes
Redo Buffers 17944576 bytes
Database mounted.
SYS@orcl>alter system set db_recovery_file_dest_size=4G; #设置闪回区域大小

System altered.

SYS@orcl>alter system set db_recovery_file_dest='/u01/app/oracle'; #设置闪回路径

System altered.

SYS@orcl>alter system set db_flashback_retention_target=2880 scope=both;

System altered.

SYS@orcl>alter database flashback on; #开启数据库的闪回功能

Database altered.

SYS@orcl>alter database open;

Database altered.

SYS@orcl>

开启了flashback

实验开始

create table t1 as select * from dba_objects;

insert into t1 select * from t1;

commit;

select count(*) from t1;

select current_scn from v$database;

truncate table t1;

create table t2 as select * from dba_objects;

#发现删错 truncate错

startup  force mount;

flashback database to scn scn号；

alter database open resetlogs;

select count(*) from t1;
#t1是有数据的
select count(*) from t2;
#发现t2没有数据

SYS@orcl>
SYS@orcl>create table t1 as select * from dba_objects;

Table created.

SYS@orcl>select count(*) from t1;

  COUNT(*)
----------
     86967

SYS@orcl>insert into t1 select * from t1;

86967 rows created.

SYS@orcl>select count(*) from t1;

  COUNT(*)
----------
    173934

SYS@orcl>select current_scn from v$database; #查找当前的scn号

CURRENT_SCN
-----------
    1445430

SYS@orcl>truncate table t1; #清空表

Table truncated.

SYS@orcl>create table t2 as select * from dba_objects;

Table created.

SYS@orcl>startup force mount;  #强行将数据库启动到挂载状态，数据库没有打开
ORACLE instance started.

Total System Global Area 6714322944 bytes
Fixed Size                  2265944 bytes
Variable Size            3556773032 bytes
Database Buffers         3137339392 bytes
Redo Buffers               17944576 bytes
Database mounted.
SYS@orcl>flashback database to scn 1445430; #闪回到指定的scn号，实际情况是无法得知之前的序列号的

Flashback complete.

SYS@orcl>alter database open resetlogs; #打开数据库

Database altered.

SYS@orcl>select count(*) from t1;

  COUNT(*)
----------
     86967

SYS@orcl>select count(*) from t2;
select count(*) from t2
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SYS@orcl>

关键是如何获取误操作前的正常的scn号呢？还请留言告知！