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

非归档模式redo丢失情况处理

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

oracle数据库版本： 11.2.0.4.0

实验1： 非归档模式ACTIVE redo丢失。
实验2： 非归档模式CURRENT redo 丢失。
实验3： 非归档模式redo 多个member 丢失部分member.
实验4： 非归档模式INACTIVE redo丢失。

1 实验1： 非归档模式ACTIVE redo丢失。

1.1 启动数据库到mount状态，查询redo状态。

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

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

col member format a50;
select group#,member from v$logfile;
    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

1.2 删除2，3 组redo文件：

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

1.3 启动数据库报错

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

1.4 恢复数据库

SQL> recover database until cancel;
ORA-00279: change 931356 generated at 08/27/2018 09:28:39 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_32_%u_.arc
ORA-00280: change 931356 for thread 1 is in sequence #32


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_32_%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_32_%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'

1.5 设置参数，强制启动数据库

SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> create pfile='/tmp/1.txt' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@prod04 ~]$ vi /tmp/1.txt 
[oracle@prod04 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 27 09:50:03 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

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> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
 
select GROUP#,STATUS,ARCHIVED from v$log;

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

SQL> exit

2 实验2： 非归档模式CURRENT redo 丢失。

2.1 启动数据库到mount状态，查询redo状态。

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> select GROUP#,STATUS,ARCHIVED from v$log;

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

2.2 删除所有redo 日志文件

[oracle@prod04 orcl]$ 
[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 redo0*
[oracle@prod04 orcl]$ ls
control01.ctl  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@prod04 orcl]$

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

2.3 设置参数，强制启动数据库

SQL> shu immediate
ORA-01109: database not open
Database dismounted.
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;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;
ORA-00279: change 931818 generated at 08/27/2018 09:56:35 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_27/o1_mf_1_5_%u_.arc
ORA-00280: change 931818 for thread 1 is in sequence #5


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_5_%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_5_%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;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

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

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

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

3 实验3： 非归档模式redo 多个member 丢失部分member.

3.1 添加MEMBER

ALTER DATABASE ADD LOGFILE MEMBER '/u01/redo/redo0302' TO GROUP 3;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/redo/redo0202' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/redo/redo0102' TO GROUP 1;
SQL> col member format a50;
SQL> select group#,member from v$logfile;
  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

3.2 启动数据库到mount状态，删除部分member;

启动数据库到mount状态
SQL> shu abort
ORACLE instance shut down.
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> select GROUP#,STATUS,ARCHIVED from v$log;

    GROUP# STATUS        ARC
---------- ---------------- ---
     1 CURRENT        NO
     3 INACTIVE        NO
     2 INACTIVE        NO
删除部分member;
[oracle@prod04 redo]$ ls
redo0102  redo0202  redo0302
[oracle@prod04 redo]$ ls
redo0102  redo0202  redo0302
[oracle@prod04 redo]$ rm redo0*
[oracle@prod04 redo]$ ls
启动数据库：
SQL> alter database open;

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

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

SQL> select group#,member from v$logfile;

    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.

部分member删除，数据库运行正常，数据库运行日志有相关redo报错信息。

Thread 1 cannot allocate new log, sequence 9
Checkpoint not complete
  Current log# 2 seq# 8 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Mon Aug 27 10:21:13 2018
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_46443.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/redo/redo0302'
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_46443.trc:
ORA-00321: log 3 of thread 1, cannot update log file header
ORA-00312: online log 3 thread 1: '/u01/redo/redo0302'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_46443.trc:
ORA-00313: open failed for members of log group 3 of thread 1
Thread 1 advanced to log sequence 9 (LGWR switch)
  Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Thread 1 cannot allocate new log, sequence 10
Checkpoint not complete
  Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_46443.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/redo/redo0102'
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_46443.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: '/u01/redo/redo0102'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_46443.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Thread 1 advanced to log sequence 10 (LGWR switch)
  Current log# 1 seq# 10 mem# 0: /u01/app/oracle/oradata/orcl/redo01.log

3.3 恢复被删除的redo member:

恢复被删除的redo member:
SQL> select GROUP#,STATUS,ARCHIVED from v$log;

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

如果状态是active,进行数据库checkpoint，使redo状态变为INACTIVE。

SQL> alter system checkpoint;

System altered.

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

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

重建INACTIVE redo file:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Database altered.

重建CURRENT redo file:

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

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

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/redo/redo0102'

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

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

SQL> 
SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

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

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

SQL> 
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

实验4： 非归档模式INACTIVE redo丢失。

4.1 删除INACTIVE redo 启动数据库

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> select GROUP#,STATUS,ARCHIVED from v$log;

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

SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> ^Hal  
SP2-0042: unknown command al" - rest of line ignored.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 46706
Session ID: 96 Serial number: 3

错误日志：

alter database open
Mon Aug 27 10:33:02 2018
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_46686.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/redo/redo0202'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.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_46686.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/redo/redo0202'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.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_46706.trc:
ORA-00313: open failed for members of log group 1 of thread 
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/redo/redo0202'
USER (ospid: 46706): terminating the instance due to error 313
Mon Aug 27 10:33:03 2018
System state dump requested by (instance=1, osid=46706), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_46676_20180827103303.trc
Dumping diagnostic data in directory=[cdmp_20180827103303], requested by (instance=1, osid=46706), summary=[abnormal instance termination].
Instance terminated by USER, pid = 46706

4.2 恢复数据库方法：


[oracle@prod04 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 27 10:35:18 2018

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

Connected to an idle instance.

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 ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

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

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

5 备注： 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
文章标签：
SQL
关系型数据库
Oracle
数据库
NoSQL
snowofsummer
目录
相关文章
snowofsummer
|
SQL Oracle 关系型数据库
归档模式redo丢失
归档模式redo丢失
snowofsummer
1103 0
科技小能手
|
Oracle 关系型数据库 数据库
控制文件丢失恢复
科技小能手
1068 0
科技小能手
|
Oracle 关系型数据库 数据库
控制文件丢失的恢复（续）
科技小能手
1279 0
范大脚脚
|
SQL 监控 Go
事务日志，备份与恢复
范大脚脚
1527 0
技术小甜
|
监控 Oracle 关系型数据库
ORACLE基础学习-RMAN应用之（归档模式无备份，丢失数据文件的恢复）
技术小甜
1180 0
小麦苗
|
SQL 监控 Oracle
【恢复】Redo日志文件丢失的恢复
第一章 Redo日志文件丢失的恢复 1.1  online redolog file 丢失 联机Redo日志是Oracle数据库中比较核心的文件，当Redo日志文件异常之后，数据库就无法正常启动，而且有丢失据的风险，强烈建议在条件允许的情况下，对Redo日志进行多路镜像。
小麦苗
2173 0
eric0435
|
Oracle 关系型数据库 数据库
使用rman增量备份来更新传输表空间
eric0435
1554 0
jeanron100
|
SQL 数据库 关系型数据库
归档模式下四种完全恢复的场景
在数据的备份恢复中，基本都在使用rman来做了，但是从数据库的内部原理来说，对于介质恢复，其实还是做两件事，restore和recover. restore是一个类似物理文件的复制，而recover则在数据库后台根据scn做相关的数据恢复。
jeanron100
1019 0
lfreeali
[20150619]undo文件损坏或者丢失的恢复2
[20150619]undo文件损坏或者丢失的恢复2.txt --昨天别人问一些undo文件损坏或者丢失的恢复，如果不正常关机，undo文件丢失，恢复与正常关机存在不同。
lfreeali
872 0
lfreeali
|
关系型数据库 数据库 数据库管理
[20150619]undo文件损坏或者丢失的恢复3
[20150619]undo文件损坏或者丢失的恢复3.txt --实际上前面的测试是非常理想情况下的测试，真实的情况肯定比上面介绍的复杂。 --一般情况下，数据库异常关机，最容易出现的是在线redo损坏，一般通过隐含参数_allow_resetlogs_corruption跳过。
lfreeali
901 0