oracle flashback 功能,**11g超强的flashback功能做到“让Oracle可以快速进行时间穿越”...

Quick

restore

===========================================

rman restore database：

RUN {

SET UNTIL TIME "to_date('2014-01-17

19:10:00','yyyy-mm-dd hh24:mi:ss')";

RESTORE DATABASE;

ECOVER DATABASE;

}

Current SCN

===========================================

1.查询系统当前SCN两条命令

SQL> select current_scn from v$database;

CURRENT_SCN

------------------------

1781888

SQL> select dbms_flashback.get_system_change_number from

dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

1781893

2.数据库全局-检查点 SCN，在控制文件中。

SYS@bys1> select dbid,checkpoint_change# from

v$database;

DBID

CHECKPOINT_CHANGE#

---------- ------------------

3957527513  1753478

3.当前数据文件SCN。在控制文件中。即checkpoint

scn,表示该数据文件最近一次执行检查点操作时的SCN

SQL> select name,checkpoint_change# from v$datafile;

NAME  CHECKPOINT_CHANGE#

--------------------------------------------------

------------------

/u01/oradata/bys1/system01.dbf  1753478

/u01/oradata/bys1/sysaux01.dbf  1753478

SQL> select

file#,name,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd

hh24:mi:ss') cptime from v$datafile;

FILE#

NAME  CHECKPOINT_CHANGE#

CPTIME

---------- -------------------------------- ------------------

-------------------

1

/u01/oradata/bys1/system01.dbf  1753478

2013-09-11 23:00:52

2

/u01/oradata/bys1/sysaux01.dbf  1753478

2013-09-11 23:00:52

Restore point

===========================================

create restore point  rpt2016918 ;

flashback database to restore

point rpt2016918 ;

Operate Procedure

===========================================

ORACLE

11g的flashback甚是强大，10g出来这个功能时曾经测试过，设备所限发生了不好的体验，由于flashback

area爆满导致数据库无法继续运行的事情，反而带来的极大的隐患，后来就不曾开过flashback。

本次在11g上试用几天，发现改进很多，至少在用完了recovery_area_dest_size的时候，不会停止数据库的正常运行。

下面主要测试了database Level flashback 功能，11g flashback

功能真的实现了“数据库级别的时空穿越，只要有足够的flash area

空间”，特别对于处理误删除，修改等局部错误尤其有效，请看下面的演示。下面的正常和异常大家都懂的，就不多解释了。

案例： 数据库下午12点后，被人偷偷的修改了5个表，现在是下午5点，如果是备份恢复，至少先找个备用机器restore

几个表空间甚至整个数据库，然后recover

很多的归档，在export恢复后的12点的表，然后再imp进来，11g可不用这么麻烦。

1.重启mount

2.flashback to 12点

3.回到了12点状态，时空重现，open

database，把这5个表export出来。

4.重启，继续recover到现在，open

database，然后把这5个表import进去，insert，delete，update就随便你使用了，把被篡改的表整成12点数据一样就可以了。

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Feb 4

11:03:00 2012

Copyright (c) 1982, 2008, Oracle.  All rights

reserved.

ORACLE instance started.

Database Buffers  360710144

bytes

Redo Buffers  4427776

bytes

Database mounted.

SQL>

SQL> select flashback_on from v$database

FLASHBACK_ON

------------------

YES

SQL> alter

system set db_flashback_retention_target=2880;

SQL> flashback database

to timestamp to_timestamp('2017-06-16

19:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> flashback database to scn 4051413;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL>  alter database close;

Database altered.

SQL> select status from v$instance;

STATUS

------------

MOUNTED

SQL> flashback database to scn 4051400;

Flashback complete.

SQL> flashback database to scn 4000000;

flashback database to scn 4000000

*

ERROR at line 1:

ORA-38729: Not enough flashback database log data to do

FLASHBACK.

SQL>  flashback database to scn

4050500;

Flashback complete.

SQL> flashback database to scn 4050200;

Flashback complete.

SQL> flashback database to scn 4050100;

Flashback complete.

SQL>  flashback database to scn

4050000;

Flashback complete.

SQL>  flashback database to scn

4040000;

flashback database to scn 4040000

*

ERROR at line 1:

ORA-38729: Not enough flashback database log data to do

FLASHBACK.

SQL> flashback database to scn 4040900;

flashback database to scn 4040900

*

ERROR at line 1:

ORA-38729: Not enough flashback database log data to do

FLASHBACK.

SQL> flashback database to scn 4048000;

Flashback complete.

SQL> alter database open read

only;

Database altered.

SQL> alter database close ;

Database altered.

SQL>  Flashback database to timestamp

to_timestamp('2012-02-04 10:30:01','yyyy-mm-dd hh24:mi:ss');

Flashback database to timestamp

to_timestamp('2012-02-04 10:30:01','yyyy-mm-dd hh24:mi:ss')

*

ERROR at line 1:

ORA-38729: Not enough flashback database log data to do

FLASHBACK.

SQL> flashback database to scn 4048055;

Flashback complete.

SQL> flashback database to timestamp

to_timestamp('2012-02-04 10:35:01','yyyy-mm-dd

hh24:mi:ss');

Flashback complete.

SQL>  flashback database to timestamp

to_timestamp('2012-02-04 10:31:01','yyyy-mm-dd

hh24:mi:ss');

Flashback complete.

SQL> flashback database to timestamp

to_timestamp('2012-02-04 10:30:40','yyyy-mm-dd

hh24:mi:ss');

flashback database to timestamp

to_timestamp('2012-02-04 10:30:40','yyyy-mm-dd

hh24:mi:ss')

*

ERROR at line 1:

ORA-38729: Not enough flashback database log data to do

FLASHBACK.

SQL>flashback database to timestamp

to_timestamp('2012-02-04 10:30:58','yyyy-mm-dd

hh24:mi:ss');

Flashback complete.

SQL>flashback database to timestamp

to_timestamp('2012-02-04 10:30:55,'yyyy-mm-dd

hh24:mi:ss');

ERROR:

ORA-01756: quoted string not properly terminated

SQL>  flashback database to timestamp

to_timestamp('2012-02-04 10:30:55','yyyy-mm-dd

hh24:mi:ss');

Flashback complete.

SQL> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-16196: database has been previously opened and

closed

SQL> shutdown

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area

Database Buffers  360710144

bytes

Redo Buffers  4427776

bytes

Database mounted.

SQL> flashback database to timestamp

to_timestamp('2012-02-04 10:30:58','yyyy-mm-dd

hh24:mi:ss');

Flashback complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for

database open

SQL>  flashback database to timestamp

to_timestamp('2012-02-04 11:00:01','yyyy-mm-dd

hh24:mi:ss');

Flashback complete.

SQL> recover database ;

Media recovery complete.

SQL> alter database

open;

Database altered.

SQL> show parameter fla

NAME  TYPE  VALUE

------------------------------------ -----------

------------------------------

db_flashback_retention_target  integer

720

plsql_ccflags  string

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition

Release 11.1.0.7.0 - 64bit Production

With the Partitioning and Data Mining options

备份过程：

*******************************************

过往的reset 记录

RMAN> list incarnation;

List of Database Incarnations

DB Key Inc Key DB Name STATUS Reset SCN Reset

Time

------- ------- -------- --- ---------- ----------

1 1 uatdhl PARENT 1 30-Jan-16

2 2 uatdhl PARENT 446075 22-APR-16

3 3 uatdhl PARENT 699141 1-JUN-16

***** incarnation 3

【10-JUN-16】

备份database level 0 db0bk1 完成。

今天 【16-JUN-16】

18：37 备份ctrl

file '/../ctf_bk1' 完成。

18：38 备份database level

0 db0bk2 完成。 18：40 备份ctrl

file '/../ctf_bk2' 完成。

22：00 flashback database to timestamp to_timestamp('2016-06-16

18:59:00','yyyy-mm-dd hh24:mi:ss');

alter database open resetlogs ;

RMAN> list incarnation;

List of Database Incarnations

DB Key Inc Key DB Name STATUS Reset SCN Reset

Time

------- ------- -------- --- ---------- ----------

1 1 uatdhl PARENT 1 30-Jan-16

2 2 uatdhl PARENT 446075 22-APR-16

3 3 uatdhl PARENT 699141 1-JUN-16 4 4 uatdhl CURRENT

729844 16-JUN-16 ***** incarnation 4

几种 恢复方式：

*******************************************

23:00 ================

restore controlfile from '/../ctf_bk1';

mount database;

RUN {

SET UNTIL TIME "to_date('2016-06-16 18:50:00','yyyy-mm-dd

hh24:mi:ss')";

RESTORE

DATABASE; } RMAN-03002: failure of set command at 06/24/2012 11:11:16

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS

time

结果：失败！

23:05 ================

restore controlfile from '/../ctf_bk1'; mount database;

reset database to incarnation 3

RUN {

SET UNTIL TIME "to_date('2016-06-16 18:50:00','yyyy-mm-dd

hh24:mi:ss')";

RESTORE

DATABASE; } RECOVER DATABASE;

结果：报错 ，失败 alter database open resetlogs ;

23:15

================

restore controlfile from '/../ctf_bk2';

mount database;

reset database to incarnation 3

RUN {

SET UNTIL TIME "to_date('2016-06-16 18:50:00','yyyy-mm-dd

hh24:mi:ss')";

RESTORE DATABASE;

} RECOVER DATABASE UNTIL TIME "to_date('2016-06-16

18:50:00','yyyy-mm-dd hh24:mi:ss')";

备注： 恢复成功 alter database open resetlogs ；

23:30

其实 ，设置成incarnation 2

也可以成功的，不过恢复的时间比较长，下次测试

reset database to incarnation 2

restore controlfile from '/../ctf_bk1';

23:45

================

restore controlfile from '/../ctf_bk2';

mount database;

reset database to incarnation 3

RUN {

SET UNTIL TIME "to_date('2016-06-16 18:50:00','yyyy-mm-dd

hh24:mi:ss')";

RESTORE DATABASE ;

RECOVER DATABASE ;

} alter database open resetlogs ；

备注： 应该能成功，还没有时间测试。