oracle跳过丢失的归档恢复datafile

偷学自大神Roger博客：http://www.killdb.com/2012/04/14/use-bbed-tool-to-skip-lost-archive-log-then-recover-datafile.html

oracle跳过丢失的归档恢复datafile

情景介绍：没有备份，归档存在但有几个归档丢失，库可以丢一些数据，最大程度的利用归档恢复数据。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE11.2.0.4.0Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> create tablespace test1 datafile '/oradata/node3/test1.dbf' size 100M extent management local uniform size 1M segment space management auto;
Tablespace created.

SQL> create table t1 tablespace test1 as select * from dba_objects;

Table created.

SQL>  create table t2 tablespace test1  as select * from dba_users;

Table created.

SQL> create table t3 tablespace test1  as select * from dba_users;
Table created.

SQL> archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       /oradata/arch

Oldest online log sequence     118

Next log sequence to archive   120

Current log sequence       120

SQL> select count(*) from t1;

  COUNT(*)

----------

     87011

SQL>  select count(*) from t2;

  COUNT(*)

----------

37

SQL>  select count(*) from t3;

  COUNT(*)

----------

37

SQL> delete from t2 where rownum <2;
1 row deleted.

SQL> delete from t3 where rownum <2;
1 row deleted.

SQL> commit;
Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       /oradata/arch

Oldest online log sequence     123

Next log sequence to archive   125

Current log sequence       125

SQL> delete from t1 where rownum <10001;
10000 rows deleted.

SQL> delete from t2 where rownum <2;
1 row deleted.

SQL> delete from t3 where rownum <2;
1 row deleted.

SQL> commit;
Commit complete.

SQL>  alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       /oradata/arch

Oldest online log sequence     127

Next log sequence to archive   129

Current log sequence       129

SQL> shutdown immediate;
Database closed.

Database dismounted.
ORACLE instance shut down.

[oracle@node3 arch]$ ls -ltr

total 67132

-rw-r----- 1 oracle dba 50049536 Apr 24 20:24 1_119_906682003.dbf

-rw-r----- 1 oracle dba 14743552 Apr 24 21:33 1_120_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:33 1_121_906682003.dbf

-rw-r----- 1 oracle dba     3072 Apr 24 21:34 1_122_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:34 1_124_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:34 1_123_906682003.dbf

-rw-r----- 1 oracle dba  3826176 Apr 24 21:35 1_125_906682003.dbf

-rw-r----- 1 oracle dba     1536 Apr 24 21:35 1_126_906682003.dbf

-rw-r----- 1 oracle dba     8704 Apr 24 21:35 1_127_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:35 1_128_906682003.dbf

++++备份数据文件

[oracle@node3 node3]$ cp /oradata/node3/test1.dbf /oradata/node3/test1.dbf.bak

[oracle@node3 node3]$ ls -ltr /oradata/node3/test*

-rw-r----- 1 oracle dba 104865792 Apr 24 21:38 /oradata/node3/test1.dbf

-rw-r----- 1 oracle dba 104865792 Apr 24 21:40 /oradata/node3/test1.dbf.bak

[oracle@node3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 24 16:35:07 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  222298112 bytes
Fixed Size                  1272912 bytes
Variable Size             109052848 bytes
Database Buffers          109051904 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

SQL> delete from t1 where rownum <10001;
10000 rows deleted.

SQL> delete from t2 where rownum <2;
1 row deleted.

SQL> delete from t3 where rownum <2; 
1 row deleted.

SQL> commit;
Commit complete.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       /oradata/arch

Oldest online log sequence     132

Next log sequence to archive   134

Current log sequence       134

SQL> 

SQL> delete from t1 where rownum <10001;

10000 rows deleted.

SQL> delete from t2 where rownum <2;

1 row deleted.

SQL> delete from t3 where rownum <2; 

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       /oradata/arch

Oldest online log sequence     136

Next log sequence to archive   138

Current log sequence       138

SQL> 

SQL> select count(*) from t1;

  COUNT(*)

----------

     57011

SQL> select count(*) from t2;

  COUNT(*)

----------

33

SQL> select count(*) from t3;

  COUNT(*)

----------

      33

SQL> alter database datafile 6 offline;

Database altered.

++++还原数据文件，并删掉部分归档

[oracle@node3 node3]$ rm test1.dbf

[oracle@node3 node3]$ mv test1.dbf.bak test1.dbf

[oracle@node3 arch]$ ls -ltr

total 74948

-rw-r----- 1 oracle dba 50049536 Apr 24 20:24 1_119_906682003.dbf

-rw-r----- 1 oracle dba 14743552 Apr 24 21:33 1_120_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:33 1_121_906682003.dbf

-rw-r----- 1 oracle dba     3072 Apr 24 21:34 1_122_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:34 1_124_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:34 1_123_906682003.dbf

-rw-r----- 1 oracle dba  3826176 Apr 24 21:35 1_125_906682003.dbf

-rw-r----- 1 oracle dba     1536 Apr 24 21:35 1_126_906682003.dbf

-rw-r----- 1 oracle dba     8704 Apr 24 21:35 1_127_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:35 1_128_906682003.dbf

-rw-r----- 1 oracle dba  4016128 Apr 24 21:42 1_129_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:42 1_130_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:42 1_132_906682003.dbf

-rw-r----- 1 oracle dba     9216 Apr 24 21:42 1_131_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:42 1_133_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:42 1_135_906682003.dbf

-rw-r----- 1 oracle dba  3931136 Apr 24 21:42 1_134_906682003.dbf

-rw-r----- 1 oracle dba     8704 Apr 24 21:42 1_136_906682003.dbf

-rw-r----- 1 oracle dba     1024 Apr 24 21:42 1_137_906682003.dbf

[oracle@node3 arch]$ rm 1_132_906682003.dbf 

[oracle@node3 arch]$ rm 1_131_906682003.dbf

 


尝试进行recover

SQL> alter database datafile 6 online;

alter database datafile 6 online

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/oradata/node3/test1.dbf'

SQL> recover datafile 6;

ORA-00279: change 913883093 generated at 04/24/2016 21:38:41 needed for thread

1

ORA-00289: suggestion : /oradata/arch/1_129_906682003.dbf

ORA-00280: change 913883093 for thread 1 is in sequence #129

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 913883399 generated at 04/24/2016 21:42:03 needed for thread

1

ORA-00289: suggestion : /oradata/arch/1_130_906682003.dbf

ORA-00280: change 913883399 for thread 1 is in sequence #130

ORA-00279: change 913883402 generated at 04/24/2016 21:42:04 needed for thread

1

ORA-00289: suggestion : /oradata/arch/1_131_906682003.dbf

ORA-00280: change 913883402 for thread 1 is in sequence #131

ORA-00308: cannot open archived log '/oradata/arch/1_131_906682003.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> select LHSEQ,LHNAM,LHRLS,LHRLC from x$kcclh;

     LHSEQ LHNAM    LHRLS     LHRLC

---------- ---------------------------------------- -------------------------------- ---------------------

       119 /oradata/arch/1_119_906682003.dbf    7053780     03/17/2016 00:06:43

       120 /oradata/arch/1_120_906682003.dbf    7053780     03/17/2016 00:06:43

       121 /oradata/arch/1_121_906682003.dbf    7053780     03/17/2016 00:06:43

       122 /oradata/arch/1_122_906682003.dbf    7053780     03/17/2016 00:06:43

       123 /oradata/arch/1_123_906682003.dbf    7053780     03/17/2016 00:06:43

       124 /oradata/arch/1_124_906682003.dbf    7053780     03/17/2016 00:06:43

       125 /oradata/arch/1_125_906682003.dbf    7053780     03/17/2016 00:06:43

       126 /oradata/arch/1_126_906682003.dbf    7053780     03/17/2016 00:06:43

       127 /oradata/arch/1_127_906682003.dbf    7053780     03/17/2016 00:06:43

       128 /oradata/arch/1_128_906682003.dbf    7053780     03/17/2016 00:06:43

       129 /oradata/arch/1_129_906682003.dbf    7053780     03/17/2016 00:06:43

       130 /oradata/arch/1_130_906682003.dbf    7053780     03/17/2016 00:06:43

       131 /oradata/arch/1_131_906682003.dbf    7053780     03/17/2016 00:06:43

       132 /oradata/arch/1_132_906682003.dbf    7053780     03/17/2016 00:06:43

       133 /oradata/arch/1_133_906682003.dbf    7053780     03/17/2016 00:06:43

       134 /oradata/arch/1_134_906682003.dbf    7053780     03/17/2016 00:06:43

       135 /oradata/arch/1_135_906682003.dbf    7053780     03/17/2016 00:06:43

       136 /oradata/arch/1_136_906682003.dbf    7053780     03/17/2016 00:06:43

       137 /oradata/arch/1_137_906682003.dbf    7053780     03/17/2016 00:06:43

SQL>

x$kcclh  - Controlfile Log History records

*** 2016-04-25 00:58:04.992

Started Serial Media Recovery

Dumping database incarnation table:

Resetlogs 0 scn and time: 0x0000.006ba1d4 03/17/2016 00:06:43

Recovery target incarnation = 1, activation ID = 0

Influx buffer limit = 22785 min(50% x 45570, 100000)

Start recovery at thread 1 ckpt scn 913883402 logseq 131 block 2 --恢复到scn 913883402   logseq 131  

Initial buffer sizes: read 1024K, overflow 832K, change 805K

*** 2016-04-25 00:58:05.021

Media Recovery add redo thread 1

*** 2016-04-25 00:58:07.848

Media Recovery Log /oradata/arch/1_131_906682003.dbf

*** 2016-04-25 00:58:07.852

Media Recovery Log /oradata/arch/1_131_906682003.dbf

*** 2016-04-25 00:58:07.855

Media Recovery drop redo thread 1

KCBR: Number of read descriptors = 1024

KCBR: Influx buffers flushed = 1 times

大家看看这这里，关键性的错误

ORA-00289: suggestion : /oradata/arch/1_131_906682003.dbf

ORA-00280: change 913883402 for thread 1 is in sequence #131

ORA-00308: cannot open archived log '/oradata/arch/1_131_906682003.dbf'

ORA-27037: unable to obtain file status

BBED> set file 6 block 1

FILE#          6

BLOCK#         1

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes                   @484     

   struct kcvcpscn, 8 bytes                 @484     

      ub4 kscnbas                           @484      0x3678c10a=913883402  +++这里对应的scn要改

      ub2 kscnwrp                           @488      0x0000

   ub4 kcvcptim                             @492      0x363e272c

   ub2 kcvcpthr                             @496      0x0001

   union u, 12 bytes                        @500     

      struct kcvcprba, 12 bytes             @500     

         ub4 kcrbaseq                       @500      0x00000083 --->131  这里对应的log seq要改

         ub4 kcrbabno                       @504      0x00000002 --->2   

         ub2 kcrbabof                       @508      0x0000     --->  RTCKP_RBA_BOF

   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

rba ? redo block address

1.改scn

2.改seq

scn 怎么查？ v$Log_history ?

 SQL> select recid,thread#,sequence#,first_change#,next_change# from v$log_history;

     RECID    THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

---------- ---------- ---------- ------------- ------------

       122    1     123     913882870  913882873

       123    1     124     913882873  913882876

       124    1     125     913882876  913882946

       125    1     126     913882946  913882952

       126    1     127     913882952  913882955

       127    1     128     913882955  913882958

       128    1     129     913882958  913883399

       129    1     130     913883399  913883402

       130    1     131     913883402  913883405

       131    1     132     913883405  913883408

       132    1     133     913883408  913883411

     RECID    THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

---------- ---------- ---------- ------------- ------------

       133    1     134     913883411  913883435

       134    1     135     913883435  913883438

       135    1     136     913883438  913883441

       136    1     137     913883441  913883444

136 rows selected.

scn改成 913883408=3678C110

seq改成 133=85

BBED> modify /x 10c17836 offset 484

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /oradata/node3/test1.dbf (6)

 Block: 1                Offsets:  484 to  995           Dba:0x01800001

------------------------------------------------------------------------

 10c17836 00000000 2c273e36 01000000 83000000 02000000 00008b91 02000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 0d000d00 0d000100 00000000 00000000 00000000 02008001 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> modify /x 85 offset 500

 File: /oradata/node3/test1.dbf (6)

 Block: 1                Offsets:  500 to 1011           Dba:0x01800001

------------------------------------------------------------------------

 85000000 02000000 00008b91 02000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000 

 00000000 02008001 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply

Check value for File 6, Block 1:

current = 0x5f85, required = 0x5f85

SQL> recover datafile 6;

ORA-00279: change 913883408 generated at 04/24/2016 21:42:04 needed for thread

1

ORA-00289: suggestion : /oradata/arch/1_133_906682003.dbf

ORA-00280: change 913883408 for thread 1 is in sequence #133

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 913883411 generated at 04/24/2016 21:42:06 needed for thread

1

ORA-00289: suggestion : /oradata/arch/1_134_906682003.dbf

ORA-00280: change 913883411 for thread 1 is in sequence #134

ORA-00279: change 913883435 generated at 04/24/2016 21:42:51 needed for thread

1

ORA-00289: suggestion : /oradata/arch/1_135_906682003.dbf

ORA-00280: change 913883435 for thread 1 is in sequence #135

Log applied.

Media recovery complete.

SQL> alter database datafile 6 online;

Database altered.

SQL> show user

USER is "SYS"

SQL> select count(*) from t1;

  COUNT(*)

----------

     57011

SQL> select count(*) from t2;

  COUNT(*)

----------

33

SQL> select count(*) from t3;

  COUNT(*)

----------

33