作为DBA在启动数据库时，可能会经常遇到数据库无法正常open的现象，其中原因有很多，今天我们讨论控制文件过旧的场景。即ORA-01110: data file 5: ‘/data/oracle/prod/test01.dbf’
一、创建测试数据
SQL> create tablespace test datafile '/data/oracle/prod/test01.dbf' size 10M autoextend on;
SQL> create user user1 default tablespace test identified by oracle;
SQL> grant connect,resource to user1;Grant succeeded.SQL> conn user1/oracle;
Connected.
SQL> create table t1 (id int,name varchar2(10));Table created.SQL>
二、测试步骤
查看系统SCN号和数据文件头SCN号
SQL> select checkpoint_change# from v$database;CHECKPOINT_CHANGE#
------------------1427574SQL> select name,CHECKPOINT_CHANGE# from v$datafile_header;NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/data/oracle/prod/system01.dbf 1427574
/data/oracle/prod/sysaux01.dbf 1427574
/data/oracle/prod/undotbs01.dbf 1427574
/data/oracle/prod/test01.dbf 1427574
/data/oracle/prod/users01.dbf 1427574
复制一下test01
cp test01.dbf test01.dbf_bak
向表T1插入一些数据
insert into t1 values(1,'aaa');insert into t1 values(2,'bbb');commit;
正常关闭数据库
shutdown immediate;
用旧的test01.dbf替换正常关机后的数据文件
mv test01.dbf_bak test01.dbf
启动数据库报错，test数据文件需要介质恢复
SQL> startup
ORACLE instance started.Total System Global Area 3221225472 bytes
Fixed Size 8625856 bytes
Variable Size 788529472 bytes
Database Buffers 2415919104 bytes
Redo Buffers 8151040 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/data/oracle/prod/test01.dbf'
查看SCN号
SQL> select checkpoint_change# from v$database;CHECKPOINT_CHANGE#
------------------1430143SQL> select name,CHECKPOINT_CHANGE# from v$datafile_header;NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/data/oracle/prod/system01.dbf 1430143
/data/oracle/prod/sysaux01.dbf 1430143
/data/oracle/prod/undotbs01.dbf 1430143
/data/oracle/prod/test01.dbf 1429387
/data/oracle/prod/users01.dbf 1430143
此时我们有一些方法让数据库正常OPEN。
- 介质恢复，如果时间很短，可以从redo中恢复，如果时间较长，可以从归档中恢复（前提是开启归档）
- 如果没有归档，将test表空间设置为offline,数据库可以正常open，但是丢失test表空间所有数据。
- 推进SCN号
三、推进SCN号
推进SCN号有很多方法，如event 10015 来增加 scn 的值、 gdb/dbx 来直接修改内存中的值、oradebug、bbed等，今天我们尝试用BBED来修改test数据文件的文件头。
安装bbed
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
通过bbed修改test文件头SCN
[oracle@test lib]$ ./bbed parfile=/home/oracle/bbed.par
BBED> showFILE# 5BLOCK# 1OFFSET 0DBA 0x01400001 (20971521 5,1)FILENAME /data/oracle/prod/test01.dbfBIFILE bifile.bbdLISTFILE /home/oracle/datafile.txtBLOCKSIZE 8192MODE BrowseEDIT UnrecoverableIBASE DecOBASE DecWIDTH 80COUNT 512LOGFILE log.bbdSPOOL NoBBED>
从1429387,修改为 1430143,
SQL> select to_char(1429387,'xxxxxxxxxxx'),to_char(1430143,'xxxxxxxxxxxx') from dual;TO_CHAR(1429 TO_CHAR(14301
------------ -------------15cf8b 15d27fBBED> set file 5 block 1FILE# 5BLOCK# 1BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x0015cf8bub2 kscnwrp @488 0x8000ub2 kscnwrp2 @490 0x0000ub4 kcvcptim @492 0x474e96f7ub2 kcvcpthr @496 0x0001union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000002ub4 kcrbabno @504 0x000005f4ub2 kcrbabof @508 0x0010ub1 kcvcpetb[0] @512 0x02ub1 kcvcpetb[1] @513 0x00ub1 kcvcpetb[2] @514 0x00ub1 kcvcpetb[3] @515 0x00ub1 kcvcpetb[4] @516 0x00ub1 kcvcpetb[5] @517 0x00ub1 kcvcpetb[6] @518 0x00ub1 kcvcpetb[7] @519 0x00#我们需要将 kscnbas即@484改为15d27f，BBED> assign dba 5,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas = 0x0015d27f
ub4 kscnbas @484 0x0015d27fBBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x0015d27fub2 kscnwrp @488 0x8000ub2 kscnwrp2 @490 0x0000ub4 kcvcptim @492 0x474e96f7ub2 kcvcpthr @496 0x0001union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000002ub4 kcrbabno @504 0x000005f4ub2 kcrbabof @508 0x0010ub1 kcvcpetb[0] @512 0x02ub1 kcvcpetb[1] @513 0x00ub1 kcvcpetb[2] @514 0x00ub1 kcvcpetb[3] @515 0x00ub1 kcvcpetb[4] @516 0x00ub1 kcvcpetb[5] @517 0x00ub1 kcvcpetb[6] @518 0x00ub1 kcvcpetb[7] @519 0x00
#可以看出，已经修改为15d27f
#保存修改
BBED> sum apply dba 5,1
Check value for File 5, Block 1:
current = 0xf73e, required = 0xf73e
查看数据文件头SCN号
SQL> select name,CHECKPOINT_CHANGE# from v$datafile_header;NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/data/oracle/prod/system01.dbf 1430143
/data/oracle/prod/sysaux01.dbf 1430143
/data/oracle/prod/undotbs01.dbf 1430143
/data/oracle/prod/test01.dbf 1430143
/data/oracle/prod/users01.dbf 1430143
此时数据文件头SCN号已经和系统SCN号一致，但test01.dbf仍然在v$recover_file，执行下recover datafile 就可以正常启库了
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;Database altered.
在12C之前的版本还可以通过oradebug的方式来推进SCN
SQL> col PLATFORM_NAME for a50;
SQL> set linesize 400;
SQL> select PLATFORM_ID,PLATFORM_NAME from v$database;PLATFORM_ID PLATFORM_NAME----------- -------------------------------------------------- 13 Linux x86 64-bit
SQL> select * from v$transportable_platform where PLATFORM_NAME='Linux x86 64-bit';PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT CON_ID----------- -------------------------------------------------- -------------- ---------- 13 Linux x86 64-bit Little 0
查看当前系统SCN号和最大数据文件头scn号
SQL> select checkpoint_change# scn from v$database;SCN
----------1430143SQL> select max(checkpoint_time),max(CHECKPOINT_CHANGE#) scn from v$datafile_header;MAX(CHECK SCN
--------- ----------
21-MAR-25 1430143
我们将最大的SCN号加100000计算其ASCII值
SQL> select to_char(1430143+100000,'xxxxxxxxxxxxxxxx') from dual;TO_CHAR(1430143+1
-----------------17591f
修改SCN
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [0600113B8, 0600113E8) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60049740 00000000SQL> oradebug poke 0x0600113B8 4 0x17591f
此时再用oradebug验证，已修改
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [0600113B8, 0600113E8) = 0017591F 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60049740 00000000
SQL>
开启数据库
alter database open;