测试目的：使用bbed修改数据库文件头的scn，完成不完全恢复数据库

测试环境：
DB：Oracle 10gR2
Os: rhel 6.4

测试步骤：
创建测试数据：
SQL> create tablespace test datafile '/app/oracle/product/10.2.0/oradata/orac/test01.dbf' size 100m;
Tablespace created.
SQL> create user test identified by test default tablespace test;
User created.
SQL> grant dba to test;
Grant succeeded.
SQL> SQL> conn test/test
Connected.
SQL> create table emp as select * from scott.emp;
Table created.
SQL> create table dept as select * from scott.dept;
Table created.
SQL> insert into emp select * from emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> insert into dept select * from dept;
4 rows created.
SQL> commit;
Commit complete.
修改数据库到归档模式，模拟归档丢失
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch';
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
备份数据文件，可以使用rman，也可以使用冷备份
[oracle@eagle orac]$ cp test01.dbf bak/
SQL> startup mount
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 469762568 bytes
Database Buffers 1124073472 bytes
Redo Buffers 14680064 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 19
Next log sequence to archive 21
Current log sequence 21
SQL> conn test
Enter password:
Connected.
SQL> insert into emp select * from emp;
28 rows created.
SQL> commit;
Commit complete.
SQL> insert into dept select * from dept;
8 rows created.
SQL> commit;
Commit complete.
SQL> conn test
Enter password:
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
56
SQL> select count(*) from dept;
COUNT(*)
----------
16
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> /
多切换几次归档
模拟数据库掉电等意外情况
SQL> shutdown abort
ORACLE instance shut down.
删除归档，更改数据文件名称
[oracle@eagle arch]$ ls
1_21_832528067.dbf 1_23_832528067.dbf 1_25_832528067.dbf 1_27_832528067.dbf 1_29_832528067.dbf
1_22_832528067.dbf 1_24_832528067.dbf 1_26_832528067.dbf 1_28_832528067.dbf
[oracle@eagle arch]$ rm *
[oracle@eagle orac]$ mv test01.dbf test01.dbf_bak
启动数据库
Size 469762568 bytes
Database Buffers 1124073472 bytes
Redo Buffers 14680064 bytes
Database mounted.
ORA-01113: file 5 needs media recovery if it was restored from backup, or END BACKUP if it was not
ORA-01110: data file 5: '/app/oracle/product/10.2.0/oradata/orac/test01.dbf'
SQL> recover database;
ORA-00279: change 667398 generated at 12/20/2013 16:52:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch/1_20_832528067.dbf
ORA-00280: change 667398 for thread 1 is in sequence #20
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/home/oracle/arch/1_20_832528067.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 '/home/oracle/arch/1_20_832528067.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
查看数据库的文件scn
SQL> select name,CHECKPOINT_CHANGE# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------------------------------------- ------------------
/app/oracle/product/10.2.0/oradata/orac/system01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/undotbs01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/sysaux01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/users01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/test01.dbf 687898
查看文件头scn
SQL> select name,CHECKPOINT_CHANGE# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------------------------------------- ------------------
/app/oracle/product/10.2.0/oradata/orac/system01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/undotbs01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/sysaux01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/users01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/test01.dbf 667398
第五个数据文件scn值不一致
查看16进制的scn号
SQL> select to_char('687898','xxxxx') from dual;
TO_CHA
------
a7f1a
SQL> c/687898/667398
1* select to_char('667398','xxxxx') from dual
SQL> /
TO_CHA
------
a2f06

配置bbed的file
[oracle@eagle ~]$ more file.txt
1 /app/oracle/product/10.2.0/oradata/orac/system01.dbf 461373440
2 /app/oracle/product/10.2.0/oradata/orac/undotbs01.dbf 94371840
3 /app/oracle/product/10.2.0/oradata/orac/sysaux01.dbf 283115520
4 /app/oracle/product/10.2.0/oradata/orac/users01.dbf 5242880
5 /app/oracle/product/10.2.0/oradata/orac/test01.dbf 10485760
[oracle@eagle ~]$ more bd.par
listfile=file.txt
mode=edit
[oracle@eagle ~]$ bbed password=blockedit parfile=bd.par

BBED: Release 2.0.0.0.0 - Limited Production on Fri Dec 20 17:10:08 2013

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

************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /app/oracle/product/10.2.0/oradata/orac/system01.dbf 56320
2 /app/oracle/product/10.2.0/oradata/orac/undotbs01.dbf 11520
3 /app/oracle/product/10.2.0/oradata/orac/sysaux01.dbf 34560
4 /app/oracle/product/10.2.0/oradata/orac/users01.dbf 640
5 /app/oracle/product/10.2.0/oradata/orac/test01.dbf 1280

BBED> d /v dba 5,1
File: /app/oracle/product/10.2.0/oradata/orac/test01.dbf (5)
Block: 1 Offsets: 484 to 995 Dba:0x01400001
-------------------------------------------------------
062f0a00 00000000 4e47c031 01000000 l ./......NG1....
14000000 5b030000 10000000 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 ................
0d000d00 0d000100 00000000 00000000 l ................
00000000 02004001 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> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000a2f06
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x31c0474e
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000014
ub4 kcrbabno @504 0x0000035b
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
BBED> d /v dba 5,1 offset 484
File: /app/oracle/product/10.2.0/oradata/orac/test01.dbf (5)
Block: 1 Offsets: 484 to 995 Dba:0x01400001
-------------------------------------------------------
062f0a00 00000000 4e47c031 01000000 l ./......NG1....
14000000 5b030000 10000000 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 ................
0d000d00 0d000100 00000000 00000000 l ................
00000000 02004001 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> m /x 1a7f0a dba 5,1 offset 484
File: /app/oracle/product/10.2.0/oradata/orac/test01.dbf (5)
Block: 1 Offsets: 484 to 995 Dba:0x01400001
------------------------------------------------------------------------
1a7f0a00 00000000 4e47c031 01000000 14000000 5b030000 10000000 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 02004001 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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> verify
DBVERIFY - Verification starting
FILE = /app/oracle/product/10.2.0/oradata/orac/test01.dbf
BLOCK = 1

Block 1 is corrupt
Corrupt block relative dba: 0x01400001 (file 0, block 1)
Bad check value found during verification
Data in bad block:
type: 11 format: 2 rdba: 0x01400001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000b01
check value in block header: 0xa8a5
computed block checksum: 0x501c
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 0
BBED> sum apply
Check value for File 5, Block 1:
current = 0xf8b9, required = 0xf8b9
BBED> exit
重新启动数据库
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 469762568 bytes
Database Buffers 1124073472 bytes
Redo Buffers 14680064 bytes
Database mounted.
ORA-01113: file 5 needs media recovery if it was restored from backup, or END BACKUP if it was not
ORA-01110: data file 5: '/app/oracle/product/10.2.0/oradata/orac/test01.dbf'
SQL> select name,CHECKPOINT_CHANGE# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------------------------------------- ------------------
/app/oracle/product/10.2.0/oradata/orac/system01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/undotbs01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/sysaux01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/users01.dbf 687898
/app/oracle/product/10.2.0/oradata/orac/test01.dbf 687898
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> conn test
Enter password:
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
28
SQL> select count(*) from dept;
COUNT(*)
----------
8
数据库启动成功，丢失部分数据

ITPub博客

使用bbed完成数据库的不完全恢复

最新文章