Oracle redo log 删除后处理方法

最新推荐文章于 2025-02-26 17:05:13 发布

JasonLiu_2012

最新推荐文章于 2025-02-26 17:05:13 发布

阅读量6.2k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/JasonLiu_2012/article/details/8701413

Oracle 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了在Oracle数据库中redo log意外删除后的处理方法，包括shutdown immediate时的数据安全性和数据库open时的不同处理方案，如非当前日志和当前日志被删除的应对策略，以及实例可用时的数据导出解决方案。

在生产中，难免碰到各种场景。下面记录一下redo log被删除掉的处理方法。

数据库版本：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

一、shutdow immediate情况下被删除redo

这种情况下最容易处理，数据完全不会丢失。

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1887350784 bytes
Fixed Size                  2229464 bytes
Variable Size            1207962408 bytes
Database Buffers          671088640 bytes
Redo Buffers                6070272 bytes
Database mounted.

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

open的时候需要resetlogs，open之后，立刻做一次全库备份。

二、数据库open的情况下被删除redo

打开情况下分两种情况，即当前日志被删除和非当前日志被删除。

1、非当前日志被删除

SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME      NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------- ------------ ---------------
         1          1          1   52428800        512          1 YES INACTIVE              10452067 21-MAR-13           10454522 21-MAR-13
         2          1          2   52428800        512          1 YES INACTIVE              10454522 21-MAR-13           10455156 21-MAR-13
         3          1          3   52428800        512          1 NO  CURRENT               10455156 21-MAR-13         2.8147E+14

SQL> alter database clear logfile group 2;

Database altered.

SQL> shutdown immediate

SQL> startup
ORACLE instance started.

Total System Global Area 1887350784 bytes
Fixed Size                  2229464 bytes
Variable Size             452987688 bytes
Database Buffers         1426063360 bytes
Redo Buffers                6070272 bytes
Database mounted.
Database opened.

上例中删除掉的redo是group2，是已经归档了，并且status为INACTIVE，才能clear成功。

如果是还没有归档，语句则不一样：

SQL> alter database clear unarchived logfile group 2;

记得打开数据库后，马上做一次全库备份。

2、当前日志被删除

分实例可用和实例不可用两种情况

①数据库实例可用，直接用expdp导出数据

SQL> conn liujie/oracle
Connected.

SQL>  insert into t6 select * from t6;

1209264 rows created.

SQL> select count(*) from t6;

  COUNT(*)
----------
   2418528
SQL> commit;

Commit complete.

打开另一个终端，
SQL> conn / as sysdba;
Connected.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME      NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------- ------------ ---------------
         1          1          7   52428800        512          1 NO CURRENT               10513263 22-MAR-13         2.8147E+14
         2          1          5   52428800        512          1 YES INACTIVE              10510895 22-MAR-13           10511610 22-MAR-13
         3          1          6   52428800        512          1 YES INACTIVE              10511610 22-MAR-13           10513263 22-MAR-13

SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         3         ONLINE /data/oracle/oradata/uldw/redo03.log     NO
         2         ONLINE /data/oracle/oradata/uldw/redo02.log     NO
         1         ONLINE /data/oracle/oradata/uldw/redo01.log     NO

删除掉所有日志

oracle@wls113:~>rm /data/oracle/oradata/uldw/redo01.log
oracle@wls113:~>rm /data/oracle/oradata/uldw/redo02.log

oracle@wls113:~>rm /data/oracle/oradata/uldw/redo03.log

此时在数据库里再次插入数据

SQL> insert into t4 select * from t4;

75579 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t4;

  COUNT(*)
----------
    151158

此时如果实例可用，千万不要shutdown数据库，而是直接用expdp导出数据

oracle@wls113:/data/oracle/dump> expdp system directory=dump_dir dumpfile=liujie02.dmp schemas=liujie;

Export: Release 11.2.0.3.0 - Production on Fri Mar 22 09:57:07 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=dump_dir dumpfile=liujie02.dmp schemas=liujie 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 676.9 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
. . exported "LIUJIE"."T7"                               317.8 MB 3378528 rows
. . exported "LIUJIE"."T6"                               233.9 MB 2418528 rows
. . exported "LIUJIE"."T4"                               14.62 MB  151158 rows
. . exported "LIUJIE"."T1"                               7.315 MB   75579 rows
. . exported "LIUJIE"."T5"                               7.315 MB   75579 rows
. . exported "LIUJIE"."T3"                               99.04 KB    1000 rows
. . exported "LIUJIE"."MV1"                              6.445 KB       7 rows
. . exported "LIUJIE"."TEST1"                            5.062 KB       3 rows
. . exported "LIUJIE"."T2"                                   0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /data/oracle/dump/liujie02.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:57:56

此时只要是commit的数据，在实例可用的情况下，都可以用expdp导出来。

这个时候数据库已经损坏掉，需要关闭数据库，然后重建数据库，把dump文件导入即可恢复数据。

②数据库实例不可用

SQL> conn liujie/oracle
Connected.
SQL> insert into t6 select * from t6;

604632 rows created.

SQL> select count(*) from t6;

  COUNT(*)
----------
   1209264
//未提交

打开另一个终端，
SQL> conn / as sysdba;
Connected.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /data/oracle/oradata/uldw/redo03.log               NO
         2         ONLINE  /data/oracle/oradata/uldw/redo02.log               NO
         1         ONLINE  /data/oracle/oradata/uldw/redo01.log               NO


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME      NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------- ------------ ---------------
         1          1         23   52428800        512          1 YES ACTIVE                10499875 21-MAR-13           10500288 21-MAR-13
         2          1         22   52428800        512          1 YES ACTIVE                10497966 21-MAR-13           10499875 21-MAR-13
         3          1         24   52428800        512          1 NO  CURRENT               10500288 21-MAR-13         2.8147E+14

然后删除掉当前日志

oracle@wls113:~>rm /data/oracle/oradata/uldw/redo03.log

然后关闭数据库，open的时候出错

SQL> startup
ORACLE instance started.

Total System Global Area 1887350784 bytes
Fixed Size                  2229464 bytes
Variable Size             452987688 bytes
Database Buffers         1426063360 bytes
Redo Buffers                6070272 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 32625
Session ID: 853 Serial number: 5

此时可以用数据库之前的备份来做不完全恢复，但是当前日志的数据会丢失掉。

RMAN> startup mount;
RMAN> restore database;
RMAN> recover database until sequence 24;

因为是不完全恢复，open的时候，需要加上resetlogs

SQL> alter database open resetlogs;

Database altered.

SQL> conn liujie 
Enter password: 
Connected.
SQL> select count(*) from t6;

  COUNT(*)
----------
    604632
//最后一个日志的内容丢失，造成数据丢失。