热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

oraclerevoveryora01110,OracleAdg备库ORA10458ORA01196ORA01110:ORA10458故障处理

一环境:主库:Oracle双节点RAC19.6C(Linux)备库:Oracle双节点RAC19.6C(Linux)二详细报错࿱

一 环境:

主库:Oracle 双节点RAC 19.6C(Linux)

备库:Oracle 双节点RAC 19.6C(Linux)

二 详细报错:

Errors with log +ORA19CB_ARCH/ORA19CB/ARCHIVELOG/2021_02_07/thread_1_seq_809.315.1063907805

2021-02-16T11:07:34.513934+08:00

Standby Crash Recovery aborted due to error 16016.

2021-02-16T11:07:34.621378+08:00

Errors in file /oracle/app/oracle/diag/rdbms/ora19cb/ora19cb1/trace/ora19cb1_ora_9831.trc:

ORA-16016: archived log for thread 2 sequence# 809 unavailable

2021-02-16T11:07:37.026592+08:00

Recovery interrupted!

Some recovered datafiles maybe left media fuzzy

Media recovery may continue but open resetlogs may fail

stopping change tracking

2021-02-16T11:07:38.154299+08:00

Completed Standby Crash Recovery.

2021-02-16T11:07:44.616632+08:00

Errors in file /oracle/app/oracle/diag/rdbms/ora19cb/ora19cb1/trace/ora19cb1_ora_9831.trc:

ORA-10458: standby database requires recovery

ORA-01196: file 1 is inconsistent due to a failed media recovery session

ORA-01110: data file 1: '+ORA19CB_DATA/ORA19CB/DATAFILE/system.262.1046531145'

ORA-10458 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:27996:2} */...

2021-02-16T11:07:47.129461+08:00

故障原因:

由于测试环境主机频繁重启,导致数据不一致(猜测)

处理方法:

分析原因后,找了好多处理思路,都是建议重新duplicate,但Oracle ADG 归档丢失后备库无法应用的场景下有一种处理方法,我们通过此思路继续一步一步处理,首先在备库查询当前的SCN 然后在主库基于SCN 进行备份数据和控制文件,在备库进行恢复,处理报错:

1)主库通过Broker 停止日志传输和日志应用:

edit database 'ora19cb' set STATE='APPLY-OFF' ;

edit database 'ora19c' set STATE='TRANSPORT-OFF' ;

2)备库确认SCN:

SELECT CURRENT_SCN FROM V$DATABASE;

select min(checkpoint_change#) from v$datafile_header

3)主库备份:

BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';

BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

4) 备库恢复:

RMAN> SHUTDOWN IMMEDIATE ;

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

RMAN> ALTER DATABASE MOUNT;

RMAN> RESTORE STANDBY CONTROLFILE FROM '/oracle/software/ForStandbyCTRL.bck';

Starting restore at 2021-02-20 16:04:15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=65 instance=ora19cb1 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

output file name=+ORA19CB_DATA/ORA19CB/CONTROLFILE/current.265.1046531113

Finished restore at 2021-02-20 16:04:17

RMAN> ALTER DATABASE MOUNT;

released channel: ORA_DISK_1

Statement processed

RMAN> list incarnation;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       ORA19C   1068822584       PARENT  1          2019-04-17 00:55:59

2       2       ORA19C   1068822584       CURRENT 1920977    2019-08-07 00:42:40

此时后台会有大量的文件rename 告警:

*****************************************

WARNING: The converted filename '+ORA19CB_DATA/ora19c/aaf3729430b47162e0531e38a8c01874/datafile/ts_swing.294.1046413467'

is an ASM fully qualified filename.

Changing the filename to '+ORA19CB_DATA/MUST_RENAME_THIS_DATAFILE_20.4294967295.4294967295'.

Please rename it accordingly.

*****************************************

2020-07-23T15:05:44.180520+08:00

*****************************************

WARNING: The converted filename '+ORA19CB_DATA/ora19c/datafile/system.258.1015634281'

is an ASM fully qualified filename.

Changing the filename to '+ORA19CB_DATA/MUST_RENAME_THIS_DATAFILE_1.4294967295.4294967295'.

Please rename it accordingly.

*****************************************

继续

RMAN> SWITCH DATABASE TO COPY;

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+ORA19CB_DATA/ORA19CB/DATAFILE/system.262.1046531145"

datafile 3 switched to datafile copy "+ORA19CB_DATA/ORA19CB/DATAFILE/sysaux.261.1046531261"

datafile 4 switched to datafile copy "+ORA19CB_DATA/ORA19CB/DATAFILE/undotbs1.260.1046531357"

datafile 5 switched to datafile copy "+ORA19CB_DATA/ORA19CB/8F8000E513704E63E0531E38A8C0B3E8/DATAFILE/system.259.1046531373"

datafile 6 switched to datafile copy "+ORA19CB_DATA/ORA19CB/8F8000E513704E63E0531E38A8C0B3E8/DATAFILE/sysaux.257.1046531389"

datafile 7 switched to datafile copy "+ORA19CB_DATA/ORA19CB/DATAFILE/users.271.1046531405"

datafile 8 switched to datafile copy "+ORA19CB_DATA/ORA19CB/8F8000E513704E63E0531E38A8C0B3E8/DATAFILE/undotbs1.270.1046531407"

datafile 9 switched to datafile copy "+ORA19CB_DATA/ORA19CB/DATAFILE/undotbs2.269.1046531417"

datafile 24 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F914FF6E0531E38A8C0072D/DATAFILE/system.284.1063557913"

datafile 25 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F914FF6E0531E38A8C0072D/DATAFILE/sysaux.263.1063557935"

datafile 26 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F914FF6E0531E38A8C0072D/DATAFILE/undotbs1.285.1063557951"

datafile 27 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F944FF6E0531E38A8C0072D/DATAFILE/system.286.1063557971"

datafile 28 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F944FF6E0531E38A8C0072D/DATAFILE/sysaux.272.1063557981"

datafile 29 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F944FF6E0531E38A8C0072D/DATAFILE/undotbs1.288.1063557993"

datafile 30 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F914FF6E0531E38A8C0072D/DATAFILE/undo_2.289.1063557997"

datafile 31 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F944FF6E0531E38A8C0072D/DATAFILE/undo_2.290.1063558003

如果有报错可以使用 switch datafile file# to copy;

5) 处理报错

数据库open 后台还有大量的 WARNING,主要是redo 的路径和temp。我通过adg 备库调好redo 的方法把redo 和temp 替换了:

SQL> alter database recover  managed  standby database cancel;

Database altered.

SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL>

SQL>

SQL>

SQL> alter database add logfile thread 1 group 2 ('+ORA19CB_DATA') size 209715200;

alter database add logfile thread 2 group 3 ('+ORA19CB_DATA') size 209715200;

alter database add logfile thread 2 group 4 ('+ORA19CB_DATA') size 209715200;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL> select name from v$tempfile;

NAME

----------------------------------------------------------------------------------------------------------------

+ORA19CB_DATA/ORA19CB/TEMPFILE/temp.304.1065025445

+ORA19CB_DATA/ORA19CB/8F8000E513704E63E0531E38A8C0B3E8/TEMPFILE/temp.305.1065025455

+ORA19CB_DATA/MUST_RENAME_THIS_TEMPFILE_3.4294967295.4294967295

+ORA19CB_DATA/MUST_RENAME_THIS_TEMPFILE_4.4294967295.4294967295

SQL>  alter tablespace temp add  tempfile '+ORA19CB_DATA' size 50M;

Tablespace altered.

SQL> alter tablespace temp drop tempfile 3;

Tablespace altered.

SQL> conn / as sysdba

Connected.

SQL> show pdbs;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                       READ ONLY  NO

3 PDBTS1                         READ ONLY  NO

4 PDBTS2                         READ ONLY  NO

SQL> alter session set container=&PDBNAME ;

Enter value for pdbname: PDBTS2

old   1: alter session set container=&PDBNAME

new   1: alter session set container=PDBTS2

Session altered.

SQL> alter tablespace temp add  tempfile '+ORA19CB_DATA' size 50M;

Tablespace altered.

SQL> alter tablespace temp drop tempfile 4;

Tablespace altered.

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

+ORA19CB_DATA/ORA19CB/B359E4EB6F944FF6E0531E38A8C0072D/TEMPFILE/temp.309.1065026937

6) 至此数据恢复完成,继续ADG 关系恢复:

备库:

SQL> alter system set standby_file_management=auto;

System altered.

主库:

edit database 'ora19cb' set STATE='APPLY-ON' ;

edit database 'ora19c' set STATE='TRANSPORT-ON' ;

oracle@ora19c1:[/home/oracle]$dgmgrl /

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Feb 20 16:15:14 2021

Version 19.7.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected to "ora19c"

Connected as SYSDG.

DGMGRL> edit database 'ora19cb' set STATE='APPLY-ON' ;

Succeeded.

DGMGRL> show database verbose ora19cb;

Database - ora19cb

Role:               PHYSICAL STANDBY

Intended State:     APPLY-ON

Transport Lag:      (unknown)

Apply Lag:          (unknown)

Average Apply Rate: (unknown)

Active Apply Rate:  (unknown)

Maximum Apply Rate: (unknown)

Real Time Query:    OFF

Instance(s):

ora19cb1 (apply instance)

ora19cb2

Properties:

DGConnectIdentifier             = 'yydbb_s'

ObserverConnectIdentifier       = ''

FastStartFailoverTarget         = ''

PreferredObserverHosts          = ''

LogShipping                     = 'ON'

RedoRoutes                      = ''

LogXptMode                      = 'ASYNC'

DelayMins                       = '0'

Binding                         = 'OPTIONAL'

MaxFailure                      = '0'

ReopenSecs                      = '300'

NetTimeout                      = '30'

RedoCompression                 = 'DISABLE'

PreferredApplyInstance          = ''

ApplyInstanceTimeout            = '0'

ApplyLagThreshold               = '30'

TransportLagThreshold           = '30'

TransportDisconnectedThreshold  = '30'

ApplyParallel                   = 'AUTO'

ApplyInstances                  = '0'

StandbyFileManagement           = ''

ArchiveLagTarget                = '0'

LogArchiveMaxProcesses          = '0'

LogArchiveMinSucceedDest        = '0'

DataGuardSyncLatency            = '0'

LogArchiveTrace                 = '0'

LogArchiveFormat                = ''

DbFileNameConvert               = ''

LogFileNameConvert              = ''

ArchiveLocation                 = ''

AlternateLocation               = ''

StandbyArchiveLocation          = ''

StandbyAlternateLocation        = ''

InconsistentProperties          = '(monitor)'

InconsistentLogXptProps         = '(monitor)'

LogXptStatus                    = '(monitor)'

SendQEntries                    = '(monitor)'

RecvQEntries                    = '(monitor)'

HostName(*)

StaticConnectIdentifier(*)

TopWaitEvents(*)

SidName(*)

(*) - Please check specific instance for the property value

Log file locations(*):

(*) - Check specific instance for log file locations.

Database Status:

SUCCESS

DGMGRL>  show database verbose ora19c;

Database - ora19c

Role:               PRIMARY

Intended State:     TRANSPORT-OFF

Instance(s):

ora19c1

Error: ORA-16739: redo transport service for member "ora19cb" is running

ora19c2

Error: ORA-16739: redo transport service for member "ora19cb" is running

Properties:

DGConnectIdentifier             = 'yydb_p'

ObserverConnectIdentifier       = ''

FastStartFailoverTarget         = ''

PreferredObserverHosts          = ''

LogShipping                     = 'ON'

RedoRoutes                      = ''

LogXptMode                      = 'ASYNC'

DelayMins                       = '0'

Binding                         = 'optional'

MaxFailure                      = '0'

ReopenSecs                      = '300'

NetTimeout                      = '30'

RedoCompression                 = 'DISABLE'

PreferredApplyInstance          = ''

ApplyInstanceTimeout            = '0'

ApplyLagThreshold               = '30'

TransportLagThreshold           = '30'

TransportDisconnectedThreshold  = '30'

ApplyParallel                   = 'AUTO'

ApplyInstances                  = '0'

StandbyFileManagement           = ''

ArchiveLagTarget                = '0'

LogArchiveMaxProcesses          = '0'

LogArchiveMinSucceedDest        = '0'

DataGuardSyncLatency            = '0'

LogArchiveTrace                 = '0'

LogArchiveFormat                = ''

DbFileNameConvert               = ''

LogFileNameConvert              = ''

ArchiveLocation                 = ''

AlternateLocation               = ''

StandbyArchiveLocation          = ''

StandbyAlternateLocation        = ''

InconsistentProperties          = '(monitor)'

InconsistentLogXptProps         = '(monitor)'

LogXptStatus                    = '(monitor)'

SendQEntries                    = '(monitor)'

RecvQEntries                    = '(monitor)'

HostName(*)

StaticConnectIdentifier(*)

TopWaitEvents(*)

SidName(*)

(*) - Please check specific instance for the property value

Log file locations(*):

(*) - Check specific instance for log file locations.

Database Status:

ERROR

DGMGRL> edit database 'ora19c' set STATE='TRANSPORT-ON' ;

Succeeded.

DGMGRL> show database verbose ora19c;

Database - ora19c

Role:               PRIMARY

Intended State:     TRANSPORT-ON

Instance(s):

ora19c1

ora19c2

Properties:

DGConnectIdentifier             = 'yydb_p'

ObserverConnectIdentifier       = ''

FastStartFailoverTarget         = ''

PreferredObserverHosts          = ''

LogShipping                     = 'ON'

RedoRoutes                      = ''

LogXptMode                      = 'ASYNC'

DelayMins                       = '0'

Binding                         = 'optional'

MaxFailure                      = '0'

ReopenSecs                      = '300'

NetTimeout                      = '30'

RedoCompression                 = 'DISABLE'

PreferredApplyInstance          = ''

ApplyInstanceTimeout            = '0'

ApplyLagThreshold               = '30'

TransportLagThreshold           = '30'

TransportDisconnectedThreshold  = '30'

ApplyParallel                   = 'AUTO'

ApplyInstances                  = '0'

StandbyFileManagement           = ''

ArchiveLagTarget                = '0'

LogArchiveMaxProcesses          = '0'

LogArchiveMinSucceedDest        = '0'

DataGuardSyncLatency            = '0'

LogArchiveTrace                 = '0'

LogArchiveFormat                = ''

DbFileNameConvert               = ''

LogFileNameConvert              = ''

ArchiveLocation                 = ''

AlternateLocation               = ''

StandbyArchiveLocation          = ''

StandbyAlternateLocation        = ''

InconsistentProperties          = '(monitor)'

InconsistentLogXptProps         = '(monitor)'

LogXptStatus                    = '(monitor)'

SendQEntries                    = '(monitor)'

RecvQEntries                    = '(monitor)'

HostName(*)

StaticConnectIdentifier(*)

TopWaitEvents(*)

SidName(*)

(*) - Please check specific instance for the property value

Log file locations(*):

(*) - Check specific instance for log file locations.

Database Status:

SUCCESS

7) 重启备库集群,查看数据库正常传输应用,至此问题解决!



推荐阅读
  • 云函数与数据库API实现增删查改的对比
    本文将深入探讨使用云函数和数据库API实现数据操作(增删查改)的不同方法,通过详细的代码示例帮助读者更好地理解和掌握这些技术。文章不仅提供代码实现,还解释了每种方法的特点和适用场景。 ... [详细]
  • 本文详细探讨了JDBC(Java数据库连接)的内部机制,重点分析其作为服务提供者接口(SPI)框架的应用。通过类图和代码示例,展示了JDBC如何注册驱动程序、建立数据库连接以及执行SQL查询的过程。 ... [详细]
  • 本文探讨了Hive中内部表和外部表的区别及其在HDFS上的路径映射,详细解释了两者的创建、加载及删除操作,并提供了查看表详细信息的方法。通过对比这两种表类型,帮助读者理解如何更好地管理和保护数据。 ... [详细]
  • 本文详细介绍了如何在Linux系统上安装和配置Smokeping,以实现对网络链路质量的实时监控。通过详细的步骤和必要的依赖包安装,确保用户能够顺利完成部署并优化其网络性能监控。 ... [详细]
  • DNN Community 和 Professional 版本的主要差异
    本文详细解析了 DotNetNuke (DNN) 的两种主要版本:Community 和 Professional。通过对比两者的功能和附加组件,帮助用户选择最适合其需求的版本。 ... [详细]
  • MySQL 数据库迁移指南:从本地到远程及磁盘间迁移
    本文详细介绍了如何在不同场景下进行 MySQL 数据库的迁移,包括从一个硬盘迁移到另一个硬盘、从一台计算机迁移到另一台计算机,以及解决迁移过程中可能遇到的问题。 ... [详细]
  • 深入解析 Apache Shiro 安全框架架构
    本文详细介绍了 Apache Shiro,一个强大且灵活的开源安全框架。Shiro 专注于简化身份验证、授权、会话管理和加密等复杂的安全操作,使开发者能够更轻松地保护应用程序。其核心目标是提供易于使用和理解的API,同时确保高度的安全性和灵活性。 ... [详细]
  • 通过Web界面管理Linux日志的解决方案
    本指南介绍了一种利用rsyslog、MariaDB和LogAnalyzer搭建集中式日志管理平台的方法,使用户可以通过Web界面查看和分析Linux系统的日志记录。此方案不仅适用于服务器环境,还提供了详细的步骤来确保系统的稳定性和安全性。 ... [详细]
  • 在网站制作中随时可用的10个 HTML5 代码片段
    HTML很容易写,但创建网页时,您经常需要重复做同样的任务,如创建表单。在这篇文章中,我收集了10个超有用的HTML代码片段,有HTML5启动模板、空白图片、打电话和发短信、自动完 ... [详细]
  • 在创建新的Android项目时,您可能会遇到aapt错误,提示无法打开libstdc++.so.6共享对象文件。本文将探讨该问题的原因及解决方案。 ... [详细]
  • 本文详细解析了Java中hashCode()和equals()方法的实现原理及其在哈希表结构中的应用,探讨了两者之间的关系及其实现时需要注意的问题。 ... [详细]
  • 中科院学位论文排版指南
    随着毕业季的到来,许多即将毕业的学生开始撰写学位论文。本文介绍了使用LaTeX排版学位论文的方法,特别是针对中国科学院大学研究生学位论文撰写规范指导意见的最新要求。LaTeX以其精确的控制和美观的排版效果成为许多学者的首选。 ... [详细]
  • 本文详细介绍了在腾讯云服务器上配置 phpMyAdmin 的方法,包括安装、配置和解决常见问题。通过这些步骤,您可以轻松地在腾讯云环境中部署并使用 phpMyAdmin。 ... [详细]
  • 目录一、salt-job管理#job存放数据目录#缓存时间设置#Others二、returns模块配置job数据入库#配置returns返回值信息#mysql安全设置#创建模块相关 ... [详细]
  • 本文详细介绍了在使用 SmartUpload 组件进行文件上传时,如何正确配置和查找文件保存路径。通过具体的代码示例和步骤说明,帮助开发者快速解决上传路径配置的问题。 ... [详细]
author-avatar
阳光-沙滩男人
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有