ORACLE 数据库闪回全面总结-阿里云开发者社区

------------------------------------------------------------------------------------------------------------------------------

-- 误修改或是删除的还原操作

-- 闪回版本查询

select versions_starttime,versions_endtime,versions_xid,versions_operation,id from t1 versions between timestamp minvalue and maxvalue;

-- 获得指定时间的SCN

select timestamp_to_scn(to_timestamp('2018-11-13 09:00:00','YYYY-MM-DD HH24:MI:SS')) as scn from dual ;

-- 查询当前数据库的SCN

select dbms_flashback.get_system_change_number scn from dual;

-- 将emp表中指定的scn点的数据取出

select * from emp AS OF SCN 15523367939370;

-- 然后可以根据这个数据进行还原操作

insert into emp select * from emp AS OF SCN 15523367939370;

------------------------------------------------------------------------------------------------------------------------------

-- 闪回表：

grant FLASHBACK ANY TABLE to user;

select versions_xid,versions_starttime,versions_endtime,versions_operation,salary

from employees versions between scn minvalue and maxvalue where employee_id=200;

alter table employees enable row movement;

flashback table employees to timestamp to_timestamp('18-AUG-17 07.48.58','dd-MON-rr hh24.mi.ss');

select employee_id,salary from employees where employee_id=200;

select versions_xid,versions_starttime,versions_endtime,versions_operation,salary from employees versions between scnminvalueand maxvalue where employee_id=200;

alter table employees modify salary number(10,2);

flashback table employees to timestamp to_timestamp('18-AUG-17 07.59.47','dd-MON-rr hh24.mi.ss');

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

------------------------------

ALTER TABLE employees_test ENABLE ROW MOVEMENT;

flashback table employees_test to timestamp to_timestamp ('2018-08-02 19:12:20','yyyy-mm-dd hh24:mi:ss');

-- flashback table emp to before drop | scn | timestamp ...

drop table employees_test;

flashback table employees_test to before drop;

flashback table employees_test to before drop rename to employees_demo;

-- recyclebin

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'recyclebin';

show parameters recyclebin; -- 如果不是为ON，使用alter system修改需加入deferred，当前session不生效

alter system set recyclebin=off deferred;

alter session set recyclebin=on; -- 使用alter session 修改当前session生效，其它会话不生效

-- recyclebin info

SELECT * FROM RECYCLEBIN;

SELECT * FROM USER_RECYCLEBIN;

SELECT * FROM DBA_RECYCLEBIN;

select 'flashback table '||owner||'.'||original_name||' to before drop'|| ';' from dba_recyclebin;

------------------------------------------------------------------------------------------------------------------------------

-- 闪回查询

select employee_id,salary from employees where employee_id=200;

select systimestamp from dual;

update employees set salary=salary*1.1 where employee_id=200;

commit;

select employee_id,salary from employees where employee_id=200;

select employee_id,salary from employees as of timestamp to_timestamp('2019-08-14 07:48:38','yyyy-mm-dd hh24:mi:ss') where employee_id=200;

update employees set salary=(select salary from employees as of timestamp to_timestamp('2019-08-14 07:48:38','yyyy-mm-dd hh24:mi:ss') where employee_id=200) where employee_id=200;

select employee_id,salary from employees where employee_id=200;

------------------------------------------------------------------------------------------------------------------------------

-- 闪回版本查询

-- time

select versions_xid,versions_starttime,versions_endtime,versions_operation,salary

from employees versions between timestamp to_timestamp('2019-08-14 07:48:38','yyyy-mm-dd hh24:mi:ss') and

systimestamp

where employee_id=200;

-- scn

select versions_xid,versions_starttime,versions_endtime,versions_operation,salary

from employees versions between scn minvalue and maxvalue

where employee_id=200;

------------------------------------------------------------------------------------------------------------------------------

-- 闪回事务查询

alter database add supplemental log data;

update employees set salary=salary*1.10 where department_id=20;

select logon_user,operation,undo_sql from flashback_transaction_query where logon_user='HR' order by start_timestamp desc;

------------------------------------------------------------------------------------------------------------------------------

-- 闪回事务

alter database add supplemental log data

alter database add supplemental log data(primary key) columns

grant execute on dbms_flashback to user

grant select any transaction to user

SQL>conn hr/hr

SQL> create table test (id number,name varchar2(10));

SQL> insert into test values(1,'A');

SQL> insert into test values(2,'B');

SQL> commit ;

SQL> insert into test values(3,'C');

SQL> commit ;

SQL> select * from test;

SQL>conn / as sysdba

SQL> select versions_xid,versions_starttime,versions_endti me,versions_operation,id ,name from hr.test versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTTIME VERSIONS_ENDTIME V ID NAME

---------------- ------------------------------ ------------------------------ - ---------- -------------

07001A00FC040000 18-AUG-18 02.18.31 PM I 3 C

0A00140002050000 18-AUG-18 02.18.25 PM I 2 B

0A00140002050000 18-AUG-18 02.18.25 PM I 1 A

SQL>declare

v_xid XID_ARRAY;

begin

v_xid:=sys.XID_ARRAY('0A00140002050000');

dbms_flashback.transaction_backout(1,v_xid);

end;

SQL> select * from hr.test;

------------------------------------------------------------------------------------------------------------------------------

-- Oracle Total Recall（闪回数据归档）

SQL> create tablespace ts_fda datafile '/home/oracle/ts_fds01.dbf' size 50m;

SQL> grant Flashbave archive administer to user;

SQL> create flashback archive fda1 tablespace ts_fda retention 10 year;

SQL> create flashback archive fda2 tablespace ts_fda quota 20m retention 5 year;

SQL> alter flashback archive fda1 set default;

SQL>create flashback archive default fda3 tablespace ts_fda retention 10 year;

SQL> conn hr/hr

SQL> alter table dept flashback archive;

SQL> alter table emp flashback archive fda2;

SQL> alter table emp no flashback archive;

SQL> select systimestamp from dual;

SQL> select * from dba_segments where tablespace_name='TS_FDA';

SQL> truncate table dept;

SQL> select owner,segment_name,partition_name,segment_type from dba_segments where tablespace_name='TS_FDA';

SQL> select * from dept;

SQL> select * from dept as of timestamp to_timestamp('2018-08-15 11:30:57','yyyy-mm-dd hh24:mi:ss');

SQL> alter flashback archive fda1 modify tablespace ts_fda quota 50M;

SQL> create tablespace ts_fda2 datafile '/home/oracle/ts_fds201.dbf' size 20m;

SQL> alter flashback archive fda1 add tablespace ts_fda2 quota 10m;

SQL> alter flashback archive fda1 modify retention 2 year;

SQL> alter flashback archive fda1 purge before timestamp (systimestamp-interval '1' day);

SQL> drop flashback archive fda1;

SQL> select * from dept as of scn 3189895;

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

SQL> create table hr.rpt (id number,name varchar2(20))

partition by range(id)

(partition p1 values less than (100) tablespace example,

partition p2 values less than (200) tablespace example,

partition p3 values less than (maxvalue) tablespace example

);

SQL> create table hr.source(id number,name varchar2(20)) tablespace example;

SQL> insert into hr.rpt values(80,'A');

SQL> insert into hr.rpt values(180,'B');

SQL> insert into hr.rpt values(280,'C');

SQL> insert into hr.source values(50,'sztech');

SQL> commit;

SQL> alter table hr.rpt exchange partition p1 with table hr.source;

SQL> alter table hr.rpt flashback archive fda1;

SQL> alter table hr.rpt exchange partition p1 with table hr.source;

ERROR at line 1:

ORA-55610: Invalid DDL statement on history-tracked table

SQL> exec dbms_flashback_archive.disassociate_fba('HR','RPT');

SQL> alter table hr.rpt exchange partition p1 with table hr.source;

SQL> exec dbms_flashback_archive.reassociate_fba('HR','RPT');

------------------------------------------------------------------------------------------------------------------------------

-- 闪回删除（flashback drop)

flashback table table_name to before drop rename to new_tablename;

rename table to new_tablename;

purge table <table_name> | index <index_name>;

purge tablespace <ts_name> | user <user_name>;

purge user_recyclebin | dba_recyclebin;

show recyclebin;

select * from recyclebin_table_name;

drop table table_name purge；

drop tablespace ts_name including contents；

drop user user_name cascade ；

alter system set recyclebin=off scope=spfile;

------------------------------------------------------------------------------------------------------------------------------

-- 闪回数据库：

SQL> show parameter db_recovery_file_dest

SQL> show parameter db_flashback_retention_target

SQL> select flashback_on from v$database;

SQL> shutdown immediate

SQL> startup mount

SQL> archive log list

SQL> alter system set db_flashback_retention_target=2880; -- 2 days

SQL> alter database flashback on;

SQL> alter database open;

SQL> conn hr/hr

SQL> create table emp as select * from employees;

SQL> select systimestamp from dual;

SQL> update emp set salary=0 ;

SQL> commit;

SQL> truncate table emp;

SQL> conn / as sysdba

SQL> shutdown immediate

SQL> startup mount

SQL> flashback database to timestamp to_timestamp('18-AUG-18 10.18.02','dd-mon-rr hh24:mi:ss');

SQL> alter database open read only;

SQL> select count(*) from hr.emp where salary>0;

-- 如果上述恢复没有达到预期，还可以继续重启到mount再进行数据库闪回

-- 也可以在rman中执行数据库闪回

RMAN> flashback database to time "to_date('18-AUG-18 10.18.02','dd-mon-rr hh24:mi:ss')";

RMAN> sql "alter database open read only";

-- 如果上述闪回确认没有问题，请重启后以resetlogs方式打开库

SQL> shutdown immediate

SQL> startup mount

SQL> alter database open resetlogs; -- resetlogs打开后，日志序列号初始化从1开始

SQL> archive log list;

-- 监控闪回数据库的相关信息

select * from v$flashback_database_log;

select * from v$flashback_database_stat;

-- 查看快速恢复区的空间使用情况

SQL> select * from v$flash_recovery_area_usage;

-- 保证闪回数据库还原点

SQL> create restore point before_upgrad guarantee flashback database;

-- 下面的例子描述了这样一个场景：

-- 用户需要一个和生产系统一样或者类似的测试环境，但是单纯的搭建一个环境显然是不值得的，对于部署了dataguard环境的生产系统来说，

-- 我们可以选择把备库临时激活，以读写的方式打开，用于测试的需要，等测试结束后，把备库通过flashback database技术闪回到激活之前的时间点，

-- 从新的加入到dataguard配置环境中。

-- 第1 步准备要被激活的物理备数据库。

-- 1 ．检查数据库的flashback database模式和闪回日志存放的目录。

SQL> select flashback_on from v$database;

SQL> show parameter db_recovery_file

-- 2 ．由于备库在应用日志，所以要取消日志应用，并创建一个激活之前的还原点。

SQL> select process,status from v$managed_standby;

SQL> recover managed standby database cancel;

SQL> create restore point test_scn guarantee flashback database;

-- 第2 步准备主数据库

-- 1 ．归档当前日志文件。

-- 在主数据库上，切换日志使得还原点（在步骤 1 中创建）的 SCN 将在物理备数据库上被归档：

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; -- 当使用备重做日志文件时，这个步骤是必须的以确保数据库能被正确地闪回到还原点。

-- 2 ．延迟指向将被激活的备库的日志归档目的地。

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

SQL> show parameter log_archive_dest_state_2

-- 第3 步激活物理备数据库。

-- 在物理备数据库上，执行下述步骤：

-- 1 ．激活物理备数据库并打开到open状态：

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

SQL> ALTER DATABASE OPEN;

-- 第4 步使用激活的数据库用于报表或测试。

-- 一旦备数据库已经被激活，你能运行报表工具或执行其它测试并激活几天甚至几周，独立于主数据库。

-- 警告：当数据库被激活时，它不从主数据库接收重做数据库并不能提供灾难保护。建议至少有两个物理备数据库参与配置，使得主数据库保持对数据丢失的保护。

-- 第5 步回复激活的数据库回到物理备数据库。

-- 在你完成测试之后，你需要重新与主数据库同步激活的数据库。在激活的数据库上执行

-- 下面语句以快速闪回它到保障的还原点并将它重新与主数据库同步：

SQL> shutdown immediate

SQL> startup mount

SQL> flashback database to restore point test_scn;

SQL> alter database convert to physical standby;

SQL> select status from v$instance;

SQL> shutdown immediate

SQL> startup mount

SQL> recover managed standby database disconnect;

-- 第6 步重新允许归档到物理备数据库目的地。

-- 在主数据库上，执行下面语句来重新允许归档到物理备数据库：

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

-- 第7 步测试dataguard环境工作正常

-- 1.在主库删除表空间usertest，并切换日志

SQL> select name from v$datafile;

SQL> drop tablespace usertest including contents and datafiles;

SQL> alter system switch logfile;

-- 2.在备库观察日志操作是否正确应用，下面的查询可以看到文件应经被删除，说明主库的日志应用到备库。

SQL> select name from v$datafile;

------------------------------------------------------------------------------------------------------------------------------

-- 使用 TRANSACTION_BACKOUT 过程

-- 在继续讨论此功能前，首先应了解一个概念：事务的依赖性。比如，两个事务TX1和TX2，若符合以下3个条件的任意一个就可以认为TX2依赖TX1：

（1）WAW依赖（Write After Write），即在TX1修改了表的某行之后，TX2又修改了同一行。

（2）主键依赖，即在一张拥有主键的表中TX1首先删除了一行，之后TX2又插入了具有相同主键值的另一行。

（3）外建依赖，即由于TX1的修改（insert或update）而产生了新的可被外键参考的字段值，之后TX2修改（insert或update）外键字段时利用了TX1所产生的字段值。

-- 了解事务依赖性有助于解决在撤销事务时遇到的矛盾，以主键依赖为例，试想若直接将事务TX1撤销并且不理会事务TX2，岂不是会出现主键值重复的行！

-- TRANSACTION_BACKOUT存储过程的OPTIONS参数就是为了解决事务依赖性问题而存在的，在该参数上管理员可以使用4种撤销事务的方案，假设被撤销的事务是TX1，

-- 若其具有依赖事务，则称为TX2：

（1）NOCASCADE TX1不可以被任何其他事务依赖（即TX2不存在），否则撤销操作报错。

（2）CASCADE 将TX1连同TX2一起撤销。

（3）NOCASCADE_FORCE 忽略TX2，直接执行TX1的撤销SQL将TX1撤销，如果没有约束上的冲突，操作将成功，否则约束报错导致撤销操作失败。

（4）NONCONFILICT_ONLY 在不影响TX2的前提下，撤销TX1的修改。与NOCASCADE_FORCE的不同点在于会首先过滤一下TX1的撤销SQL，确保它们不会作用在TX2修改的行上。

-- 使用 DBMS_FLASHBACK.BACKOUT_TRANSACTION 的步骤如下：

（1）将需要撤销的事务的事务号或事务名载入对应的VARRAY集合变量。

（2）以NOCASCADE方式调用BACKOUT_TRANSACTION。如果报错，再从另外3种方式中选择一个调用BACKOUT_TRANSACTION。

（3）查看闪回事务操作的报告。

（4）最后决定提交或回滚。

REM eg.

create table flash_test2 (empno varchar2(20),salary number);

insert into flash_test2 values('1',10000);

insert into flash_test2 values('2',13000);

insert into flash_test2 values('3',8000);

insert into flash_test2 values('4',5000);

commit;

transaction T1

update flash_test2 set salary=salary*3;

transaction T2

update flash_test2 set salary=salary*1.1 where empno='1';

REM In the example above, because of the wrong operation in T1, the operation in T2 also got the wrong result. So we need to undo T1 and T2 together.

REM Query transaction number.

select distinct xid,commit_scn

from flashback_transaction_query

where table_owner='ADMIN' and

table_name='FLASH_TEST2' and

commit_timestamp > systimestamp - interval '15' minute

order by commit_scn;

REM Because T1 T2 has a WAW dependency, options select cascade.

declare

xids sys.xid_array;

begin

xids := sys.xid_array('05001200412C0000');

dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);

end;

commit;

------------------------------------------------------------------------------------------------------------------------------

-- 闪回注意事项

统计信息不被闪回

当前的索引和依赖的对象会保留

不能闪回系统表（sys用户的表）

不能跨DDL（要闪回的时间点以后如果有执行过DDL时不能被闪回）

闪回也要生成UNDO和REDO

sys用户的表无法闪回

闪回技术只能保护非系统（non-SYSTEM）表空间中的表,而且这些表还必须存放在本地管理的表空间中。尽管在一个表被删除时，

依赖于该表的绝大多数对象也受到回收站的保护，但是位图连接索引、引用完整性约束等不受到回收站的保护。

-- 闪回归档注意事项

闪回归档的限制

存在混合列压缩的表不支持闪回归档（比如ORACLE一体机中支持混合列压缩）

分离历史表和基表后，历史表的不可变性无法保证

历史表不具有可传输性

注意事项

使用SCN号进行历史数据的精确查看

查询历史数据时使用当前服务器的相关设置（比如国家语言和字符集等）

无法查看动态视图的历史数据

可以查看静态视图的历史数据

闪回归档相关视图

*_flashback_archive: -- 闪回归档区信息

*_flashback_archive_ts: -- 闪回归档区所使用表空间信息

*_flashback_archive_tables: -- 所有已经开启闪回归档的表的信息

-- 闪回数据库原理步骤：

主要解决数据库的业务逻辑错误

原理：

通过RVWR后台进程利用闪回日志闪回数据库到过去某个时间点

然后再利用REDO进行前滚RECOVERY

类似于利用备份执行不完整恢复

步骤：

配置快速恢复区（用于存放闪回日志）

设置可以闪回到之前的时间

开启数据库闪回

-- 设置保证还原点的前提条件

数据库的兼容性必须是10.2及以上的版本

数据库必须打开归档

必须存在从闪回还原点开始的归档日志用于数据库闪回

必须要配置好快速恢复区

-- 数据库闪回注意事项：

执行闪回以read only方式打开数据进行数据验证

验证通过后重启数据库以resetlogs方式打开

"闪回"与"恢复"是两种对立的概念

使用to before resetlogs 来闪回到最后一次曾经以resetlogs方式打开之前的时间点

flashback retention target无法绝对保证能闪回成功，因为快速恢复区没有空间时，闪回日志会自动被删除

以下几种方式无法执行数据库闪回

①数据库控制文件被还原或重建

②表空间被删除

③数据文件被手动的修改了大小

------------------------------------------------------------------------------------------------------------------------------

ORACLE 数据库闪回全面总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像