正文首要对Oracle常用闪回使用做些详细介绍,本文首要对Oracle常用闪回使用做些详细介绍

Oracle 9i
初始扶助闪回,Oracle10g开班周全支持闪回成效,Oracle11g有所完善,为大家赶快的还原数据,查询历史数据提供了相当的大的省事格局。

Oracle 9i
早先协理闪回,Oracle10g始于完善帮忙闪回功效,Oracle11g有所完善,为我们连忙的还原数据,查询历史数据提供了极大的省事措施。

正文首要对Oracle常用闪回使用做些详细介绍,在那之中对于不常用的事体和本子闪回,那里就不做牵线

正文主要对Oracle常用闪回使用做些详细介绍,当中对于不常用的事务和本子闪回,那里就不做牵线

一 、Oracle闪回概述

① 、Oracle闪回概述

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  

  

 

 

 二 、Oracle闪回使用详解

 贰 、Oracle闪回使用详解

壹 、闪回开启
(1)开启闪回须要条件
a.开启归档日志

一 、闪回开启
(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;
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.设置合理的闪回区

b.设置合理的闪回区

db_recovery_file_dest:内定闪回过来区的职位
db_recovery_file_dest_size:钦点闪回过来区的可用空间大小
db_flashback_retention_target:钦定数据库能够回退的年月,单位为分钟,暗许1440分钟(1天),实际取决于闪回区大小
(2)检查是还是不是打开闪回

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
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

(3)开启闪回

(3)开启闪回

a.开启归档

a.开启归档

mount状态:alter database archivelog;
mount状态:alter database archivelog;

b.设置闪回区

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.    
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开启)

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

SQL> alter database flashback on;
Database altered.
SQL> alter database flashback on;
Database altered.

(4)明确闪回开启

(4)鲜明闪回开启

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

(5)关闭闪回

(5)关闭闪回

SQL> alter database flashback off;
Database altered.
SQL> alter database flashback off;
Database altered.

② 、闪回使用

② 、闪回使用

(1)闪回查询
闪回查询首借使根据Undo表空间数据进行多版本查询,针对v$和x$动态质量视图无效,但对DBA_、ALL_、USER_是行之有效的
a.闪回查询
同意用户查询过去某些时间点的数目,用以重构由于意外删除或改变的数码,数据不会变动。

(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
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.闪回版本查询

b.闪回版本查询

用以查询行级数据库随时间变化的不二法门

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

c.闪回事情查询
用以提供查看工作级别数据库变化的主意

c.闪回事情查询
用来提供查看工作级别数据库变化的艺术

(2)闪回表(update/insert/delete)
闪回表正是对表的数目做回退,回退到前边的某部时间点,其采用的是undo的历史数据,与undo_retention设置有关,暗许是14400分钟(1天)
平等,sys用户表空间不扶助闪回表,要想表闪回,需求允许表运行行迁移(row
movement)
闪回表示例:

(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.
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)

(3)闪回DROP(drop table)

当3个表被drop掉,表会被放入recyclebin回收站,可经过回收站做表的闪回。表上的目录、约束等一律会被恢复生机
不支持sys/system用户表空间对象,可经过alter system set
recyclebin=off;关闭回收站功效
闪回DROP示例:

当二个表被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    
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表。

备考:固然不开首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.
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/多表数据变动)

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

数据库闪回必须在mounted状态下进展,基于快速照相的可以再open下开始展览闪回库
闪回数据库重点是将数据库还原值过去的有个别时间点或SCN,用于数据库出现逻辑错误时,供给open
database resetlogs
a.全库闪回
数据库闪回示例

数据库闪回必须在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.
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.快速照相闪回

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';
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

 c.闪回snapshot standby

此功能在11GRAV42相当实用,可自行创造闪回点、开启闪回日志,可实现线上数据测试后,然后做数据库闪回复苏主备关系

此效能在11G翼虎2卓殊实用,可自动创制闪回点、开启闪回日志,可成功线上数据测试后,然后做数据库闪回苏醒主备关系

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
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表、修改表的羁绊、以及修改分区表的分区规范)

(5)闪回归档(扩张、修改、重命名、删除表的列、truncate表、修改表的束缚、以及修改分区表的分区规范)

 

 

③ 、闪回注意事项
(1)数据库闪回供给在mounted下进行,并且open时索要利用resetlogs
(2)闪回DROP只可以用于非系统表空间和本地管理的表空间,外键约束不能苏醒,对方覆盖、重命名需注意
(3)表DROP,对应的物化视图会被彻底剔除,物化视图不会存放在recyclebin里
(4)闪回表,要是在做过dml,然后举办了表结构修改、truncate等DDL操作,新增/删除结构无法做闪回
(5)闪回归档,必须在assm管理tablespace和undo auto管理下展开
(6)注意闪回区管理,制止磁盘爆满,闪回区空间不足等

三 、闪回注意事项
(1)数据库闪回须要在mounted下开展,并且open时索要运用resetlogs
(2)闪回DROP只好用于非系统表空间和本土管理的表空间,外键约束不能够恢复,对方覆盖、重命名需注意
(3)表DROP,对应的物化视图会被彻底删除,物化视图不会存放在recyclebin里
(4)闪回表,假设在做过dml,然后进行了表结构修改、truncate等DDL操作,新增/删除结构不能做闪回
(5)闪回归档,必须在assm管理tablespace和undo auto管理下进展
(6)注意闪回区管理,幸免磁盘爆满,闪回区空间不足等

(7)主库做库的闪回,会影响备库,供给再一次联合
(8)snapshot standby 不帮忙最高珍爱格局

(7)主库做库的闪回,会影响备库,要求再行联合
(8)snapshot standby 不帮衬最高爱慕方式

三、备注
壹 、相关数据字典
V$FLASHBACK_DATABASE_LOG ##翻开数据库可闪回的岁月点/SCN等新闻
V$flashback_database_stat ##查看闪回日志空间记录新闻
二 、常用查询语句
(1)查看数据库状态

三、备注
① 、相关数据字典
V$FLASHBACK_DATABASE_LOG ##查阅数据库可闪回的小运点/SCN等音信
V$flashback_database_stat ##翻开闪回日志空间记录新闻
贰 、常用查询语句
(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
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

(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
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)查看数据库可复原的小时点

(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
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)查看闪回日志空间情形

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

(5)SCN和timestamp装换关系查询

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

(6)查看闪回restore_point

(6)查看闪回restore_point

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

(7)闪回语句

(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;

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.闪回快速照相

e.闪回快速照相

  create restore point before_201712151111 guarantee flashback
database;

  create restore point before_201712151111 guarantee flashback
database;

  flashback database to restore point before_201712151111;

  flashback database to restore point before_201712151111;

(7)闪回空间爆满难点处理

(7)闪回空间爆满难题处理

请参见笔者事先的今日头条:http://blog.itpub.net/27067062/viewspace-2129130/

请参见作者事先的新浪:http://blog.itpub.net/27067062/viewspace-2129130/

 

 

网站地图xml地图