利用bbed来修复ora-01190 ora-01189 错误

最新推荐文章于 2024-08-14 15:10:21 发布

老苏畅谈运维

最新推荐文章于 2024-08-14 15:10:21 发布

阅读量389

点赞数 4

分类专栏： oracle BBED 数据恢复文章标签： oracle 数据库运维

文章来源于老苏畅谈运维，欢迎您转载，转载声明来源老苏畅谈运维

本文链接：https://blog.csdn.net/zhirongsu/article/details/139341972

版权

oracle 同时被 3 个专栏收录

36 篇文章 2 订阅

订阅专栏

数据恢复

13 篇文章 1 订阅

订阅专栏

BBED

1 篇文章 0 订阅

订阅专栏

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG数据库运维（如安装迁移，性能优化、故障应急处理等）
公众号：老苏畅谈运维
欢迎关注本人公众号，更多精彩与您分享。

在做数据库的恢复，很多时候，我们会遇到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，修改文件头以下几个地方：
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

请添加图片描述

老苏畅谈运维

关注

4
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
利用bbed来修复ora-01190 ora-01189 错误

在做数据库的恢复，很多时候，我们会遇到datafile的一些异常情况，比如重建controlfile时少加了某个datafile，这样在后面操作时候通常会遇到ora-01190 或者 ora-01189 错误，针对这两个错误，我们该如何解决呢？
复制链接

扫一扫