基于Oracle閃回的示例分析

197次閱讀

共計 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.

正文完