12C bbed modify datafile header scn
来源：互联网 发布：前端集合与数组的区别 编辑：程序博客网 时间：2024/04/27 16:04
在系统运维中经常会遇到datafile被offline以后归档日志被删除导致datafile不能recover的case，我们需要借助bbed修改datafile header 的scn 和目前的其他datafile header scn 一致，然后recover datafile。
今天我们模拟offline datafile 然后alter system checkpoint 修改数据头SCN,然后通过bbed修改offline 的数据文件头的scn号，测试环境为 Linux 7 & oracle 12.2.0.1，bbed 的安装不在此nlqgn.cn
fzwfp.cn
plqhs.cn
rwldw.cn
llnsy.cn说明。
确保归档开启否则offline datafile 报错jzntq.cn
nhmwr.cn
kznpk.cn：
SQL> alter database datafile 13 offline;
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
略开启归档
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 242
Next log sequence to archive 244
Current log sequence 244
a：查询数据文件名和SCN,offline,checkpoint
set lines 1200 pages 500
col name for a80
col checkpoint_change for a20
select CON_ID,file#,name,status,to_char(checkpoint_change#) checkpoint_change
from v$datafile_header order by 1;
CON_ID FILE# NAME hrprs.cn
llzxj.cn
kbdtp.cn
mfqrk.cn STATUS CHECKPOINT_CHANGE
---------- ---------- -------------------------------------------------------------------------------- --------------------- --------------------
0 4 +DATA/ORADB/DATAFILE/undotbs1.482.938191755 ONLINE 14919467
3 10 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/sysaux.495.938193177 ONLINE 14920021
3 14 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/bftbs.498.938193177 ONLINE 14920021
3 12 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/users.493.938193177 ONLINE 14920021
3 13 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177 ONLINE 14920021
3 9 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/system.496.938193177 ONLINE 14920021
3 11 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/undotbs1.497.938193177 ONLINE 14920021
7 rows selected.
SQL> alter database datafile 13 offline;
Database altered.
SQL> alter system checkpoint;
System altered.
SQL> set lines 1200 pages 500
SQL> col name for a80
SQL> col checkpoint_change for a20
SQL> select CON_ID,file#,name,status,to_char(checkpoint_change#) checkpoint_change
2 from v$datafile_header order by 1;
3 14 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/bftbs.498.938193177 ONLINE 14920187
3 12 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/users.493.938193177 ONLINE 14920187
3 13 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177 OFFLINE 14920021 --没有改变
3 9 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/system.496.938193177 ONLINE 14920187
3 11 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/undotbs1.497.938193177 ONLINE 14920187
7 rows selected.
b: 转换asm块到filesystem
grid: sqlplus / as sysasm
SQL> @/home/grid/bbed/cp_block_to_fs.sql
Enter value for asm_file_name: +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177
old 14: v_AsmFilename := '&ASM_File_Name';
new 14: v_AsmFilename := '+DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177';
Enter value for block_to_extract: 1
old 15: v_offstart := '&block_to_extract';
new 15: v_offstart := '1';
Enter value for number_of_blocks_to_extract: 1
old 16: v_numblks := '&number_of_blocks_to_extract';
new 16: v_numblks := '1';
Enter value for filesystem_file_name: /home/grid/test13.dbf
old 17: v_FsFilename := '&FileSystem_File_Name';
new 17: v_FsFilename := '/home/grid/test13.dbf';
PL/SQL procedure successfully completed.
c：修改filesystem block header scn：
bbed
BBED> set filename '/home/oracle/bbed/test13.dbf' blocksize 8192 mode edit
FILENAME /home/oracle/bbed/test13.dbf
BLOCKSIZE 8192
MODE Edit
BBED> p kcvfhckp.kcvcpscn.kscnbas
BBED> dump offset 484 count 8
File: /home/oracle/test13.dbf (0)
Block: 1 Offsets: 484 to 491 Dba:0x00000000
------------------------------------------------------------------------
55a9e300 00800000
<32 bytes per line> --注意字节顺序，Linux字节顺序相反！
查看正常的12 号文件头的SCN号：
BBED> set filename '/home/oracle/bbed/test12.dbf' blocksize 8192 mode edit
FILENAME /home/oracle/bbed/test13.dbf
BLOCKSIZE 8192
MODE Edit
BBED> p kcvfhckp.kcvcpscn.kscnbas
BBED>
BBED> dump offset 484 count 8
File: /home/oracle/bbed/test13.dbf (0)
Block: 1 Offsets: 484 to 491 Dba:0x00000000
------------------------------------------------------------------------
06b1e300 00800000
修改13号文件头的SCN:
BBED> m /x 06b1 offset 484
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /home/oracle/bbed/test13.dbf (0)
Block: 1 Offsets: 484 to 491 Dba:0x00000000
------------------------------------------------------------------------
06b1e300 00800000
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 1:
current = 0xd947, required = 0xd947
BBED> verify
DBVERIFY - Verification starting
FILE = /home/oracle/bbed/test13.dbf
BLOCK = 1
d: 转换filesystem block header to asm datafile header
@/home/grid/bbed/cp_block_to_asm.sql
Enter value for file_with_patched_block: /home/grid/bbed/test19.dbf
old 15: v_FsFileName := '&file_with_patched_block';
new 15: v_FsFileName := '/home/grid/bbed/test19.dbf';
Enter value for file_to_patch_in_asm: +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177
old 16: v_AsmFileName := '&file_to_patch_in_ASM';
new 16: v_AsmFileName := '+DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177';
Enter value for block_to_patch: 1
old 17: v_offstart := '&block_to_patch';
new 17: v_offstart := '1';
PL/SQL procedure successfully completed.
e: 查看修改后的asm datafile block header scn：
SQL> set lines 1200 pages 500
SQL> col name for a80
SQL> col checkpoint_change for a20
SQL> select CON_ID,file#,name,status,to_char(checkpoint_change#) checkpoint_change
2 from v$datafile_header order by 1;
CON_ID FILE# NAME STATUS CHECKPOINT_CHANGE
---------- ---------- -------------------------------------------------------------------------------- --------------------- --------------------
0 4 +DATA/ORADB/DATAFILE/undotbs1.482.938191755 ONLINE 14920187
3 10 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/sysaux.495.938193177 ONLINE 14921990
3 14 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/bftbs.498.938193177 ONLINE 14921990
3 12 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/users.493.938193177 ONLINE 14921990
3 13 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177 OFFLINE 14921990 --OK 已经修改
3 9 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/system.496.938193177 ONLINE 14921990
3 11 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/undotbs1.497.938193177 ONLINE 14921990
7 rows selected.
f: recover datafile
SQL> alter database datafile 13 online;
alter database datafile 13 online
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '+DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177'
SQL> recover datafile 13;
Media recovery complete.
SQL> alter database datafile 13 online;
Database altered.
SQL> set lines 1200 pages 500
SQL> col name for a80
SQL> col checkpoint_change for a20
SQL> select CON_ID,file#,name,status,to_char(checkpoint_change#) checkpoint_change
2 from v$datafile_header order by 1;
CON_ID FILE# NAME STATUS CHECKPOINT_CHANGE
---------- ---------- -------------------------------------------------------------------------------- --------------------- --------------------
0 4 +DATA/ORADB/DATAFILE/undotbs1.482.938191755 ONLINE 14920187
3 10 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/sysaux.495.938193177 ONLINE 14921990
3 14 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/bftbs.498.938193177 ONLINE 14921990
3 12 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/users.493.938193177 ONLINE 14921990
3 13 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177 ONLINE 14926508
3 9 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/system.496.938193177 ONLINE 14921990
3 11 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/undotbs1.497.938193177 ONLINE 14921990
7 rows selected.
数据文件正常恢复！
今天我们模拟offline datafile 然后alter system checkpoint 修改数据头SCN,然后通过bbed修改offline 的数据文件头的scn号，测试环境为 Linux 7 & oracle 12.2.0.1，bbed 的安装不在此nlqgn.cn
fzwfp.cn
plqhs.cn
rwldw.cn
llnsy.cn说明。
确保归档开启否则offline datafile 报错jzntq.cn
nhmwr.cn
kznpk.cn：
SQL> alter database datafile 13 offline;
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
略开启归档
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 242
Next log sequence to archive 244
Current log sequence 244
a：查询数据文件名和SCN,offline,checkpoint
set lines 1200 pages 500
col name for a80
col checkpoint_change for a20
select CON_ID,file#,name,status,to_char(checkpoint_change#) checkpoint_change
from v$datafile_header order by 1;
CON_ID FILE# NAME hrprs.cn
llzxj.cn
kbdtp.cn
mfqrk.cn STATUS CHECKPOINT_CHANGE
---------- ---------- -------------------------------------------------------------------------------- --------------------- --------------------
0 4 +DATA/ORADB/DATAFILE/undotbs1.482.938191755 ONLINE 14919467
3 10 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/sysaux.495.938193177 ONLINE 14920021
3 14 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/bftbs.498.938193177 ONLINE 14920021
3 12 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/users.493.938193177 ONLINE 14920021
3 13 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177 ONLINE 14920021
3 9 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/system.496.938193177 ONLINE 14920021
3 11 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/undotbs1.497.938193177 ONLINE 14920021
7 rows selected.
SQL> alter database datafile 13 offline;
Database altered.
SQL> alter system checkpoint;
System altered.
SQL> set lines 1200 pages 500
SQL> col name for a80
SQL> col checkpoint_change for a20
SQL> select CON_ID,file#,name,status,to_char(checkpoint_change#) checkpoint_change
2 from v$datafile_header order by 1;
CON_ID FILE# NAME
xlykk.cn
bcbpz.cn
pgtrf.cn
fsmxh.cn STATUS CHECKPOINT_CHANGE
0 4 +DATA/ORADB/DATAFILE/undotbs1.482.938191755
smnwn.cn
rzybc.cn
jwtsr.cn
jqwxf.cn
nywyr.cn
trfhx.cn
xhfqt.cn ONLINE 14920187
3 14 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/bftbs.498.938193177 ONLINE 14920187
3 12 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/users.493.938193177 ONLINE 14920187
3 13 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177 OFFLINE 14920021 --没有改变
3 9 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/system.496.938193177 ONLINE 14920187
3 11 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/undotbs1.497.938193177 ONLINE 14920187
7 rows selected.
b: 转换asm块到filesystem
grid: sqlplus / as sysasm
SQL> @/home/grid/bbed/cp_block_to_fs.sql
Enter value for asm_file_name: +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177
old 14: v_AsmFilename := '&ASM_File_Name';
new 14: v_AsmFilename := '+DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177';
Enter value for block_to_extract: 1
old 15: v_offstart := '&block_to_extract';
new 15: v_offstart := '1';
Enter value for number_of_blocks_to_extract: 1
old 16: v_numblks := '&number_of_blocks_to_extract';
new 16: v_numblks := '1';
Enter value for filesystem_file_name: /home/grid/test13.dbf
old 17: v_FsFilename := '&FileSystem_File_Name';
new 17: v_FsFilename := '/home/grid/test13.dbf';
PL/SQL procedure successfully completed.
c：修改filesystem block header scn：
bbed
BBED> set filename '/home/oracle/bbed/test13.dbf' blocksize 8192 mode edit
FILENAME /home/oracle/bbed/test13.dbf
BLOCKSIZE 8192
MODE Edit
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas
hcglh.cn
ymjyl.cn
crtwt.cn
dbhwz.cn
ckpdn.cn
xsnlj.cn @484 0x00e3a955
BBED> dump offset 484 count 8
File: /home/oracle/test13.dbf (0)
Block: 1 Offsets: 484 to 491 Dba:0x00000000
------------------------------------------------------------------------
55a9e300 00800000
<32 bytes per line> --注意字节顺序，Linux字节顺序相反！
查看正常的12 号文件头的SCN号：
BBED> set filename '/home/oracle/bbed/test12.dbf' blocksize 8192 mode edit
FILENAME /home/oracle/bbed/test13.dbf
BLOCKSIZE 8192
MODE Edit
BBED> p kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas
xcwrb.cn
hlnnw.cn
mfzll.cn
tyzws.cn @484 0x00e3b106
--- 转换10进制： TO_NUMBER('00E3B106','XXXXXXXXX')
srpbx.cn
rlnxd.cn
lmgbb.cn
kqwxn.cn
sqhyc.cn
kkwyn.cn
ljgtn.cn
wnqxq.cn
mtyrs.cn
npsld.cn
krrzp.cn
kfrcd.cn
zqnqg.cn
mhtnt.cn
14921990
BBED>
BBED> dump offset 484 count 8
File: /home/oracle/bbed/test13.dbf (0)
Block: 1 Offsets: 484 to 491 Dba:0x00000000
------------------------------------------------------------------------
06b1e300 00800000
修改13号文件头的SCN:
BBED> m /x 06b1 offset 484
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /home/oracle/bbed/test13.dbf (0)
Block: 1 Offsets: 484 to 491 Dba:0x00000000
------------------------------------------------------------------------
06b1e300 00800000
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 1:
current = 0xd947, required = 0xd947
BBED> verify
DBVERIFY - Verification starting
FILE = /home/oracle/bbed/test13.dbf
BLOCK = 1
d: 转换filesystem block header to asm datafile header
@/home/grid/bbed/cp_block_to_asm.sql
Enter value for file_with_patched_block: /home/grid/bbed/test19.dbf
old 15: v_FsFileName := '&file_with_patched_block';
new 15: v_FsFileName := '/home/grid/bbed/test19.dbf';
Enter value for file_to_patch_in_asm: +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177
old 16: v_AsmFileName := '&file_to_patch_in_ASM';
new 16: v_AsmFileName := '+DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177';
Enter value for block_to_patch: 1
old 17: v_offstart := '&block_to_patch';
new 17: v_offstart := '1';
PL/SQL procedure successfully completed.
e: 查看修改后的asm datafile block header scn：
SQL> set lines 1200 pages 500
SQL> col name for a80
SQL> col checkpoint_change for a20
SQL> select CON_ID,file#,name,status,to_char(checkpoint_change#) checkpoint_change
2 from v$datafile_header order by 1;
CON_ID FILE# NAME STATUS CHECKPOINT_CHANGE
---------- ---------- -------------------------------------------------------------------------------- --------------------- --------------------
0 4 +DATA/ORADB/DATAFILE/undotbs1.482.938191755 ONLINE 14920187
3 10 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/sysaux.495.938193177 ONLINE 14921990
3 14 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/bftbs.498.938193177 ONLINE 14921990
3 12 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/users.493.938193177 ONLINE 14921990
3 13 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177 OFFLINE 14921990 --OK 已经修改
3 9 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/system.496.938193177 ONLINE 14921990
3 11 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/undotbs1.497.938193177 ONLINE 14921990
7 rows selected.
f: recover datafile
SQL> alter database datafile 13 online;
alter database datafile 13 online
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '+DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177'
SQL> recover datafile 13;
Media recovery complete.
SQL> alter database datafile 13 online;
Database altered.
SQL> set lines 1200 pages 500
SQL> col name for a80
SQL> col checkpoint_change for a20
SQL> select CON_ID,file#,name,status,to_char(checkpoint_change#) checkpoint_change
2 from v$datafile_header order by 1;
CON_ID FILE# NAME STATUS CHECKPOINT_CHANGE
---------- ---------- -------------------------------------------------------------------------------- --------------------- --------------------
0 4 +DATA/ORADB/DATAFILE/undotbs1.482.938191755 ONLINE 14920187
3 10 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/sysaux.495.938193177 ONLINE 14921990
3 14 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/bftbs.498.938193177 ONLINE 14921990
3 12 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/users.493.938193177 ONLINE 14921990
3 13 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/testbs.494.938193177 ONLINE 14926508
3 9 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/system.496.938193177 ONLINE 14921990
3 11 +DATA/ORADB/4A453209EFB25340E0536538A8C08684/DATAFILE/undotbs1.497.938193177 ONLINE 14921990
7 rows selected.
数据文件正常恢复！
阅读全文
0 0
- 12C bbed modify datafile header scn
- 目的：使用bbed将已经offline掉的datafile 5 的scn信息改为与其他datafile一致。
- 使用BBED修改SCN
- 从control file 和 datafile header上获得不同的scn信息
- 12c online move datafile
- 使用BBED修改SCN让数据文件online
- C# modify http header
- Cannot modify header information
- Check the recover datafile header
- 使用bbed跳过丢失的归档进行recover datafile
- Warning: Cannot modify header information - header
- Warning: Cannot modify header information - header
- warning:Cannot modify header information
- Cannot modify header information - headers
- Cannot modify header information解决办法
- Warning: Cannot modify header information
- Android Studio Modify File Header
- Warning: Cannot modify header information
- Unity3D
- select下拉菜单所有选项均罗列显示
- 如何设计自适应屏幕大小的网页
- EU4-63: Giving details
- Java常见面试（6）
- 12C bbed modify datafile header scn
- 先码后看 java IO系统继承结构图 侵立删
- 大数据架构方案总结-ljt（转载）
- Java常见面试（7）
- Java---学习（2）
- pandas 转换成行列表进行读取与Nan处理
- Longest Repeating Character Replacement
- Kubernetes e2e test and test framework
- HDU--1181--变形课（深搜）
