使用Flashback Database进行数据表级别的定点恢复
使用Flashback Database进行数据表级别的定点恢复
Oracle已经提供了非常完善的数据备份恢复措施，从9i开始，针对一些常见场景下的小规模数据恢复需求，Oracle推出了一系列的Flashback技术。
Flashback中文称为“闪回”，Oracle的闪回技术并不是一个单独技术，而是根据不同的恢复粒度而推出的一系列数据快速恢复技术。更重要的是，各个Flashback技术虽然名称相同或者相似，但底层依赖的技术还是存在很大的差异。
在笔者之前的系列中，针对flashback query、flashback table和flashback archive等进行过比较详细的介绍。Flashback Database是Flashback家族中恢复粒度最大的项目，本篇介绍如何利用Flashback Database实现数据表级别数据恢复。
1、环境和前提条件介绍
笔者使用Oracle 11gR2进行实验，具体版本为11.2.0.4。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production
数据库运行在归档模式下，配置Fast Recovery Area实行自动管理。
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/fast_recovery_area
db_recovery_file_dest_size big integer 10000M
recovery_parallelism integer 0
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Next log sequence to archive 21
Current log sequence 21
此时，系统保持默认的配置项目，不开启flashback database特性。
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> show parameter flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
注意：参数db_flashback_retention_target是控制flashback database的重要参数项目。该参数在11g中默认为1440，单位是分钟，也就是折合24小时。Flashback Database是基于在recovery area中单独建立的Flashback Database Log。如果对一些业务处理量比较大且频繁，变化比较大的数据库开启较长的Retention Target时间，需要确保Fast Recovery Area的大小足够大。
2、启动Flashback Database
开启Flashback Database，需要正常关闭数据库，之后启动到mount状态。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 372449280 bytes
Fixed Size 1364732 bytes
Variable Size 306187524 bytes
Database Buffers 58720256 bytes
Redo Buffers 6176768 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
启动数据库到open状态。
SQL> alter database open;
Database altered.
Flashback信息和Flashback能恢复到的最早时间，可以通过视图查看到。
SQL> select current_scn, flashback_on from v$database;
CURRENT_SCN FLASHBACK_ON
----------- ------------------
1350667 YES
--最早时间和SCN号
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------------------- ---------------- -------------- ------------------------
1350306 19-六月-15 13:33:30 1440 131072000 220200960
Recovery Area中的Flashback Database Log，如下：
SQL> select * from v$flashback_database_logfile;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME TYPE
-------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------- ----------- ---------
/u01/app/fast_recovery_area/ORA11G/flashback/o1_mf_br7bf8xq_.flb 1 1 1 65536000 1350353 19-六月-15 NORMAL
/u01/app/fast_recovery_area/ORA11G/flashback/o1_mf_br7bfdjj_.flb 2 1 1 65536000 0 RESERVED
3、故障发生模拟
下面模拟故障场景，用户test下面有若干段对象。
SQL> select owner, tablespace_name, count(*) from dba_segments where owner='TEST' group by owner, tablespace_name;
OWNER TABLESPACE_NAME COUNT(*)
------------------------------ ------------------------------ ----------
TEST TESTTBL 5
误删除几张数据表。
SQL> drop table test.emp;
Table dropped
SQL> drop table test.dept;
Table dropped
此时数据库SCN和时间信息如下：
SQL> select current_scn, flashback_on from v$database;
CURRENT_SCN FLASHBACK_ON
----------- ------------------
1351835 YES
SQL> select sysdate a from dual;
A
--------------------
19-六月-15 13:54:20
4、恢复操作
下面进行过去test.emp和test.dept数据的寻找。首先，关闭数据库，进行Flashback闪回数据库到未发生故障时间点。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 372449280 bytes
Fixed Size 1364732 bytes
Variable Size 289410308 bytes
Database Buffers 75497472 bytes
Redo Buffers 6176768 bytes
Database mounted.
直接在mount状态闪回到过去时间点，之后以read only（注意：这个很重要，关系能否回到原点状态）打开数据库。
SQL> flashback database to timestamp to_date('2015-6-19 13:35:00','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open read only;
Database altered.
登录闪回版本数据库，查看被删除数据表是否存在。
SQL> conn sys/xxx@ora11g as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as SYS
SQL> select owner, tablespace_name, count(*) from dba_segments where owner='TEST' group by owner, tablespace_name;
OWNER TABLESPACE_NAME COUNT(*)
------------------------------ ------------------------------ ----------
TEST TESTTBL 5
下面就清晰很多，可以以数据表（Schema也可以）为目标，导出数据。
[oracle@SimpleLinux ~]$ expdp \"/ as sysdba\" dumpfile=test_part.dmp tables=test.emp,test.dept
Export: Release 11.2.0.4.0 - Production on Fri Jun 19 14:07:38 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-16000: database open for read-only access
数据泵（Datapump）显然是不行，因为expdp/impdp是工作在后台的数据工具，启动作业起码需要创建一张作业主表。这个对于只读状态数据库显然不行。
退而求其次，使用exp工具，小巧简单。
[oracle@SimpleLinux ~]$ exp \"/ as sysdba\" file=test_part.dmp tables=test.emp,test.dept
Export: Release 11.2.0.4.0 - Production on Fri Jun 19 14:22:33 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to TEST
. . exporting table EMP 14 rows exported
. . exporting table DEPT 4 rows exported
Export terminated successfully without warnings.
注意：现在已经成功分离出被误删除的数据，下面可以将数据库恢复到故障恢复点状态。此时，数据库依然是Read Only状态。
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
注意：虽然现在Flashback到一个旧的版本，但是由于并没有发生读写操作（Read Only）。所以原有数据库的归档日志依然是可用的，当前数据库依然是在一个原有的运行路径上。所以，才存在通过“应用”archive redo log，将数据恢复到原有状态的机会。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 372449280 bytes
Fixed Size 1364732 bytes
Variable Size 289410308 bytes
Database Buffers 75497472 bytes
Redo Buffers 6176768 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
将exp导出的文件数据表，导入到数据库中。
[oracle@SimpleLinux ~]$export NLS_LANG=american_america.al32utf8
[oracle@SimpleLinux ~]$ imp \"/ as sysdba\" file=scott_part.dmp fromuser=test touser=test
Import: Release 11.2.0.4.0 - Production on Fri Jun 19 14:27:49 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table "EMP" 14 rows imported
. . importing table "DEPT" 4 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@SimpleLinux ~]$
确认数据情况。
SQL> select owner, tablespace_name, segment_name from dba_segments where owner='TEST';
OWNER TABLESPACE_NAME SEGMENT_NAME
------------------------------ ------------------------------ -------------------------------
TEST TESTTBL PK_DEPT
TEST TESTTBL PK_EMP
TEST TESTTBL BIN$GNiF1FvbCgngVQAAAAAAAQ==$0
TEST TESTTBL BIN$GNiF1FveCgngVQAAAAAAAQ==$0
TEST TESTTBL DEPT
TEST TESTTBL EMP
TEST TESTTBL BIN$GNiF1FvcCgngVQAAAAAAAQ==$0
TEST TESTTBL BIN$GNiF1FvfCgngVQAAAAAAAQ==$0
TEST TESTTBL SALGRADE
9 rows selected
SQL> purge dba_recyclebin;
Done
SQL> select owner, tablespace_name, segment_name from dba_segments where owner='TEST';
OWNER TABLESPACE_NAME SEGMENT_NAME
------------------------------ ------------------------------ ------------------------
TEST TESTTBL PK_DEPT
TEST TESTTBL PK_EMP
TEST TESTTBL DEPT
TEST TESTTBL EMP
TEST TESTTBL SALGRADE
恢复成功。
5、结论
Oracle Flashback技术本身就是针对原有介质恢复技术时间长、业务影响大等缺点进行的有益补充。上面实验中，通过Flashback Database技术，实现了最小粒度对象的恢复功能。
评论暂时关闭