闪回数据库
如果数据库启用了闪回日志，那么可以使用闪回数据库将数据库的内容回退到闪回窗口中的某一时间点也可以使用flashback database将数据库回退到之前定义的受保护还原点所对应的时间点。
闪回数据库使用的场景
在大多数情况下执行闪回数据库使用时间表达式，正常或受保护还原点或SCN来指定所要回退到的过去时间点。
使用RMAN执行闪回数据库的过程如下：
1.决定一个能代表flashback database命令要将数据库回退到过去时间点的SCN，还原点或时间表达式
创建一个表emp_test
SQL> create table emp_test as select * from emp;
Table created.
SQL> select count(*) from emp_test;
COUNT(*)
----------
14
查看当前数据库的SCN号
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3111823
永久删除表emp_test
SQL> drop table emp_test purge; Table dropped.
2.使用RMAN连接到目标数据库
[oracle@oracle11g ~]$ rman target/ Recovery Manager: Release 10.2.0.5.0 - Production on Tue Feb 3 16:27:32 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: TEST (DBID=2155613261)
3.完全干净地关闭数据库后并将数据库启动到mount状态:
RMAN> shutdown immediate; using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down RMAN> startup mount; connected to target database (not started) Oracle instance started database mounted Total System Global Area 327155712 bytes Fixed Size 1273516 bytes Variable Size 138412372 bytes Database Buffers 184549376 bytes Redo Buffers 2920448 bytes
4.执行查询来判断执行闪回数据库的时间窗口。如果有些闪回数据库日志因为闪回区空间压力而被删除了，那么可能不能将数据库回退到所有指定的SCN所对应的时间点。如果flashback database指定的目标SCN超出过闪回窗口，那么flashback database命令将会报ora-38729错误。在执行闪回数据库操作时，RMAN可能需要从备份中还原一些归档重做日志。如果备份存储在SBT上并且对于要访问的SBT设备没有配置必要的通道，那么可以在RUN块中使用allocate channel命令来允许RMAN检索磁盘或磁带上的这些日志文件。
SQL> select oldest_flashback_scn,to_char(oldest_flashback_time,'YYYY-MM-DD HH24:MI:SS') from v$flashback_database_log;
OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLAS
-------------------- -------------------
3069719 2015-02-02 15:38:40
这里我们的闪回窗口能将数据库回退到SCN号为3069719的时间点，但我们的表emp_test是在
2015-02-02 15:38:40以后创建，且删除的时间在SCN:3111823以后，我们想要通过闪回数据库来恢复表emp_test话恢复的窗口应该在SCN:3111823以后。SCN：3111823对应的时间为2015-02-03 16:26:27
SQL> select to_char(scn_to_timestamp(3111823),'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SCN_TO_TIME ------------------- 2015-02-03 16:26:27
5.使用RMAN来执行flashback database命令，并指定一个目标时间点：
RMAN> flashback database to scn 3111823; Starting flashback at 03-FEB-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=157 devtype=DISK starting media recovery media recovery complete, elapsed time: 00:00:15 Finished flashback at 03-FEB-15
也可以执行如下命令:
RMAN> flashback database to time "to_date('2015-02-03 16:26:27','yyyy-mm-dd hh24:mi:ss') ";
Starting flashback at 03-FEB-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:15
Finished flashback at 03-FEB-15
当flashback database命令执行完成后，数据库仍然处于mount状态并且恢复到了指定的时间点。
通过将数据库收只读模式打开来执行某些查询来检查数据库的内容来判断是否将数据库恢复到了你所期望的状态。
RMAN> sql 'alter database open read only'; sql statement: alter database open read only
查询表emp_test是否存在且是否有14条记录
SQL> select * from emp_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
这说明恢复到了我们所期望的状态。
在闪回数据库操作将数据库回退到你所期望的时间点后，可以有两个选择：
1.以open resetlogs选项打开数据库,但在目标SCN号之后的改变将会丢失
RMAN>alter database open resetlogs;
2.使用Oracle的导出工具将被删除的对象导出。然后将数据库恢复到当前时间点：
RMAN>recover database;
这步操作将会撤消闪回数据库的影响，通过对数据库应用重做日志中的所有改变，将数据库恢复到最近的SCN时间点。在以读写模式打开数据库后，可以将导出的表导入数据库。
如果将数据库闪回到一个错误的时间点之后的选择
如果我们将上面的数据库使用闪回回退到表emp_test创建之前SCN:3111000，那么在闪回数据库之后表emp_test是不存在的。
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 327155712 bytes
Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes
RMAN> flashback database to scn 3111000;
Starting flashback at 03-FEB-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:15
Finished flashback at 03-FEB-15
RMAN> sql 'alter database open read only';
sql statement: alter database open read only
SQL> select * from emp_test;
select * from emp_test
*
ERROR at line 1:
ORA-00942: table or view does not exist
确实闪回成功后表emp_test不存在。
1.如果选择的闪回目标时间回退的不够，那么可以再次执行flashback database命令将数据库回退到比当前更前的时间
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 327155712 bytes
Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes
RMAN> flashback database to scn 3111993;
Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:15
Finished flashback at 03-FEB-15
RMAN> sql 'alter database open read only';
sql statement: alter database open read only
SQL> select * from emp_test;
select * from emp_test
*
ERROR at line 1:
ORA-00942: table or view does not exist
回退的时间点比所期待的时间晚，所以表emp_test还是被删除了。
2.如果选择的目标SCN将数据库回退到比期望的时间点更早的时间，那么可以加载数据库，使用recover database until scn命令来恢复到所期望的时间点：
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 327155712 bytes
Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes
RMAN> flashback database to scn 3110000;
Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:15
Finished flashback at 03-FEB-15
RMAN> sql 'alter database open read only';
sql statement: alter database open read only
SQL> select * from emp_test;
select * from emp_test
*
ERROR at line 1:
ORA-00942: table or view does not exist
回退的时间点比所期待的时间早，所以表emp_test还没有被创建,执行recover database until scn命令将数据库恢复到表emp_test删除之前（因为表emp_test删除之前系统的SCN为:3111823)
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 327155712 bytes
Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes
RMAN> recover database until scn 3111823;
Starting recover at 03-FEB-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 03-FEB-15
RMAN> sql 'alter database open read only';
sql statement: alter database open read only
SQL> select * from emp_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
从查询结果可以看到表emp_test被恢复回来了
3.如果想完全撤消flashback database命令的影响，可以使用recover database命令对数据库执行完全恢复。
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 327155712 bytes
Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes
RMAN> recover database;
Starting recover at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:05
Finished recover at 03-FEB-15
RMAN> sql 'alter database open';
sql statement: alter database open
SQL> select * from emp_test;
select * from emp_test
*
ERROR at line 1:
ORA-00942: table or view does not exist
可以看到对数据库执行完全恢复后，表emp_test不存在，这也就撤消了闪回数据库的影响。
执行闪回数据库来撤消open resetlogs的影响,将数据库回退到表emp_test被删除之前.
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 327155712 bytes
Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes
RMAN> flashback database to scn 3111823;
Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:15
Finished flashback at 03-FEB-15
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
SQL> select * from emp_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
验证闪回窗口开始时间是否比最近open resetlogs的时间早
SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
3111834
SQL> select oldest_flashback_scn from v$flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
3074835
如果v$database.resetlogs_change#比v$flashback_database_log.oldest_flashback_scn大，那么可以使用闪回数据库来撤消open resetlogs操作对数据库的改变。
关闭数据库后将数据库置于mount状态，并重新检查闪回窗口。如果resetlogs的SCN仍然在闪回窗口中，那么可以执行flashback database命令来撤消open resetlogs操作对数据库的改变。
SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
3111834
SQL> select oldest_flashback_scn from v$flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
3074835
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 327155712 bytes
Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes
RMAN> flashback database to before resetlogs;
Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished flashback at 03-FEB-15
RMAN> sql 'alter database open read only';
sql statement: alter database open read only
SQL> select * from emp_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
如果目标SCN在闪回数据库窗口的开始时间之前，那么flashback database命令会报错。如果闪回数据库执行成功，那么数据库还处于mount状态，并将数据库恢复到了之前数据库incarnation对应的open resetlogs操作之前最后的SCN所对应的时间点。因为数据库现在对应的resetlogs_change#为3111834，上面的闪回数据库将数据库回退到SCN为3111834之前的状态，所以以只读模式将数据库打开后表emp_test应该是存在的，来验证数据库是否恢复到你所期望的状态。为了使用数据库可以进行更新，使用alter database open resetlogs命令来打开数据库。
RMAN> alter database open resetlogs; database opened
将数据库闪回到正确的open resetlogs状态
在有些情况下，可能需要将数据库回退到parent incarnation所对应的时间。可以使用RMAN的reset database to incarnation命令来指定flashback database to scn来引用的current incarnation
操作过程如下：
1.验证闪回日志所包含的信息能将数据库回退到的SCN：
SQL> select oldest_flashback_scn from v$flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
3077255
2.判断闪回数据库要使用的目标incarnation
SQL> select * from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TI PRIOR_RESETLOGS_CHANGE# PRIOR_RESETL STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------------ ----------------------- ------------ ------- ------------ ------------------ --------------------------
1 1 30-JUN-05 0 PARENT 562360180 0 NO
2 446075 05-SEP-14 1 30-JUN-05 PARENT 857466832 1 NO
3 2849317 27-JAN-15 446075 05-SEP-14 PARENT 870102602 2 NO
4 2880152 27-JAN-15 2849317 27-JAN-15 PARENT 870133266 3 NO
5 3017109 01-FEB-15 2880152 27-JAN-15 PARENT 870550288 4 NO
6 3041066 01-FEB-15 3017109 01-FEB-15 PARENT 870563157 5 NO
7 3041350 01-FEB-15 3041066 01-FEB-15 PARENT 870564201 6 YES
8 3111834 03-FEB-15 3041350 01-FEB-15 ORPHAN 870724654 7 YES
9 3111834 03-FEB-15 3041350 01-FEB-15 ORPHAN 870726369 7 YES
10 3114665 03-FEB-15 3041350 01-FEB-15 CURRENT 870726883 7 YES
SQL> select prior_incarnation# from v$database_incarnation where status ='CURRENT';
PRIOR_INCARNATION#
------------------
7
从上面的信息可以知道当前的incarnation号为10,它的parent incarnation号是7
3.在RMAN在关闭数据库，并mount数据库：
RMAN> shutdown immediate database closed database dismounted Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 327155712 bytes Fixed Size 1273516 bytes Variable Size 138412372 bytes Database Buffers 184549376 bytes Redo Buffers 2920448 bytes
4.将数据库的incarnation设置为它的parent incarnation:
RMAN> reset database to incarnation 7; database reset to incarnation 7 5.运行flashback database命令： RMAN> flashback database to scn 3077255; Starting flashback at 03-FEB-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=157 devtype=DISK starting media recovery archive log thread 1 sequence 2 is already on disk as file /u02/1_2_870564201.dbf media recovery complete, elapsed time: 00:00:01 Finished flashback at 03-FEB-15 RMAN> sql 'alter database open read only'; sql statement: alter database open read only
当闪回操作成功后，可以验证闪回数据库的结果，如果数据库回退到你所期望的状态就以resetlogs选项打开数据库。
RMAN> shutdown immediate database closed database dismounted Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 327155712 bytes Fixed Size 1273516 bytes Variable Size 138412372 bytes Database Buffers 184549376 bytes Redo Buffers 2920448 bytes RMAN> alter database open resetlogs; database opened