存储断电导致ORA-00325故障恢复

发表于 2023 年 11 月 11 日 由 惜分飞

由于存储断电,数据库启动最初报错

Reconfiguration complete
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 22887 KB redo, 2025 data blocks need recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl11/trace/orcl11_ora_25449.trc  (incident=25522):
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [203596], [1537], [3046], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl11/incident/incdir_25522/orcl11_ora_25449_i25522.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Abort recovery for domain 0
Aborting crash recovery due to error 600
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl11/trace/orcl11_ora_25449.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [203596], [1537], [3046], []
Abort recovery for domain 0
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl11/trace/orcl11_ora_25449.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [203596], [1537], [3046], []
ORA-600 signalled during: ALTER DATABASE OPEN...

尝试重建ctl之后,恢复数据库报错ORA-00325错误
ORA-00325

[oracle@xifenfei ~]$ oerr ora 325
00325, 00000, "archived log for thread %s, wrong thread # %s in header"
// *Cause:  The archived log is corrupted or for another thread. Can not
//          use the log for applying redo.
// *Action: Find correct archived log.

归档日志文件的头部中的线程编号和实际使用的线程编号不匹配的时候报出的错误。该错误的常见情形是由于线程 ID (thread ID) 没有被正确分配或更新造成的，如从主控制档（control file）恢复用户表空间后更改线程 ID 但系统没有知道，导致系统在访问归档日志文件时，由于归档日志文件的头部中的线程编号与实际使用的线程编号不匹配，而出现该错误。

尝试强制拉库,报ORA-600 2662错误
ORA-600-2662

尝试修改scn继续启动库报ORA-600 kclchkblk_4
ORA-600-kclchkblk_4
继续调整scn,数据库open成功.后续就是各种收尾工作(比如undo回滚段,tempfile,各种ORA-600错误等)

发表在 Oracle备份恢复 | 标签为 | 评论关闭

重建ctl遭遇ORA-01163 ORA-01110和ORA-600 kccccfl_1错误

发表于 2023 年 11 月 9 日 由 惜分飞

有客户联系我,数据库强制关闭之后无法正常启动,通过他们一系列尝试,数据库报open 报ORA-01555错误
ORA-01555

通过分析确认是由于客户屏蔽一致性强制拉库失败导致

Wed Nov 01 19:49:03 2023
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 1199697777
Archived Log entry 68106 added for thread 1 sequence 3829 ID 0x761a769a dest 1:
Archived Log entry 68107 added for thread 1 sequence 3830 ID 0x761a769a dest 1:
Archived Log entry 68108 added for thread 1 sequence 3828 ID 0x761a769a dest 1:
Clearing online redo logfile 1 /u01/app/oracle/oradata/ORACLE/redo01.log
Clearing online log 1 of thread 1 sequence number 3829
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/ORACLE/redo02.log
Clearing online log 2 of thread 1 sequence number 3830
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/ORACLE/redo03.log
Clearing online log 3 of thread 1 sequence number 3828
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 1981445786 (0x761a769a)
Online log /u01/app/oracle/oradata/ORACLE/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/ORACLE/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/ORACLE/redo03.log: Thread 1 Group 3 was previously cleared
Wed Nov 01 19:49:09 2023
Setting recovery target incarnation to 3
Wed Nov 01 19:49:09 2023
Assigning activation ID 2024817472 (0x78b04340)
LGWR: STARTING ARCH PROCESSES
Wed Nov 01 19:49:09 2023
ARC0 started with pid=21, OS id=2078 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Wed Nov 01 19:49:10 2023
ARC1 started with pid=22, OS id=2080 
Thread 1 advanced to log sequence 2 (thread open)
Wed Nov 01 19:49:10 2023
ARC2 started with pid=23, OS id=2082 
Wed Nov 01 19:49:10 2023
ARC3 started with pid=24, OS id=2084 
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ORACLE/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Nov 01 19:49:10 2023
SMON: enabling cache recovery
ARC2: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Wed Nov 01 19:49:10 2023
NSA2 started with pid=25, OS id=2086 
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.4781ef78):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file /u01/app/oracle/diag/rdbms/oracle_pd/ORACLE/trace/ORACLE_ora_2076.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11_467363169$" too small
Errors in file /u01/app/oracle/diag/rdbms/oracle_pd/ORACLE/trace/ORACLE_ora_2076.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11_467363169$" too small
Error 704 happened during db open, shutting down database
USER (ospid: 2076): terminating the instance due to error 704
Instance terminated by USER, pid = 2076
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (2076) as a result of ORA-1092

这种错误比较常见恢复简单,以前有过类似的文章:
在数据库open过程中常遇到ORA-01555汇总
数据库open过程遭遇ORA-1555对应sql语句补充
重建control遗漏数据文件,reseltogs报ORA-1555错误处理
Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障
这个错误恢复本身不难,只是在数据库的恢复过程中需要重建文件,在这次的重建文件中由于ctl语句错误【语句是通过sql查询拼接而成】,导致重建文件报了两个错误,供参考:
ORA-01163 ORA-01110错误

SQL> CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS  NOARCHIVELOG
        MAXLOGFILES 50
       MAXLOGMEMBERS 5
       MAXDATAFILES 1000
       MAXINSTANCES 8
       MAXLOGHISTORY 2920
LOGFILE
       group 1   '/u01/app/oracle/oradata/ORACLE/redo01.log' size 200M,
  2         group 3   '/u01/app/oracle/oradata/ORACLE/redo03.log' size 200M,
       group 2   '/u01/app/oracle/oradata/ORACLE/redo02.log' size 200M,
DATAFILE
        '/u01/app/oracle/oradata/ORACLE/system01.dbf',
        …………
        '/u01/app/oracle/oradata/ORACLE/xifenfei06.dbf',
CHARACTER SET  ZHS16GBK ;
 23   24   25   26   27  CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS	NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01163: SIZE clause indicates 12800 (blocks), but should match header
1564672
ORA-01110: data file 15: '/u01/app/oracle/oradata/ORACLE/xifenfei06.dbf'

出现该错误是由于我写路径的时候在最后一个数据文件路径后面多了一个【,】导致

ORA-600 kccccfl_1错误

SQL> CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS  NOARCHIVELOG
        MAXLOGFILES 50
       MAXLOGMEMBERS 5
       MAXDATAFILES 1000
       MAXINSTANCES 8
       MAXLOGHISTORY 2920
LOGFILE
       group 1   '/u01/app/oracle/oradata/ORACLE/redo01.log'  size 200M,
       group 3   '/u01/app/oracle/oradata/ORACLE/redo03.log' size 200M,
       group 2   '/u01/app/oracle/oradata/ORACLE/redo02.log' size 200M,
DATAFILE
        '/u01/app/oracle/oradata/ORACLE/system01.dbf',
        ………… 
        '/u01/app/oracle/oradata/ORACLE/xifenfei06.dbf'
CHARACTER SET  ZHS16GBK ;
 23   24   25   26   27  CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS	NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00600: internal error code, arguments: [kccccfl_1], [], [], [], [], [], [],
[], [], [], [], []

出现该错误是由于在最后redo文件路径后面多了【,】导致

发表在 Oracle备份恢复 | 标签为 , , | 评论关闭

RFS[22349]: Database mount ID mismatch

发表于 2023 年 11 月 6 日 由 惜分飞

今天检查数据库发现一套主库上面报大量RFS[22349]: Database mount ID mismatch之类的错误

[oracle@hisdb2 trace]$ tail -f alert_hisdb2.log 
LNS: Standby redo logfile selected for thread 2 sequence 133319 for destination LOG_ARCHIVE_DEST_2
Mon Nov 06 20:56:09 2023
RFS[27080]: Assigned to RFS process 15518
RFS[27080]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 20:57:09 2023
RFS[27081]: Assigned to RFS process 16299
RFS[27081]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 20:57:09 2023
RFS[27082]: Assigned to RFS process 16319
RFS[27082]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)

这种错误表示rfs进程在传输的时候检测到database mount id不匹配(也就是认为这个库不是主库该传输或者接受日志的),出现类似这样的错误,大概率是由于这个dg成员(主库和备库)中应该有不匹配的库,回想这个库近期的操作,突然想到对这个主库在虚拟化平台的备库做了一次克隆,然后吧克隆库激活作为测试库的操作.基于此种情况,先判断该库是否和上次克隆的库之间创建有会话连接

--主库
[oracle@hisdb2 trace]$ netstat -natp|grep 192.168.106.63
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 192.168.115.12:52785        192.168.106.63:1521         ESTABLISHED -                   
tcp        0      0 192.168.115.14:1521         192.168.106.63:21598        TIME_WAIT   - 

--克隆库
[oracle@HIS_DG ~]$ netstat -atpn|grep 192.168.115.12
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 ::ffff:192.168.106.63:1521  ::ffff:192.168.115.12:52785 ESTABLISHED 6126/oraclehisdb

两个库之间确实存在会话,检查克隆库相关的dg配置

[oracle@HIS_DG ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 6 21:01:05 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter fal;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      HISDBDG
fal_server                           string      HISDB
SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> show parameter archive;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     1800
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST valid_for=(all_logfiles,a
                                                 ll_roles) db_unique_name=hisdb
                                                 dg
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      service=hisdb lgwr async valid
                                                 _for=(online_logfiles,primary_
                                                 role) db_unique_name=hisdb

该库的dg配置没有清除,当该库变为主库时,log_archive_dest_2刚好指向主库,从而使得主库上出现了类似RFS[22349]: Database mount ID mismatch的错误.处理方法是清除掉克隆库上面dg备库相关配置

SQL> alter system set log_archive_dest_2='';

System altered.

SQL> alter system set fal_server='';

System altered.

主库日志中未再出现类似错误

Mon Nov 06 20:48:08 2023
RFS[22349]: Assigned to RFS process 7383
RFS[22349]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
[oracle@hisdb1 trace]$ tail -f alert_hisdb1.log 
RFS[22352]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 20:58:09 2023
RFS[22353]: Assigned to RFS process 14958
RFS[22353]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 21:01:09 2023
RFS[22354]: Assigned to RFS process 18580
RFS[22354]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 21:01:09 2023
RFS[22355]: Assigned to RFS process 18598
RFS[22355]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 21:03:28 2023
Thread 1 advanced to log sequence 129769 (LGWR switch)
  Current log# 9 seq# 129769 mem# 0: +DATA/hisdb/onlinelog/group_9.276.976991877
  Current log# 9 seq# 129769 mem# 1: +FRA/hisdb/onlinelog/group_9.2334.976991877
Mon Nov 06 21:03:28 2023
Archived Log entry 524082 added for thread 1 sequence 129768 ID 0x70864b41 dest 1:
Mon Nov 06 21:03:28 2023
LNS: Standby redo logfile selected for thread 1 sequence 129769 for destination LOG_ARCHIVE_DEST_2

事后在MOS上有一篇文档供参考:Database mount ID mismatch ORA-16009: invalid redo transport destination (Doc ID 1450132.1)

发表在 Data Guard | 评论关闭