数据库恢复遇到ora-01190 或 ora-01189 错误，如何解决？

原创 szrsu 2024-05-31

843

在做数据库的恢复，很多时候，我们会遇到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: {<RET>=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，修改文件头

我们可以利用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

数据查询正常

方法二：推进SCN
```language
--首先启动到mount状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
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
Database mounted.


alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col scn for 99999999999999
select checkpoint_change# scn from v$database;
	    SCN
---------------
	2187462

select max(checkpoint_time),max(CHECKPOINT_CHANGE#) scn from v$datafile_header;

MAX(CHECKPOINT_TIME		SCN
------------------- ---------------
2024-05-31 22:53:05	    2187462


select max(checkpoint_time),max(CHECKPOINT_CHANGE#) scn from v$datafile;

MAX(CHECKPOINT_TIME		SCN
------------------- ---------------
2024-05-31 22:53:05	    2187462



--取上面最大的scn，计算新的scn号
select to_char(2187462+100000,'xxxxxxxxxxxxxxxx') from dual;   -- 2287462
TO_CHAR(2187462+1
-----------------
	   22e766

--修改
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000


--因为当前SCN和我们要更改的目标SCN都没有超过一个wrap，因此wrap位是0，更改base为 22e766 即可，因此我们从SCN地址06001AE70，往后推4位，为base。
--前边值是上一步第一个值,内存地址，4 代表修改字节，后边是修改后的scn号
SQL> oradebug poke 0x06001AE70 4 0x22e766
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER:	[06001AE70, 06001AE74) = 0022E766

再次查看更改后的SCN：
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 0022E766 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000


接下来启动数据库，查看SCN，我们预计是比 2287462 大一点的：
SQL> alter database open;

Database altered.

SQL> select checkpoint_change# scn from v$database;

	    SCN
---------------
	2287463
	
	
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
RECOVER                                             


8 rows selected.

####推进SCN后，打开数据库后，recover datafile 成功
SQL> recover datafile 8;
Media recovery complete.
SQL> alter database datafile 8 online;

Database altered.

欢迎关注我的公众号

墨力计划 oracle

最后修改时间：2024-06-20 22:21:07

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者