备案控制台
探索云世界
开发者社区 数据库 文章 正文

归档模式redo丢失

2018-08-27 1105
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 归档模式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
1352 0
科技小能手
|
Oracle 关系型数据库 数据库
控制文件丢失恢复
科技小能手
1068 0
范大脚脚
|
SQL 监控 Go
事务日志，备份与恢复
范大脚脚
1530 0
科技小能手
|
Oracle 关系型数据库 数据库
控制文件丢失的恢复（续）
科技小能手
1283 0
技术小胖子
|
SQL Oracle 关系型数据库
更改重做日志（redolog）文件的大小
技术小胖子
1073 0
小麦苗
|
SQL 监控 Oracle
【恢复】Redo日志文件丢失的恢复
第一章 Redo日志文件丢失的恢复 1.1  online redolog file 丢失 联机Redo日志是Oracle数据库中比较核心的文件，当Redo日志文件异常之后，数据库就无法正常启动，而且有丢失据的风险，强烈建议在条件允许的情况下，对Redo日志进行多路镜像。
小麦苗
2175 0
小麦苗
|
SQL 监控 关系型数据库
【故障处理】DG环境主库丢失归档情况下数据文件的恢复
【故障处理】DG环境主库丢失归档情况下数据文件的恢复 1  BLOG文档结构图     2  前言部分   2.
小麦苗
1320 0
jeanron100
|
SQL 监控 Oracle
备库跳归档恢复的有趣案例
    在Data Guard环境中，主备库基本都是使用归档来传递数据的变化。如果主备的归档传输中断，同时主库的归档被删除或者损坏，这种情况下备库是没法开始继续接收归档，应用新的数据变更了。
jeanron100
898 0
jeanron100
|
SQL 数据库 关系型数据库
归档模式下四种完全恢复的场景
在数据的备份恢复中，基本都在使用rman来做了，但是从数据库的内部原理来说，对于介质恢复，其实还是做两件事，restore和recover. restore是一个类似物理文件的复制，而recover则在数据库后台根据scn做相关的数据恢复。
jeanron100
1019 0
lfreeali
[20150619]undo文件损坏或者丢失的恢复2
[20150619]undo文件损坏或者丢失的恢复2.txt --昨天别人问一些undo文件损坏或者丢失的恢复，如果不正常关机，undo文件丢失，恢复与正常关机存在不同。
lfreeali
873 0