主頁(yè) > 知識(shí)庫(kù) > 基于Oracle閃回詳解(必看篇)

基于Oracle閃回詳解(必看篇)

熱門(mén)標(biāo)簽:呼倫貝爾智能手機(jī)地圖標(biāo)注 安陽(yáng)外呼系統(tǒng)免費(fèi) 400電話是不是免費(fèi)申請(qǐng) 海南400電話哪里辦理 分布式呼叫中心 濟(jì)南地圖標(biāo)注公司 南寧人工智能電銷機(jī)器人費(fèi)用 貴陽(yáng)電話外呼系統(tǒng)哪家好 圖像地圖標(biāo)注

Oracle 9i 開(kāi)始支持閃回,Oracle10g開(kāi)始全面支持閃回功能,Oracle11g有所完善,為大家快速的恢復(fù)數(shù)據(jù),查詢歷史數(shù)據(jù)提供了很大的便捷方法。

本文主要對(duì)Oracle常用閃回使用做些詳細(xì)介紹,其中對(duì)于不常用的事務(wù)和版本閃回,這里就不做介紹

一、Oracle閃回概述

閃回級(jí)別 閃回場(chǎng)景 閃回技術(shù) 對(duì)象依賴   影響數(shù)據(jù)
數(shù)據(jù)庫(kù) 表截?cái)?、邏輯錯(cuò)誤、其他多表意外事件 閃回DATABASE 閃回日志、undo
DROP 刪除表 閃回DROP 回收站(recyclebin)
更新、刪除、插入記錄 閃回TABLE 還原數(shù)據(jù),undo
查詢 當(dāng)前數(shù)據(jù)和歷史數(shù)據(jù)對(duì)比 閃回QUERY 還原數(shù)據(jù),undo
版本查詢 比較行版本 閃回Version Query 還原數(shù)據(jù),undo
事務(wù)查詢 比較 閃回Transaction Query 還原數(shù)據(jù),undo
歸檔 DDL、DML 閃回Archive 歸檔日志

二、Oracle閃回使用詳解

1、閃回開(kāi)啟

(1)開(kāi)啟閃回必要條件

a.開(kāi)啟歸檔日志

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

##如未開(kāi)啟,在mount狀態(tài)執(zhí)行alter database archivelog;

b.設(shè)置合理的閃回區(qū)

db_recovery_file_dest:指定閃回恢復(fù)區(qū)的位置

db_recovery_file_dest_size:指定閃回恢復(fù)區(qū)的可用空間大小

db_flashback_retention_target:指定數(shù)據(jù)庫(kù)可以回退的時(shí)間,單位為分鐘,默認(rèn)1440分鐘(1天),實(shí)際取決于閃回區(qū)大小

(2)檢查是否開(kāi)啟閃回

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

(3)開(kāi)啟閃回

a.開(kāi)啟歸檔

mount狀態(tài):alter database archivelog;

b.設(shè)置閃回區(qū)

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.開(kāi)啟flashback (10g在mount開(kāi)啟)

SQL> alter database flashback on;
Database altered.

(4)確定閃回開(kāi)啟

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

(5)關(guān)閉閃回

SQL> alter database flashback off;
Database altered.

2、閃回使用

(1)閃回查詢

閃回查詢主要是根據(jù)Undo表空間數(shù)據(jù)進(jìn)行多版本查詢,針對(duì)v$和x$動(dòng)態(tài)性能視圖無(wú)效,但對(duì)DBA_、ALL_、USER_是有效的

a.閃回查詢

允許用戶查詢過(guò)去某個(gè)時(shí)間點(diǎn)的數(shù)據(jù),用以重構(gòu)由于意外刪除或更改的數(shù)據(jù),數(shù)據(jù)不會(huì)變化。

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 hh24: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.閃回版本查詢

用于查詢行級(jí)數(shù)據(jù)庫(kù)隨時(shí)間變化的方法

c.閃回事務(wù)查詢

用于提供查看事務(wù)級(jí)別數(shù)據(jù)庫(kù)變化的方法

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

閃回表就是對(duì)表的數(shù)據(jù)做回退,回退到之前的某個(gè)時(shí)間點(diǎn),其利用的是undo的歷史數(shù)據(jù),與undo_retention設(shè)置有關(guān),默認(rèn)是14400分鐘(1天)

同樣,sys用戶表空間不支持閃回表,要想表閃回,需要允許表啟動(dòng)行遷移(row movement)

閃回表示例:

SQL> flashback table scott.dept to timestamp to_timestamp('2017-12-14 16:20:00','yyyy-mm-dd hh24:mi:ss');
flashback table scott.dept to timestamp to_timestamp('2017-12-14 16:20:00','yyyy-mm-dd hh24: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 hh24: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)

當(dāng)一個(gè)表被drop掉,表會(huì)被放入recyclebin回收站,可通過(guò)回收站做表的閃回。表上的索引、約束等同樣會(huì)被恢復(fù)

不支持sys/system用戶表空間對(duì)象,可通過(guò)alter system set recyclebin=off;關(guān)閉回收站功能

閃回DROP示例:

SQL> select * from t ;

ID NAME
---------- ---------------------------------------
2
4

SQL> drop table t;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$YEh2QcvZdJLgUxyAgQpnVQ==$0 TABLE 2017-12-14:15:02:06
SQL> flashback table t to before drop;

Flashback complete.

SQL> select * from t;

ID NAME
---------- -------------------------------------
2
4

備注:即使不開(kāi)始flashback,只要開(kāi)啟了recyclebin,那么就可以閃回DROP表。

但如果連續(xù)覆蓋,就需要指定恢復(fù)的表名,如果已經(jīng)存在表,則需要恢復(fù)重命名。

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

(4)閃回?cái)?shù)據(jù)庫(kù)(truncate/多表數(shù)據(jù)變更)

數(shù)據(jù)庫(kù)閃回必須在mounted狀態(tài)下進(jìn)行,基于快照的可以再open下進(jìn)行閃回庫(kù)

閃回?cái)?shù)據(jù)庫(kù)主要是將數(shù)據(jù)庫(kù)還原值過(guò)去的某個(gè)時(shí)間點(diǎn)或SCN,用于數(shù)據(jù)庫(kù)出現(xiàn)邏輯錯(cuò)誤時(shí),需要open database resetlogs

a.全庫(kù)閃回

數(shù)據(jù)庫(kù)閃回示例

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.快照閃回

針對(duì)主庫(kù)和備庫(kù)都可以創(chuàng)建閃回快照點(diǎn),然后恢復(fù)到指定的快照點(diǎn),但主庫(kù)一旦恢復(fù)到快照點(diǎn),備庫(kù)的同步則需要重新同步

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.

此時(shí)主庫(kù)scott.t已不存在:
SQL> select * from scott.t;
select * from scott.t
          *
ERROR at line 1:
ORA-00942: table or view does not exist
此時(shí)從庫(kù)的scott.依舊存在,主備同步終止
解決方案:在主庫(kù)創(chuàng)建快照時(shí)間點(diǎn),從庫(kù)自動(dòng)停止應(yīng)用日志,等主庫(kù)閃回后,重新應(yīng)用日志即可。
如果已經(jīng)做了上述操作,從庫(kù)可以選擇重建
ALTER DATABASE REGISTER LOGFILE '/xx/xx/archive.dbf';

c.閃回snapshot standby

此功能在11GR2非常實(shí)用,可自動(dòng)創(chuàng)建閃回點(diǎn)、開(kāi)啟閃回日志,可完成線上數(shù)據(jù)測(cè)試后,然后做數(shù)據(jù)庫(kù)閃回恢復(fù)主備關(guān)系

select scn, STORAGE_SIZE ,to_char(time,'yyyy-mm-dd hh24: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> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; 

Database altered.

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


此時(shí)備庫(kù)操作:
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.

主庫(kù)操作:
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

備庫(kù)恢復(fù)到物理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


ORACLE instance shut down.
SQL> startup ;
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.
Database opened.

##此時(shí)備庫(kù)的數(shù)據(jù)已經(jīng)恢復(fù)到轉(zhuǎn)變snapshot standby時(shí)間點(diǎn)
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.


SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select * from scott.t;

  DEPTNO DNAME     LOC      ADDR
---------- -------------- ------------- ------------------------------
ACCOUNTING   NEW YORK
RESEARCH    DALLAS
SALES     CHICAGO

    
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 WITH APPLY testdbms            NO

(5)閃回歸檔(增加、修改、重命名、刪除表的列、truncate表、修改表的約束、以及修改分區(qū)表的分區(qū)規(guī)范)

3、閃回注意事項(xiàng)

(1)數(shù)據(jù)庫(kù)閃回需要在mounted下進(jìn)行,并且open時(shí)需要使用resetlogs

(2)閃回DROP只能用于非系統(tǒng)表空間和本地管理的表空間,外鍵約束無(wú)法恢復(fù),對(duì)方覆蓋、重命名需注意

(3)表DROP,對(duì)應(yīng)的物化視圖會(huì)被徹底刪除,物化視圖不會(huì)存放在recyclebin里

(4)閃回表,如果在做過(guò)dml,然后進(jìn)行了表結(jié)構(gòu)修改、truncate等DDL操作,新增/刪除結(jié)構(gòu)無(wú)法做閃回

(5)閃回歸檔,必須在assm管理tablespace和undo auto管理下進(jìn)行

(6)注意閃回區(qū)管理,防止磁盤(pán)爆滿,閃回區(qū)空間不足等

(7)主庫(kù)做庫(kù)的閃回,會(huì)影響備庫(kù),需要重新同步

(8)snapshot standby 不支持最高保護(hù)模式

三、備注

1、相關(guān)數(shù)據(jù)字典

V$FLASHBACK_DATABASE_LOG ##查看數(shù)據(jù)庫(kù)可閃回的時(shí)間點(diǎn)/SCN等信息 V$flashback_database_stat ##查看閃回日志空間記錄信息

2、常用查詢語(yǔ)句

(1)查看數(shù)據(jù)庫(kù)狀態(tài)

SQL> select NAME,OPEN_MODE ,DATABASE_ROLE,CURRENT_SCN,FLASHBACK_ON from v$database;

NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FLASHBACK_ON
------------- -------------------- ---------------- ----------- ------------------
TESTDB READ WRITE PRIMARY 16812246 YES

(2)獲取當(dāng)前數(shù)據(jù)庫(kù)的系統(tǒng)時(shí)間和SCN

SQL> select to_char(systimestamp,'yyyy-mm-dd HH24:MI:SS') as sysdt , dbms_flashback.get_system_change_number scn from dual;

SYSDT SCN
------------------- ----------
2017-12-14 14:28:33 16813234

(3)查看數(shù)據(jù)庫(kù)可恢復(fù)的時(shí)間點(diǎn)

SQL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
16801523 2017-12-14 11:35:05 4320 104857600 244113408

(4)查看閃回日志空間情況

SQL> select * from V$flashback_database_stat;
BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2017-12-14 14:34:53 2017-12-14 14:56:43 1703936 9977856 1487872 0  

(5)SCN和timestamp裝換關(guān)系查詢

select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss')from sys.smon_scn_time;

(6)查看閃回restore_point

select scn, STORAGE_SIZE ,to_char(time,'yyyy-mm-dd hh24:mi:ss') time,NAME from v$restore_point;

(7)閃回語(yǔ)句

a.閃回?cái)?shù)據(jù)庫(kù)

FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2017-12-14 14:28:33','yyyy-mm-dd HH24:MI:SS');;
flashback database to scn 16813234;

b.閃回DROP

其中table_name可以是刪除表名稱,也可以是別名

flashback table table_name to before drop;
flashback table table_name to before drop rename to table_name_new;

c.閃回表

flashback table table_name to scn scn_number;
flashback table table_name to timestamp to_timestamp('2017-12-14 14:28:33','yyyy-mm-dd hh24:mi:ss');

d.閃回查詢

select * from table_name as of timestamp to_timestamp('2017-12-14 14:28:33','yyyy-mm-dd hh24:mi:ss');
select * from scott.dept as of scn 16801523;

e.閃回快照

create restore point before_201712151111 guarantee flashback database;

flashback database to restore point before_201712151111;

(7)閃回空間爆滿問(wèn)題處理

請(qǐng)參照 基于ORA-19815閃回空間爆滿問(wèn)題的處理方法

以上這篇基于Oracle閃回詳解(必看篇)就是小編分享給大家的全部?jī)?nèi)容了,希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。

您可能感興趣的文章:
  • Oracle 閃回技術(shù)詳細(xì)介紹及總結(jié)
  • oracle 數(shù)據(jù)庫(kù)閃回相關(guān)語(yǔ)句介紹

標(biāo)簽:遼源 郴州 合肥 滁州 涼山 許昌 南充 焦作

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《基于Oracle閃回詳解(必看篇)》,本文關(guān)鍵詞  基于,Oracle,閃回,詳解,必看篇,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《基于Oracle閃回詳解(必看篇)》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于基于Oracle閃回詳解(必看篇)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章