Oracle故障处理之错误代码ORA-01195

数据与人 2020-12-15

634

Oracle故障处理之ORA-01195: online backup of file 1 needs more recovery to be consistent

问题背景：
客户测试环境启动报错，协助排查处理

1> 启动日志

SQL> startup;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size    2213776 bytes
Variable Size    1056966768 bytes
Database Buffers    536870912 bytes
Redo Buffers    7360512 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

原因分析：
2> 数据库恢复的时侯有一个问题：
DB恢复需要这个归档日志文件/data/oracle/flash_recovery_area/ECOLOGY/archivelog/2020_01_14/o1_mf_1_165_%u_.arc，
但是在备份中没有。

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 28004892 generated at 11/24/2019 02:00:30 needed for thread 1
ORA-00289: suggestion :
/data/oracle/flash_recovery_area/ECOLOGY/archivelog/2020_01_14/o1_mf_1_165_%u_.arc
ORA-00280: change 28004892 for thread 1 is in sequence #165
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: 无法打开归档日志
'/data/oracle/flash_recovery_area/ECOLOGY/archivelog/2020_01_14/o1_mf_1_165_%u_.
arc'
ORA-27037: 无法获得文件状态
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-10879: error signaled in parallel recovery slave
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误 ORA-01195:
文件 1 的联机备份需要更多的恢复来保持一致性 ORA-01110: 数据文件
1: '/data/oracle/oradata/ecology/system01.dbf'

解决过程：
准备使用隐含参数_allow_resetlogs_corruption强制启动DB：
提示：Oracle的隐含参数只应该在测试环境或者在Oracle Support的支持下使用。
设置此参数之后，在数据库Open过程中，Oracle会跳过某些一致性检查，从而使数据库可能跳过不一致状态。

SQL> shutdown immediate;
ORA-01109: database not open




Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.




Total System Global Area 1603411968 bytes
Fixed Size    2213776 bytes
Variable Size    1056966768 bytes
Database Buffers    536870912 bytes
Redo Buffers    7360512 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [28004903], [0],
[28017149], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [28004902], [0],
[28017149], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [28004900], [0],
[28017149], [12583040], [], [], [], [], [], []
Process ID: 5434
Session ID: 115 Serial number: 3

以上强制启动后，产生Ora-600错误了，在预料之中

强制启动

[oracle@oat ecology]$ sqlplus  as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 14 13:21:18 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.


SQL> startup force;
ORACLE instance started.


Total System Global Area 1603411968 bytes
Fixed Size    2213776 bytes
Variable Size    1056966768 bytes
Database Buffers    536870912 bytes
Redo Buffers    7360512 bytes
Database mounted.
Database opened.
SQL>

现在没问题了，把隐含参数改回默认的：

SQL> 
SQL> alter system set "_allow_resetlogs_corruption" =false scope=spfile;
System altered.

关闭测试启动

SQL> shutdown immedaite;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup;
ORACLE instance started.


Total System Global Area 1603411968 bytes
Fixed Size    2213776 bytes
Variable Size    1056966768 bytes
Database Buffers    536870912 bytes
Redo Buffers    7360512 bytes
Database mounted.
Database opened.
SQL>

问题解决

往期回顾

Oracle故障处理之:Heavy swapping observed on system in last 5 mins

Oracle故障处理之导入TYPE对象报错ORA-02304：无效的对象标识符文字

Oracle故障处理之ORA-00445: process "J0" didnot start after 120 second

客官长按关注

吾辈自强不息

oracle oracle

文章转载自数据与人，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle故障处理之错误代码ORA-01195

评论