闪回数据库

闪回数据库
如果数据库启用了闪回日志,那么可以使用闪回数据库将数据库的内容回退到闪回窗口中的某一时间点也可以使用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

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.