Oracle flashback database的使用有一些限制，其中最主要的是flashback database不支持跨数据文件删除闪回和不支持跨数据文件shrink闪回。对于已经删除的数据文件只能采用恢复机制进行恢复，具体操作方法见以下测试。
1. 测试环境准备
1.1 开启flashback
检查flashback是否开启，如未开启，先开启flashback. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES 检查当前scn SQL> select current_scn from v$database; CURRENT_SCN ----------- 1295139 检查当前归档日志 SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 56
1.2 创建表空间、用户、表，插入数据
创建测试表空间 SQL> create tablespace testtbs datafile '+DATA/beicenp/datafile/testtbs.dbf' size 10m autoextend on; Tablespace created. SQL> alter system archive log current; System altered. SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 57 创建测试用户 SQL> create user test identified by test default tablespace testtbs; User created. SQL> grant connect,resource to test; Grant succeeded. SQL> create table test.testtb (id number); Table created. SQL> insert into test.testtb values (1); 1 row created. SQL> commit; Commit complete. SQL> alter system archive log current; System altered. SQL> select current_scn from v$database; CURRENT_SCN ----------- 1295236
1.3 执行数据库备份
执行以下脚本备份数据库： run { allocate channel ch00 type disk; backup as compressed backupset format '/app/oracle/bk_%s_%p_%t' database; sql 'alter system archive log current'; backup as compressed backupset format '/app/oracle/arch_%s_%p_%t' archivelog all delete input; backup format '/app/oracle/cntl_%s_%p_%t' current controlfile; release channel ch00; }
1.4 备份后模拟一些数据库操作
模拟一些数据库操作： SQL> insert into test.testtb values (2); 1 row created. SQL> commit; Commit complete. SQL> alter system archive log current; System altered. SQL> select current_scn from v$database; CURRENT_SCN ----------- 1295364 SQL> insert into test.testtb values (3); 1 row created. SQL> commit; Commit complete. SQL> alter system archive log current; System altered. SQL> select current_scn from v$database; CURRENT_SCN ----------- 1296106
flashback database时闪回到此scn
1.5 删除测试表空间
模拟表空间删除： SQL> drop tablespace testtbs including contents and datafiles; Tablespace dropped. SQL> select * from test.testtb; select * from test.testtb * ERROR at line 1: ORA-00942: table or view does not exist SQL> alter system archive log current; System altered.
2. 闪回测试
2.1 执行闪回
关闭数据库，并启动到mount状态： SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1052233728 bytes Fixed Size 2217704 bytes Variable Size 805308696 bytes Database Buffers 239075328 bytes Redo Buffers 5632000 bytes Database mounted. 检查数据库是否能够闪回的所需要的SCN： SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> set lines 120 SQL> select OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME,RETENTION_TARGET,FLASHBACK_SIZE/1024/1024/1024 as size_G,ESTIMATED_FLASHBACK_SIZE/1024/1024/1024 as estimate_size_G from v$flashback_database_log; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET SIZE_G ESTIMATE_SIZE_G -------------------- ------------------- ---------------- ---------- --------------- 1295593 2014-01-03 10:00:15 1440 .007629395 0 执行闪回操作： SQL> flashback database to scn 1296106; flashback database to scn 1296106 * ERROR at line 1: ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below ORA-01245: offline file 5 will be lost if RESETLOGS is done ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: '/app/oracle/product/10.2/dbs/UNNAMED00005' 闪回时数据库会报错，因为flashback不支持跨数据文件删除操作。
此时alert日志中的闪回信息如下：
Fri Jan 3 10:13:22 2014 flashback database to scn 1296106 Fri Jan 3 10:13:23 2014 Flashback Restore Start Flashback: created tablespace #6: 'TESTTBS' in the controlfile. Flashback: created OFFLINE file 'UNNAMED00005' for tablespace #6 in the controlfile. Filename was: '+DATA/beicenp/datafile/testtbs.dbf' when dropped. File will have to be restored from a backup and recovered. Flashback Restore Complete Flashback Media Recovery Start parallel recovery started with 2 processes Fri Jan 3 10:13:24 2014 Recovery of Online Redo Log: Thread 1 Group 2 Seq 62 Reading mem 0 Mem# 0 errs 0: +DATA/beicenp/onlinelog/group_2.258.798647877 Mem# 1 errs 0: +FLASH/beicenp/onlinelog/group_2.258.798647877 Fri Jan 3 10:13:26 2014 Recovery of Online Redo Log: Thread 1 Group 3 Seq 63 Reading mem 0 Mem# 0 errs 0: +DATA/beicenp/onlinelog/group_3.259.798647879 Mem# 1 errs 0: +FLASH/beicenp/onlinelog/group_3.259.798647881 Fri Jan 3 10:13:26 2014 Incomplete Recovery applied until change 1296111 Flashback Media Recovery Complete ORA-38795 signalled during: flashback database to scn 1296106...
日志中明确说明被删除的数据文件需要从备份中恢复。
2.2 恢复数据文件
被删除的数据文件需要使用备份的控制文件才能恢复，不能使用当前控制文件进行恢复。
关闭数据库并启动到nomount状态： SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 935329792 bytes Fixed Size 2025168 bytes Variable Size 310380848 bytes Database Buffers 616562688 bytes Redo Buffers 6361088 bytes 执行以下脚本恢复控制文件、数据文件和归档日志： run { allocate channel ch00 type disk; restore controlfile from '/app/oracle/cntl_8_1_835869096'; sql 'alter database mount'; restore datafile 5; restore archivelog from sequence 58; release channel ch00; } 然后执行database recover： SQL> recover database using backup controlfile until change 1296106; ORA-00279: change 1295287 generated at 01/03/2014 09:50:41 needed for thread 1 ORA-00289: suggestion : +ARCH ORA-00280: change 1295287 for thread 1 is in sequence #59 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 1295313 generated at 01/03/2014 09:51:23 needed for thread 1 ORA-00289: suggestion : +ARCH ORA-00280: change 1295313 for thread 1 is in sequence #60 ORA-00278: log file '+ARCH/beicenp/archivelog/2014_01_03/thread_1_seq_59.286.835871009' no longer needed for this recovery ORA-00279: change 1295318 generated at 01/03/2014 09:51:23 needed for thread 1 ORA-00289: suggestion : +ARCH ORA-00280: change 1295318 for thread 1 is in sequence #61 ORA-00278: log file '+ARCH/beicenp/archivelog/2014_01_03/thread_1_seq_60.289.835871011' no longer needed for this recovery ORA-00279: change 1295363 generated at 01/03/2014 09:52:04 needed for thread 1 ORA-00289: suggestion : +ARCH ORA-00280: change 1295363 for thread 1 is in sequence #62 ORA-00278: log file '+ARCH/beicenp/archivelog/2014_01_03/thread_1_seq_61.283.835869125' no longer needed for this recovery ORA-00279: change 1296104 generated at 01/03/2014 10:00:45 needed for thread 1 ORA-00289: suggestion : +ARCH ORA-00280: change 1296104 for thread 1 is in sequence #63 ORA-00278: log file '+ARCH/beicenp/archivelog/2014_01_03/thread_1_seq_62.284.835869645' no longer needed for this recovery Log applied. Media recovery complete. recover完成后，以resetlogs方式打开： SQL> alter database open resetlogs; Database altered. 打开后检查测试数据： SQL> select * from test.testtb; ID ---------- 3 1 2
以下是尝试用当前控制文件恢复数据文件
执行数据库闪回： SQL> flashback database to scn 1296106; flashback database to scn 1296106 * ERROR at line 1: ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below ORA-01245: offline file 5 will be lost if RESETLOGS is done ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: '/app/oracle/product/10.2/dbs/UNNAMED00005' 因数据文件已经被删除，闪回报错。 创建数据文件： SQL> alter database create datafile '/app/oracle/product/10.2/dbs/UNNAMED00005' as '+DATA/beicenp/datafile/testtbs.dbf'; Database altered. 执行RMAN脚本，恢复数据文件及归档日志： run { allocate channel ch00 type disk; restore datafile 5; restore archivelog from sequence 58; release channel ch00; } 再次执行闪回： SQL> flashback database to scn 1296106; flashback database to scn 1296106 * ERROR at line 1: ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below ORA-01245: offline file 5 will be lost if RESETLOGS is done ORA-01110: data file 5: '+DATA/beicenp/datafile/testtbs.dbf' 闪回依然报错。网上有些测试说在创建文件后再执行闪回，就可以恢复被删除的数据文件。其前提条件是需要有文件创建以来的所有归档日志。很多时候这个条件并不是很容易满足。 测试使用当前控制文件恢复： SQL> recover database until change 1296106; ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01245: offline file 5 will be lost if RESETLOGS is done ORA-01110: data file 5: '+DATA/beicenp/datafile/testtbs.dbf' 恢复仍然报错。这说明flashback后是不能使用当前控制文件恢复被删除的数据文件的，必须使用备份的控制文件。如果此时将控制文件替换为备份控制文件，然后再执行恢复，恢复仍然能够成功。