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
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> delete from scott.dept where deptno=40;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from scott.dept as of timestamp sysdate-10/1440;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 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
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select * from scott.dept as of scn 16801523;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 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 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
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 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
---------- ---------------------------------------
1
2
3
4
30

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
---------- -------------------------------------
1
2
3
4
30    

备考:即使不上马始flashback,只要打开了recyclebin,那么即便足以闪回DROP表。

但是要总是覆盖,就待指定恢复的表名,如果已经存在表,则需要恢复重命名。

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)闪转数据库(truncate/多表数据变动)

数据库闪回必须于mounted状态下进行,基于快照的得重新open下开展闪回库
闪回数据库重点是以数据库尚原值过去的有时间点还是SCN,用于数据库出现逻辑错误时,需要open
database resetlogs
a.全库闪回
数据库闪回示例

SQL> select * from scott.EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

14 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
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

14 rows selected.

b.快照闪回

 针对主库和备库都可创造闪转快照点,然后还原至指定的快照点,但主库一旦恢复到快照点,备库的一块儿则需再次联合

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC           ADDR
---------- -------------- ------------- ------------------------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 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 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


此时备库操作:
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 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
---------- -------------- ------------- ------------------------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 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


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.

##此时备库的数据已经恢复到转变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
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.


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

Database altered.

SQL> select * from scott.t;

    DEPTNO DNAME          LOC           ADDR
---------- -------------- ------------- ------------------------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 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表、修改表的自律、以及修改分区表的分区规范)

 

3、闪回注意事项
(1)数据库闪回需要以mounted下展开,并且open时需动用resetlogs
(2)闪转DROP只能用来非系统阐发空间和本地管理之表空间,外键约束无法恢复,对方覆盖、重命名需注意
(3)表DROP,对应之物化视图会给彻底去除,物化视图不会见满怀放在recyclebin里
(4)闪转表,如果当做了dml,然后进行了表结构修改、truncate等DDL操作,新增/删除结构无法做闪回
(5)闪回归档,必须于assm管理tablespace和undo auto管理下开展
(6)注意闪回区管理,防止磁盘爆满,闪回区空间欠缺等

(7)主库做仓库底闪回,会潜移默化备库,需要重联合
(8)snapshot standby 不支持最高保护模式

三、备注
1、相关数据字典
V$FLASHBACK_DATABASE_LOG ##查阅数据库可闪转之日子点/SCN等消息
V$flashback_database_stat ##查闪回日志空间记录信息
2、常用查询语句
(1)查看数据库状态

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)获取当前数据库的网时与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)查看数据库可还原的辰点

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装换关系查询

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)闪转语句

a.闪回数据库
  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)闪转空间爆满问题处理

恳请参考我前的微博:http://blog.itpub.net/27067062/viewspace-2129130/

 

相关文章