Oracle----BBED初探
BBED是用来直接查看和修改数据文件数据的一个工具，是Oracle一款内部工具，可以直接修改Oracle数据文件块的内容，在一些极端恢复场景下比较有用
在11g下使用需要10g的几个包
[oracle@Ora lib]$ pwd /test/app/oracle/product/11.2.0/db_home1/rdbms/lib [oracle@Ora lib]$ ls sbbdpt.o sbbdpt.o [oracle@Ora lib]$ ls ssbbded.o ssbbded.o [oracle@Ora mesg]$ pwd /test/app/oracle/product/11.2.0/db_home1/rdbms/mesg [oracle@Ora mesg]$ ls bbedus.msb bbedus.msb
编译安装
cd $ORACLE_HOME/rdbms/lib/ [oracle@Ora lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
运行
[oracle@Ora bin]$ pwd /test/app/oracle/product/11.2.0/db_home1/bin
[oracle@Ora bin]$ ./bbed
输入密码：blockedit
Password: BBED: Release 2.0.0.0.0 - Limited Production on Fri Apr 27 19:14:19 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> show FILE# 0 BLOCK# 1 OFFSET 0 DBA 0x00000000 (0 0,1) FILENAME BIFILE bifile.bbd LISTFILE BLOCKSIZE 8192 MODE Browse EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 512 LOGFILE log.bbd SPOOL No
丢失归档，但是客户不想resetlogs open库。可以使用bbed修改scn实现完全恢复
以下是样例：
[oracle@Ora bin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 27 19:17:23 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@ykyk> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@ykyk> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@Ora bin]$ mkdir /home/oracle/bak
一致性关库并建立目录
查看数据文件
[oracle@Ora ykyk]$ ls ora_contro101.ctl redo02.log system01.dbf tsb01.dbf undotbs01.dbf ora_contro102.ctl redo03.log temp01.dbf tsc01.dbf users01.dbf redo01.log sysaux01.dbf tsa01.dbf tsd01.dbf
物理备份
[oracle@Ora ykyk]$ cp * /home/oracle/bak/
登录数据库，查看归档模式
[oracle@Ora ykyk]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 27 19:20:51 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SYS@ykyk> startup ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2229944 bytes Variable Size 184551752 bytes Database Buffers 331350016 bytes Redo Buffers 3805184 bytes Database mounted. Database opened. SYS@ykyk> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 8 Next log sequence to archive 10 Current log sequence 10
查看redo日志
set line 1000 SYS@ykyk> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 10 104857600 512 1 NO CURRENT 298819 27-APR-18 2.8147E+14 2 1 8 104857600 512 1 YES INACTIVE 228754 25-APR-18 270063 27-APR-18 3 1 9 104857600 512 1 YES INACTIVE 270063 27-APR-18 298819 27-APR-18
切日志产生归档
YS@ykyk> alter system switch logfile; System altered. SYS@ykyk> / System altered. SYS@ykyk> / System altered. SYS@ykyk> / System altered. SYS@ykyk> / System altered.
查看scn
SYS@ykyk> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 13 104857600 512 1 YES INACTIVE 303491 27-APR-18 303494 27-APR-18 2 1 14 104857600 512 1 YES INACTIVE 303494 27-APR-18 303497 27-APR-18 3 1 15 104857600 512 1 NO CURRENT 303497 27-APR-18 2.8147E+14
查看归档
select * from v$archived_log 2 ; RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS RESETLOGS_ID FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTR STA ARC APPLIED DEL S COMPLETIO DIC DIC END BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_ COM FAL END_OF_RED BAC ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ----------------- --------- ------------ ------------- --------- ------------ --------- ---------- ---------- ------- ------- --- --- --------- --- - --------- --- --- --- ------------ ---------------- ----------- --- --- --- ---------- --- 1 974412061 /test/app/oracle/flash_recovery_area/YKYK/archivelog/2018_04_25/o1_mf_1_7_fg12jwcv_.arc 1 1 7 1 25-APR-18 974397893 191169 25-APR-18 228754 25-APR-18 192074 512 ARCH ARCH NO YES NO NO A 25-APR-18 NO NO NO 0 1 4016609925 YES NO NO NO 2 974513460 /test/app/oracle/flash_recovery_area/YKYK/archivelog/2018_04_27/o1_mf_1_8_fg45kmfg_.arc 1 1 8 1 25-APR-18 974397893 228754 25-APR-18 270063 27-APR-18 50052 512 ARCH ARCH NO YES NO NO A 27-APR-18 NO NO NO 0 1 4016609925 YES NO NO NO 3 974568230 /test/app/oracle/flash_recovery_area/YKYK/archivelog/2018_04_27/o1_mf_1_9_fg5v15oy_.arc 1 1 9 1 25-APR-18 974397893 270063 27-APR-18 298819 27-APR-18 24939 512 ARCH ARCH NO YES NO NO A 27-APR-18 NO NO NO 0 1 4016609925 YES NO NO NO 4 974575378 /test/app/oracle/flash_recovery_area/YKYK/archivelog/2018_04_27/o1_mf_1_10_fg620lhl_.arc 1 1 10 1 25-APR-18 974397893 298819 27-APR-18 303485 27-APR-18 8435 512 ARCH ARCH NO YES NO NO A 27-APR-18 NO NO NO 0 1 4016609925 YES NO NO NO 5 974575379 /test/app/oracle/flash_recovery_area/YKYK/archivelog/2018_04_27/o1_mf_1_11_fg620mnz_.arc 1 1 11 1 25-APR-18 974397893 303485 27-APR-18 303488 27-APR-18 1 512 ARCH ARCH NO YES NO NO A 27-APR-18 NO NO NO 0 1 4016609925 YES NO NO NO 6 974575381 /test/app/oracle/flash_recovery_area/YKYK/archivelog/2018_04_27/o1_mf_1_12_fg620ogz_.arc 1 1 12 1 25-APR-18 974397893 303488 27-APR-18 303491 27-APR-18 3 512 ARCH ARCH NO YES NO NO A 27-APR-18 NO NO NO 0 1 4016609925 YES NO NO NO 7 974575381 /test/app/oracle/flash_recovery_area/YKYK/archivelog/2018_04_27/o1_mf_1_13_fg620oh8_.arc 1 1 13 1 25-APR-18 974397893 303491 27-APR-18 303494 27-APR-18 1 512 ARCH ARCH NO YES NO NO A 27-APR-18 NO NO NO 0 1 4016609925 YES NO NO NO 8 974575382 /test/app/oracle/flash_recovery_area/YKYK/archivelog/2018_04_27/o1_mf_1_14_fg620p4y_.arc 1 1 14 1 25-APR-18 974397893 303494 27-APR-18 303497 27-APR-18 1 512 ARCH ARCH NO YES NO NO A 27-APR-18 NO NO NO 0 1 4016609925 YES NO NO NO 8 rows selected.
[oracle@Ora 2018_04_27]$ ls o1_mf_1_10_fg620lhl_.arc o1_mf_1_12_fg620ogz_.arc o1_mf_1_14_fg620p4y_.arc o1_mf_1_9_fg5v15oy_.arc o1_mf_1_11_fg620mnz_.arc o1_mf_1_13_fg620oh8_.arc o1_mf_1_8_fg45kmfg_.arc
移除在此期间产生的归档
[oracle@Ora 2018_04_27]$ mv o1_mf_1_10_fg620lhl_.arc o1_mf_1_12_fg620ogz_.arc o1_mf_1_11_fg620mnz_.arc o1_mf_1_8_fg45kmfg_.arc o1_mf_1_9_fg5v15oy_.arc o1_mf_1_13_fg620oh8_.arc o1_mf_1_14_fg620p4y_.arc /home/oracle/
模拟数据文件丢失
[oracle@Ora ykyk]$ rm -rf users01.dbf
报错
SYS@ykyk> conn scott/tiger Connected. SCOTT@ykyk> select * from emp; select * from emp * ERROR at line 1: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/test/app/oracle/oradata/ykyk/users01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
文件丢失
SCOTT@ykyk> conn / as sysdba Connected. SYS@ykyk> select status from v$instance; STATUS ------------ OPEN SYS@ykyk> select * from v$recover_file; no rows selected SYS@ykyk> alter system checkpoint; System altered. SYS@ykyk> select * from v$recover_file; no rows selected SYS@ykyk> select name from v$datafile; NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /test/app/oracle/oradata/ykyk/system01.dbf /test/app/oracle/oradata/ykyk/sysaux01.dbf /test/app/oracle/oradata/ykyk/undotbs01.dbf /test/app/oracle/oradata/ykyk/users01.dbf /test/app/oracle/oradata/ykyk/tsa01.dbf /test/app/oracle/oradata/ykyk/tsb01.dbf /test/app/oracle/oradata/ykyk/tsc01.dbf /test/app/oracle/oradata/ykyk/tsd01.dbf 8 rows selected.
一致性关库，报错
SYS@ykyk> select * from v$recover_file; no rows selected SYS@ykyk> shu immediate ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/test/app/oracle/oradata/ykyk/users01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
此时数据库状态为
强制停库
SYS@ykyk> shu abort;
ORACLE instance shut down.
SYS@ykyk> startup
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2229944 bytes
Variable Size 184551752 bytes
Database Buffers 331350016 bytes
Redo Buffers 3805184 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/test/app/oracle/oradata/ykyk/users01.dbf'
查看数据库状态
查看需要恢复的文件
SYS@ykyk> select * from v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ----------------------------------------------------------------- ---------- --------- 4 ONLINE ONLINE FILE NOT FOUND 0
将原来的备份拷贝回来
[oracle@Ora ykyk]$ cp /home/oracle/bak/users01.dbf . [oracle@Ora ykyk]$ ls ora_contro101.ctl redo02.log system01.dbf tsb01.dbf undotbs01.dbf ora_contro102.ctl redo03.log temp01.dbf tsc01.dbf users01.dbf redo01.log sysaux01.dbf tsa01.dbf tsd01.dbf
恢复，但归档被移走，无法恢复
SYS@ykyk> recover datafile 4; ORA-00279: change 303310 generated at 04/27/2018 19:18:01 needed for thread 1 ORA-00289: suggestion : /test/app/oracle/flash_recovery_area/YKYK/archivelog/2018_04_27/o1_mf_1_10_fg620lhl_.arc ORA-00280: change 303310 for thread 1 is in sequence #10 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/test/app/oracle/flash_recovery_area/YKYK/archivelog/2018_04_27/o1_mf_1_10_fg620lhl_.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
使用bbed恢复
1.查看数据文件头scn
SYS@ykyk> select status from v$instance; STATUS ------------ MOUNTED SYS@ykyk> select name,checkpoint_change# from v$datafile_header; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /test/app/oracle/oradata/ykyk/system01.dbf 303714 /test/app/oracle/oradata/ykyk/sysaux01.dbf 303714 /test/app/oracle/oradata/ykyk/undotbs01.dbf 303714 /test/app/oracle/oradata/ykyk/users01.dbf 303310 /test/app/oracle/oradata/ykyk/tsa01.dbf 303714 /test/app/oracle/oradata/ykyk/tsb01.dbf 303714 /test/app/oracle/oradata/ykyk/tsc01.dbf 303714 /test/app/oracle/oradata/ykyk/tsd01.dbf 303714
2.将4号文件scn转换成16进制
3.使用bbed恢复
[oracle@Ora bin]$ cat par.bdb blocksize=8192 listfile=bbedfile.txt mode=edit
[oracle@Ora bin]$ cat bbedfile.txt 1 /test/app/oracle/oradata/ykyk/system01.dbf 340787200 2 /test/app/oracle/oradata/ykyk/sysaux01.dbf 340787200 3 /test/app/oracle/oradata/ykyk/undotbs01.dbf 209715200 4 /test/app/oracle/oradata/ykyk/users01.dbf 524288000 5 /test/app/oracle/oradata/ykyk/tsa01.dbf 104857600 6 /test/app/oracle/oradata/ykyk/tsb01.dbf 104857600 7 /test/app/oracle/oradata/ykyk/tsc01.dbf 104857600 8 /test/app/oracle/oradata/ykyk/tsd01.dbf 104857600
[oracle@Ora bin]$ bbed parfile=par.bdb
查看文件
BBED> info File# Name Size(blks) ----- ---- ---------- 1 /test/app/oracle/oradata/ykyk/system01.dbf 41600 2 /test/app/oracle/oradata/ykyk/sysaux01.dbf 41600 3 /test/app/oracle/oradata/ykyk/undotbs01.dbf 25600 4 /test/app/oracle/oradata/ykyk/users01.dbf 64000 5 /test/app/oracle/oradata/ykyk/tsa01.dbf 12800 6 /test/app/oracle/oradata/ykyk/tsb01.dbf 12800 7 /test/app/oracle/oradata/ykyk/tsc01.dbf 12800 8 /test/app/oracle/oradata/ykyk/tsd01.dbf 12800
修改
BBED> dump File: /test/app/oracle/oradata/ykyk/system01.dbf (1) Block: 1 Offsets: 0 to 511 Dba:0x00400001 ------------------------------------------------------------------------ 0ba20000 01004000 00000000 00000104 cfff0000 00000000 0000200b 855f68ef 594b594b 00000000 fa010000 80a20000 00200000 01000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 08024000 07000000 00000000 c821143a c521143a 01000000 00000000 00000000 00000000 00000000 00000420 22000000 04e0163a 1f000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 06005359 5354454d 00000000 00000000 00000000 00000000 00000000 00000000 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0a000a00 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 62a20400 00000000 7cd9163a 01000000 0f000000 12000000 10007e04 <32 bytes per line> BBED> set mode edit; MODE Edit BBED> modify /x 0ba200 dba 4,1 offset 0; Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /test/app/oracle/oradata/ykyk/users01.dbf (4) Block: 1 Offsets: 0 to 511 Dba:0x01000001 ------------------------------------------------------------------------ 0ba20000 01000001 00000000 00000104 f4330000 00000000 0000200b 855f68ef 594b594b 00000000 f4010000 00fa0000 00200000 04000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 d00c0000 00000000 d221143a c521143a 01000000 00000000 00000000 00000000 00000000 00000000 1a000000 f9da163a 19000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 04000000 05005553 45525300 00000000 00000000 00000000 00000000 00000000 00000000 04000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 62a20400 00000000 e9d5163a 01000000 0a000000 94200000 1000b67e <32 bytes per line> BBED> sum apply; Check value for File 4, Block 1: current = 0x33f4, required = 0x33f4 BBED> exit
sqlplus启动
[oracle@Ora bin]$ sqlplus / as sysdba SYS@ykyk> recover database; Media recovery complete. SYS@ykyk> alter database open; Database altered.
成功。
以下是验证
SYS@ykyk> select status from v$instance; STATUS ------------ OPEN SYS@ykyk> select name from v$datafile; NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /test/app/oracle/oradata/ykyk/system01.dbf /test/app/oracle/oradata/ykyk/sysaux01.dbf /test/app/oracle/oradata/ykyk/undotbs01.dbf /test/app/oracle/oradata/ykyk/users01.dbf /test/app/oracle/oradata/ykyk/tsa01.dbf /test/app/oracle/oradata/ykyk/tsb01.dbf /test/app/oracle/oradata/ykyk/tsc01.dbf /test/app/oracle/oradata/ykyk/tsd01.dbf SYS@ykyk> conn scott/tiger; Connected. SCOTT@ykyk> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.
附注：
BBED> set offset 484 OFFSET 484 BBED> dump File: /test/app/oracle/oradata/ykyk/system01.dbf (1) Block: 1 Offsets: 484 to 995 Dba:0x00400001 ------------------------------------------------------------------------ 9bfb0400 0000ae19 def3163a 01000000 11000000 02000000 10000000 02000000 9bfb0400 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0004fb9b 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 02004000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 022315b6 2f3fa600 e44c6a47 e9c17e39 70000000 00000000 00000000 00000000 0001565f 267f0000 38020000 00000000 c02bd4bd 4766af9d 3a0704a8 bfd64f4a a3b30600 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 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> p kcvfh
……
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0004fb9b 数据文件头scn
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x3a16f3de
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000011
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0010
……
Nothing is true,Nothing is fake.
