oracle跳过丢失的归档恢复datafile

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zhengwei125/article/details/51692260

偷学自大神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


 

 

 

 

没有更多推荐了,返回首页