Oracle 之利用BBED跳过归档日志实现恢复（二）
来源：互联网 发布：js router 编辑：程序博客网 时间：2024/05/24 05:45
本次实验和之前做的实验：Oracle 之利用BBED修改数据块SCN----极端环境下的数据恢复（一）差不多，本次实验将删除归档进行恢复
http://blog.csdn.net/shiyu1157758655/article/details/56286018
使用BBED跳过丢失的归档，在recover datafile的过程当中如果丢失了需要的归档将使得recover无法进行，使用bbed工具可以跳过丢失的归档进行recover datafile。
1.测试环境
OS:RedHat 5.9
Oracle:12.1.0.2
2.模拟实验环境
a）物理备份
ocrl:/data/oradata/ocrl/archivelog@oracle1>cd /data/oradata/ocrl/datafile/
ocrl:/data/oradata/ocrl/datafile@oracle1>ls
OCRL sysaux01.dbf system01.dbf system01.dbf.bak test.dbf tstest.dbf undotbs01.dbf users01.dbf
ocrl:/data/oradata/ocrl/datafile@oracle1>cp test.dbf test.dbf.bak
SQL> alter system switch logfile;//切日志产生归档
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select * from v$log;//查看序列号
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------------- ------------- --------- ------------ --------- ----------
1 1 226 52428800 5121 NO
CURRENT 13300023 22-FEB-17 2.8147E+14 0
2 1 224 52428800 5121 YES
INACTIVE 13296997 22-FEB-17 13297004 22-FEB-17 0
3 1 225 52428800 5121 YES
INACTIVE 13297004 22-FEB-17 13300023 22-FEB-17 0
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oradata/ocrl/archivelog
Oldest online log sequence 231
Next log sequence to archive 233
Current log sequence 233
SQL> exit
b）删除数据文件和归档文件
ocrl:/data/oradata/ocrl/archivelog@oracle1>ll -tr
total 24120
-rw-r----- 1 oracle dba 14556672 Feb 22 11:04 1_223_936091272.arc
-rw-r----- 1 oracle dba 4096 Feb 22 11:04 1_224_936091272.arc
-rw-r----- 1 oracle dba 1226752 Feb 22 11:23 1_225_936091272.arc
-rw-r----- 1 oracle dba 8521216 Feb 22 11:42 1_226_936091272.arc
-rw-r----- 1 oracle dba 2048 Feb 22 11:42 1_227_936091272.arc
-rw-r----- 1 oracle dba 3072 Feb 22 11:42 1_228_936091272.arc
-rw-r----- 1 oracle dba 2048 Feb 22 11:42 1_229_936091272.arc
-rw-r----- 1 oracle dba 1536 Feb 22 11:42 1_230_936091272.arc
-rw-r----- 1 oracle dba 1536 Feb 22 11:42 1_232_936091272.arc
-rw-r----- 1 oracle dba 1536 Feb 22 11:42 1_231_936091272.arc
-rw-r----- 1 oracle dba 278016 Feb 22 11:45 1_233_936091272.arc
-rw-r----- 1 oracle dba 1536 Feb 22 11:45 1_234_936091272.arc
-rw-r----- 1 oracle dba 1024 Feb 22 11:46 1_236_936091272.arc
-rw-r----- 1 oracle dba 4096 Feb 22 11:46 1_235_936091272.arc
-rw-r----- 1 oracle dba 13824 Feb 22 11:46 1_237_936091272.arc
-rw-r----- 1 oracle dba 4608 Feb 22 11:46 1_238_936091272.arc
ocrl:/data/oradata/ocrl/archivelog@oracle1>rm -rf * //删除所有归档
ocrl:/data/oradata/ocrl/archivelog@oracle1>cd /data/oradata/ocrl/datafile/
ocrl:/data/oradata/ocrl/datafile@oracle1>ls
OCRL sysaux01.dbf system01.dbf system01.dbf.bak test.dbf test.dbf.bak tstest.dbf undotbs01.dbf users01.dbf
ocrl:/data/oradata/ocrl/datafile@oracle1>rm -rf test.dbf //删除test数据文件
ocrl:/data/oradata/ocrl/datafile@oracle1>ls
OCRL sysaux01.dbf system01.dbf system01.dbf.bak test.dbf.bak tstest.dbf undotbs01.dbf users01.dbf
查询test上的table
SQL> conn test/test
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
T_BITMAP
T_BTREE
GROUP_TEST
FT_1
JUST
AISHU1
6 rows selected.
SQL> select * from just;
select * from just
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
这里已经发现test的数据文件已经损坏
SQL> shutdown immediate //这里正常关闭报错
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort; //强制关闭
ORACLE instance shut down.
SQL> startup //打开数据库
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf' //这里缺少test数据文件
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select * from v$recover_file;//查看需要恢复的文件
FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME CON_ID
------------------------------------------------------------------------------------------------
5 ONLINE ONLINE FILE NOT FOUND 0 0
把原来的数据文件备份回来
ocrl:/data/oradata/ocrl/datafile@oracle1>mv test.dbf.bak test.dbf
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'
SQL> recover datafile 5; //恢复
ORA-00279: change 13303047 generated at 02/22/2017 11:42:56 needed for thread 1
ORA-00289: suggestion : /data/oradata/ocrl/archivelog/1_233_936091272.arc
ORA-00280: change 13303047 for thread 1 is in sequence #233
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto --自动模式让他去选择
ORA-00308: cannot open archived log
'/data/oradata/ocrl/archivelog/1_233_936091272.arc'
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
'/data/oradata/ocrl/archivelog/1_233_936091272.arc' //需要seq为233的归档 此文件已经被我们删除
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
3.利用BBED跳过归档进行恢复
a）查看数据头文件scn
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> col name for a50
SQL> select name,checkpoint_change# from v$datafile_header;//查看数据文件头scn
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/data/oradata/ocrl/datafile/system01.dbf 13304100
/data/oradata/ocrl/datafile/sysaux01.dbf 13304100
/data/oradata/ocrl/datafile/undotbs01.dbf 13304100
/data/oradata/ocrl/datafile/users01.dbf 13304100
/data/oradata/ocrl/datafile/test.dbf 13303047
/data/oradata/ocrl/datafile/tstest.dbf 13304100
6 rows selected.
SQL> select to_char('13303047','XXXXXXXX') from dual;//将5号文件scn 转成16进制值
TO_CHAR('
---------
CAFD07
b）BBED修改
用BBED查询test数据文件结构信息
BBED> set dba 5,1
DBA 0x01400001 (20971521 5,1)
BBED> map
File: /data/oradata/ocrl/datafile/test.dbf (5)
Block: 1 Dba:0x01400001
------------------------------------------------------------
Data File Header
struct kcvfh, 1112 bytes @0
ub4 tailchk @8188
BBED查看kcvfh信息
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00cafd07
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x37d3a8c0
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x000000e9
ub4 kcrbabno @504 0x00000002
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 / /查看5号数据头文件的scn
File: /data/oradata/ocrl/datafile/test.dbf (5)
Block: 1 Offsets: 484 to 995 Dba:0x01400001
-------------------------------------------------------
07fdca00 00000000 c0a8d337 01001e82 l .?.....括?....
e9000000 02000000 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 d9e63e00 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> d /v dba 1,1 offset 484 //1号数据文件
File: /data/oradata/ocrl/datafile/system01.dbf (1)
Block: 1 Offsets: 484 to 995 Dba:0x00400001
-------------------------------------------------------
2401cb00 00000000 39aad337 01000000 l $.?....9?7....
ef000000 b5000000 100072f0 02000000 l ?..?....r?...
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 02004000 6c3d9200 00000000 l ......@.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 0215e11b l ..............?
fb1ef7c6 d7131af0 7f44f1f4 59000000 l ?髌?.?D耵Y...
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 0013b9e3 l ..............广
13aac34f aebfb3af e5f7d22b 03000600 l .?O?朝鬻?....
f3b89100 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> d /v dba 2,1 offset 484 //2号数据文件
File: /data/oradata/ocrl/datafile/sysaux01.dbf (2)
Block: 1 Offsets: 484 to 995 Dba:0x00800001
-------------------------------------------------------
2401cb00 00000000 39aad337 01000000 l $.?....9?7....
ef000000 b5000000 100072f0 02000000 l ?..?....r?...
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 02008000 75ba9700 00000000 l ........u?.....
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 0215e11b l ..............?
fb1ef7c6 d7131af0 7f44f1f4 59000000 l ?髌?.?D耵Y...
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 0013b9e3 l ..............广
13aac34f aebfb3af e5f7d22b 03000600 l .?O?朝鬻?....
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> set mode edit
MODE Edit
BBED> modify /x 2401cb dba 5,1 offset 484 //修改5号文件的数据文件头scn一致
File: /data/oradata/ocrl/datafile/test.dbf (5)
Block: 1 Offsets: 484 to 995 Dba:0x01400001
------------------------------------------------------------------------
2401cb00 00000000 c0a8d337 01001e82 e9000000 02000000 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 d9e63e00 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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 5, Block 1:
current = 0xe558, required = 0xe558
BBED> d /v dba 5,1 offset 484 //查看修改后的test的头文件
File: /data/oradata/ocrl/datafile/test.dbf (5)
Block: 1 Offsets: 484 to 995 Dba:0x01400001
-------------------------------------------------------
2401cb00 00000000 c0a8d337 01001e82 l $.?....括?....
e9000000 02000000 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 d9e63e00 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>
c）打开数据库
SQL> startup force
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
- Oracle 之利用BBED跳过归档日志实现恢复（二）
- Oracle 之利用BBED跳过归档日志实现恢复
- Oracle非常规恢复（使用BBED跳过归档）
- BBED跳过归档恢复
- Oracle非常规恢复(使用BBED跳过归档)
- 跳过归档日志的非常规恢复（二）
- 使用BBED跳过归档进行恢复
- 【Oracle】使用BBED跳过丢失的归档
- oracle bbed 恢复无归档的数据库
- Oracle bbed的使用(跨归档恢复)
- 跳过归档日志的非常规恢复（一）
- Oracle之BBED之(二）-----BBED更改数据块
- 跳过归档日志的非常规恢复
- 跳过归档日志的非常规恢复
- Oracle 之利用BBED修改数据块SCN----极端环境下的数据恢复（一）
- oracle跳过丢失的归档恢复datafile
- 利用 BBED 恢复非归档模式下 OFFLINE 数据文件
- Oracle RMAN Recover中使用BBED 跳过缺失的归档 继续 Recover 的测试
- 下载安装破解最新PyCharm2016.3.2专业版破解步骤
- 做一个理直气壮的嵌入式驱动开发者
- nginx配置https以及能够局域网访问
- Fiddler的实践心得（四）:修改request的body
- unity检测键盘的按键名称-JS
- Oracle 之利用BBED跳过归档日志实现恢复（二）
- Tensorflow 自定义loss的情况下初始化部分变量
- Python入门（七）：生成列表、条件过滤、多层表达式
- Android studio的NDK开发基础
- 深入理解Java中为什么内部类可以访问外部类的成员
- 111. Minimum Depth of Binary Tree
- vue搭建简单项目（新手操作）
- leecode 第一题 TWO SUM
- 《剑指offer》—1、二维数组中的查找
