1、故障概要
一套基于ASM的RAC数据库，处于非归档模式，现场人员误将其中的一个数据文件改成了offline状态，等到发现异常时，redo日志已经被覆盖，没有办法recover该数据文件。
本文主要记录测试环境模拟本故障，以及使用BBED修复的过程。
2、故障模拟及处理办法
（1）、准备环境，创建一个名为test的表空间，该表空间下有4个数据文件，然后在该表空间下存放一些数据：
|
drop user test cascade; drop tablespace test including contents and datafiles;
create tablespace test datafile '+dg_data' size 10m; alter tablespace test add datafile '+dg_data' size 10m; alter tablespace test add datafile '+dg_data' size 10m; alter tablespace test add datafile '+dg_data' size 10m;
create user test identified by test; grant dba to test; conn test/test create table mm tablespace test as select * from dba_objects; |
（2）、模拟故障，将test表空间下的某个数据文件置于offline状态，同时多次切换日志，后期进行recover操作时，会提示无法找到日志文件。
|
alter database datafile 9 offline drop; alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile;
SQL> select file_name, file_id, online_status from dba_data_files; FILE_NAME FILE_ID ONLINE_ ------------------------------------------------------------ ---------- ------- +DG_DATA/racdb/datafile/users.274.1154847857 4 ONLINE +DG_DATA/racdb/datafile/undotbs1.270.1154847857 3 ONLINE +DG_DATA/racdb/datafile/sysaux.259.1154847857 2 ONLINE +DG_DATA/racdb/datafile/system.275.1154847857 1 SYSTEM +DG_DATA/racdb/datafile/undotbs2.262.1154847967 5 ONLINE +DG_DATA/racdb/datafile/test.265.1154869715 6 ONLINE +DG_DATA/racdb/datafile/test.257.1154869715 7 ONLINE +DG_DATA/racdb/datafile/test.287.1154869717 8 ONLINE +DG_DATA/racdb/datafile/test.267.1154869719 9 RECOVER |
（3）、模拟常规的恢复步骤，recover数据文件时，由于是非归档模式，需要的redo日志已经被覆盖。
|
SQL> alter database datafile 9 online; alter database datafile 9 online * ERROR at line 1: ORA-01113: file 9 needs media recovery ORA-01110: data file 9: '+DG_DATA/racdb/datafile/test.258.1154811361'
SQL> SQL> recover datafile 9; ORA-00279: change 971756 generated at 12/05/2023 20:56:01 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_5_1154807853.dbf ORA-00280: change 971756 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_5_1154807853.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_5_1154807853.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 |
（4）、将数据文件从ASM 复制到文件系统中，bbed修复，修复完成后，再将修复后的数据文件复制回ASM。
|
[grid@11grac1 ~]$ asmcmd cp +DG_DATA/racdb/datafile/test.267.1154869719 /tmp/datafile9
bbed修改/tmp/datafile9这个文件的SCN号： SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 1040862 2 1040862 3 1040862 4 1040862 5 1040862 6 1040862 7 1040862 8 1040862 9 1040312
9 rows selected.
SQL>
1040862（十进制） => 0FE1DE（十六进制）
set dba 9,1 set offset 484 m /x DEE10F sum apply
[grid@11grac1 ~]$ asmcmd cp /tmp/datafile9 +DG_DATA copying /tmp/datafile9 -> +DG_DATA/datafile9
SQL> alter database rename file '+DG_DATA/racdb/datafile/test.267.1154869719' to '+DG_DATA/datafile9';
Database altered.
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 1040862 2 1040862 3 1040862 4 1040862 5 1040862 6 1040862 7 1040862 8 1040862 9 1040862
9 rows selected.
SQL> recover datafile 9; Media recovery complete. SQL> alter database datafile 9 online;
Database altered.
SQL> SQL> select file_name, file_id, online_status from dba_data_files;
FILE_NAME FILE_ID ONLINE_ ------------------------------------------------------------ ---------- ------- +DG_DATA/racdb/datafile/users.274.1154847857 4 ONLINE +DG_DATA/racdb/datafile/undotbs1.270.1154847857 3 ONLINE +DG_DATA/racdb/datafile/sysaux.259.1154847857 2 ONLINE +DG_DATA/racdb/datafile/system.275.1154847857 1 SYSTEM +DG_DATA/racdb/datafile/undotbs2.262.1154847967 5 ONLINE +DG_DATA/racdb/datafile/test.265.1154869715 6 ONLINE +DG_DATA/racdb/datafile/test.257.1154869715 7 ONLINE +DG_DATA/racdb/datafile/test.287.1154869717 8 ONLINE +DG_DATA/datafile9 9 ONLINE |
3、建议
上述过程，是将整个数据文件复制到文件系统中进行修复，如果数据文件比较大，可以考虑将特定的数据块使用dd工具复制到文件系统中进行修改，修改完成后，再使用dd工具将数据块复制回ASM的磁盘中。