在做数据库的恢复，很多时候，我们会遇到datafile的一些异常情况，比如重建controlfile时少加了某个datafile，这样在后面操作时候通常会遇到 ora-01190 或者 ora-01189 错误，针对这两个错误，我们该如何解决呢？
一、模拟ora-01190错误
1.1 关闭数据库和关闭归档
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> col name for a50;
SQL> set linesize 400;
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/oradata/dbbbed/system01.dbf SYSTEM
2 /u01/oradata/dbbbed/sysaux01.dbf ONLINE
3 /u01/oradata/dbbbed/undotbs01.dbf ONLINE
4 /u01/oradata/dbbbed/users01.dbf ONLINE
5 /u01/oradata/dbbbed/tbst01.dbf ONLINE
6 /u01/oradata/dbbbed/szr01.dbf ONLINE
7 /u01/oradata/dbbbed/mssm01.dbf ONLINE
7 rows selected.
1.2 创建测试文件
--创建测试表空间
SQL> create tablespace test0529 datafile '/u01/oradata/dbbbed/test0529.dbf' size 5m;
Tablespace created.
--创建测试表，插入数据
SQL> conn szr/szr
Connected.
SQL> create table t0529(a date) tablespace test0529;
SQL> insert into t0529 values(sysdate);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select *from t0529;
A
-------------------
2024-05-29 23:20:46
2024-05-29 23:20:50
2024-05-29 23:20:52
1.3 生成控制文件
SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug close_trace
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/dbbbed/dbbbed/trace/dbbbed_ora_1371.trc
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
1.4 重建控制文件
--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--重建控制文件
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DBBBED" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/dbbbed/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/dbbbed/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/dbbbed/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/dbbbed/system01.dbf',
'/u01/oradata/dbbbed/sysaux01.dbf',
'/u01/oradata/dbbbed/undotbs01.dbf',
'/u01/oradata/dbbbed/users01.dbf',
'/u01/oradata/dbbbed/tbst01.dbf',
'/u01/oradata/dbbbed/szr01.dbf',
'/u01/oradata/dbbbed/mssm01.dbf'
CHARACTER SET AL32UTF8
;
ORACLE instance started.
Total System Global Area 3691200512 bytes
Fixed Size 2258680 bytes
Variable Size 788531464 bytes
Database Buffers 2885681152 bytes
Redo Buffers 14729216 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
Control file created.
注意上面我重建controlfile时，少加一个datafile test0529.dbf（实际上很多人在重建时都容易犯这样的错误）
1.5 不完全恢复
--查看文件状态
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------------------------------------- -------
1 /u01/oradata/dbbbed/system01.dbf SYSTEM
2 /u01/oradata/dbbbed/sysaux01.dbf RECOVER
3 /u01/oradata/dbbbed/undotbs01.dbf RECOVER
4 /u01/oradata/dbbbed/users01.dbf RECOVER
5 /u01/oradata/dbbbed/tbst01.dbf RECOVER
6 /u01/oradata/dbbbed/szr01.dbf RECOVER
7 /u01/oradata/dbbbed/mssm01.dbf RECOVER
7 rows selected.
文件状态为RECOVER，需要做一下恢复。
--不完全恢复
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2064122 generated at 05/29/2024 23:22:16 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_48_1164710666.dbf
ORA-00280: change 2064122 for thread 1 is in sequence #48
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/arch/1_48_1164710666.dbf'
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 '/u01/arch/1_48_1164710666.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
--不完全恢复后，查看文件状态
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------------------------------------- -------
1 /u01/oradata/dbbbed/system01.dbf SYSTEM
2 /u01/oradata/dbbbed/sysaux01.dbf ONLINE
3 /u01/oradata/dbbbed/undotbs01.dbf ONLINE
4 /u01/oradata/dbbbed/users01.dbf ONLINE
5 /u01/oradata/dbbbed/tbst01.dbf ONLINE
6 /u01/oradata/dbbbed/szr01.dbf ONLINE
7 /u01/oradata/dbbbed/mssm01.dbf ONLINE
8 /u01/app/oracle/product/11.2.0/db/dbs/MISSING00008 RECOVER
8 rows selected.
注意看这个最开始我们故意漏掉的datafile，现在变成missing了。
二、修复 ORA-01190 错误
--rename有问题的文件
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db/dbs/MISSING00008' to '/u01/oradata/dbbbed/test0529.dbf';
Database altered.
已经rename完成了之后，能不能直接把这个文件online呢，我们尝试一下，发现报错：
SQL> alter database datafile 8 online;
alter database datafile 8 online
*
ERROR at line 1:
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/u01/oradata/dbbbed/test0529.dbf'
这里出现ORA-01190错误，跟ORA-01189错误类似，ORA-01189错误是在重建controlfile时出现的
从这2个错误来看，根本的原因是什么呢？之所以报这个错误，是因为这个datafile的某些信息跟其他datafile的resetlogs信息不同。要想解决这个问题，那么我们就需要对datafile header 结构比较了解，要处理这类问题就比较容易了。
我们可以利用bbed，修改文件头以下几个地方：
offset 112
offset 116
offset 484
offset 492
先找一个正常文件头（如file 7 block 1）查看这几个地方的值
BBED> set file 7 block 1
FILE# 7
BLOCK# 1
BBED> map /v
File: /u01/oradata/dbbbed/mssm01.dbf (7)
Block: 1 Dba:0x01c00001
------------------------------------------------------------
Data File Header
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x45c12574
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x001f7efb
ub2 kscnwrp @120 0x0000
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x001f7efe
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x45c12575
我们在看一下有问题的文件这几个地方的值
BBED> set file 8 block 1
FILE# 8
BLOCK# 1
BBED> map /v
File: /u01/oradata/dbbbed/test0529.dbf (8)
Block: 1 Dba:0x02000001
------------------------------------------------------------
Data File Header
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x456c130a
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x00123e6b
ub2 kscnwrp @120 0x0000
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x001f7efa
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x45c124a8
对比一下，这个几个值，跟 file 7 是不是一致，将 file 8 的文件头上述几个位置修改成跟file 7 一样就可以了。
以下是修改过程：
BBED> modify /x 7425c145 offset 112
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x45c12574
BBED> modify /x fb7e1f00 offset 116
BBED-00209: invalid number (fb7e1f00)
BBED> modify /x fb offset 116
BBED> modify /x 7e offset 117
BBED> modify /x 1f offset 118
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x001f7efb
ub2 kscnwrp @120 0x0000
BBED> modify /x fe offset 484
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x001f7efe
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x45c124a8
BBED> sum apply
Check value for File 8, Block 1:
current = 0xa62b, required = 0xa62b
修改完成之后，我们刷新buffer cache即可，不需要重启实例。
SQL> alter system flush buffer_cache;
System altered.
SQL> recover datafile 8;
Media recovery complete.
SQL> alter database datafile 8 online;
Database altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------------------------------------- -------
1 /u01/oradata/dbbbed/system01.dbf SYSTEM
2 /u01/oradata/dbbbed/sysaux01.dbf ONLINE
3 /u01/oradata/dbbbed/undotbs01.dbf ONLINE
4 /u01/oradata/dbbbed/users01.dbf ONLINE
5 /u01/oradata/dbbbed/tbst01.dbf ONLINE
6 /u01/oradata/dbbbed/szr01.dbf ONLINE
7 /u01/oradata/dbbbed/mssm01.dbf ONLINE
8 /u01/oradata/dbbbed/test0529.dbf ONLINE
8 rows selected.
--验证
SQL> conn szr/szr
Connected.
SQL> select * from t0529;
A
-------------------
2024-05-29 23:20:46
2024-05-29 23:20:50
2024-05-29 23:20:52
数据查询正常
