[[toc]]

适用范围

11G

问题概述

数据文件offline drop后，无法online

问题原因

丢失了归档日志

解决方案

创建表空间：

  
create tablespace tbs1 datafile '/u01/app/oradata/orcl/tbs1_1.dbf' size 100m ;
alter tablespace tbs1 add datafile '/u01/app/oradata/orcl/tbs1_2.dbf' size 100m;

创建表：

  
create table scott.ttbs1 (f1 number);

模拟业务，执行多次：

  
declare
begin
for i in 1..1000 loop
insert into scott.ttbs1 values (i);
end loop;
commit;
end;
/

alter system archive log current;

alter system archive log current;

==============================
模拟数据文件offline

alter database datafile 9 offline drop;

select file#,name,status,checkpoint_change# from v$datafile order by 1;

     FILE# NAME                                                                                     STATUS  CHECKPOINT_CHANGE#
---------- ---------------------------------------------------------------------------------------- ------- ------------------
         1 /u01/app/oradata/orcl/system01.dbf                                                       SYSTEM             2661784
         2 /u01/app/oradata/orcl/sysaux01.dbf                                                       ONLINE             2661784
         3 /u01/app/oradata/orcl/undotbs01.dbf                                                      ONLINE             2661784
         4 /u01/app/oradata/orcl/users01.dbf                                                        ONLINE             2661784
         5 /u01/app/oradata/orcl/users001.dbf                                                       ONLINE             2661784
         6 /u01/app/oradata/orcl/users02.dbf                                                        ONLINE             2661784
         7 /u01/app/oradata/orcldg/users03.dbf                                                      ONLINE             2661784
         8 /u01/app/oradata/orcldg/users04.dbf                                                      ONLINE             2661784
         9 /u01/app/oradata/orcl/tbs1_1.dbf                                                         RECOVER            2661290
        10 /u01/app/oradata/orcl/tbs1_2.dbf                                                         ONLINE             2661784



select name,thread#,SEQUENCE#,FIRST_CHANGE#,RESETLOGS_CHANGE#,next_change# from v$archived_log where dest_id=1 and first_change#=2661290 order by 3;

NAME                                                       THREAD#  SEQUENCE# FIRST_CHANGE# RESETLOGS_CHANGE# NEXT_CHANGE#
------------------------------------------------------- ---------- ---------- ------------- ----------------- ------------
/archivelog/1_261_1008423780.dbf                                 1        261       2661290                 1      2661463

Select dest_id,sequence#,name from v$archived_log where &scn + 1 between first_change# and next_change# ;
Select name,sequence# from v$archived_log where sequence# >= 261;
//需要261归档

rm -f /archivelog/1_261_1008423780.dbf

  
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: '/u01/app/oradata/orcl/tbs1_1.dbf'

  
SQL> recover datafile 9;
ORA-00279: change 2661290 generated at 08/18/2022 08:55:45 needed for thread 1
ORA-00289: suggestion : /archivelog/1_261_1008423780.dbf
ORA-00280: change 2661290 for thread 1 is in sequence #261


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/archivelog/1_261_1008423780.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 '/archivelog/1_261_1008423780.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

==============================

安装BBED：

  
cd /software
unzip bbed.zip

chown oracle:oinstall ssbbded.o
chown oracle:oinstall sbbdpt.o
chown oracle:oinstall bbedus.msb
chown oracle:oinstall bbedus.msg

chmod 644 ssbbded.o
chmod 644 sbbdpt.o
chmod 644 bbedus.msb
chmod 644 bbedus.msg

cp *.o $ORACLE_HOME/rdbms/lib/
cp *.ms* $ORACLE_HOME/rdbms/mesg/

cd $ORACLE_HOME/rdbms/lib
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

which bbed
/u01/app/oracle/product/11.2.0/dbhome_1/bin/bbed

==============================
通过BBED推进SCN实现数据文件重新online

生成数据文件清单列表：

  
sqlplus -S / as sysdba << "EOF" > /tmp/listfile.txt
set feedback      off
set heading       off
set line 300 pages 999
select file#||chr(9)||name||chr(9)||bytes from v$datafile where file# in (9,10);
EOF

配置BBED的parfile

  
cat << "EOF" >> /tmp/bbed.par
mode=edit
listfile=/tmp/listfile.txt
blocksize=8192
EOF

  
bbed parfile=/tmp/bbed.par password=blockedit
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     9  /u01/app/oradata/orcl/tbs1_1.dbf                                 12800
    10  /u01/app/oradata/orcl/tbs1_2.dbf                                 12800

BBED> set file 9 block 1

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x00289baa  ==> 这里就是SCN的16进制，目的就是要推进这里的SCN号，换算成10进制就是 2661290
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x42579891
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x00000105
         ub4 kcrbabno                       @504      0x00000002
         ub2 kcrbabof                       @508      0x0010
   ub1 kcvcpetb[0]                          @512      0x02
   ub1 kcvcpetb[1]                          @513      0x00
   ub1 kcvcpetb[2]                          @514      0x00
   ub1 kcvcpetb[3]                          @515      0x00
   ub1 kcvcpetb[4]                          @516      0x00
   ub1 kcvcpetb[5]                          @517      0x00
   ub1 kcvcpetb[6]                          @518      0x00
   ub1 kcvcpetb[7]                          @519      0x00

因为Linux的字节序是little endian，编码需倒序

0x289D98

modify /x 989D28 dba 9,1 offset 484

sum apply

==============================
恢复 datafile 9

  
recover datafile 9;
alter database datafile 9 online;
alter system checkpoint;

注意

bbed工具是Oracle内部工具，慎用。