基于Oracle闪回的示例分析

64次阅读

共计 11929 个字符，预计需要花费 30 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下基于 Oracle 闪回的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

Oracle 9i 开始支持闪回，Oracle10g 开始全面支持闪回功能，Oracle11g 有所完善，为大家快速的恢复数据，查询历史数据提供了很大的便捷方法。

本文主要对 Oracle 常用闪回使用做些详细介绍，其中对于不常用的事务和版本闪回，这里就不做介绍

一、Oracle 闪回概述

闪回级别闪回场景闪回技术对象依赖影响数据数据库表截断、逻辑错误、其他多表意外事件闪回 DATABASE 闪回日志、undo 是 DROP 删除表闪回 DROP 回收站 (recyclebin) 是表更新、删除、插入记录闪回 TABLE 还原数据,undo 是查询当前数据和历史数据对比闪回 QUERY 还原数据,undo 否版本查询比较行版本闪回 Version Query 还原数据,undo 否事务查询比较闪回 Transaction Query 还原数据,undo 否归档 DDL、DML 闪回 Archive 归档日志是

二、Oracle 闪回使用详解

1、闪回开启

(1)开启闪回必要条件

a. 开启归档日志

SQL  archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/U01/app/oracle/oradata/testdb/arch
Oldest online log sequence 844
Next log sequence to archive 846
Current log sequence 846
## 如未开启，在 mount 状态执行 alter database archivelog;

b. 设置合理的闪回区

db_recovery_file_dest：指定闪回恢复区的位置

db_recovery_file_dest_size：指定闪回恢复区的可用空间大小

db_flashback_retention_target：指定数据库可以回退的时间，单位为分钟，默认 1440 分钟(1 天), 实际取决于闪回区大小

(2)检查是否开启闪回

SQL  select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

(3)开启闪回

a. 开启归档

mount 状态：alter database archivelog;

b. 设置闪回区

SQL  alter system set db_recovery_file_dest= /home/U01/app/oracle/fast_recovery_area  scope=both;
System altered.
SQL  alter system set db_recovery_file_dest_size=60G scope=both;
System altered.
SQL  alter system set db_flashback_retention_target=4320 scope=both;
System altered.

c. 开启 flashback (10g 在 mount 开启)

SQL  alter database flashback on;
Database altered.

(4)确定闪回开启

SQL  select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

(5)关闭闪回

SQL  alter database flashback off;
Database altered.

2、闪回使用

(1)闪回查询

闪回查询主要是根据 Undo 表空间数据进行多版本查询，针对 v$ 和 x$ 动态性能视图无效，但对 DBA_、ALL_、USER_是有效的

a. 闪回查询

允许用户查询过去某个时间点的数据，用以重构由于意外删除或更改的数据，数据不会变化。

SQL  select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
SQL  delete from scott.dept where deptno=40;
row deleted.
SQL  commit;
Commit complete.
SQL  select * from scott.dept as of timestamp sysdate-10/1440;
DEPTNO DNAME LOC
---------- -------------- -------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
SQL  select * from scott.dept as of timestamp to_timestamp( 2017-12-14 16:20:00 , yyyy-mm-dd hh34:mi:ss 
DEPTNO DNAME LOC
---------- -------------- -------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
SQL  select * from scott.dept as of scn 16801523;
DEPTNO DNAME LOC
---------- -------------- -------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON

b. 闪回版本查询

用于查询行级数据库随时间变化的方法

c. 闪回事务查询

用于提供查看事务级别数据库变化的方法

(2)闪回表(update/insert/delete)

闪回表就是对表的数据做回退，回退到之前的某个时间点，其利用的是 undo 的历史数据，与 undo_retention 设置有关，默认是 14400 分钟（1 天）

同样，sys 用户表空间不支持闪回表, 要想表闪回，需要允许表启动行迁移(row movement)

闪回表示例：

SQL  flashback table scott.dept to timestamp to_timestamp( 2017-12-14 16:20:00 , yyyy-mm-dd hh34:mi:ss 
flashback table scott.dept to timestamp to_timestamp(2017-12-14 16:20:00 , yyyy-mm-dd hh34:mi:ss)
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL  select row_movement from dba_tables where table_name= DEPT  and owner= SCOTT 
ROW_MOVE
--------
DISABLED
SQL  alter table scott.dept enable row movement;
Table altered.
SQL  flashback table scott.dept to timestamp to_timestamp( 2017-12-14 16:20:00 , yyyy-mm-dd hh34:mi:ss 
Flashback complete.
SQL  select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON 
SQL  alter table scott.dept disable row movement;
Table altered.

(3)闪回 DROP(drop table)

当一个表被 drop 掉，表会被放入 recyclebin 回收站，可通过回收站做表的闪回。表上的索引、约束等同样会被恢复

不支持 sys/system 用户表空间对象，可通过 alter system set recyclebin=off; 关闭回收站功能

闪回 DROP 示例：

SQL  select * from t ;
ID NAME
---------- ---------------------------------------
SQL  drop table t;
Table dropped.
SQL  show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$YEh3QcvZdJLgUxyAgQpnVQ==$0 TABLE 2017-12-14:15:02:06
SQL  flashback table t to before drop;
Flashback complete.
SQL  select * from t;
ID NAME
---------- -------------------------------------
4

备注：即使不开始 flashback，只要开启了 recyclebin，那么就可以闪回 DROP 表。

但如果连续覆盖，就需要指定恢复的表名，如果已经存在表，则需要恢复重命名。

SQL  show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$YEh3QcvddJLgUxyAgQpnVQ==$0 TABLE 2017-12-14:15:07:54
T BIN$YEh3QcvcdJLgUxyAgQpnVQ==$0 TABLE 2017-12-14:15:07:27
SQL  flashback table  BIN$YEh3QcvcdJLgUxyAgQpnVQ==$0  to before drop ;
Flashback complete.
SQL  show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$YEh3QcvddJLgUxyAgQpnVQ==$0 TABLE 2017-12-14:15:07:54
SQL  flashback table t to before drop rename to tt;
Flashback complete.

(4)闪回数据库(truncate/ 多表数据变更)

数据库闪回必须在 mounted 状态下进行，基于快照的可以再 open 下进行闪回库

闪回数据库主要是将数据库还原值过去的某个时间点或 SCN，用于数据库出现逻辑错误时，需要 open database resetlogs

a. 全库闪回

数据库闪回示例

SQL  select * from scott.EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
SMITH CLERK 7902 1980-12-17 00:00:00 800 20
ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
KING PRESIDENT 1981-11-17 00:00:00 5000 10
TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
JAMES CLERK 7698 1981-12-03 00:00:00 950 30
FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
rows selected.
SQL  truncate table scott.EMP;
Table truncated.
SQL  shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL  startup mount;
ORACLE instance started.
Total System Global Area 9.4067E+10 bytes
Fixed Size 2263936 bytes
Variable Size 9395242112 bytes
Database Buffers 8.4557E+10 bytes
Redo Buffers 112766976 bytes
Database mounted.
SQL  flashback database to timestamp to_timestamp( 2017-12-14 14:12:46 , yyyy-mm-dd HH24:MI:SS 
Flashback complete.
SQL  alter database open resetlogs;
Database altered.
SQL  select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
SMITH CLERK 7902 1980-12-17 00:00:00 800 20
ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
KING PRESIDENT 1981-11-17 00:00:00 5000 10
TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
JAMES CLERK 7698 1981-12-03 00:00:00 950 30
FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
rows selected.

b. 快照闪回

针对主库和备库都可以创建闪回快照点，然后恢复到指定的快照点，但主库一旦恢复到快照点，备库的同步则需要重新同步

SQL  select * from scott.dept;
 DEPTNO DNAME LOC ADDR
---------- -------------- ------------- ------------------------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
 
SQL  create restore point before_201712151111 guarantee flashback database;
Restore point created.
SQL  create table scott.t as select * from scott.dept;
Table created.
SQL  truncate table scott.t;
Table truncated.
SQL  shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL  startup mount;
ORACLE instance started.
Total System Global Area 9.4067E+10 bytes
Fixed Size 2263936 bytes
Variable Size 9663677568 bytes
Database Buffers 8.4289E+10 bytes
Redo Buffers 112766976 bytes
Database mounted.
SQL  flashback database to restore point before_201712151111;
Flashback complete.
SQL  alter database open resetlogs;
Database altered.
此时主库 scott.t 已不存在：SQL  select * from scott.t;
select * from scott.t
 *
ERROR at line 1:
ORA-00942: table or view does not exist
此时从库的 scott. 依旧存在，主备同步终止
解决方案：在主库创建快照时间点，从库自动停止应用日志，等主库闪回后，重新应用日志即可。如果已经做了上述操作，从库可以选择重建
ALTER DATABASE REGISTER LOGFILE  /xx/xx/archive.dbf

c. 闪回 snapshot standby

此功能在 11GR2 非常实用，可自动创建闪回点、开启闪回日志，可完成线上数据测试后，然后做数据库闪回恢复主备关系

select scn, STORAGE_SIZE ,to_char(time, yyyy-mm-dd hh34:mi:ss) time,NAME from v$restore_point;
select database_role,open_mode,db_unique_name,flashback_on from v$database;
SQL set line 200;
SQL set pagesize 2000;
SQL select database_role,open_mode,db_unique_name,flashback_on from v$database;
DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME FLASHBACK_ON
---------------- -------------------- ------------------------------ ------------------
PHYSICAL STANDBY READ ONLY testdbms NO

SQL select database_role,open_mode,db_unique_name,flashback_on from v$database;
DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME FLASHBACK_ON
---------------- -------------------- ------------------------------ ------------------
SNAPSHOT STANDBY MOUNTED testdbms RESTORE POINT ONLY
SQL alter database open;
Database altered.
SQL select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
SMITH CLERK 7902 1980-12-17 00:00:00 800 20
ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
KING PRESIDENT 1981-11-17 00:00:00 5000 10
TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
JAMES CLERK 7698 1981-12-03 00:00:00 950 30
FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
rows selected.
SQL truncate table scott.emp;
Table truncated.
主库操作：
SQL create table scott.t as select * from scott.dept;
Table created.
SQL select * from scott.t;
DEPTNO DNAME LOC ADDR
---------- -------------- ------------- ------------------------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
备库恢复到物理 standby
SQL shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL startup mount;
ORACLE instance started.
Total System Global Area 9.4067E+10 bytes
Fixed Size 2263936 bytes
Variable Size 9663677568 bytes
Database Buffers 8.4289E+10 bytes
Redo Buffers 112766976 bytes
Database mounted.
SQL ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL shutdown immediate;
ORA-01507: database not mounted

Total System Global Area 9.4067E+10 bytes
Fixed Size 2263936 bytes
Variable Size 9663677568 bytes
Database Buffers 8.4289E+10 bytes
Redo Buffers 112766976 bytes
Database mounted.
Database opened.
## 此时备库的数据已经恢复到转变 snapshot standby 时间点
SQL select database_role,open_mode,db_unique_name,flashback_on from v$database;
DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME FLASHBACK_ON
---------------- -------------------- ------------------------------ ------------------
PHYSICAL STANDBY READ ONLY testdbms NO
SQL select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
SMITH CLERK 7902 1980-12-17 00:00:00 800 20
ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
KING PRESIDENT 1981-11-17 00:00:00 5000 10
TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
JAMES CLERK 7698 1981-12-03 00:00:00 950 30
FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
rows selected.

正文完