场景描述:
数据库将进行xtts迁移,在进行环境检查时,发现数据库中核心表空间中存在一个offline的数据文件,并且操作系统上数据文件已不存在,而且该数据文件属于表空间的一号文件,无法进行drop,由于出现offline的数据文件,所以无法进行xtts迁移,最终使用bbed跳过归档将数据文件online,online以后不影响表空间正常读写。
环境构建:
SQL> create tablespace dbhang datafile '/oracle/app/oradata/prod/dbhang01.dbf' size 50M;
Tablespace created.
SQL> alter tablespace dbhang add datafile '/oracle/app/oradata/prod/dbhang02.dbf' size 50M;
Tablespace altered.
更加贴合生产,模拟表空间中第一个数据文件为空,向第二个数据文件中插入数据。
6号文件:/oracle/app/oradata/prod/dbhang01.dbf
7号文件:/oracle/app/oradata/prod/dbhang02.dbf
SQL> alter database datafile 6 offline;
Database altered.
SQL> create table test tablespace dbhang as select * from dba_objects;
Table created.
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
一致性关库:
SQL> shutdown immediate;
将6号数据文件移走。
[oracle@server1 prod]$ mv dbhang01.dbf /tmp/
启库:
SQL> startup;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 541068408 bytes
Database Buffers 281018368 bytes
Redo Buffers 6586368 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/oracle/app/oradata/prod/dbhang01.dbf'
将6号文件offline:
SQL> alter database datafile 6 offline;
Database altered.
SQL> alter database open;
Database altered.
SQL> select file#,status from v$datafile;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 OFFLINE
7 ONLINE
此时想要将6号数据文件online,进而进行xtts迁移
开始恢复:
由于操作系统上已不存在6号文件,现创建6号数据文件:
SQL> alter database create datafile 6 as '/oracle/app/oradata/prod/dbhang01.dbf' ;
Database altered.
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: '/oracle/app/oradata/prod/dbhang01.dbf'
归档不存在。
(使用alter database create datafile 6 as这种方式创建的数据文件需要读取的日志,
必须是创建该原数据文件以来所有的日志)
查看SCN信息:
查看块头SCN:
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1115118
2 1115118
3 1115118
4 1115118
5 1115118
6 1113754
7 1115118
7 rows selected.
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 1115118
2 1115118
3 1115118
4 1115118
5 1115118
6 1115115 1115115
7 1115118
将6号文件的scn1113754推到scn1115115即可online
使用bbed推进数据文件SCN:
BBED> set filename '/oracle/app/oradata/prod/dbhang01.dbf'
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0010fe9a
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x3ec415e7
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000a
ub4 kcrbabno @504 0x00000089
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
当前484偏移量(SCN):
SQL> select to_number('0010fe9a','xxxxxxxxxx') from dual;
TO_NUMBER('0010FE9A','XXXXXXXXXX')
----------------------------------
1113754 //与查询出来的一致
我们需要将1113754-> 1115115
转换16进制:
SQL> select to_char('1115115','xxxxxxxxx') from dual;
TO_CHAR('1
----------
1103eb
我们需要修改的值为001103eb
但是注意,对于little-endian的format,他存储是先存储低位的,因此实际block 存储的是:eb031100
修改offset 484:
BBED> set offset 484
OFFSET 484
BBED> dump count 64
File: /oracle/app/oradata/prod/dbhang01.dbf (0)
Block: 1 Offsets: 484 to 547 Dba:0x00000000
------------------------------------------------------------------------
9afe1000 00000000 e715c43e 01000000 0a000000 89000000 10000000 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
需要将 9afe1000 修改为 eb031100
BBED> set offset 484
OFFSET 484
BBED> dump count 64
File: /oracle/app/oradata/prod/dbhang01.dbf (0)
Block: 1 Offsets: 484 to 547 Dba:0x00000000
------------------------------------------------------------------------
9afe1000 00000000 e715c43e 01000000 0a000000 89000000 10000000 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set mode edit
MODE Edit
BBED> modify /x eb031100
BBED-00209: invalid number (eb031100)
---由于这个信息表头高位,输入前4为字符就行了
BBED> modify /x eb0311
File: /oracle/app/oradata/prod/dbhang01.dbf (0)
Block: 1 Offsets: 484 to 547 Dba:0x00000000
------------------------------------------------------------------------
eb031100 00000000 e715c43e 01000000 0a000000 89000000 10000000 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
应用保存:
BBED> sum apply
Check value for File 0, Block 1:
current = 0x526d, required = 0x526d
再次查看:
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x001103eb
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x3ec415e7
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000a
ub4 kcrbabno @504 0x00000089
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
再次查看SCN信息:
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1116270
2 1116270
3 1116270
4 1116270
5 1116270
6 1115115
7 1116270
7 rows selected.
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 1116270
2 1116270
3 1116270
4 1116270
5 1116270
6 1115115 1115115
7 1116270
7 rows selected.
执行recover:
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
查看是否有异常:
SQL> select count(*) from test;
COUNT(*)
----------
86957
SQL> create table test2 tablespace dbhang as select * from dba_objects;
Table created.
SQL> insert into test2 select * from test2;
86958 rows created.
online成功,表空间正常读写。
本次只修改了offset 484
其他有关bbed的案例: