AI 助理
备案控制台
开发者社区 数据库 文章 正文

归档模式redo丢失

2018-08-27 1134
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 归档模式redo丢失

1，redo member 丢失。
2，ACTIVE redo 丢失。
3，INACTIVE redo 丢失。
4，CURRENT redo 丢失。

数据库版本：
SQL*Plus: Release 11.2.0.4.0

1，部分redo member 丢失。

SQL> col member format a50;
select group#,member from v$logfile;
SQL> 
    GROUP# MEMBER
---------- --------------------------------------------------
     3 /u01/app/oracle/oradata/orcl/redo03.log
     2 /u01/app/oracle/oradata/orcl/redo02.log
     1 /u01/app/oracle/oradata/orcl/redo01.log
     3 /u01/redo/redo0302
     2 /u01/redo/redo0202
     1 /u01/redo/redo0102

6 rows selected.

SQL> 
SQL> select GROUP#,STATUS,ARCHIVED from v$log;

    GROUP# STATUS        ARC
---------- ---------------- ---
     1 ACTIVE        YES
     3 CURRENT        NO
     2 ACTIVE        YES

关闭数据库 删除部分redo member：

[root@prod04 redo]# ls
redo0102  redo0202  redo0302
[root@prod04 redo]# pwd
/u01/redo
[root@prod04 redo]# rm redo0*
rm: remove regular file ?.edo0102?. y
rm: remove regular file ?.edo0202?. y
rm: remove regular file ?.edo0302?. y
[root@prod04 redo]# ls

启动数据库：

SQL> alter database open;

Database altered.

SQL> select GROUP#,STATUS,ARCHIVED from v$log;

    GROUP# STATUS        ARC
---------- ---------------- ---
     1 CURRENT        NO
     2 INACTIVE        YES
     3 INACTIVE        YES

结论： 部分redo member 丢失不影响数据库使用。

故障处理方法：

SQL> select GROUP#,STATUS,ARCHIVED from v$log;

    GROUP# STATUS        ARC
---------- ---------------- ---
     1 INACTIVE        YES
     2 CURRENT        NO
     3 INACTIVE        YES

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select GROUP#,STATUS,ARCHIVED from v$log;

    GROUP# STATUS        ARC
---------- ---------------- ---
     1 CURRENT        NO
     2 ACTIVE        YES
     3 UNUSED        YES

SQL> alter system checkpoint;

System altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Database altered.

2，部分ACTIVE redo 丢失。

启动数据库 mount状态：

SQL> startup mount
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size            2255712 bytes
Variable Size          637535392 bytes
Database Buffers     1795162112 bytes
Redo Buffers           20275200 bytes
Database mounted.

SQL> @1

    GROUP# MEMBER
---------- --------------------------------------------------
     3 /u01/app/oracle/oradata/orcl/redo03.log
     2 /u01/app/oracle/oradata/orcl/redo02.log
     1 /u01/app/oracle/oradata/orcl/redo01.log


    GROUP# STATUS        ARC
---------- ---------------- ---
     1 ACTIVE        YES
     3 CURRENT        NO
     2 ACTIVE        YES

1，2 redo logfile 丢失：

[oracle@prod04 orcl]$ ls
control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
redo01.log     redo03.log  system01.dbf  undotbs01.dbf
[oracle@prod04 orcl]$ rm redo01.log redo02.log 
[oracle@prod04 orcl]$ ls
control01.ctl  sysaux01.dbf  temp01.dbf     users01.dbf
redo03.log     system01.dbf  undotbs01.dbf

open数据库：

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

故障处理：
检查redo状态：ARCHIVED=yes 表示已经归档，可以使用rman恢复数据。

    GROUP# STATUS        ARC
---------- ---------------- ---
     1 ACTIVE        YES
     3 ACTIVE        YES
     2 CURRENT        NO

恢复数据库：
recover database until cancel;
SQL> recover database until cancel;
ORA-00279: change 1036374 generated at 08/27/2018 11:29:01 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_5_fr6vzxkh
_.arc
ORA-00280: change 1036374 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1036377 generated at 08/27/2018 11:29:01 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_6_fr6vzy95
_.arc
ORA-00280: change 1036377 for thread 1 is in sequence #6
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_5_fr6vzxk
h_.arc' no longer needed for this recovery
ORA-00279: change 1036381 generated at 08/27/2018 11:29:02 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_7_%u_.arc
ORA-00280: change 1036381 for thread 1 is in sequence #7
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_6_fr6vzy9
5_.arc' no longer needed for this recovery
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_7_%u_.arc
'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

#手动指定currnt redo 文件位置，完成日志恢复。
SQL> recover database until cancel;
ORA-00279: change 1036381 generated at 08/27/2018 11:29:02 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_7_%u_.arc
ORA-00280: change 1036381 for thread 1 is in sequence #7

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;
Database altered.
SQL> 1

    GROUP# MEMBER
---------- --------------------------------------------------
     3 /u01/app/oracle/oradata/orcl/redo03.log
     2 /u01/app/oracle/oradata/orcl/redo02.log
     1 /u01/app/oracle/oradata/orcl/redo01.log

    GROUP# STATUS        ARC
---------- ---------------- ---
     1 CURRENT        NO
     2 UNUSED        YES
     3 UNUSED        YES

3，INACTIVE redo 丢失。

启动数据库mount状态：

SQL> startup mount
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size            2255712 bytes
Variable Size          637535392 bytes
Database Buffers     1795162112 bytes
Redo Buffers           20275200 bytes
Database mounted.
SQL> @1

    GROUP# MEMBER
---------- --------------------------------------------------
     3 /u01/app/oracle/oradata/orcl/redo03.log
     2 /u01/app/oracle/oradata/orcl/redo02.log
     1 /u01/app/oracle/oradata/orcl/redo01.log


    GROUP# STATUS        ARC
---------- ---------------- ---
     1 INACTIVE        YES
     3 INACTIVE        YES
     2 CURRENT        NO

删除1，3 INACTIVE redo file:

[oracle@prod04 orcl]$ ls
control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
redo01.log     redo03.log  system01.dbf  undotbs01.dbf
[oracle@prod04 orcl]$ rm redo01.log redo03.log 
[oracle@prod04 orcl]$ ls
control01.ctl  sysaux01.dbf  temp01.dbf     users01.dbf
redo02.log     system01.dbf  undotbs01.dbf

启动数据库：

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 48461
Session ID: 96 Serial number: 3

错误日志：

alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 5, block 2, scn 1036734
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 5, block 2, scn 1056735
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Mon Aug 27 11:45:23 2018
LGWR: STARTING ARCH PROCESSES
Mon Aug 27 11:45:23 2018
ARC0 started with pid=27, OS id=48503 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_48441.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_48441.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_48461.trc:
ORA-00313: open failed for members of log group 1 of thread 
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
Mon Aug 27 11:45:24 2018
ARC1 started with pid=28, OS id=48505 
USER (ospid: 48461): terminating the instance due to error 313
Mon Aug 27 11:45:24 2018
System state dump requested by (instance=1, osid=48461), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_48431_20180827114524.trc
Dumping diagnostic data in directory=[cdmp_20180827114524], requested by (instance=1, osid=48461), summary=[abnormal instance termination].
Instance terminated by USER, pid = 48461

故障恢复：

recover database until cancel;
SQL> startup mount
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size            2255712 bytes
Variable Size          637535392 bytes
Database Buffers     1795162112 bytes
Redo Buffers           20275200 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

4，CURRENT redo 丢失。

启动数据库 mount状态：

SQL> startup mount
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size            2255712 bytes
Variable Size          637535392 bytes
Database Buffers     1795162112 bytes
Redo Buffers           20275200 bytes
Database mounted.
SQL> @1

    GROUP# MEMBER
---------- --------------------------------------------------
     3 /u01/app/oracle/oradata/orcl/redo03.log
     2 /u01/app/oracle/oradata/orcl/redo02.log
     1 /u01/app/oracle/oradata/orcl/redo01.log


    GROUP# STATUS        ARC
---------- ---------------- ---
     1 CURRENT        NO
     3 INACTIVE        YES
     2 INACTIVE        YES

删除1 redo logfile:

[oracle@prod04 orcl]$ rm redo01.log 
[oracle@prod04 orcl]$ ls
control01.ctl  redo03.log    system01.dbf  undotbs01.dbf
redo02.log     sysaux01.dbf  temp01.dbf    users01.dbf

启动数据库：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> recover database until cancel;
ORA-00279: change 1057068 generated at 08/27/2018 11:48:49 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_4_%u_.arc
ORA-00280: change 1057068 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_4_%u_.arc
'
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/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_4_%u_.arc
'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

故障恢复：
使用如下参数强制打开数据库：

*._allow_resetlogs_corruption=true

*._allow_error_simulation=true

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount pfile='/tmp/1.txt';
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size            2255712 bytes
Variable Size          637535392 bytes
Database Buffers     1795162112 bytes
Redo Buffers           20275200 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> @1

    GROUP# MEMBER
---------- --------------------------------------------------
     3 /u01/app/oracle/oradata/orcl/redo03.log
     2 /u01/app/oracle/oradata/orcl/redo02.log
     1 /u01/app/oracle/oradata/orcl/redo01.log


    GROUP# STATUS        ARC
---------- ---------------- ---
     1 CURRENT        NO
     2 UNUSED        YES
     3 UNUSED        YES

参数文件：

pfile添加如下参数：
*._allow_resetlogs_corruption=true

*._allow_error_simulation=true

more /tmp/1.txt

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=817889280
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2453667840
*.undo_tablespace='UNDOTBS1'
*._allow_resetlogs_corruption=true
*._allow_error_simulation=true
文章标签：
关系型数据库
Oracle
SQL
数据库
运维
网络协议
snowofsummer
目录
相关文章
snowofsummer
|
SQL Oracle NoSQL
非归档模式redo丢失情况处理
实验1： 非归档模式ACTIVE redo丢失。实验2： 非归档模式CURRENT redo 丢失。实验3： 非归档模式redo 多个member 丢失部分member.实验4： 非归档模式INACTIVE redo丢失。
snowofsummer
1412 0
科技小能手
|
Oracle 关系型数据库 数据库
控制文件丢失恢复
科技小能手
1098 0
科技小能手
|
Oracle 关系型数据库 数据库
控制文件丢失的恢复（续）
科技小能手
1340 0
范大脚脚
|
SQL 监控 Go
事务日志，备份与恢复
范大脚脚
1609 0
小麦苗
|
SQL 监控 Oracle
【恢复】Redo日志文件丢失的恢复
第一章 Redo日志文件丢失的恢复 1.1  online redolog file 丢失 联机Redo日志是Oracle数据库中比较核心的文件，当Redo日志文件异常之后，数据库就无法正常启动，而且有丢失据的风险，强烈建议在条件允许的情况下，对Redo日志进行多路镜像。
小麦苗
2231 0
明俨
|
NoSQL 调度 索引
Mongorestore的archive(归档)模式恢复原理解析
在上篇Mongodump的archive（归档）模式原理解析中介绍过，Mongodump的archive（归档）模式产生的文件是将多个集合的数据通过一个Multiplexer多路复用混合在一起，因此对应在恢复的时候就需要有一个Demultiplexer来将数据进行解析，是一个多路复用的逆过程。对应.
明俨
5703 0
小麦苗
|
SQL 监控 关系型数据库
【故障处理】DG环境主库丢失归档情况下数据文件的恢复
【故障处理】DG环境主库丢失归档情况下数据文件的恢复 1  BLOG文档结构图     2  前言部分   2.
小麦苗
1358 0
jeanron100
|
SQL 监控 Oracle
备库跳归档恢复的有趣案例
    在Data Guard环境中，主备库基本都是使用归档来传递数据的变化。如果主备的归档传输中断，同时主库的归档被删除或者损坏，这种情况下备库是没法开始继续接收归档，应用新的数据变更了。
jeanron100
921 0
小麦苗
|
监控 关系型数据库 数据库
ORA-01555 快照太旧、Undo表空间、一致性读、延时块清除
  ORA-01555 快照太旧、Undo表空间、一致性读、延时块清除 回滚与撤销 回滚与撤销： 为了保证数据库中多个用户间的读一致性和能够回退事务，Oracle必须拥有一种机制，能够为变更的数据构造一种前镜像（before image）数据（保存修改之前的旧值），以保证那够回滚或撤销对数据库所作的修改，同时为数据恢复以及一致性读服务。
小麦苗
1073 0
jeanron100
|
SQL 数据库 关系型数据库
归档模式下四种完全恢复的场景
在数据的备份恢复中，基本都在使用rman来做了，但是从数据库的内部原理来说，对于介质恢复，其实还是做两件事，restore和recover. restore是一个类似物理文件的复制，而recover则在数据库后台根据scn做相关的数据恢复。
jeanron100
1093 0

热门文章

最新文章

  • 1
    为什么说流处理即未来？
  • 2
    【实战】锐捷AC+AP配置WLAN基本服务系列
  • 3
    Mac：安装Python3并配置环境变量（本地多个Python版本选择配置）
  • 4
    丰富、连接、待集成—MaxCompute 生态再出发
  • 5
    Tomcat 7.0 64位免安装解压版 安装及配置
  • 6
    securecrt克隆会话与sshd 的 MaxSessions
  • 7
    C语言结构体的使用！简易学生成绩统计程序发布！
  • 8
    《VMware Virtual SAN权威指南》一2.2　VSAN的要求
  • 9
    webstorm9.0.3 注册码
  • 10
    数据文件offline后unusable索引造成的问题
  • 1
    LangGraph：构建多代理动态工作流的开源框架，支持人工干预、循环、持久性等复杂工作流自动化
    30
  • 2
    Eliza：TypeScript 版开源 AI Agent 开发框架，快速搭建智能、个性的 Agents 系统
    28
  • 3
    AI Dev Gallery：微软开源 Windows AI 模型本地运行工具包和示例库，助理开发者快速集成 AI 功能
    34
  • 4
    「Mac畅玩鸿蒙与硬件51」UI互动应用篇28 - 模拟记账应用
    102
  • 5
    我的博客年度之旅：感恩、成长与展望
    88
  • 6
    《构建高效K近邻算法：降低计算复杂度的策略与实践》
    25
  • 7
    《提升支持向量机泛化能力：核函数改进策略大揭秘》
    17
  • 8
    《智能算法设计与开发的全解析：从理念到实践》
    21
  • 9
    《深度学习梯度消失问题：原因与解决之道》
    24
  • 10
    《docker基础篇：8.Docker常规安装简介》包括：docker常规安装总体步骤、安装tomcat、安装mysql、安装redis
    25

    • 相关电子书

    更多
  • 俞航翔｜基于Log的通用增量Checkpoint
  • 低代码开发师（初级）实战教程
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • 快照备份与恢复
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月