Oracle 10g RAC全库flashback
因业务原因，今天需要做一次全库flashback。以下是操作全过程：
1、确认主库是否能flashback到需要的时间点
在节点1上执行：
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
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
-------------------------------- ----------------------------- -------------------------- ---------- ------------------------
2.5369E+10 2013-08-14 13:16:18 2880 360.928505 154.009277
在确认了数据库可以flashback以后，进行以下步骤。
2、停止备库
此数据库有一个Dataguard备库，由于希望备库能够保持与flashback之前的主库一致。我做了以下操作：
2.1 同步备库
在业务停止后，在主库上执行多次
SQL> alter system archive log current;
并确保主库上归档日志和在线日志的数据完全同步到备库上。
2.2 禁止主库向备库传输日志
由于主库即将要进行flashback，此时最好断开主库和备库的联系，避免备库受影响。
在节点1上执行：
SQL> alter system set log_archive_dest_state_2=defer;
禁止主库再向备库传输日志
2.3 停止备库
为确保flashback操作不会影响到备库，手工停止备库。
SQL> shutdown immediate;
3、主库flashback
3.1 停止主库
在所有节点上分别执行
SQL> shutdown immediate;
将数据库所有实例停掉。
3.2 执行flashback操作
执行flashback时经历了一些波折，以下是全过程。
1) 把节点1启动到mount状态
sql> startup mount;
2) 执行flashback database脚本
由于是通过外网进行远程操作，担心在执行flashback database时出现网络中断，导致flashback出错，因此写了个脚本(flashback_database.sh)放在后台执行。
脚本内容如下：
#!/bin/bash
export ORACLE_HOME=/app/oracle/product/server_rac/10.2.0.3
export ORACLE_SID=BEICENP1$ORACLE_HOME/bin/sqlplus "/as sysdba" <<EOF
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
flashback database to timestamp to_timestamp('2013-08-17 13:40:30','yyyy-mm-dd hh24:mi:ss');
exit;
EOF然后将脚本放在后台执行：
nohup /bin/bash flashback_database.sh > flashback_database.sh.out 2>&1 &
3) 第一次解决flashback报错
flashback的脚本刚执行了一会儿就报错了。报错信息表明缺少了归档日志。
SQL> flashback database to timestamp to_timestamp('2013-08-17 13:40:30','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38761: redo log sequence 26603 in thread 2, incarnation 6 could not be
accessed出现这个问题的原因是归档日志在做完RMAN备份后被删除。于是想到直接从带库恢复，但后来一想这部分日志可能在备库上还有。从备库copy过来肯定比从带库恢复要快。于是将归档日志从备库copy到主库上，然后再次执行flashback脚本。
4) 第二次解决flashback报错
flashback的脚本刚执行了一会儿又报错了。报错信息与上一次一样，缺少了归档日志
SQL> flashback database to timestamp to_timestamp('2013-08-17 13:40:30','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38761: redo log sequence 26603 in thread 2, incarnation 6 could not be
accessed我确定需要的归档日志已经从备库copy到主库了。那为什么还报错呢？很快我就想到了，是因为控制文件中没有这些归档日志的信息。主库在做完RMAN备份后，归档日志就被RMAN自动删除了。归档日志copy过来以后，还需要注册到主库的控制文件中。
注册归档日志：
$> rman target /
RMAN> catalog start with '/oracle/arch/BEICENP';
searching for all files that match the pattern /oracle/arch/BEICENP
List of Files Unknown to the Database
=====================================
File Name: /oracle/arch/BEICENP/2_26600_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26601_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26602_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26603_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26604_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26605_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26606_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53800_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53801_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53802_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53780_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53781_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53782_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53783_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53784_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53785_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53786_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53787_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53788_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53789_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53790_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53791_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53792_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53793_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53794_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53795_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53796_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53797_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53798_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53799_787686703.dbfDo you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging doneList of Cataloged Files
=======================
File Name: /oracle/arch/BEICENP/2_26600_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26601_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26602_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26603_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26604_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26605_787686703.dbf
File Name: /oracle/arch/BEICENP/2_26606_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53800_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53801_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53802_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53780_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53781_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53782_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53783_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53784_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53785_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53786_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53787_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53788_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53789_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53790_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53791_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53792_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53793_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53794_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53795_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53796_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53797_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53798_787686703.dbf
File Name: /oracle/arch/BEICENP/1_53799_787686703.dbfRMAN> EXIT
归档日志注册完成后，再次执行flashback脚本。
5) flashback database完成
过了20多分钟后，flashback完成了。但出现了warning信息。warning信息出现在flashback脚本的执行日志里。
SQL> flashback database to timestamp to_timestamp('2013-08-17 13:40:30','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 90 will be lost if RESETLOGS is done
ORA-01111: name for data file 90 is unknown - rename to correct file
ORA-01110: data file 90:
'/app/oracle/product/server_rac/10.2.0.3/dbs/UNNAMED00090'以上的信息已经明确表明flashback实际上已经成功了，只是在打开数据库时会报错。查看了一下alert日志，里面也有相关提示。
以下是alert日志信息：
Sat Aug 17 20:45:46 2013
flashback database to timestamp to_timestamp('2013-08-17 13:40:30','yyyy-mm-dd hh24:mi:ss')
Sat Aug 17 20:46:27 2013
Flashback Restore Start
Sat Aug 17 21:03:17 2013
Flashback: created tablespace #3992: 'CUT_MAY172013_INDX' in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00091' for tablespace #3992 in the controlfile.
Filename was:
'/oracle/data1/BEICENP/CUT_MAY172013_INDX.dbf' when dropped.
File will have to be restored from a backup and recovered.
Flashback: created tablespace #3991: 'CUT_MAY172013_DATA' in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00090' for tablespace #3991 in the controlfile.
Filename was:
'/oracle/data2/BEICENP/CUT_MAY172013_DATA.dbf' when dropped.
File will have to be restored from a backup and recovered.
Deleted file /oracle/data1/BEICENP/CUT_AUG232013_INDX.dbf
Flashback: deleted datafile #88 in tablespace #4188 from control file.
Flashback: dropped tablespace #4188: 'CUT_AUG232013_INDX' from the control file.
Deleted file /oracle/data2/BEICENP/CUT_AUG232013_DATA.dbf
Flashback: deleted datafile #87 in tablespace #4187 from control file.
Flashback: dropped tablespace #4187: 'CUT_AUG232013_DATA' from the control file.
Flashback Restore Complete
Flashback Media Recovery Start
parallel recovery started with 7 processes
Sat Aug 17 21:03:35 2013
Flashback Media Recovery Log /oracle/arch/BEICENP/2_26603_787686703.dbf
Flashback Media Recovery Log /oracle/arch/BEICENP/1_53793_787686703.dbf
Sat Aug 17 21:06:15 2013
Flashback Media Recovery Log /oracle/arch/BEICENP/1_53794_787686703.dbf
Sat Aug 17 21:06:40 2013
Flashback Media Recovery Log /oracle/arch/BEICENP/2_26604_787686703.dbf
Sat Aug 17 21:08:13 2013
Incomplete Recovery applied until change 25388917158
Flashback Media Recovery Complete
ORA-38795 signalled during: flashback database to timestamp to_timestamp('2013-08-17 13:40:30','yyyy-mm-dd hh24:mi:ss')...因为对业务应用比较熟悉，看了一下alert日志我就知道是怎么回事了。出现这个问题的原因是: 在13:40:30(需要恢复到的时间点)和20:46:27(flashback开始时间)之间，应用程序删除了2013-05-17号的两个表空间(各有一个数据文件也连带被删除)并创建了2013-08-23号的两个表空间。因此在flashback database的时候先创建了2013-05-17号的表空间(其实是个空表空间，里面没有任何数据，而alert也明确提示如果需要表空间里的数据，必须从备份中恢复数据文件再做recover. File will have to be restored from a backup and recovered.)，再删除了2013-08-23号的表空间。好在业务并不需要使用2013-05-17号的表空间上的数据, 这两个表空间可以删除，否则就要做表空间恢复了。而2013-08-23号本来就没有数据, 这天的表空间也不需要。
6) rename datafile
虽然业务上用不到2013-05-17号表空间，但oracle自动产生的数据文件名看起来太别扭了(参见前面的alert日志)，于是决定重命名一下。
先检查一下表空间和文件名
SQL> select name from v$tablespace where ts#=3992;
NAME
------------------------------
CUT_MAY172013_INDXSQL> select name from v$datafile where file#=91;
NAME
--------------------------------------------------------------------------------
/app/oracle/product/server_rac/10.2.0.3/dbs/UNNAMED00091文件重命名，正确的文件名在alert日志中有
SQL> alter database create datafile '/app/oracle/product/server_rac/10.2.0.3/dbs/UNNAMED00091' as '/oracle/data1/BEICENP/CUT_MAY172013_INDX.dbf' reuse;
Database altered.
检查另一表空间和文件名
SQL> select name from v$tablespace where ts#=3991;
NAME
------------------------------
CUT_MAY172013_DATASQL> select name from v$datafile where file#=90;
NAME
--------------------------------------------------------------------------------
/app/oracle/product/server_rac/10.2.0.3/dbs/UNNAMED00090文件重命名
SQL> alter database create datafile '/app/oracle/product/server_rac/10.2.0.3/dbs/UNNAMED00090' as '/oracle/data2/BEICENP/CUT_MAY172013_DATA.dbf' reuse;
Database altered.
7) 以read only方式打开
打开时报错：
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01245: offline file 90 will be lost if RESETLOGS is done
ORA-01110: data file 90: '/oracle/data2/BEICENP/CUT_MAY172013_DATA.dbf'
以resetlogs方式打开也报错：
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 90 will be lost if RESETLOGS is done
ORA-01110: data file 90: '/oracle/data2/BEICENP/CUT_MAY172013_DATA.dbf'
由于2013-05-27号的数据根本不需要，尝试删除表空间
SQL> drop tablespace CUT_MAY172013_DATA including contents and datafiles;
drop tablespace CUT_MAY172013_DATA including contents and datafiles
*
ERROR at line 1:
ORA-01109: database not open数据库还没打开，不能做drop tablespace. 只能drop datafile了
SQL> alter database datafile '/oracle/data2/BEICENP/CUT_MAY172013_DATA.dbf' offline drop;
Database altered.
SQL> alter database datafile '/oracle/data1/BEICENP/CUT_MAY172013_INDX.dbf' offline drop;
Database altered.
然后再以read only方式打开数据库
SQL> alter database open read only;
Database altered.
8) 以resetlogs方式打开
在确认数据没有问题之后，以resetlogs方式打开数据库。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup mount;
ORACLE instance started.Total System Global Area 8589934592 bytes
Fixed Size 2142160 bytes
Variable Size 3248312368 bytes
Database Buffers 5335154688 bytes
Redo Buffers 4325376 bytes
Database mounted.
SQL> alter database open resetlogs;Database altered.
9) 启动另一节的数据库
连接到节点2上，启动数据库。
SQL> startup;
备注：
通过这次全库flashback让我注意到一个以前没有注意的地方，即：flashback database并不能解决表空间被删除的问题，只能通过恢复来解决。
