sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 产品DBA > Oracle BBED >

【案例】Oracle使用bbed跳过丢失的归档进行恢复数据 详细步骤

时间:2016-12-03 12:25   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:测试环境Oracle数据库归档部分丢失,而需要进行恢复,使用bbed跳过丢失的归档进行恢复数据测试。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 使用bbed跳过丢失的归档进行recover datafile

使用bbed 跳过丢失的归档进行recover datafile

SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS FOR Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> CREATE tablespace alex datafile '/oracle/product/oradata/red5db/alex01.dbf' SIZE 10M
  2  extent management LOCAL uniform SIZE 1M
  3  segment SPACE management auto;

Tablespace created.

SQL> CREATE TABLE t1 tablespace alex AS SELECT * FROM dba_objects;  
TABLE created.

SQL> CREATE TABLE t2 tablespace alex AS SELECT * FROM dba_users;
TABLE created.

SQL> CREATE TABLE t3 tablespace alex AS SELECT * FROM dba_users;
TABLE created.

SQL> archive log list;

DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/arch1
Oldest online log SEQUENCE     1
NEXT log SEQUENCE TO archive   1
CURRENT log SEQUENCE           1

SQL> ALTER system switch logfile;
System altered.

SQL> ALTER system switch logfile;
System altered.

SQL> ALTER system switch logfile;
System altered.

SQL> archive log list;

DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/arch1
Oldest online log SEQUENCE     2
NEXT log SEQUENCE TO archive   4
CURRENT log SEQUENCE           4

SQL> SELECT COUNT(*) FROM t1;

  COUNT(*)
----------
     50411

SQL> SELECT COUNT(*) FROM t2;

  COUNT(*)
----------
        34

SQL> SELECT COUNT(*) FROM t3;

  COUNT(*)
----------
        34

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> archive log list;

DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/arch1
Oldest online log SEQUENCE     3
NEXT log SEQUENCE TO archive   5
CURRENT log SEQUENCE           5

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> ALTER system switch logfile;
System altered.

SQL> ALTER system switch logfile;
System altered.

SQL> ALTER system switch logfile;
System altered.

SQL> archive log list;

DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/arch1
Oldest online log SEQUENCE     7
NEXT log SEQUENCE TO archive   9
CURRENT log SEQUENCE           9
SQL>

SQL> shutdown immediate;

DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.

SQL> !
[oracle@red-db1 ~]$ cd /oracle/arch1

[oracle@red-db1 arch1]$ ls -ltr

-rw-r----- 1 oracle oinstall 6760448 12-20 21:52 1_1_738279142.dbf
-rw-r----- 1 oracle oinstall    1024 12-20 21:52 1_2_738279142.dbf
-rw-r----- 1 oracle oinstall    3072 12-20 21:52 1_3_738279142.dbf
-rw-r----- 1 oracle oinstall    5632 12-20 21:53 1_4_738279142.dbf
-rw-r----- 1 oracle oinstall 4369408 12-20 21:54 1_5_738279142.dbf
-rw-r----- 1 oracle oinstall    2048 12-20 21:54 1_6_738279142.dbf
-rw-r----- 1 oracle oinstall   12800 12-20 21:54 1_7_738279142.dbf
-rw-r----- 1 oracle oinstall    1024 12-20 21:54 1_8_738279142.dbf

[oracle@red-db1 arch1]$ cp /oracle/product/oradata/red5db/alex01.dbf /oracle/product/oradata/red5db/alex01_bak.dbf

[oracle@red-db1 arch1]$ ls -ltr /oracle/product/oradata/red5db/alex01*

-rw-r----- 1 oracle oinstall 10493952 12-20 21:58 /oracle/product/oradata/red5db/alex01.dbf
-rw-r----- 1 oracle oinstall 10493952 12-20 21:58 /oracle/product/oradata/red5db/alex01_bak.dbf
[oracle@red-db1 udump]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production ON Mon DEC 20 21:58:03 2010
Copyright (c) 1982, 2010, 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            /oracle/arch1
Oldest online log SEQUENCE     11
NEXT log SEQUENCE TO archive   13
CURRENT log SEQUENCE           13

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> archive log list;

DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/arch1
Oldest online log SEQUENCE     11
NEXT log SEQUENCE TO archive   13
CURRENT log SEQUENCE           13

SQL> shutdown immediate;
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.

SQL> !
[oracle@red-db1 udump]$ cd /oracle/product/oradata/red5db

[oracle@red-db1 red5db]$ rm alex01.dbf

[oracle@red-db1 red5db]$ mv alex01_bak.dbf alex01.dbf

[oracle@red-db1 udump]$ cd /oracle/arch1

[oracle@red-db1 arch1]$ ls -ltr

total 17028
-rw-r----- 1 oracle oinstall 6760448 Dec 20 21:52 1_1_738279142.dbf
-rw-r----- 1 oracle oinstall    1024 Dec 20 21:52 1_2_738279142.dbf
-rw-r----- 1 oracle oinstall    3072 Dec 20 21:52 1_3_738279142.dbf
-rw-r----- 1 oracle oinstall    5632 Dec 20 21:53 1_4_738279142.dbf
-rw-r----- 1 oracle oinstall 4369408 Dec 20 21:54 1_5_738279142.dbf
-rw-r----- 1 oracle oinstall    2048 Dec 20 21:54 1_6_738279142.dbf
-rw-r----- 1 oracle oinstall   12800 Dec 20 21:54 1_7_738279142.dbf
-rw-r----- 1 oracle oinstall    1024 Dec 20 21:54 1_8_738279142.dbf
-rw-r----- 1 oracle oinstall 4751872 Dec 20 22:00 1_9_738279142.dbf
-rw-r----- 1 oracle oinstall  218112 Dec 20 22:00 1_10_738279142.dbf
-rw-r----- 1 oracle oinstall  523776 Dec 20 22:00 1_11_738279142.dbf
-rw-r----- 1 oracle oinstall  711168 Dec 20 22:00 1_12_738279142.dbf

[oracle@red-db1 arch1]$ rm 1_2_738279142.dbf
[oracle@red-db1 arch1]$ rm 1_9_738279142.dbf
[oracle@red-db1 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production ON Mon DEC 20 22:15:04 2010
Copyright (c) 1982, 2010, Oracle.  ALL Rights Reserved.

Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - Production
WITH the Partitioning, OLAP, DATA MiningOracleо AND REAL Application Testing options

SQL> recover datafile 36;

ORA-00279: CHANGE 5068824 generated at 12/20/2010 21:58:30 needed FOR thread 1
ORA-00289: suggestion : /oracle/arch1/1_9_738279142.dbf        <== 该归档已经被我RM掉了
ORA-00280: CHANGE 5068824 FOR thread 1 IS IN SEQUENCE #9

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot OPEN archived log '/oracle/arch1/1_9_738279142.dbf'
ORA-27037: unable TO obtain file STATUS
Linux Error: 2: No such file OR directory
Additional information: 3

ORA-00308: cannot OPEN archived log '/oracle/arch1/1_9_738279142.dbf'
ORA-27037: unable TO obtain file STATUS
Linux Error: 2: No such file OR directory
Additional information: 3

SQL> SELECT RTCKP_SCN,RTCKP_TIM,RTCKP_RBA_SEQ,RTCKP_RBA_BNO,RTSEQ FROM x$kccrt;

RTCKP_SCN        RTCKP_TIM            RTCKP_RBA_SEQ RTCKP_RBA_BNO      RTSEQ
---------------- -------------------- ------------- ------------- ----------
5071949          12/20/2010 22:01:41             13         25960         13   <== 最新的SCN值

###### 使用如下方式查询也可以:######

SQL> SELECT name, to_char(checkpoint_change#,'XXXXXXXXXXXX') FROM v$datafile WHERE name LIKE '%alex%';

NAME                                                         TO_CHAR(CHECK
------------------------------------------------------------ -------------
/oracle/product/oradata/red5db/alex01.dbf                           4D644D  <== 5071949

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes                   @484
   struct kcvcpscn, 8 bytes                 @484
      ub4 kscnbas                           @484      0x004d5818  

      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x2c014506
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500
      struct kcvcprba, 12 bytes             @500
         ub4 kcrbaseq                       @500      0x00000009 

         ub4 kcrbabno                       @504      0x0000027b
         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

下面做一个datafile header的dump 来看看情况:
SQL> ALTER SESSION SET events 'immediate trace name FILE_HDRS level 10';

SESSION altered.
Tablespace #25 - ALEX  rel_fn:36
Creation   at   scn: 0x0000.004d54d4 12/20/2010 21:51:13
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2c013ee6 scn: 0x0000.004d507b reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2ba3523b scn: 0x0000.0040f2ab prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 12/20/2010 23:40:10
status:0x4 root dba:0x00000000 chkpt cnt: 10 ctl cnt:9
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.0022584d 12/20/2010 21:58:30

BBED> modify /x 0a offset 500

File: /oracle/product/oradata/red5db/alex01.dbf (36)
Block: 1                Offsets:  500 to 1011           Dba:0x09000001
------------------------------------------------------------------------
0a000000 7b020000 1000a32b 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 0a000a00 0a000100 00000000 00000000
00000000 02000009 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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 36, Block 1:
current = 0x667a, required = 0x667a

SQL>  recover datafile 36;

ORA-00279: CHANGE 5067988 generated at 12/20/2010 21:58:30 needed FOR thread 1
ORA-00289: suggestion : /oracle/arch1/1_160_738279142.dbf
ORA-00280: CHANGE 5067988 FOR thread 1 IS IN SEQUENCE #160

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-01013: USER requested cancel OF CURRENT operation

SQL> recover datafile 36;

ORA-00279: CHANGE 5067988 generated at 12/20/2010 21:58:30 needed FOR thread 1
ORA-00289: suggestion : /oracle/arch1/1_10_738279142.dbf
ORA-00280: CHANGE 5067988 FOR thread 1 IS IN SEQUENCE #10

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00326: log begins at CHANGE 5069504, need earlier CHANGE 5067988
ORA-00334: archived log: '/oracle/arch1/1_10_738279142.dbf'

BBED> d /v

File: /oracle/product/oradata/red5db/alex01.dbf (36)
Block: 1       Offsets:  484 to  995  Dba:0x09000001
-------------------------------------------------------
d4544d00 00004d00 0645012c 0100012c l .TM...M..E.,...,
0a000000 7b020000 1000a32b 02000000 l ....{......+....
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
0a000a00 0a000100 00000000 00000000 l ................
00000000 02000009 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................

<16 bytes per line>

BBED> modify /x c05a4d

File: /oracle/product/oradata/red5db/alex01.dbf (36)
Block: 1                Offsets:  484 to  995           Dba:0x09000001
------------------------------------------------------------------------
c05a4d00 00004d00 0645012c 0100012c 0a000000 7b020000 1000a32b 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
0a000a00 0a000100 00000000 00000000 00000000 02000009 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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 36, Block 1:
current = 0x686e, required = 0x686e

下面我们来看看能恢复不了呢?
SQL> recover datafile 36;

ORA-00279: CHANGE 5069504 generated at 12/20/2010 21:58:30 needed FOR thread 1
ORA-00289: suggestion : /oracle/arch1/1_10_738279142.dbf
ORA-00280: CHANGE 5069504 FOR thread 1 IS IN SEQUENCE #10

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

终于可以恢复了。。。。

SQL> ALTER DATABASE OPEN;
DATABASE altered.

SQL> SHOW USER
USER IS "SYS"

SQL> SELECT COUNT(*) FROM t1;
  COUNT(*)
----------
     40411

SQL> SELECT COUNT(*) FROM t2;
  COUNT(*)
----------
        32

SQL> SELECT COUNT(*) FROM t3;
  COUNT(*)
----------
        32

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle使用bbed跳过丢失的归档进行恢复数据 详细步骤

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1357.html

Oracle研究中心

关键词:

使用bbed跳过丢失的归档进行recover datafile

Oracle部分归档丢失的恢复方法