BBED修复Oracle 12C ASM文件方法

数据库工作笔记 Sharing 2021-08-12

1070

bbed很好的数据文件修复工具，解决二进制文件修改难题。在没有备份、灾难发生、恢复失效时挽救DB的终极武器。

有了它可以将很多行将就木的DB启死回生（不到万不得已，不建议使用）。

资深DBA的法宝。

很多数据修复公司用它赚到盆满钵溢（抢救一个企业的数据库，尤其是核心DB,一个订单少则几十万，多则上百万），将这个方法总结出来分享给大家。希望大家都可以发发发，哈哈哈！

## asm转换成平面文件

方法一 (rman)：

RMAN> convert datafile '+DATA/oracleasm/datafile/test.366.88888' format'/home/oracle/test.dbf';

方法二（OS）：

sql>alter tablespace test offline;

# su - grid

ASMCMD> cp +DATA/oracleasm/datafile/ test.366.88888 /home/grid/testdg.dbf

sql>alter tablespace test online ;

# chown oracle:oinstall /home/grid/test.dbf

方法三(sql)：

sql>alter tablespace test offline;

sql>alter database rename file '+DATA/oracleasm/datafile/test.366.88888' to'/home/oracle/test.dbf';

sql>alter tablespace test online;

## 平面文件转换成asm

方法一 (sql)：

SQL> create directory D_FILE as '/home/oracle/file';

SQL> create directory D_ASM as '+DATA/DATAFILE';

SQL> select directory_name, directory_path from dba_directories;

用dbms_file_transfer执行文件格式转换

SQL> exec dbms_file_transfer.copy_file('D_FILE ','system.777.93459','D_ASM', 'system');

将目标库的数据文件重命名

SQL> startup mount;

SQL> alter database rename file '+DATA/DATAFILE/system.777.93459' to'/home/oracle/file/system';

SQL> recover

SQL> select tablespace_name, file_name from dba_data_files;

SQL> alter database open;

方法二：

sql> alter database rename file '+DATA/DATAFILE/system.777.93459' to'/home/oracle/file/system';

sql> alter database datafile '/home/oracle/file/system' offline;

rman> convert datafile '/home/oracle/file/system' format '+DATA/DATAFILE/system.777.93459';

-- rman> copy datafile '/home/oracle/file/system ' format'+DATA/DATAFILE/system.777.93459';

sql> alter database rename file '/home/oracle/file/system' to '+DATA/DATAFILE/system.777.93459';

sql> alter database datafile '+DATA/DATAFILE/system.777.93459' online;

## 如果上面online失败执行下面这两步

--- sql> recover datafile '+DATA/DATAFILE/system.777.93459';

--- sql> alter database datafile '+DATA/DATAFILE/system.777.93459' online;

备注： 12c pdb模式下的数据文件需要切换到对应pdb下才能 offline/online

alter session set container= PDB_DEV;

alter database datafile '+DATA/DATAFILE/system.777.93459' offline;

alter database rename file '/home/oracle/file/system' to '+DATA/DATAFILE/system.777.93459';

============ 开始修改数据块===项目案例 =============

找到损坏的数据文件

select file# from v$datafile_header where to_char(checkpoint_change#)<>'20157658979';

查询好的数据文件块头

dump offset 484 count 4;

File: /data3/system.623.1079 (1)

Block: 1 Offsets: 484 to 487 Dba:0x00400001

------------------------------------------------------------------------

dc2cfef4

<32 bytes per line>

BBED> dump offset 492 count 4;

File: /data3/system.623.1079 (1)

Block: 1 Offsets: 492 to 495 Dba:0x00400001

------------------------------------------------------------------------

a4f95a40

<32 bytes per line>

BBED> dump offset 504 count 4;

File: /data3/system.623.1079 (1)

Block: 1 Offsets: 504 to 507 Dba:0x00400001

------------------------------------------------------------------------

02000000

批量收集坏块文件号

--select listagg(file#,',') within group (order by file#) as cols from v$datafile_header where con_id=4;

-- select listagg(file#,',') within group (order by file#) as cols from v$datafile_header where CHECKPOINT_CHANGE#='20157658983';

SELECT file#,to_char(checkpoint_change#),to_char(resetlogs_change#),name,status from v$datafile_header;

1. 从ASM转换为平面文件

select 'convert datafile ' ||''''||name ||''''||' format '||''''||'/data3/'||substr(name,-(length(name)-instr(name,'/',-1)))||''';' from v$datafile_header where file# in (55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

查数据文件状态

select file#,name,status,to_char(checkpoint_change#) from v$datafile where file# in (55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

--offline

## 12c行转列

select listagg(file#,',') within group (order by file#) as cols from v$datafile_header where STATUS='OFFLINE' and CHECKPOINT_CHANGE#<>'20157658979';

spool /home/oracle/filelist.txt

select file#||' /data3/'||substr(name,-(length(name)-instr(name,'/',-1)))||' '||bytes from v$datafile_header

where file# in (55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

spool off

2. bbed 修改datafile_header

Cat p.par

blocksize=8192 ---指定block size大小

listfile=/home/oracle/filelist.txt ---指定文件名

password=blockedit ----默认密码

logfile=/home/oracle/log.bbd ----制定记录的日志文件及位置

mode=edit ---指定操作模式(分edit和view两种)

spool=yes ---是否记录bbed的操作日志

bbed parfile=p.par

----set filename '/home/oracle/offline/users.451.991392703';

verify

set dba 134,1;

m /x 63777db1 dba 134,1 offset 484;

-- assign kcvfhckp.kcvcpscn.kscnbas=0xe9911800; (1891e9)

--assign kcvfh.kcvfhckp.kcvcpscn.kscnbas = 20168425658

m /x e991 offset 484

set offset +2

m /x 1800

assign kcvfh.kcvfhckp.kcvcptim=0x4052d9ca; (492)

m /x 01 dba 134,1 offset 500;

m /x 02 dba 134,1 offset 504;

sum apply;

verify;

set dba 133,1

m /x bac0 offset 484

set offset +2

m /x 21b2

m /x 0400 offset 488

备注：以上为bbed的简易配置和使用方法，安装过程不再赘述。

===== 项目案例 bbed 批量修改 484，488，500====

-- 484

-- spool /home/oracle/484.txt

select 'set dba '||file#||',1 '||chr(10)||'m /x dc2c offset 484'||chr(10)||'set offset +2'||chr(10)||'m /x fef4'||chr(10)||'sum apply'||chr(10) from

v$datafile_header where file# in

(55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

--spool off

-- 488

select 'set dba '||file#||',1 '||chr(10)||'m /x 2e offset 488'||chr(10)||'y'||chr(10)||'sum apply'||chr(10) from v$datafile_header where file# in (55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

-- 500

select 'm /x 0f800300 dba '||file#||',1 offset 500'||chr(10)||'sum apply'||chr(10) from v$datafile_header where file# in (55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

-- select 'm /x 14800300 dba '||file#||',1 offset 500'||chr(10)||'y'||chr(10)||'sum apply'||chr(10) from v$datafile_header where file# in

(55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

------- offline 文件增加修改 492，504

select 'm /x a4f95a40 dba '||file#||',1 offset 492'||chr(10)||'sum apply'||chr(10) from v$datafile_header where file# in (55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

select 'm /x 02000000 dba '||file#||',1 offset 504'||chr(10)||'sum apply'||chr(10) from v$datafile_header where file# in (55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

--- resetlog change#不一致时增加修改 116

select 'm /x 01000000 dba '||file#||',1 offset 116'||chr(10)||'y'||chr(10)||'sum apply'||chr(10) from v$datafile_header where file# in

(55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

-- 验证

p kcvfhckp

找出下一个归档号及日志号

select * from (SELECT sequence#,to_char(resetlogs_change#),to_char(first_change#),to_char(next_change#) FROM v$archived_log order by sequence# desc) where rownum<10;

查询结果： 229391 20167879394

--scn号 next_change# 484

select to_char(201678793948,'xxxxxxxxxxxxxxxx') from dual;

TO_CHAR(201678793

-----------------

2ef4fe2cdc

20167879394

--下一个归档文件号 500

select to_char(229391,'xxxxxxxxxxxxxxxx') from dual;

TO_CHAR(229391,'X

-----------------

3800f

0f800300

SELECT file#,to_char(checkpoint_change#),to_char(resetlogs_change#),name,checkpoint_count,checkpoint_time FROM v$datafile_header;

---rman> copy datafile '+DATA/EFSDB/sysaux311' to '/home/oracle/offline/sysaux311';

rman> convert datafile '/home/oracle/offline/sysaux311' format '+DATA/EFSDB/sysaux311.dbf';

sql> alter database rename file '+DATA/EFSDB/sysaux311' to '+DATA/EFSDB/sysaux311.dbf';

alter session set container=EFSPDB_SIT;

alter session set container=CDB$ROOT;

alter database datafile 134 online;

sql> alter database datafile '+DATA/EFSDB/users.451.991392703.dbf' online;

SQL> recover datafile 67;

--alter database open;

--3. online datafile重命名需先offline datafile

-- offline datafile 方法一：

select 'alter database datafile ' ||''''||'/data3/'||substr(name,-(length(name)-instr(name,'/',-1)))||''' offline;' from v$datafile_header where file# in (55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

-- offline datafile 方法二：

select 'alter database datafile ' ||file# ||' offline drop;' from v$datafile_header where file# in (55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

alter session set container=EFSPDB_SANDBOX;

alter database datafile 10 offline drop;

-- recover datafile 2;

alter session set container= EFSPDB_DEV;

alter database datafile 10 offline drop;

--recover datafile 2;

alter session set container=EFSPDB_SIT;

alter database datafile 10 offline drop;

--recover datafile 2;

alter session set container=PDB$SEED;

alter database datafile 10 offline drop;

--recover datafile 2;

alter session set container=CDB$ROOT;

--alter session set container=PDB$SEED;

--alter database datafile '/data/online/undotbs1.294.984673693' offline;

4.转回 ASM文件

select 'convert datafile ' ||'''/data3/'||substr(name,-(length(name)-instr(name,'/',-1))) ||''''||' format '||''''||'+DATA/EFSDB/NEW/'||substr(name,-(length(name)-instr(name,'/',-1)))||'.dbf'';' from v$datafile_header where file# in

(55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

将上面生成的结果放入下面批量转换

run

{

}

5 .重定位ASM中文件位置、文件名

-- select 'alter database rename file ' ||''''||name ||''''||' to '||''''||'/data3/'||substr(name,-(length(name)-instr(name,'/',-1)))||''';' from v$datafile_header where file# in(55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

select 'alter database rename file ' ||''''||name ||''''||' to '||''''||'+DATA/EFSDB/NEW/'||substr(name,-(length(name)-instr(name,'/',-1)))||'.dbf'';' from v$datafile_header where file# in(55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);

6 . online datafile ：

select 'alter database datafile ' ||file# ||' online;' from v$datafile_header where file# in

(55,56,60,67,68,69,70,72,73,112,113,114,115,116,139,140,141,142,143,144,170,171,182,187,188,190,191,192,193,195);