ORACE 非归档模式redo文件恢复

最新推荐文章于 2024-04-30 11:00:00 发布
最新推荐文章于 2024-04-30 11:00:00 发布
阅读量802 收藏 1
点赞数
分类专栏： 备份与恢复 OCM Training 文章标签： oracle 数据库 备份与恢复
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhuke0203/article/details/71378616
版权

实验环境

  • 操作系统 Redhat5.4 x86
  • 数据库版本 oracle 11gR2 (11.2.0.1.0)
  • 数据库为未开启归档模式
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 7 22:12:48 2017

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         553651876 bytes
Database Buffers      276824064 bytes
Redo Buffers            5160960 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode          No Archive Mode
Automatic archival         Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Current log sequence           21

SQL> select LOG_MODE,OPEN_MODE,FORCE_LOGGING from v$database;

LOG_MODE     OPEN_MODE        FOR
------------ -------------------- ---
NOARCHIVELOG READ WRITE       NO

实验模拟

1.redo日志组中部分member文件丢失或损坏

redo日志组部分member文件丢失，无论当前日志组是inactive， active（非current，current）都不会造成数据丢失，恢复方法也相同，因此不做特别区分。
详细参见：http://blog.csdn.net/zhuke0203/article/details/71311993#t2

案例模拟

SQL> set line 180
SQL> col member for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
     1     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

6 rows selected.
SQL> select group#,sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     19          2 NO  INACTIVE
     2     20          2 NO  INACTIVE
     3     21          2 NO  CURRENT

SQL> !rm /u01/app/oracle/oradata/PROD1/disk1/redo01a.log

SQL> startup force;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         553651876 bytes
Database Buffers      276824064 bytes
Redo Buffers            5160960 bytes
Database mounted.
Database opened.

--数据库可以正常启动，alert日志会有相关报错信息：
Sun May 07 22:15:19 2017
db_recovery_file_dest_size of 2048 MB is 18.19% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_m000_8957.trc:
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD1/disk2/redo01b.log'
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_m000_8957.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/PROD1/disk1/redo01a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Checker run found 1 new persistent data failures

案例恢复

由于损坏的只是日志组中的部分member，因此可以将损坏的member file文件删除以后重新添加进来：

SQL> alter database drop logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log';
alter database drop logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD1/disk2/redo01b.log'


SQL> alter system switch logfile;

System altered.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     22          2 NO  ACTIVE
     2     23          2 NO  CURRENT
     3     21          2 NO  INACTIVE

SQL> alter database drop logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log';

Database altered.

SQL> alter database add logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log' reuse to group 1;

Database altered.

SQL> !ls -la /u01/app/oracle/oradata/PROD1/disk1/redo01a.log
-rw-r----- 1 oracle oinstall 104858112 May  7 22:17 /u01/app/oracle/oradata/PROD1/disk1/redo01a.log

这种情况数据库可以正常启动，不会造成数据丢失。

2.非active redo日志组中所有member文件都丢失或损坏

案例模拟

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1 INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
     1     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

6 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     22          2 NO  ACTIVE
     2     23          2 NO  CURRENT
     3     21          2 NO  INACTIVE

SQL> !rm /u01/app/oracle/oradata/PROD1/disk3/redo03a.log /u01/app/oracle/oradata/PROD1/disk4/redo03b.log

SQL> startup force;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         553651876 bytes
Database Buffers      276824064 bytes
Redo Buffers            5160960 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9392
Session ID: 1 Serial number: 5

案例恢复

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount; 
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         553651876 bytes
Database Buffers      276824064 bytes
Redo Buffers            5160960 bytes
Database mounted.

SQL> set line 180
SQL> col member for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1 INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
     1     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

6 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;    

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     22          2 NO  INACTIVE
     3     21          2 NO  INACTIVE
     2     23          2 NO  CURRENT

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

这种情况数据库无法正常启动，恢复不会造成数据丢失。

3.active redo日志组中所有member文件都丢失或损坏

ACTIVE redo日志组无论是否为current状态，恢复方法相同。

案例模拟

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
     1     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
     3 INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

6 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     25          2 NO  CURRENT
     2     23          2 NO  INACTIVE
     3     24          2 NO  ACTIVE

SQL> !rm /u01/app/oracle/oradata/PROD1/disk3/redo03a.log /u01/app/oracle/oradata/PROD1/disk4/redo03b.log

SQL> startup force;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         553651876 bytes
Database Buffers      276824064 bytes
Redo Buffers            5160960 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD1/disk3/redo03a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

案例恢复

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select * from v$logfile;  

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
     1     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
     3 INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

6 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
     1     25          2 NO  CURRENT
     3     24          2 NO  ACTIVE
     2     23          2 NO  INACTIVE

--尝试clear日志组：
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance PROD1 (thread 1)
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD1/disk3/redo03a.log'
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD1/disk4/redo03b.log'

--尝试recover database：
SQL> recover database using backup controlfile;
ORA-00279: change 577067 generated at 05/07/2017 22:55:46 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/PROD1/archivelog/2017_05_07/o1_mf_1_24_%u_.arc
ORA-00280: change 577067 for thread 1 is in sequence #24


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/PROD1/archivelog/2017_05_07/o1_mf_1_24_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

--尝试resetlogs:
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD1/disk1/system01.dbf'

--尝试设置隐藏参数，让数据库跳过一致性检查：
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         553651876 bytes
Database Buffers      276824064 bytes
Redo Buffers            5160960 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

--将隐藏参数重置回默认值：
SQL> alter system reset "_allow_resetlogs_corruption";

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         553651876 bytes
Database Buffers      276824064 bytes
Redo Buffers            5160960 bytes
Database mounted.
Database opened.
SQL> 
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> set line 180
SQL> col member for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
     1     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
     1     ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
     2     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
     3     ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

6 rows selected.

SQL> select group#, members, sequence#, archived, status from v$log;

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

--注：这种方法慎用，只有当其它方法都尝试了无效，作为最后一种尝试的时候使用，设置隐藏参数有可能造成意想不到的后果。

该情况下，数据库无法正常启动，会造成部分数据丢失。

kevin-ke
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle的错误日志,用Oracle查看报警日志错误的方法
weixin_35180056的博客
04-04 5963
这几日小编在在网上查了关于清除告警日志内容及建外部表的内容，但是查看报警日志错误的方法不是很多用户们了解，那么接下来我们就去看看用Oracle查看报警日志错误的方法。在网上查了几天的资料，尝试综合清除告警日志内容及建外部表的方式来解决这一问题。一：备份并清除告警日志内容将每天的告警日志备份好，然后进行清除。1:备份报警日志在$ORACLE_HOME/SID/bdump/ 目录下，按日期备份al...
ORA-00301
喝醉酒的小白
05-18 1247
问题1 oerr ora 01589 01589, 00000, "must use RESETLOGS or NORESETLOGS option for database open" // *Cause: Either incomplete or backup control file recovery has been // performed. After these types of recovery you must specify either // th
oracle ora报错问题与解决方法（ora-00301-ora-00400）
weixin_69200200的博客
06-11 1233
ora-00301-ora-00400 的报错信息及解决方法
MySQL中添加Redo日志文件的步骤详解
最新发布
Mortal3306的博客
04-30 312
Redo日志是MySQL中重要的事务日志，用于记录事务对数据库进行的修改操作，确保数据库的持久性和恢复能力。本文将详细介绍在MySQL中添加Redo日志文件的步骤，包括设置Redo日志文件大小、位置和数量等。
oracle 归档redo解析,Oracle 11g logminer解析redo日志
weixin_33331063的博客
04-11 614
下面的示例是分析在线redo日志，分析归档redo日志过程也是如此。Toad 里面也集成了logminer的功能。SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enter...
REDO日志损坏,非归档模式数据文件恢复
cuixi0781的博客
08-14 329
今天一同事项目9I数据库 运行在非归档模式下，应用前端提示ORA-1033: ORACLE 正在初始化或关闭过程中 。OPEN过程中出现ORA-16068: redo log file activ...
Networker备份恢复ORACE RAC数据库操作文档
12-01
2. **删除原有文件**：彻底删除原有的数据库文件和归档日志文件，这一步是为了避免旧文件干扰新的恢复过程。 3. **数据库nomount状态**：启动数据库至nomount状态，此时数据库并未加载控制文件。在此状态下，可以...
Orace10g安装文档(32bit)
10-17
该文档为32位系统下的Orace10g安装文档。内容详细，附带详细步骤解说及截图。
Orace与SQL常用函数对照文档
07-26
Orace与SQL常用函数对照，列举各种函数，包含用例，简洁而易上手！
ORACE教程文件
12-22
ORAEL教程文件:Tables Used in this Book
dbForge Studio for Orace 3.8.50 含破解补丁
12-14
dbForge Studio for Oracle 是 Devart 公司出品的专门用于管理 Oracle数据库的工具。 功能十分强大，集查询、管理、分析以及报表于一身，是十分值得一试的管理工具。 无需安装oracle Client 软件
Oracle 错误总结及问题解决 ORA
热门推荐
基于Oracle11G
07-08 18万+
ORA 错误大全
oracle 11g 使用logminer分析redo和归档日志
daf380的专栏
09-19 1893
一、设置日期格式（方便查看） SQL&gt; alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile; System altered. SQL&gt; SQL&gt; select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual ; TO_CHAR(S...
归档模式下redo丢失或损坏(inactive状态）
小伟的博客
12-29 845
 归档模式下redo丢失或损坏(inactive状态） 1）inactive  logfile损坏的情况 ++++Session 1 SQL> select * from v$Log; GROUP#   THREAD#  SEQUENCE#    BYTES  BLOCKSIZE   MEMBERS ARC STATUS   FIRST_CHANGE# FIRST_TIM NEX
ORA-27040: 文件创建错误, 无法创建文件
IT小生
09-25 3万+
一天犯了两次 标点符号的错误，悲剧   SQL 错误: ORA-01119: 创建数据库文件 ' F:\oracle\product\10.2.0\oradata\orcl\GIMSsS.dbf' 时出错 ORA-27040: 文件创建错误, 无法创建文件 OSD-04002: 无法打开文件 O/S-Error: (OS 123) 文件名、目录名或卷标语法不正确。 01119. 000
Oracle 数据库归档模式和非归档模式的区别以及设置
weixin_44519342的博客
01-12 1227
Oracle 数据库归档模式和非归档模式的区别以及设置
oracle设置不生成trc文件,产生大量.trc文件的处理方法
weixin_35387118的博客
04-02 1352
产生大量trc文件os：hp-ux 11.31oracle:10.2.0.4最近整了个测试库，环境如上。不过发现$oracle_base/admin/instance_name/bdump下面有很多trc文件，大概一分钟会产生一个文件，如下：-rw-r----- 1 oracle oinstall 512 May 25 13:40 test_ora_25809.trc-r...
oracle教程之oralce非归档模式下的恢复（一）
weixin_34123613的博客
05-27 439
案例1： 历史日志没有被覆盖（可以完全恢复）1）切换到非归档模式SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /disk1/arch/annyOldes...
RMAN备份与恢复系列之redo日志文件恢复
kevin-ke的专栏
05-07 3548
RMAN 备份与恢复系列之redo日志文件恢复
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值